Conditional value list spanning various tables

[AndyCan]

Good evening,

I'm having difficulty getting my head around the correct way to create a conditional value list that only shows related values once a certain field has been filled.

The Tables are as follows (i have omitted some fields so as to not complicate the example)

SUPPLIERS

pk_supplierID

supplierName

PRODUCTS

pk_productID

fk_supplierID

productName

price

INVOICES

pk_invoiceID

fk_supplierID

dateReceived

INVOICE LINES

pk_lineID

fk_productID

quantity

When entering an invoice into the system, the first thing a user does is select the supplier of the invoice. This is done by a simple value list that references the pk_supplierID and supplierName field of the SUPPLIERS table.

Now comes the interesting part... Within the invoice layout including the suppliers name and date, etc. I have a portal to INVOICE LINES. This is where the user inputs the contents of the invoice, inluding quantity, product ID, etc. This is where i would like to have a drop-down value list for products that is filtered to display only the products that are offered by the supplier that was selected in the parent table INVOICES.

The main problem i'm having is figuring out how to get this to work if the conditional field (INVOICE:fk_supplierID) is not in the same table as the one where i need to use the filtered value list (INVOICE LINES:fk_productID)

If i'm aproaching the problem in the wrong way, please let me know as i'm a bit confused to be honest. Thanks for all and any help.

Regards,

Andy

[elo]

This is a common need and I'll try my best to explain what to do assuming you are looking at the Invoice Lines in a portal on an INVOICES layout.

Basically, the first step is to throw out traditional relational database thinking (e.g. that the Relationships diagram is some sort of ERD)

and (for your own sanity) create some extra table occurences (TO's) in the Manage Database view "Relationships" tab.

(1) Call one, say "ProductsHelper" and make it point to your INVOICES table.

(2) Create another TO and point it to PRODUCTS, call it "SuppliersProducts"

(3) Create a relationship by connecting "ProductsHelper and "SuppliersProducts" by the relevant supplier ID key field

This is the basis point, you now have a way of easily finding all of the PRODUCTS permissible for a given invoice by looking at the "SuppliersProduct" TO through the "ProductsHelper" TO

The next step is to wire up your Value List to make use of these new TOs:

(4) Manage Value Lists

(4a) Use value from field [specify field...] button

(4b) Use your new "SuppliersProducts" TO in both columns for the first/second fields

(4c) use the "include only related values starting from:" set to "ProductsHelper" option

Depending on how your layouts are set up this should get you what you want.

Edited July 26, 2008 by Guest
fixed a typo on TO name

[AndyCan]

Elo, Thanks for your quick reply. I'm going to study it in detail and i'll let you know how i do...

[AndyCan]

Thanks Elo, your technique worked great... Better still, i now understand much better how i should be using the relationship graph in Filemaker.

Thanks again!

Andy.

[elo]

Andy: Glad to hear I was able to help and that it worked.

It took me being beaten over the head with the issue to realize that the relationship graph isn't what it first appears. I spent most of Friday re-doing my solution using the "anchor-buoy" technique so that I could better be able to manage my database going forward.