Format Rules of Column by Comparing Values from different tables

[David11]

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 !

[Kirk_Masden]

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:

Format rule condition vs. Show condition Questions

I’ve been working on a prototype app that is designed to be used by a single user in prototype (free) mode. So, there’s only one user. Since there’s only one user per app, I can have a small table (only a few rows) of “options” from which the user can choose: [Screen Shot 2020-02-07 at 19.19.12] One of the options is “Word” size, that is, an option to change the display size of the vocabulary items in this flashcard app. The size that the user selects is stored in a small “options” table (j…

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!

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

help.appsheet.com

List Expressions

List expressions use and/or produce lists of values.

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

[David11]

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 !!

[Kirk_Masden]

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!

[David11]

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

[Kirk_Masden]

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!