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
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”
@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.
@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.
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. ^^
Bahbus:
IN(“Visit Due”, [Maintenance Call out Contracts][Maintenance Visit Status])
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.
