I am new to Filemaker and have been working on my first database for my own use. It has become quite complex (for me at least) with 25 tables and numerous relationships. (I am confident it is not very efficient).
I have been pulling my hair out today trying to get a calculated field to display correctly in a layout. What is most frustrating is that I am implementing something that is working perfectly in another table/field/layout and I just replicated it. This is the relevant table:
-Org ID is the primary key in an Organization table with basic details about an organization. In this Organization Values table, it is a global variable $$Current_Organization so that each record is related to an organization being worked on.
-Value ID is the primary key for a Values table that holds an identifier and a name for various values (text values such as "Integrity"). It is related to that table.
-Value ID gets populated in this table by selecting values for an Organization from a Values value list which is calculated from the Values table.
-Value Names looks up from the Value table the names associated with the Value ID's stored for the Organization.
-Value List calculates a comma delimited list of the Value Names stored for an Organization as
Substitute ( List ( Value Names ) ; "¶" ; "," )
-PROBLEM: I have added the Value List field to a list layout based on the Organization table. Only one Value Name appears. When I track the value of "Value List" using Data Viewer, it is the most recent Value Name added and then reverts to the first Value Name.
-When I use that same calculation as placeholder text for an empty global field in the same layout, it produces the correct result, but I want the field to be searchable (and placeholder text does not seem to be searchable). So I know the calculation is working on Value Names.
-In a portal list of Value Names for the organization, all the relevant names appear in the list.
I have tried creating a mirror table of Organization Values and putting the Value List calculation in there, but the same thing happens.
As noted, I have the same structure for a different set of tables and it works fine.
I welcome any guidance. Many thanks.