Jump to content

Subsets of Value lists: Broken functionality?


This topic is 2798 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.


Edited by Sedawk
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 2798 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.