I want to format based on a column in a related record

[Patrick_Paul]

I have mentor records that are related to trainee records. In the mentor records, there is a “backwards” reference called [Related Trainees] that contains a list of all the related trainee records to that mentor. I want to format the mentor based on if the column [Current Status] in any [Related Trainees] is equal to “Active Approved”… What would the code look like for that? I’m stumped as to where to start.

Would something like IF(ANY([related trainees].[Current Status])=“Active Approved”, True) work at all? I hesitate to think so…

[Steve (Steve Coile)]

Try:

IN("Active Approved", [Related Tranees][Current Status])

See also:

IN()

List Dereference

[Patrick_Paul]

This seems like it should work but doesn’t?! Here is what I have for a screenshot:

When I click to test the expression, I get many records with “Y” results, but nothing gets formatted in the app after saving and resyncing, etc.

Is this a glitch with the format rule?

[Steve (Steve Coile)]

You’ve saved our changes and synced the app?

Do you have any other format rules for the same columns?

[Patrick_Paul]

Yes and yes. I have about 10 format rules for mentor records, most of which act on the mentor name. Basically to denote different statuses. Usually only up to 3 may apply to a given record at one time.

[Steve (Steve Coile)]

Could this apply?

See also:

Format Rules: The Essentials

[Patrick_Paul]

All the rules use icons, so I should see the icon I selected for this rule, which I do not, only the other rules. I actually intend to make this test for more than just the condition discussed, so it will have a narrower application that it does now. At this point, about half (or more) my mentor records should have this icon, and none do. I should add that no existing rule overlaps this one, hence why I was unsure how to code it.

[OptimiX_XcrY]

I would divide et impera by first disabling the other 9 format rules and only keeping this one and test it until I achieve what I want.

Also there could be an issue (not very sure) that if some statuses are blanks , it could impact the result

[Patrick_Paul]

I wish I had the luxury of disabling the others, but this is a production system and it would be far from ideal to remove that much functionality.

[OptimiX_XcrY]

Disabling, not removing, for half hour, the reenabling them, for “debugging” purposes

[Patrick_Paul]

Understood, would need to do it outside business hours as the “icons” are critical to business functions.

[Patrick_Paul]

I gave a broadcast notice to my users. Turning off the other format rules did not solve the problem.

@Steve … should I take this up with support, or is there something more I could post here to help you troubleshoot this? Here are my expression test results:

As you can see, there are plenty of positive results, but the records do not get formatted still, even with all others turned off. Please advise, and thanks!

[Patrick_Paul]

I noticed when I disabled this format rule, by the way, that the column I’m attempting to apply the formatting to shifts left to right, the same amount of space as the icon. The icon simply does not appear. So weird.

[Steve (Steve Coile)]

@Patrick_Paul

should I take this up with support

Sounds like a good idea at this point. I’m at a loss.

[Patrick_Paul]

Thanks for your help, it should have worked, it seems!

[Patrick_Paul]

Support has found that this is a glitch in the dereferencing utilized in this expression. When dereferenced in this manner, all column names and commands are case-sensitive unlike other instances in Appsheet.
They are working on a fix. In the meantime, a workaround is to ensure all commands and column names are capitalized exactly.

Quote from support:

It seems a bit weird, but I’ve replaced condition IN(“Active Approved”, [related trainees][current status])

with IN(“Active Approved”, [Related Trainees][Current Status]) and it starts to work

It is worth noting that I wrapped this expression in another “and()” expression, and all of it had to be capitalized exactly, including AND(), for it to work.