Format Colours in a view based on another view

BlakeHammond
Participant IV

Hi
I am trying to change the colour of a customers name based on contract status.

To explain.

When maintenance contract expires ( Displayed under Maintenance contract view)

I want the customers name under Live customer list to change colour depending on status.

but I keep getting an error when trying to reference a different table under the format condition formula.

I hope I have made this clear :).

Thanks

Blake

Solved Solved
0 18 1,045
  • UX
1 ACCEPTED SOLUTION

Ok, so now I understand why it was yelling at you about Text and Lists.

MAINTENANCECALL OUT CONTRACTS[Maintenance Visit Status]

This expression here returns the List of every rowโ€™s [Maintenance Visit Status]. As a side note, since you have these two tables already linked, you donโ€™t need the superfluous information. For example, you shouldnโ€™t need customer name in both. Because the records are linked by Customer ID. However, in the Live table, I think it would be easier to have ID as the Key but Name be the Label. That way anytime you reference the Key it will display their Name. Also, MaintenceCall out Contract should have its own unique ID as its Key.

I donโ€™t know if it will be any faster, but try this as the expression:
[Maintenance Call out Contracts][Maintenance Visit Status]="Visit Due"

And if it is still yelling about lists (I suppose there could be multiple contract for the same customer), try:
IN("Visit Due", [Maintenance Call out Contracts][Maintenance Visit Status])

The reason the Select is adding so much time is because you already have VCs doing that work for you, so itโ€™s basically doubling the time needed, or worse.

View solution in original post

18 REPLIES 18

May I ask what kind of formula you have tried? Are you using related table structure with the Ref field?

You may try something like this:

ANY(SELECT(Live[Status],[Customer]= [_THISROW].[Customer])) = โ€œOpenโ€

BlakeHammond
Participant IV

@Bellave_Jayaram Thank you this formula works but cause the app to sync slowly. Is there away of doing it without slowing the app?

@Aleksi I tryed using Tablename[Column Name]=โ€œLiveโ€ & virtual column inside of customer list with a ref to the status column in Maintenance table but both bring back the same error of โ€œComparing list with Textโ€

Thanks

Blake

Are you using Related table structure with these two tables?

@Aleksi There are table references setup between the tables. Is this what you mean?

@BlakeHammond Correct. Now we should know what kind of you have.

BlakeHammond
Participant IV

@Aleksi not sure what you mean?

I have come as summoned. Please send me screenshots of where your trying to use that SELECT expression, as well as the the structure of the table Live in AppSheet, and preferably (if allowed) the spreadsheet of โ€œLiveโ€ so I can see some example data.

Hi @Bahbus

Basically What I am trying to achieve is:

I have a view called Live Customer List which is populated from a table called Live customers

I have a table called โ€œMaintenanceCall out Contractsโ€ which contains a column called Maintenance Contract Status.

In the the view โ€œLive Customer Listโ€ I want to change the colour of the customer name depending on the status.

The formula i was originally trying to use was MaintenanceCall out Contracts[Maintenance Contract Status]=โ€œLiveโ€.

But I get an error โ€œCannot compare List with Text in (MAINTENANCECALL OUT CONTRACTS[Maintenance Visit Status] = โ€œVisit Dueโ€)โ€.

Maintenance Visit Status is of column type text and is populated with the Formula you kindly helped me with in the other question.

The Select Function as suggested by @Bellave_Jayaram Works well but due to the high number of rows it slows the app considerably.

Unfortunately I canโ€™t share spreadsheet as it has customer information in it.

Ok, I can deal without the raw data. Can you post a screenshot of the table structure on the Data tab of โ€œLive Customer Listโ€ or โ€œLiveโ€ and โ€œMainenanceCall out Contractsโ€? Just so I can see what all the columns types and names are, no actual data.

I think I can handle that

^^^^ live customer list

^^^^^^ MaintenanceCall out Contract

anything else let me know

Ok, so now I understand why it was yelling at you about Text and Lists.

MAINTENANCECALL OUT CONTRACTS[Maintenance Visit Status]

This expression here returns the List of every rowโ€™s [Maintenance Visit Status]. As a side note, since you have these two tables already linked, you donโ€™t need the superfluous information. For example, you shouldnโ€™t need customer name in both. Because the records are linked by Customer ID. However, in the Live table, I think it would be easier to have ID as the Key but Name be the Label. That way anytime you reference the Key it will display their Name. Also, MaintenceCall out Contract should have its own unique ID as its Key.

I donโ€™t know if it will be any faster, but try this as the expression:
[Maintenance Call out Contracts][Maintenance Visit Status]="Visit Due"

And if it is still yelling about lists (I suppose there could be multiple contract for the same customer), try:
IN("Visit Due", [Maintenance Call out Contracts][Maintenance Visit Status])

The reason the Select is adding so much time is because you already have VCs doing that work for you, so itโ€™s basically doubling the time needed, or worse.

BlakeHammond
Participant IV

I tried the suggested formula but get the same error.

โ€œCannot compare List with Text in ([Maintenance Call out Contracts][Maintenance Visit Status] = โ€œVisit Dueโ€)โ€

Just updated with that contingency. ^^

That seems to work a treat. Didnโ€™t think to use the IN statement. The power of a fresh set of eyes

Yeah most customers have more than 1 site with a contract.

again thank you for your help on this one. I guess I will see you at the next

I owe you a pint

Blake

Lastly, in regards to the point I made about superfluous information in the tables. Anything table in which you reference Customer ID, you can then reference anything in that base table. So if you ever need to access Customer Name from mainenance on call, you can just do [Customer ID].[Customer Name] or [Customer ID].[Telephone Number].

I was using live customer list[Customer Name] inside the valid_IF so when I select the customer it only gives me their sites to select as options in a drop-Down.

Is there a better way to do it ?

I think if you make the Customer Name the label in that top live table, any Ref to Customer ID will display their name. Iโ€™m like 90% sure at the moment. So you could make the Valid_If on the Customer ID in the maintenance on call table Customer List[Customer ID] (or whatever the name of the table is that has the original Customer ID) and the dropdown should only display their names. This will be useful if you ever happen to have two different customers with the exact same name.

Top Labels in this Space