How to filter in dropdown lists in filemaker pro 15

Question

I've been searching a lot for this. I do not want a drill down filter where I start with 1 dropdownand narrow down in the other based on one choice. No, I want to filter on 1 specific database field.

An example:

I have 1 table Companies. In that table I have a FK to CompanyType (ex. Supplier, Customer, ...)

In my invoices Form I want to have a dropdown from my Companies table with only the Customers in. (so, for example, dropdown where CustomerTypeID = 1)

In my Expenses Form I want to have a dropdown from my Companies table with only the Suppliers in. (so, for example, dropdown where CustomerTypeID = 2)

Thoughts? Many thx!

Answer 1

In my invoices Form I want to have a dropdown from my Companies table with only the Customers in. (so, for example, dropdown where CustomerTypeID = 1)

1. In your Invoices table, define an unstored calculation field cConstant1 (result is Number) and enter:

   1
   

   as the formula.

2. Define a relationship between Invoices and a new occurrence of the Companies table (let's name it Customers) as:

   Invoices::cConstant1 = Customers::CustomerTypeID
   

3. Define your value list to use values from Customers::CompanyID, include only related values starting from Invoices.

---

Alternatively, define a calculation field named cCustomerID in the Companies table =

If ( CustomerTypeID = 1 ; CompanyID )

and define your value list to use this field.