This is a continuation of the "Magic Value Lists" thread we had back in the summer of 2016. I was helped a great deal by the community, as can be seen here: Populate Value list with a calculated selection?
- I needed a way to define a value-list out of a selection of companies in a table Companies. The selection was particular companies with Supplier ticked in the field Status.
- I used script-trigger upon exit field (Status field), to update a global field in the table Companies and the script filled that field with a list of all the companies with the Supplier status ticked. Like this:Set Variable [$sqlQuery; Value: "SELECT Companies.Companyname FROM Companies WHERE Companies.Status LIKE '%supplier%'"]Set Variable [$SupplierCompaniesList; Value ExcuteSQL ( $sqlQuery ; "" ; "¶")]Set Field [Companies::SupplierCompanies; $SupplierCompaniesList]
- It worked well.
- I defined the Value-list "supplier companies" as Values from field > Specify field > Use Values from first field > ID, [v] Also display values from second field > Companyname > Include only related values starting from Companies, [v] Show only values from second field.
- Then I used the Value-list in a dropdown-field where I needed it, and the dropdown perfectly showed me the supplier companies.
So far so good.
But after selecting one of these supplying companies, the drop down would drop-up again and inside the field is now visible, the ID-number of the supplying company. Hmmmm, that doesn't look good! I solved it by making a $$Variable for the company-name and depicting it across the ID-number over the dropdown. I know, not so elegant.
But now, my client has asked for new features, and guess what... I need that field to be filled with the actual Supplying company name, not it's ID-numer! I wonder how to best solve this.
- I could create a script that every time the user exits the dropdown, goes to the companies layout, to the related supplier company record to look up the name, and then back, and replace the ID-number in the field by the name.
- But am I missing a more elegant way? That the ID-number never ends up in that field in the first place?
Thank you for your help again! :-)