New User Design Questions

I’m a new user who is trying to learn Five by converting a Microsoft Access application to Five. I have several questions, and am not sure if I should combine them in a single post or have a separate post for each question.

I have a club application. There are 2 main tables, Members and Groups.

The Groups table contains fields for day of week, and which weeks it meets each month. Assuming a month can have 5 weeks, I have a field called Weeks which could contain the values 1, 2, 4, 8 and 16 (or any combination thereof), which allows me to choose a combination of weeks. If group meets 2nd and 3rd weeks, the field value would be 6, the total of 2 & 4. This requires ANDing the value to convert it to something meaningful. If value is 17, this indicates that weeks 1 and 5 are included, since both 1 and 16 can be ANDed with 17 and return a non-zero value

There must be a better way to do this in Five. Should I use separate boolean fields for weeks 1…5 instead? Just looking for best practice here.

Thanks for any help

MembersWithDB.fdf (2.9 MB)
MembersApp.fdf (2.9 MB)

Hi RMittelman

The way you are choosing to do it is the efficient way for storage in the database. I would do how you are suggesting.

I have attached two FDFs, both have three tables Member, MemberGroup and a join table of MemberGroupJoin.

The MembersWithDB.fdf shows you how every field on the Member Groups form is attached to the database, no code is required, however it can’t be stored in the database as you are suggesting as all the Week fields are attached to the database. And you were correct in making the fields booleans so they could just be turned on.

I think you have this method under control, but I have attached the FDF so you can compare the two.

The MembersApp.fdf shows how you can show the boolean fields on a form, but, store the data as you want in the database.

Please follow the following steps:

  1. Import and run the MembersApp.fdf.

  2. Select Members Group in the menu.

  3. Either edit one of the records there or add a new one and click some of the week switches on.

  4. Click the Save button in the form app bar. (You will see here the storage that you are after is entered into the Weeks field.)

The following steps will show how you can do this in Five.

  1. Go back into Five.

  2. Navigate to the Forms view under the Visual menu and select the Member Groups form.

  3. Navigate to the field records by clicking the Pages tab, selecting the General record and clicking the fields tabs. (Here you will see there is a Weeks field, if you select it you will see it is attached to the database, you can tell this by the value in the Fields field, this is selecting a field from the table in the database.)

  1. Click the back button and select the Week 1 record. (You will see here this field is not attached the database as there is no field selected in the Field field. So whatever data is entered into this field will not be stored in the database. All the other Week fields are set up the same. If you go to the table view you will see these fields do not exist in the table.)

Two functions are involved to do this.

  1. Navigate to the Functions view under the Logic menu. These two functions are used.

  2. Click in the Code field to open the editor.

In these functions you will see how the DisplayWeeks function turns on or off the Week switches automatically using bitwise operations using the Weeks field from the database and the opposite in the SaveWeeks function.

These functions are attached to events on the Member Groups form.

  1. Navigate back to the Member Groups form and click the Events tab.

The DisplayWeeks function will execute on show, on editing and on canceling the form record.

The SaveWeeks function will execute on completion of the form.

On events are client-side, Do events are server-side.
Clicking the VIEW button will take you straight to the code in our large code editor.

One tip I can give you is you probably do not want to display the Weeks field on the your form as this is just for your storage in the database. To hide this field on your form follow the steps below.

  1. Navigate to the field records again on the Members Group form.

  2. Select the Weeks field.

  3. Click the Display tab.

  4. In the Show If field type false. (Must be all lower case as the If fields take a JavaScript statement.) Now the field will be hidden on the form and the data is being stored how you want.

There is a third option by writing a field plugin.

I hope this helps you achieve your requirements.

Wow, Jo-Anne, what a complete reply! Thanks so much! And thanks for the examples.

If I understand correctly, the no-code version simply has 5 boolean fields in the table, and 5 on the form, right?

The second version stores the week values in a single number, similar to how my Access application works, and the functions encode/decode the number back and forth to the 5 visual controls on the form, right?

I think, since there will not be thousands of records, the no-code version would make more sense, unless I’m missing something. I may also need to have the values on reports, so more code running if I need to have several records, each needing the calculation to display the logical value for the week.

Not only that, I have another field that has 5 choices. A member can be part of a group, so they have an entry in the join table. There are 5 positions in the group: Leader, Coordinator, CoLeader, Assistant and Sunshine (sends out greeting cards, etc.), and each member can have 0 or 1 or multiple positions. So that’s a lot of code running.

Please correct me if my assumptions are incorrect.

I will drill down on your examples and see what’s going on with them.

Thanks again…

Hi RMittelman,

Yes, the no-code version has 5 boolean fields in the table, and 5 on the form and all values are saved in the database.

Yes, that is exactly how the second version works and I agree if you are not going to have thousands of records in the database the no-code would be fine, your values would all be stored in separate fields rather than calculated and held in one field.

For your reports, it would be easier to have separate fields as you would not need to write code everytime you want to pull out the values.

Understanding your scenario more five fields is probably easier as there are only five opitons and you won’t have thousands of records. This would make it less code for you in the reports.