Format Rules of Column by Comparing Values from different tables

David11
Participant I

Hi there
I would like to highlight all values of A column if they appears in another table with the same description text

So for example : Highlight values from (table A, Column 4) if (table A, Column 4)=(tableB, Column5) and if (table A, Column1)=(tableB, Column1)

I tried this : AND(table A[Column 4]=table B[Column 5],table A[Column 1]=table B[Column 1]) But it did not work.

Can you help me please ? Thank you !

0 5 1,547
5 REPLIES 5

Hi @David1! Welcome to the community!

In AppSheet, an expression like

table A[Column 4]

will produce a โ€œlistโ€ of all of the values in column 4 of table A. Format rules evaluate expressions from the perspective of the table they are associated with. So, since your format rule is for table A, [Column 4] is what you need, not table A[Column 4]. To determine whether or not Column 4 in table A is the same as Column 5 in the same row of table B, I think you will need to use a LOOKUP() expression. The problem with a LOOKUP() expression in a format rule is that, even though you should be able to get it to work properly, youโ€™ll get a warning about it being โ€œexpressiveโ€ (possibly slowing down your app). I posted a little about that problem, including a LOOKUP() expression I used in a format rule, here:

Since you want to combine two conditions of this type, it is all the more likely to slow your app down if your app gets very big.

Reorganizing the data in your tables into one table might simplify things. Or, if you do need to have separate tables, you might want to use virtual columns to prepare simple YES/NO data (based on the conditions youโ€™ve outlined), that your format rules can refer to. Even though virtual columns also take some computing power, itโ€™s my impression that they are not as โ€œexpensiveโ€ as format rules with more-or-less the same expressions.

If youโ€™re fairly new to AppSheet, I can imagine that this is a lot to take in. Iโ€™ve been using AppSheet for a few years and I had lots of warnings about my format rules being โ€œexpressiveโ€ until just last week. Good luck!

List Operators
LIST(any-value[, another-value]โ€ฆ)
{ any-constant[, another-constant]โ€ฆ }
table-name[column-name]

David11
Participant I

Thank you @kirk !
I tried also tried with lookup formulas but did not work as wellโ€ฆ it was something like : (LOOKUP(โ€œNameโ€, โ€œTravauxโ€, โ€œAnimalโ€, โ€œtravauxโ€))=(LOOKUP(โ€œNameโ€, โ€œObjectifsโ€, โ€œAnimalโ€, โ€œObjectifโ€))

I tried with the select function as wellโ€ฆ did not work too !!

But Iโ€™ll opt for the virtual column or a new column in my Worksheetโ€ฆ very easier !

Thank you very much for your help !!

LOOKUP() and SELECT() can be tricky. Iโ€™m not sure why they didnโ€™t work but perhaps youโ€™ll have better luck with virtual columns.

One advantage of working with virtual columns is that, while you are learning, you can divide your task into several different virtual columns (each with a different role) that are brought together in yet another virtual column. That may not be the most efficient strategy in the long run but it makes testing and debugging a lot easier than working with one complicated expression.

Good luck!

I finally made it wth a new column in my worksheetโ€ฆit works fine !

kirk, i know it is not the good thread for this but i have another problem : Do you know how to chose the columns i want to see in a related table ? (iโ€™m not sure my explanation is goodโ€ฆ sorry for this) ! HEre is a screenshot : Screenshot_20200217_142658_x1Trackmaster.x1Trackmaster|230x500

For example in โ€œrelated Varietesโ€ I do not want to show โ€œKeyโ€ Or โ€œParcelleโ€ columns bot others.; I tried to change the label column but i do not find any logacl explanation of how the app choses which column to show here

Thank you

I donโ€™t have much experience in working with related tables. I hope someone will see this and offer some help. If you donโ€™t get a good response, it might be a good idea to post a separate inquiry. Good luck!

Top Labels in this Space