Managing Relationships

Do you have a relationship that you’d like to be based on a selection in a value list but be able to see all of the items if nothing is selected? There’s a very simple solution.

In many cases, I use portal filtering, instead of a specific relationship using the parameters in the portal filter, but when I want the best of both worlds, there is a methodology that will give you that.

For this tip to work, you need to have a value list that will have the values you want to use; often that value list is from a relationship.

Here is the portal filtering calculation:

The field that is being used to filter the portal is called G_Category4RC and it is populated, as radio buttons, with the values for the rate card that I’m working on.

But what if I want to be able to see all the records, from the relationship, and then be able to select one of more categories to view any items for the selected categories?

In order to do that, I have to change a few things. Firstly I need to find the value list that is being used and copy the name of the value list which, in this case, is called Categories4RC. I then need to know the name of the field that uses that value list, which is, as I said earlier, G_Category4RC from the Admin table.

With that information, I go into the database and create a new, calculated, field in the Admin table

What this calculation is saying is if there is a value, or values, selected in the G_Category4RC field, then use those values in the relationship; otherwise use all of the values from the value list.

No values selected so the field shows all values from the value list.

One value selected so the field shows that value.

Multiple values are selected here creating what is known as a multi-key

We then have to modify the original relationship from:


With the relationship above populating the portal.

But this has just caused a problem because the relationship now means that when you select a value in the categories field, the list of categories only shows the value that was selected because it’s using the value list from that relationship.

Don’t worry, it’s an easy fix. Just create a new relationship which is the same as the original one

All that’s left to do is change the G_CategoryRC field to be formatted as checkboxes instead of as radio buttons.

While this might seem complicated, it really isn’t and it is one of those techniques that you will find enormously useful and will use over and over again; I guarantee it.

Leave a Reply

Subscribe To Our FileMaker Tips & Tricks

Join our mailing list to receive the latest FileMaker tips, tricks and video how tos from Michael Rocharde

You have Successfully Subscribed!

Website Apps