Complex conditional value lists

[devices99]

I'm having real trouble getting my head around this in FileMaker, yet I can mock it up in 5 minutes in Access with SQL queries, and changing the row source of a combo box

First table BOOKING = BookingID, BookingDate, BookingTitle, CustomerID, CategoryGroup

Second table CATEGORY = CategoryID, CatDescription, CatInfo, CategoryGroup

Third intermediate table (Portal) BOOKINGCATEGORY = BookingID, CategoryID (with relations to show the description and info in the portal row)

[bOOKINGCATEGORY is related to BOOKING via BookingID and CATEGORY via CategoryID]

I need a value list dropdown in the Portal for the CategoryID which only shows the Category records which match the CategoryGroup in the master Booking table.

There are only a small set of CategoryGroups, these are, for example: 1, 2, 3, 4 and All.

So to further complicate things, I need the value list in the portal also to show, for a master record with CategoryGroup=1, all Categories with CategoryGroup=1 and CategoryGroup=All records.

So for example Record 1:

BookingID=1, CategoryGroup=1

Portal: CategoryID Value list to show all Category records with CategoryGroup=1 and =All, (but not CategoryGroup 2, 3 or 4).

I can't seem to find an elegant way of doing this without creating a temp table first for the list.

Any tips gratefully received!

(FileMaker Pro 11v2 at present)

[comment]

In addition to your core relationships:

Booking -< BookingCategory >-Category

define an auxiliary relationship (using a new occurrence of the Category table) as:

Booking::CategoryGroup = Category 2::CategoryGroup

Set your value list to show values from Category 2::CategoryID, show only related values starting from Booking.

[devices99]

Terrific Comment, thanks for the speedy reply, it works! Now I just need to tackle the "all" issue!

(Finding it hard to change my logic from SQL/queries to the way FileMaker uses relationships and value lists.)

[comment]

Try defining a calculation field in the Booking table (result is Text) =

List ( CategoryGroup ; "All" )

Use this field for the auxiliary relationship instead of Booking::CategoryGroup.

[devices99]

Try defining a calculation field in the Booking table (result is Text) =

List ( CategoryGroup ; "All" )

Use this field for the auxiliary relationship instead of Booking::CategoryGroup.

Thanks again Comment. Another great tip, never used the List function before. Had a slight issue as I'd made one of the group fields a numeric, but switched to text and now it's working my small test system, will now add some proper data and see how we go!