How do you leverage the ability of FileMaker to work with multi-key fields?
Is this a common practice for you? Why or why not?
I use multi-key fields for populating my valuelists based on a "systemcode" table.
During Startup I have an ini script that fills in the fields.
Below you find an example of my script. This script runs as a subscript in my "ini.Startup" script.
By changing the variable $context I can select which "zzzSYSids_NameYourField" must be updated and when. These fields are global fields by the way. For example when adding a Communicationtype record to the systemcode table, only the zzzSYSids_CommunicationType field gets updated after confirming the new communication type. I'll probably set the variable $context as "communication" and will ad an if step to the script below.
If $context = "communication"
Set field zzzSYSids_CommunicationType (Execute SQL)
No need to update the other fields.
This is a screenshot of the subscript
This is an example of an SQL Query used for feeding the set fields script step.
~query = "SELECT ~id FROM ~table WHERE ~type = ? " ;
~sql = Substitute ( ~query ;
[ "~id" ; CF._SQL.FieldName ( SYS~::__id ) ];
[ "~table" ; CF._SQL.TOName ( SYS~::__id ) ];
[ "~type" ; CF._SQL.FieldName ( SYS~::Type ) ] ) ;
~result = ExecuteSQL ( ~sql ; "" ; "" ; zzkAPPType_CommunicationType )
This is a screenshot of my TO's. The valuelists are populated from these TO's (choose only related values from HOME)
This is an example of my systemcode table.
Hope this helps. Others may have better ideas or implementations. This works well for me.
I usually use multi-keys in two scenarios:
1) During scripting for temporary joins (usually from a global field).
2) For display of selected records in a dynamic display (again, usually in a global field).
Those are about the only times I use them. They're extremely handy in certain circumstances, but I prefer not to use them for permanent storage (join tables are my preference for many-to-many relationships).
I do the same as Mike_Mitchell:
I gather IDs in a global field (e.g.: a user marks multiple records as “selected“),
and I stopped using them for n:m relationships (for n:m I use join tables).
Multi-keys (lists of matching values in one field separated by returns) are quicker, easier to set up than join tables, but not as flexible. They are easily managed in many contexts with a value list and a check box format since check box selections build a return separated list. They can also be an easy way to start with a pasted list of values and show matching records in a portal or list view (GTRR)-with no need to parse values into separate records or find request entries.
But I can pretty much do all but that paste with a join table plus gain display/reporting options not really available with a multi-key field. (That includes check box formats.)
On the other hand, if I set up a multi-key and later realize that I need a join table, it’s a pretty simple script to move the data into individual join table records.
Retrieving data ...