Jump to content

Complex conditional value lists


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

Recommended Posts

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 4381 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.