My membership application has tables for Members, StudyGroups and a GroupMembers join table containing MembersKey and StudyGroupsKey, plus some other fields.
I read an article on adding form pages, and was happy to see that I could add a page that is join-table-specific.
Is it possible to add the page with only a grid to edit the join records, rather than the typical list and form next to it? That would save a lot of real-estate on the screen.
Now I need to add a “join” form to use in the new page. I will be using the join table in 2 different ways: From the Members form, I need to see a list of the Study Groups the member is in. From the StudyGroups form, I need to see a list of what members are in the group. Even though I’m using the same join table, do I need 2 different forms, one for using on the Members form and one for using on the StudyGroups form?
By the way, what is the button next to the Action field that says “VI…”? It doesn’t seem to do anything, but I see it very often when designing the other forms.
Thanks…
Thanks Jo-Anne!
Regarding #2:
The join form is slightly different depending on whether called from Members or StudyGroups. From Members, it shows StudyGroup name plus other fields from the join table itself. From StudyGroups, it shows Member name plus same other fields from the join table itself. Sounds like 2 forms are needed, but reading about join forms, it seems I can use one which contains both member name and group name, then using a javascript expression in the show-in-list property. Would you have a suggestion on how to not show the member if I’m coming from the member form, and not show the group name if I’m comig from the Study Groups form? Thanks…
Isn’t there a way to customize the forms a bit, such as moving fields around or putting multiple fields on same line? I could swear I saw that, but can’t figure out how to do it. I can’t seem to find a topic on advanced form design.
Hi @RMittelman,
in response to your question regarding advanced form design, here’s where to find the form designer.
- Go to Visual > Forms, and select the form you’d like to customize.
- Now, for your form, click on Pages, and select the form page you’d like to customize.
- Last, click the Open Form Designer button.
The Form Designer lets you move fields around, change their size, put multiple fields on the same line, etc.:
Best
Dom
Hi RMittleman,
Yes you are correct, you can still just use the one form and use the Show If field to show which fields you want on which forms. I have attached an FDF that you can import and view as an example.
DynamicJoinForm.fdf (2.9 MB)
Follow the steps below to see an example that will work for you.
-
Import the FDF attached.
-
Navigate to Tables under the Data menu. You’ll see I have three tables and the GroupJoin table is my join table. It holds the keys and two extra fields color and food.
Important: Take note the table is called Person.
- Navigate to Forms under the Visual menu. You’ll see I have three forms.
Important: Take note the form is called People. I have done this on purpose so you see I am using the Person table in the JavaScript statement.
- Select the GroupJoins form and navigate to the field records.
-
Select the Color record in the list.
-
Click the Display tab and you’ll see that there is a JavaScript statement in the Show If field.
=(five.stack.Person !== undefined)
I have provided a link for more documentation on five.stack. The stack property is used on the Five object and is used at the table-level. That’s why I am using the Data Source ID for the table (Person) here rather than the Form ID (People).
-
Return to the field list by clicking the Back button and select the Name field.
-
Click the Display tab and you’ll see I have a JavaScript statement in the Show If field.
Once again here I am passing in the DataSource ID for the table.
=(five.stack.GymGroups !== undefined)
-
Run the app.
-
Select the Jo record in the People view and click the Groups tab.
-
Click the Add Groups button and you will see only the Color field is shown here not the Food field.
-
Select Gym Groups in the menu, select the Eagles record and click the People tab.
-
Click the Add People button and you will see only the Food field is shown here not the Color field.
I did discover through this process that the Show If conditions are showing on a list, so you will need to leave these out of your list otherwise both will be shown. I have logged this with the QA team to be fixed.
You could also use five.form.actionID (This is the action ID for the form) instead of five.stack.datasourceID this would allow you to change the behavior of a different set of forms that all use the same table.
Thanks Jo-Anne. Typically how long does it take to get a change through to completion? I ask because the list is exactly where I want this functionality. If you’re viewing the Study group form, you want to see a list of members in the group. If viewing the Members form you want to see a list of StudyGroups they belong to.
I guess temporarily I could define 2 different forms.
Thanks…
Ron Mittelman
Hi Ron,
Yes if you could define two forms it would get you through what you need for now.
In regards to turn around of software issues, it all depends on the complexity and importance of the issue. Customers on paid plans have a higher level of support but all issues are reviewed according to severity.
Thanks for your help so far, Jo-Anne.
I am trying to add a join form, and I can’t seem to get it to work. I created the form, then followed instructions to add it to my Members area. But it is not really working, and I am not sure why. All I see when I choose a Member then go to the Member-Groups page is 2 checkboxes/switches for my 2 groups. If I check one ON, when I save the record I get an error message saying required field not set.
Update:
I set several fields from the join table to show in the list. But there is no list, just a switch for each of the 2 StudyGroups that I had created. I guess this is fine so far, because the Member has no corresponding records in the Join table yet. All of the required fields in the join table have a default of false, as they are boolean fields. But the default doesn’t seem to take, as the error message says it’s not supplied.
Update redux:
I switched off the required flag for most fields in the join table. Now I don’t get the error. Still no list shows up. I revisited the definition of the second page of the Members form, and noticed it had no fields at all. So I started adding fields. But it only allows me to add fields from the Members table, not the Join table. Plus, any fields that were already defined on the General page of the form cannot be used again on the new page. All of the fields from the join table ARE defined on the join form itself, and all set to include in list. What I hope to see is a list with Member name, plus all fields from the join table.
BrandeisConejo-20240312-2332533451718.fdf (3.5 MB)
Hi Ron,
Thanks for the fdf, it helps a lot!
For the first issue of required fields, the Required switch at the table-level will always take precedence, even if the field is not on the form, Five will still take the configurations from the database level.
If you have a field marked Required like in the image below, this will take precedence over the Default Value field on the table form and a non-required field at the form-level.
So even if it is not on the form, you will receive an error, shown in the image below. You would need to make the fields non-required to remove this error.
With your members form, I think you have it all set up correctly except for one thing, instead of the page type being 'Join" make it a ‘List’ this way you’ll get the list and the fields you are after. Once you change the page type, you will need to add the Action again, which is the same as you are currently using MemberGroups form.
Now when you run your app again, any fields that are included in the list on the MemberGroups form will be in the list and the fields will be available on the form when you click the Add Groups button.
You can use the page type ‘List’ for a form. If you use ‘Join’ you will only get switches. I hope this helps your situation.
Thanks Jo-Anne, you’ve been very helpful. I’ve removed the read-only conditions and now I don’t get those errors. Changing the page type to “List” gave me just what I wanted.
The screen shot below is almost perfect. I changed all of the headings to fit better.
Couple of questions:
Why can’t I permanently make the “Paid” column wider? I need to adjust it each time to see the full date. I tried changing the Display settings for desktop to much smaller for most of the columns and quite larger for the Paid column, but that didn’t work.
I put in some help text for columns 2-6 showing what those choices meant, but I can’t figure out how to show the help.
Why don’t the boolean columns show switches instead of “true”? Also, why don’t the ones that aren’t set to true show “false”?
Update:
I added a page to my StudyGroups form, made it a list and added the GroupMembers form to it. For a moment, I thought you were mistaken about needing 2 “join” forms, because it showed what I needed on both. Clicking on a member in the Members form and selecting the Groups page, it showed all the study groups for the member, and similarly, clicking on a group in the StudyGroups form and selecting the Members page, it showed the members in that group.
Unfortunately, if I try to add a member to the study group, it gives me an error, saying MemberKey required. The member does not get added to the group. Conversely, if I try adding the new group from the Members form, it works fine. So does this mean I DO need 2 different Join forms after all? I don’t want to remove the Required setting for any of the key fields, right? Here is my latest application:
BrandeisConejo-20240313-2257015088729.fdf (3.6 MB)
That’s great Ron, I’m glad it’s working for you now!
We have a weekly meeting to discuss features that can be implemented in the future, I will add the following for our next discussion:
- The ability to set the width of fields on a list.
- Boolean columns show switches in the list rather than true and false
If you hover your mouse on the field that you added the help you will get a View Help button, shown in the image below.
Click this View Help button and your help for the field will be displayed, shown below.
On your Group Members form you have MemberKey in the Dependent field. What this does is it makes the StudyGroupKey dependent on the MemberKey so when the member changes, the StudyGroupKey will clear itself. All you need to do is delete MemberKey out of the Dependent field on the GroupMembers form and your app will work correctly again. The reason why this caused an issue is Five automatically selected the StudyGroupKey behind the scenes and since you had this dependent on the MemberKey, once you selected a member it inadvertently cleared StudyGroupKey!
I hope all that helps Ron. I’m impressed with your app!
Thanks Jo-Anne, that worked perfectly.
I notice that I can add the same member to a study group more than once. How would I prevent that?
That’s great Ron! All you need to do to prevent the same member being added to a study group twice is on your GroupMembers form change the display type to _LookupUnique. Once a member has been saved to that particular group they will no longer be in the lookup so you can’t select them.
I just tested it out in your app and it will work perfectly for you!
Thanks Jo-Anne, that worked perfectly! I also made that change for the Group field to prevent adding the same group to a member. Otherwise, I could still add the group again.
Perfect, glad its working how you want Ron!
