James wanted to know:
How do I add a look-up field on a form that references records from the same form (i.e., in the same table).E.g., I have a form called Case and I wish to relate a specific Case to another Case in the same table so that I know they are related.Another example is that I have a People form and I wish to show that a person is related to another Person with a parent/child relationship.
So, here we go ……
Response:
- We cannot use Lookup-field to reference data on the same form.
There is a workaround, and we will be using that instead.
Working DEMO here.
THE FORM
- Instead of a “Lookup field”, we will be using s a regular “Drop down field”
- Leave the default list-options as they are, we will not be using them
- Instead, we will be adding the “self-lookup-values” at run-time
THE CODE
- Using the “Aggregate Records” function, we will collect distinct values into a “list-variable”
- We now add these to the drop-down field
Hope this clarifies. Do let me know if you require additional assistance.