Subsets of Value lists: Broken functionality?

[Sedawk]

I believe some of the functionality of creating value lists in Filemaker is broken when you want to display values from a second field for a relational database when using Value list subsets created by a case statement.  Let me explain.

I have a relational database that contains a list of Stakeholders in a table.  Some of these Stakeholders are considered to be suppliers (consultants, contractors, etc..).  I have a field called “isSupplier” – and is set to either “Yes” or “No”.

I can easily create a list of just supplier names using this case statement for a calculated field called “SupplierList”

Case ( IsSupplier = "Yes" ; Name)

However, when creating lists in a pop-up menu I want the user to select the record ID of the Suppliers – not the name.  Therefore, I switch the case statement to:

Case ( IsSupplier = "Yes" ; pkStakeholderID )

If I simply use “SupplierList” the value list field generates the proper subset list of suppliers (shown as stakeholderIDs).

Here is where it all breaks:  I click “Also display values from Second Field” and select the name field – so the names are shown to the user in the pop up menu - not the meaningless record IDs.  The value list no long filters and now shows ALL the records in the supplier table, not just records marked as a supplier.  Very frustrating.   I tested this with a simple database just to make sure and this is the case. If you would like to see it, here it is: Test Database (Even and Odds)

Does anyone know a way around this?  I don’t want to move the suppliers into another table because it breaks so many other use task tracking items in the database that apply to all Stakeholders.

Any help would be appreciated.

Thanks!

Edited August 12, 2016 by Sedawk

[bcooney]

http://www.soliantconsulting.com/blog/2013/03/context-independent-value-list-active-records

 

[rwoods]

Hi Sedawk

The issue comes because you are asking FM to produce a value list across all your records. You are telling FM that the 'Field1' value is blank for certain records, if they are not a supplier. However, those records that give an empty result are still there, and they still have a value in Field2, so they will still populate the value list. There is no broken functionality, rather is an incorrect way of trying to do what you are trying to do.

Instead, how about creating a Self-relation, from your primary table back to another occurrence of itself, but only when IsSupplier is true. You then, create the value list based on the SupplierList field that is in that related table, as that will only return the records you are interested in.

I didn't read the Soliant link that bcooney just posted, so apologies if that says the same thing!