cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate III
Advocate III

How to use Common Data Service 'Option Select' field in a formula to change colour of control versus Sharepoint List

Hi,

 

Within a CDS entity, I have a field called "Proof Status", it is an option select field with these values: New, In Progress, Complete, Query.

 

Very simply, in Powerapps I want to make each status a different colour, New=Yellow, In Progress=Orange, Complete=Green, Query=Red.

 

Previously I had a Sharepoint list "Proof Requests" with a Task Outcome column called "Status" with the same values.

 

Sharepoint List App

 

I had Powerapps generate a basic app from my Sharepoint list, called "Proof Requests,".

 

The formula in the Edit Screen, for the drop down control for "Status" field was: 


Items: Choices([@'Proof Requests'].Status)"

 

My label in the gallery was:

Text: ThisItem.Status.Value

 

So to change the colour of a label control in my gallery used:

Color: If(ThisItem.Status.Value="New", Yellow, ThisItem.Status.Value="Query", Red, ThisItem.Status.Value="Complete", Green, Orange)

 

This worked and I was able to get different colours for my label controls.

 

sample 1.JPG


Common Data Service App

 

Similarly I generated an app from a CDS entity with a field called "Proof Status".

 

The combo box control generated from the "Proof status" field includes this formula:

Items: Choices('Proofing Status')

 

I have a Gallery control where I have a subtitle label with:

 

Text: ThisItem.'Proofing Status'


I tried using below to change the colour:

 

Color: If(ThisItem.'Proofing Status'.Value="New", Yellow, ThisItem.'Proofing Status'.Value="Query", Red, ThisItem.'Proofing Status'.Value="Complete", Green, Orange)

 

This does not work.

 

sample 2.JPG

 

Obviously, I am missing some understanding on the differences between a Sharepoint list task outcome column vs. a common data service option select field and how those are used/referenced in powerapps controls. 

 

Question: How do I use the CDS field option select item from the combo box in a formula, in this specific case, to change the colour of a text label in another screen?

 

Any guidance on the CDS entity fields vs sharepoint lists would be greatly appreciated. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

 

Hi@virakones,

 

Based on the issue you mentioned, you want to get different colors for your Label using the 'Option Select' field in a formula.

 

Use the special [@ColumnName] reference that directly points to the Option Set. You’ll see the Option Set values you can easily choose from.

 

4.png

 

You can modify the formula like this:

 

If(ThisItem.'Proof Status' = [@'Proof Status'].New,Yellow,'Proof Status' = [@'Proof Status'].Complete,Green,'Proof Status' = [@'Proof Status'].Query,Red,Orange)

 

For more information:

https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

https://michalguzowski.pl/how-to-work-with-option-set-in-powerapps/

 

Best Regards,

Qi Qiao

 

View solution in original post

4 REPLIES 4
Super User II
Super User II

Hi @virakones 

I would use the Switch() function as follows in the Color property of the item in the gallery. 

 

Switch(ThisItem.'Proofing Status', "New", Yellow, Query", Red, "Complete", Green, Orange)

 

 

Community Support
Community Support

 

Hi@virakones,

 

Based on the issue you mentioned, you want to get different colors for your Label using the 'Option Select' field in a formula.

 

Use the special [@ColumnName] reference that directly points to the Option Set. You’ll see the Option Set values you can easily choose from.

 

4.png

 

You can modify the formula like this:

 

If(ThisItem.'Proof Status' = [@'Proof Status'].New,Yellow,'Proof Status' = [@'Proof Status'].Complete,Green,'Proof Status' = [@'Proof Status'].Query,Red,Orange)

 

For more information:

https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

https://michalguzowski.pl/how-to-work-with-option-set-in-powerapps/

 

Best Regards,

Qi Qiao

 

View solution in original post

Just to expand on the other answers, the reason for the difference between CDS and SharePoint is that in CDS, each Option Set item has 2 components; a value (an integer), and a label (the text you see). Whereas in SharePoint you just have the text. The advantage of the CDS approach is that you can change the label without affecting any logic based on the value, or any existing data. The CDS approach also allows different labels based on the user's language

Thank you!

 

Great to know the difference in this case, is to refer to the selected option itself

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Users online (8,102)