How do you show only certain rows from reference field?

[ddsgadget]

So our situation is that we have a table Contacts. There is a field in that table called Message, which is a Reference field to a Message table. In the Message table we have a column called Active (boolean), which means the message is active. Now we are in the Contacts table and we want to choose a Message, we get a drop down of all the Messages. However, we want to restrict the drop down to show only Messages that where Active is true. How do we do that? I don’t see any choice in the Reference field setting to restrict based on a certain value from the reference table. Thanks.

[natalie-grist]

Hi there!

You can restrict access using Access Rules. I created an example for you at the link below:

https://public.getgrist.com/wRJbEVjZMqQ7/Community-1333

I have a table of messages, most where active is true but Example 1 is false. In the Contacts table, I have a reference column that points to the Messages table.

We can use access rules to restrict access to those Messages where Active is false.

Add Table Rules for the Messages table. Then, add the following conditions and allow all permissions;

1. user.Access == OWNER
2. user.Access != OWNER and rec.Active == True

Add a final condition for Everyone Else and deny all permissions.

We want to make sure that OWNER always has access to those Messages where Active is false.

Our next condition grants all permissions to users who do not have OWNER access when the value in the Active column is True.

Last, we deny all permissions for everyone else. This means that when the value in the Active column is false, all permissions are denied.

Keep in mind that the second permission grants all permissions in the example, meaning that non-owner users can read, update, create and delete records where Active is true. You may prefer to only grant read permissions and deny others depending on what overall access users should have to these records.

Be sure to save your changes.

Now, when a non-owner user accesses the Messages table, they can only see records where Active is true. Because of this, the reference column ‘Message’ in the Contacts table only shows records where Active is true.

Thanks,
Natalie

[ddsgadget]

The problem with this approach is that if the Owner is using the tables, they still see all the choices in the Contacts table. We need to so that anyone who is using the Contacts table, will only see the Messages where active==true. And then if we set the settings for everyone, the owner can’t see the non-active messages in the Message table. The result we need is that the owner (or whatever user has rights) can see All the messages in the Messages table, whether active or not, but when you go to the Contacts table to choose a Message, the drop down is only for the messages where active == true. The set up above, doesn’t accomplish that, because whoever only sees the active==true messages in the Contacts table, also can’t see any of the other messages in the Message table. But, then how is anyone supposed to be able to set a message to active?

[alexmojaki]

You can’t customise/filter the dropdowns for reference/choice[list] fields, but there are several requests of this nature and it’s something we’d like to do. In the meantime, here are some ideas:

1. Add a hidden formula column to Messages that adds [INACTIVE] to the label for an inactive message and make the reference column in Contacts show that formula column, so that it’s easier to avoid choosing an inactive message from the dropdown.
2. Add some conditional formatting to the reference column to highlight when the referenced message is inactive.
3. Use access rules to prevent setting the reference column to an inactive message. This would not affect the dropdown or which data is visible anywhere, but it would prevent invalid updates. It could apply equally to all users, even owners. Doing this requires an access formula using newRec - see the Checking new values section.

[alexmojaki]

Another idea:

4. Similar to (1), make the reference column show a formula column, but instead of adding [INACTIVE] to the label, just return an empty string for inactive messages. This actually will filter out all inactive messages from the dropdown, because they’ll be blank. The downside is that the column won’t be able to show you labels of inactive messages, so you’ll need to add another column to the Contacts table to show the original label of the referenced message.

[Jose_Luis_Moya]

I understand this topic remains the same? To me, it’s also pretty indispensable and required to be able to relate only certain records of a relation with conditions.

[Sylvain_Page]

same for me …