@TGrounds wrote:
Here are the different formulas that work together to set the color:
There is a Dropdown in my gallery {Customer_Usage) that looks up a value from a SharePoint listing (CustomerFunctionAdoption) - there are 4 potential values:
"Partial"
"Potential"
"Full"
and "None"
Note: In the data the values of "Full", "Potential" and "Partial" are written to the list and the default is 'none' but there may not be a record in the list for 'None' - they will if it had been set to one of the other values and later turned to 'None' - this reduces the number of records in the SharePoint List.
On the Gallery - TemplateFill:
If(Customer_Usage.Selected.Value = "None",RGBA(255,255,255,1),
If(Customer_Usage.Selected.Value="Partial",RGBA(102,182,227,1),
If(Customer_Usage.Selected.Value="Potential",RGBA(255,191,0,1),
If(Customer_Usage.Selected.Value="Full",RGBA(127,178,57,1)))))
The "Customer_Usage" dropdown - Default:
If('CMB:SelectCustomer'.Selected.Title<> "---" && 'CMB:SelectSuccessEngagement'.Selected.Title <> "---",
LookUp(CustomerFunctionAdoption,'CMB:SelectCustomer'.Selected.Title exactin Customer.Value &&
'CMB:SelectSuccessEngagement'.Selected.Title exactin Success_Engagement.Value &&
Function_Code.Text exactin Function_ID.Value,Adoption_Level.Value),
If('CMB:SelectCustomer'.Selected.Title<>"---" && 'CMB:SelectSuccessEngagement'.Selected.Title="---",
LookUp(CustomerFunctionAdoption,'CMB:SelectCustomer'.Selected.Title exactin Customer.Value &&
Function_Code.Text exactin Function_ID.Value,Adoption_Level.Value),
"None"))
It is looking at two values - Customer and Engagement to look up the FunctionAdoption. If an Engagement isn't selected, then it looks up by Customer only. If it doesn't find either, then it sets the value to 'None'.
The only user interaction that is happening is scrolling down through the vertical gallery - once I get one or two pages scrolled, that is when the dropdown is being set at a value that isn't correct - I look in the SharePoint list for the customer adoption and don't find any records for the items that are being set to this random color. The dropdown is being set to the associated value of the color that the gallery cell is being set to - but the value that the dropdown is being set to doesn't have an associated record in the SharePoint list - meaning it should default to 'None'. It appears that it is when it doesn't find the lookup and should default to 'None' is when it's having the issue.
I hope this makes sense.
Tom.
Maybe I'm confused, if those yellow blocks don't have a value how can they find the "None" to show the color correctly?
Edit: I was confused, I see now how that is working, I'd still go with a switch and evaluate a missing lookup to the dropdown as white.
If(Customer_Usage.Selected.Value = "None",RGBA(255,255,255,1),
If(Customer_Usage.Selected.Value="Partial",RGBA(102,182,227,1),
If(Customer_Usage.Selected.Value="Potential",RGBA(255,191,0,1),
If(Customer_Usage.Selected.Value="Full",RGBA(127,178,57,1)))))
You could change this to a switch and include a null/none as the "else" of the switch and make your code much neater.
Switch(Customer_Usage.Selected.Value,
"None",RGBA(255,255,255,1),
"Partial",RGBA(102,182,227,1),
"Potential",RGBA(255,191,0,1),
"Full",RGBA(127,178,57,1),
RGBA(255,255,255,1))
In my example you don't really need the none to evaluate but I think that makes the point.
