AnsweredAssumed Answered

Tricky Value List Issue

Question asked by dale_allyn on Aug 18, 2017
Latest reply on Aug 20, 2017 by dale_allyn

I have a value list issue that is tricky (for me, as I've not faced it before).


Field 1 is "Comments" (English language), Field 2 is to be auto-populated with the Arabic equivalent from the "Comments" table. A drop down menu on the English field offers the select options in English.


Problem 1: Due to Arabic conventions, the user must select one of three options which in English are exactly the same. Value list is populated from a table with English and Arabic versions for each record (about 16 records from which to select).


For example:

Surface is nacreous (for one item) => Arabic translation for one item

Surface is nacreous (for two items) => Arabic translation for two items (expressed differently)

Surface is nacreous (for more than two items) => Arabic translations for more than two items (expressed differently again).


The Arabic field is populated by lookup so that the English can be edited a bit at times without the Arabic field being cleared upon edit. If it's an auto-entered calculation, editing the English field will break the Arabic field (blank).


The client says: "Just list them as three choices and we know which to select."


The English field value goes into the report output. The Arabic value goes into another page of the report.


In a simple value list: Filemaker removes all of the duplicates of "Surface is nacreous" and displays only one of the value list records. This breaks the Arabic (of course).


Using a two fields in the VL where record ID is also included at least shows all English VL choices, such as:

Surface is nacreous  1

Surface is nacreous  2

Surface is nacreous  3

Surface is non-nacreous  4

Surface is non-nacreous  5

Surface is non-nacreous  6


with the numerical value being the record ID (serial number).


Presenting this value list provides all values, but if one selects the 2nd or 3rd value of a "set", the Arabic field is populated only with the first Arabic entry in the VL table. As a kludge, I can add content to each value in the list, for example:


Surface is non-nacreous (1)  4

Surface is non-nacreous (2)  5

Surface is non-nacreous (3+)  6


Selecting the second value above will populate the English field with "Surface is non-nacreous (2)"


Again, the 4, 5, 6 above are the record ID values and don't populate the English field, they're just collateral clutter. In this situation, the Arabic populates correctly from the translation VL. But the user must then delete the added elements such as "(2)" after the desired English entry.


Can the community suggest a cleaner (or even elegant) solution here? Just tonight I added the parenthesis to the item (I was experimenting with just adding a number to get it to show up in the list), so now I'm thinking I should use pattern count and trim, or similar.


Formulating this post is sort of shedding light for me as to a solution, but thoughts, suggestions, feedback, are greatly appreciated.