Hello,
I’m new here (1 hour playing after listening to the 1 hour demo) and can’t find out how to add a field in the list of a page form (with many-to-many relationship table). I have the first name but not the last name displayed.
In the related form, both first and last name have the option “Include in list”.
Thank you.
Hi f.i.Sciences,
Follow the steps below to include your Last Name field in the list.
Because you are working with a many-to-many relationship, you may find this link helpful, there is also a sample StudyGroups.fdf that you can download and import and is using a many-to-many relationship.
Select Visual in the menu.
Select Forms in the sub-menu. (All your form records will be listed here, I am not sure if you originally added your forms through the Form Wizard or if you added them here, so just letting you know where your form records are after saving them through the Form Wizard.)
Select your form record in the list.
Click the Pages tab.
I hope this helps your development in Five!
Hello Jo-Anne,
Thank you, but this is not the issue.
The relation many-2-many work perfectly.
I’m using a form who use a ForeignKey (UtilisateurRecuDeKey) with “_Lookup” type and based on the form “Utilisateurs”. The form “Utilisateurs” has the two fields name with the option “Include in list” enabled. But only the first name is displayed in the list for the “Tips” form.
Thank you.
Hi f.iSciences,
That’s good the many-to-many relationship works. Just to clarify, you have a First Name field and a Last Name field and both fields are included in the list? And are you meaning in the lookup list (dropdown) for the Tips form that only the first name is being displayed? If this is the problem I know how to help you, just getting some more clarification.
It’s YES to both questions.
Now I fully understand your situation!
When you create a _Lookup type, Five will take the first logical field in the referenced form and display this in your lookup. For you it is taking the First Name field.
Here are my members, inclusive of first name and last name.
In my lookup only the first name is listed when I come to my Membership form and go to select a member.
To have the First Name and the Last Name fields both displayed, you will need to change your display type to a _LookupQuery and write a small amount of SQL to concat these fields together. When you are writing your query please ensure to use the values in the ID fields on the Table form, for example, Data Source ID and Field ID.
Select Data in the menu.
Select Queries in the sub-menu.
Click the Add Item button.
Type an ID in the Data Source ID field.
Click in the Query field to open Five’s Query Builder.
Note: When you add a _LookupQuery, at a minimum you need a primary key field and a value field, Five will hide the primary key field in your application.
SELECT
MemberKey,
CONCAT(FirstName, ’ ’ , LastName) AS Name
FROM
Member
Tip: You can click the Run button here to check your results.
Navigate to your form record in the Forms view.
Navigate to your field record that you want the First Name and Last Name to be displayed. (Same steps as previous for the Include in List switch)
Click the lookup icon in the Display Type field and select _LookupQuery.
Click the Cancel button in the Reference Form field.
Click the lookup icon in the Lookup Query field and select the name of your query. (If you only have one, Five will populate it for you.)
Click the Save buttons in the form app bar and run your app again.
Navigate to your form and you will have both names in the dropdown.
Just for your knowledge, the reason we allow more than one field in the list is these will become the fields in the list. So if you look at the above image, the Memberships form has Member and Member No included in the list so they are visible on the left where the list of records will be displayed.
I hope this helps you.
Hello Jo-Anne,
That does it!
Thank you very much,
Sincerely
You’re welcome, I’m glad it’s sorted for you now.
