Hello everyone!
I have an issue described in the title.
In a simple scenario, I am able to create a dependent field with a lookup as shown in this first schema. When I create or edit a new Task, the lookup list of the Assistant field is correctly filtered according to the Leader selected.
But, in my real application, both Leader and Assistant are child of Person. In this configuration, I cannot reproduce the same dependant lookup behavior.
No matter how I configure the foreign keys or relationships, the Assistant lookup always displays all assistants, instead of only those linked to the selected Leader.
I suspect that the issue is related to setting the proper foreign keys to the proper tables, but after trying many combinations, I still cannot achieve the expected filtering.
I have attached a simplified sample of my application as an .fdf file containing both implementations in the same project.
Any help to solve this situation would be greatly appreciated.
Thank you!
DependentField-20260207-024716469229882.fdf (3.5 MB)
Hi Jean,
Thank you for raising this question and for sharing the attachment.
Regarding the scenario where Leader and Assistant are children of Person, you can follow the same approach used in your first example.
You may notice that when you are in the Task2 form and attempt to select either Sub Leader or Sub Assistant, the person’s name is not displayed. Instead, the value shown comes from the next field on the referenced form (a text field). This is expected behavior: by default, a lookup displays the first text field of the referenced form.
To ensure this workflow functions smoothly and aligns with your database design, and the users can see the Person’s name, I implemented the following changes:
-
Simplified the database design to match the first example, removing unnecessary dependent relationships.
-
Ensured the Person table is properly connected to both SubAssistant and SubLeader tables.
-
(Optional) Created a dedicated form for the Person table and updated the People menu to reference this form.
-
Created two lookup queries: one for SubLeader and one for SubAssistant.
-
The SubLeaderLookUpQuery used by the SubAssistant field accepts multiple parameters to meet the workflow requirements.
-
When creating a new record, the query returns all records so the full list is visible. Once a SubLeader is selected, the list is filtered accordingly.
-
Created one function to set a variable and another to clear it.
-
Added the function that sets the variable to the Task2s form’s On Show event.
-
Added the function that clears the variable to the AssistantKey field’s On List Select event.
With these changes:
-
The list on the left side of the form displays values correctly.
-
When creating a new record, the AssistantKey field only shows options after a value is selected in LeaderKey.
- In the Task2s form, the LeaderKey field is now driven by the SubLeaderLookUpQuery.
In the Task2s form, the AssistantKey field is now driven by the SubAssistantLookUpQuery, with AssistantKey configured as a dependent field.
These steps are necessary because, in the current database design, when you look up the Leader (PersonKey) field in the Task2 form, the system displays the next available field (such as Dept) rather than performing a lookup against the People form. The same behavior applies to Sub Assistant, with additional complexity due to its dependency.
I’ve attached an updated FDF that includes all of these changes. Please feel free to reach out if you have any questions or would like to review any part of the setup in more detail.
Regards,
Elton S
DependentField_ForJean.fdf (3.6 MB)
Hello Elton, thank you for your detailed reply.
If I understand well, the proposed solution requires removing the Dependent relationship on the SubLeader and SubAssistant tables and replacing it with simple foreign keys.
Unfortunately, I cannot apply this approach. One of the main reasons I am using Dependent Tables is precisely to allow the user to create and edit both the Person data and the role-specific data (SubLeader or SubAssistant) within a single form. If I switch to foreign keys, users would need to manage two separate forms: one for Person and another for the child table, which defeats the purpose of using Dependent Tables in the first place.
An alternative would be to duplicate all Person fields into each child table. However, this would negate the benefit of Dependent Tables as a way to simulate object inheritance. In my real application, the Person table contains 17 fields and is extended by four different child tables, so this approach is not practical or desirable.
From my understanding, Dependent Tables are a powerful feature, and Dependent Fields are also a powerful feature. However, based on this behavior, it seems that they cannot currently be used together to achieve a filtered lookup on dependent child tables.
If this is a known limitation of the platform, I would appreciate confirmation so I can adjust my design accordingly.
Thank you again for your time.
Jean
Hi Jean,
Sorry if my previous response was not aligned with your current DB design.
It seems that we are not handling the result properly when combining with a dependent field.
I will further investigate this scenario and advise our internal team.
Meanwhile, please consider this new application (I have not changed your DB structure), which partially uses the approach I sent on my previous suggestion.
1 - In the Task2s form, the AssistantKey field is now driven by the SubAssistantLookUpQuery, with AssistantKey configured as a dependent field.
Created one function to set a variable and another to clear it.
2 - Added the function that sets the variable to the Task2s form’s On Show event.
3 - Added the function that clears the variable to the AssistantKey field’s On List Select event.
I believe it should work as you expected. Please let me know if otherwise.
Regards,
Elton S
DependentField_ForJean_No_DB_Changes.fdf (3.5 MB)
Hi Elton,
Yes! Great! That’s the expected behavior while keeping the Dependent Tables.
Thank you so much for the clarification and for taking the time to investigate this further.
I’m happy that I don’t have to change my design and that solution allows me to use the combination of Dependent Tables and Dependent Fields.
Best regards, Jean
1 Like
Hello,
I tested the suggested workaround, but the behavior is still not reliable. In the sample application, the list of assistants sometimes does not match the selected LeaderKey.
When I select a Leader, the lookup_query (SubAssistantLookUpQuery) runs multiple times (often three). Sometimes it receives the previous value, and sometimes NULL. The parameter passed to the lookup_query is not synchronized with the actual value of the LeaderKey field.
I also tried using a “virtual field” or a global variable updated through On List Select, but the query still receives a value that does not always match what I see in the debug panel.
Is it possible to fix this so the value received by the lookup_query is always synchronized with the LeaderKey parameter?
Thank you,
Regards,
Jean
Hi Jean,
Thank you for letting me know.
I tried a few times, and I could not simulate this scenario, just for curiosity to ensure I have not missed anything:
Are you using the same app that I gave you, or did you implement it in your application?
Are you able to share a few images of it?
You also mentioned that the query is executed multiple times; this is a known issue of the inspector, which duplicates the result (the query is not being executed twice). I have already notified the development team about it.
Just to let you know, the main issue has already been reported to the development team.
Regards,
Elton S
Hi Elton,
Thank you for pointing out the main problem.
I retested using the application you sent me via this thread.
DependentField_ForJean_No_DB_Changes.fdf
I added the following data:
Add Leader3
Add Assistant4Leader3 (Leader3)
Add Assistant5Leader3 (Leader3)
The first few clicks work correctly. However, at a certain point, when I select a Leader Key, the “No options” option appears for the Leader’s assistant.
Then, subsequent clicks display the wrong assistants for the selected leader as you can see on the following image.
Regards,
Jean
Hi Jean,
Thank you for the further details.
Let’s organise a video call, as I unfortunately couldn’t simulate this behaviour.
It seems to be working fine from my side; I even tested using a Mac and a Windows machine.
Would be able to send an email to elton@five.co with your suitable time from 8 a.m. to 5 p.m. (Brisbane time).
Thank you again.
Regards,
Elton S
Hello Elton, I will contact you later for setting a conference call.
Could the problem be due to network latency?
I am located in Montreal, you might be able to reproduce the issue by testing through a VPN located in Canada (or US East Coast).
I can reproduce it easily but maybe the latency from the server is different from Australia…
Regards,
Jean
Hi Jean,
Thank you for the additional details.
I’ve tested this again using a VPN located in the USA, and everything appears to be working correctly on my end.
What I noticed is that when switching quickly between Leaders and then clicking on the Assistant field, there is a brief delay (about a second) before the drop-down list updates. However, the list does update correctly afterwards. Please see the attached GIF for reference.
Could you please confirm whether the Assistant drop-down updates properly on your side when you switch quickly between Leaders and then click into the Assistant field?
Also, could you let me know:
For your convenience, I have reattached the version of the application I am currently testing.
DependentField_ForJean_No_DB_ChangesV2.fdf (3.5 MB)
Regards,
Elton S
