Value List vs. Lookups

[timthegreek]

Hi

 

I've already posted this question on the FMP 9 forum but didn't get any replies. Apologies if re-posting is against the etiquette but I'm really stumped and getting desperate for some pointers!

 

 

I've read up as much as I can on Conditional Value List Filtering but I'm still stuck on something.

 

My database consists of 2 tables, the first with the following fields: Category, Make, Item & Colour; the 2nd table has the same fields. I use the first table to store my stock details and the 2nd table is where I enter the stock when it comes in. I've set up relationships for the 4 fields between the 2 tables and it works to a point - Category is fine, it lists all the different categories. Make is also fine - it shows only the makes associated with the category chosen in the first step. But when I get to Item it lists all the items associated with the category but doesn't filter out the unassociated items. Ditto with Colour. The first field is set to show all values via a conditional value list and the remaining 3 fields are set to show related values only.

 

I suppose what I'm asking FMP to do is filter 3 value lists but to remember the filtering from the previous request. EG:

 

Category - show all records

Make - show only Make associated with chosen Category

Item - show only Item associated with chosen Category AND Make

Colour - show only Colour associated with Item, Make AND Category

 

Now is this something that can be achieved through conditional value list filtering or should I be using lookup tables. Or am I completely off the mark here and should be looking at a script?!

 

This is the first time I've attempted a project like this so please excuse my lack of knowledge

 

Thanks

 

Tim

[LingoJango]

You need a separate relationship to a different occurrence of the first table for each of those value lists - or some calculated fields.

 

For Item you need the relationship to be Category = Category and Make = Make. For Color you need it to be Category = Category and Make = Make and Item = Item.

 

If you don't want to set up so many TOs, you will need your key fields to be calculated. For the fields in table 1 (the one you look up), you can add calculated fields like

k_CategorySource = Category & ¶ & "-" )

(and the equivalent for each of the other fields).

 

For each of the fields in table 2, you would add

k_Category = Case ( Category ; Category ; "-" )

 

Then you need just one relationship: k_Category = k_CategorySource, k_Make = k_MakeSource, k_Item = k_ItemSource, k_Color = k_ColorSource.

 

To understand the logic you need to understand that every new paragraph is a new value in FileMaker, and that relationships match values rather than field contents.

 

Hope I got that right.

[timthegreek]

Hope I got that right.

 

Hi LingoJango

 

Thanks for the reply. I'm going to have a look at your suggestions and see what I can do.

 

Many thanks

 

Tim

[LingoJango]

It just occurred to me that the calculated fields version does not require a particular sequence: you can start by selecting Color, if you like, followed by Make and Item. This may not be the ideal behavior you have in mind - but it's flexible and if your tab order is right it shouldn't be a problem.

[timthegreek]

You need a separate relationship to a different occurrence of the first table for each of those value lists - or some calculated fields.

 

For Item you need the relationship to be Category = Category and Make = Make. For Color you need it to be Category = Category and Make = Make and Item = Item.

 

Hi LingoJango

 

I've set up extra relationships as you suggested but it's still not filtering as it should - I'm still getting unwanted ITEMs listed after I've selected the CATEGORY.

 

In the relationships screen I've added 2 more sets of graphs so that I now have:

 

Table 1 - Table 2 -- CATEGORY is linked on both tables

Table 1 (2) - Table 2 (2) -- CATEGORY and MAKE are linked on both tables

Table 1 (3) - Table 2 (3) -- CATEGORY, MAKE and ITEM are linked on both tables

 

Any ideas where I could be going wrong?

 

many thanks

 

Tim

[LingoJango]

Your layout is based on table 2. Hence, you need:

 

Table 1 - Table 2 -- CATEGORY is linked on both tables

Table 1 (2) - Table 2 -- CATEGORY and MAKE are linked on both tables

Table 1 (3) - Table 2 -- CATEGORY, MAKE and ITEM are linked on both tables

 

and you might as well name the table 1 occurences something meaningful - like SKUCategory, SKUCategoryMake and SKUCategoryMakeItem.

[timthegreek]

Your layout is based on table 2. Hence, you need:

 

Table 1 - Table 2 -- CATEGORY is linked on both tables

Table 1 (2) - Table 2 -- CATEGORY and MAKE are linked on both tables

Table 1 (3) - Table 2 -- CATEGORY, MAKE and ITEM are linked on both tables

 

Hi LingoJango

 

Works perfectly! Thanks very much smiley-laughing