Trouble with Entering Data into Table

[RMittelman]

I’m having trouble entering data into my StudyGroups table.

The fields where I have defined custom display types seem to work fine. I click the drop-down next to the field name, and it shows a list of items to choose from.

The foreign keys don’t seem to work properly. The Category field is GUID not generated, with a _lookup type. I have a Category index which is a foreign key to the GroupCategories table using dependent index CategoryKey (which is a generated GUID in the GroupCategories table.

When I click the drop-down for that field, I expected to see a list of the available categories from the GroupCategories table to choose from. Instead, it drops down to show “No options”. Is this caused by the field and index having the same name?

Also, all boolean fields with checkboxes on the add record form are off, even though some of them have true in the “default value” field when I defined them.

I would really appreciate any help I can get. Thanks…

[Jo-Anne]

StudyGroups.fdf (2.9 MB)

Hi RMittelman,

I hope I have your scenario correct, I am assuming you are working with a many-to-many relationship with your GroupCategories table as the join table?

If so this is how you would correct your keys and I have also added some additional information for the form-level to ensure this works for you.

Table-Level

1. Add your first table. (For me it was the Category table and it just has a primary key and a Name field.)

2. Add your second table. (For me it was the StudyGroups table and it just has a primary key and a Name field.)

3. Add the join table. (For me it was the GroupCategory table and I added two relationships, one to the Category table and one to the StudyGroups table. So if you were using the Table Wizard it would like the image below.)

4. If you are in the Tables view, your GroupCategory table would have the following (plus its primary key of course):

A CategoryKey field, not generated with a display type of _Lookup.

A StudyGroupsKey field, not generated with a display type of _Lookup.

An CategoryKey index, with a type of Foreign which references the Category table and using the CategoryKey in the Category foreign table. If you click the Index Fields tab you will see the CategoryKey field in the current table is used to link the two tables together.

A StudyGroups index, with a type of Foreign which references the StudyGroups table using the StudyGoupsKey in the StudyGroups foreign table. If you click the Index Fields tab you will see the StudyGroupsKey field in the current table is used to link the two tables together.

So this is the model.

Form-Level

1. Add your first form. (For me it was the Categories form.)

2. Add your second form. (For me it was the StudyGroups form).

3. Add your third table. (For me it was the GroupCategories form. This is using the join table. With this form, turn the Add Menu Item switch off.

4. And ensure both fields are included in the list.

5. Edit the StudyGroups page, cick the Pages tab and add a new page. I called this Categories and gave it a Page List of ‘List’ and the action I reference is the the GroupCategories form (the join form). Note: If you want these to be switches rather than a dropdown choose 'Join" as your page type.

6. Once you have saved this, run the app and it will work like the following.

In-App

1. Add some Categories.

2. Select Study groups in the menu and give your study group a name followed by clicking the Categories tab.

3. Click the Add Categories button and you wil now be able to select the categories.

I have attached the fdf that I used so you can import it and use it as a reference. If I have your scenario incorrect please let me know!

In regards to your boolean fields default values will only apply when creating new records. So if I already had records saved in the system and then add a default value. The default value would not apply to any saved records.

The image below shows my set up in Five.

If I run the app again and select a category record, this default value won’t apply as it is already saved in the system.

However, if I create a new record, the default value will apply.

I hope this helps, let me know if you need more assistance.

[RMittelman]

Hi Jo-Anne. I’m not sure I understand your reply. But from the first statement it seems I didn’t properly explain my problem. I think the relationship is one-to-many, not many-to-many.

The StudyGroups table besides key and name has a Category field. It should be a foreign key pointing to the GroupCategories table. There are various categories to choose from, but each study group can only have 1 category. Conversely, several groups may have a particular category. That seems like a classic one-to-many relationship to me.

If it is designed properly, when adding a new Study Group record (or editing an existing one) I should be presented with a list of possible Categories to choose from, right? Isn’t that one of the main reasons to use a foreign key relationship?

The GroupCategories table has 13 records, so any StudyGroup record should be able to choose between them and pick one for the Category field, right?

If I can’t get this to work properly, I could dump the GroupCategories table entirely and just make a display type list containing the 13 values instead. But it should work using a separate table with foreign key relationship, shouldn’t it?

Regarding the boolean field issue, of course I’m aware that a default value is only a default for new records. Currently I have no records in the StudyGroups table. 1 of the boolean fields has a default of true. When I add a new record, the toggle is off for that field. The field is called Active. I want it to indicate, well, an active Study Group. That way, I never need to actually delete a study group, I just need to mark it inactive. When I add a new study group, it makes sense to default that to true, otherwise why would I be adding the group?

Thanks…

[Jo-Anne]

StudyApp2.fdf (2.9 MB)

Hi RMittelman,

This makes it a lot simpler! I think we just had some confusion there. And yes it is just a foreign key that you need, and you are dealing with just a one-to-many relationship.

Table-Level

1. Add your first table called GroupCategories with just a Name field.

2. Add your second table called StudyGroups. This table has three fields the pk field, the GroupCatergoriesKey (can call this whatever you like such as Category) field and a name field.

3. The GroupCategoriesKey should be required if it is meant to have a value before saving, not generated and a display type of _Lookup.

4. The GroupCategoriesKey (Can call this whatever you like such as Category) should have a Foreign key type, reference the GroupCategories table and have a dependent index of GroupCategoriesKey.

5. The index field on the current table when setting up the index should have an index field of (GroupCategoriesKey, or whatever you called the field in the StudyGroups table.)

So the database design you are after now looks like below (one-to-many relationship)? But may have different field names.

Form-Level

1. Add the Group Categories form first and ensure you have the Name field included in the list.

2. Add the Study Groups form. In your Study Groups form ensure the Group Categories field references the GroupCategories form in the Reference Form field.

Note: If you add these in this order through the form wizard, Five will automatically make reference to the other form as long as at the table-level the foreign relationship was created (which we have done). The form being referenced just needs to be saved first.

In-App

1. Run the app and add some Group Categories.

2. Select Study Groups in the menu and add a new record. Your lookup will work now.

A couple of key things to remember at the form-level:

1. The Name field in the Group Categories form is included in the list.
2. The Group Categories field in the Study Groups form references the GroupCategories form.

Boolean field set to true

I did some further testing on setting a boolean field to true in the Default Value field. You have brought it to our attention that if it is set at the table-level you are correct that it does not work, this is now in the process of being fixed.

If you set the Default Value to be true at the form-level for the Boolean field it will work for you when you add a new record.

When set at the form-level the switch will be on for you automatically when you add a new record.

I hope this helps you, please let me know how you go and once again I added the fdf that I used to create your scenario with your foreign key.

[RMittelman]

Hi Jo-Anne,

Once again, thanks for the detailed answer.

I got the default value to work, once I saw your comment about setting the default value in the form field. I must tell you, this is non-intuitive. In all my database experience, this has been something you set at the table level. I’ve never had to set this at the form level. In fact, the field definition in the table does have a default value item. It’s confusing to be told that the default has to be set in the form field also.

I’m still struggling with the foreign key. It seems like I did everything you mentioned, but the Category drop-down still comes up empty when I click on that field. I really need to get this to work. There are 2 different fields in this table which are foreign keys to “parent” tables. Not to mention other tables/forms also have this requirement. In addition to the GroupCategories table I have a Portfolios table. Not sure why they call it that, but basically it’s a grouping mechanism so different board officers can have responsibility for different study groups. The Portfolios table consists of 3 records, with names “A”, “B” and “C”. In the StudyGroups table, there is a field called Portfolio which is a foreign key to the Portfolios table. Both of these fields, when adding/editing a record, should provide a drop-down to their respective “parent” tables and show the choices from that table.

I’ve exported my project, if that would be helpful. Perhaps you could see what I did wrong. If you do this, please let me know what to do at this end, rather than simply fixing it. Thanks…

BrandeisConejo-20240304-212815297756.fdf (3.2 MB)

[RMittelman]

Hi Jo-Anne,

Actually, I have figured out this issue. I had neglected to set the “Reference Form” in the definition of the StudyGroups form Category field. Once I did this, the list of categories show up when I run the application and add a study group. Ditto for the Portfolio field.
Once again, non-intuitive to set this on the form definition since it seems more like a data definition thing.

I have a few more questions about setting up forms. It’s not clear whether it would be better to start a new topic or continue with this one.

Thanks again…

[Jo-Anne]

Hi RMittelman,

I’m glad you got it sorted! Yes when you do everything on the form it all needs to be performed manually. If you use the form wizard it will make the reference for you automatically if you already have the form saved in Five first that needs to be referenced, like in the image below.

I’m happy for you to start a new thread, just because it may make it easier for another user to find answers on the forum too.

[RMittelman]

Thanks Jo-Anne,

I will start a new thread for my new questions. But, one final thing on this thread:

I was trying to see where I change the fields that appear in the list. It’s so easy on the wizard. I guess I need to go into every field and toggle the “show in list” instead, right?

[Jo-Anne]

Hi RMittelman,

We have three Lists in Five.

1. Lookup. Five will take the first logical field marked as in the list in the referenced form and display it in the dropdown. When I say logical something like a text field rather than an integer field.

2. The list of records that is displayed on the main view. Any fields included in the list will be in the list here.

3. And then a List page Type. Any fields included in the list will be in the list here.

And you’re correct, if you are not in the form wizard, you need to navigate to your form fields and turn the Include In List switch on for any fields you want in the list.

[Jo-Anne]

Hi RMittelman,

With our upgrade you will be able to set a default value of true on a boolean data type at the table-level.

As mentioned before, all your applications will be upgraded into the new version, but just as a precaution we always advise to backup.