Conditional Formatting of Gallery Text Fields and Toggle Control

[Anonymous]

I have a Gallery that has a 'DaystoRenewal' field that counts down the days since last compliance. 

I would like the text in the field to turn red once the counter goes passed '0' and turns negative. See screenshot below.

I currently have the following in the field. I'd like to add the above need to the existing formula.

"<sb>Days to Renewal: </b> <font color=blue>" & LookUp(DaystoRenewalCollection,ClassTitles=ThisItem.CQTitle).DaysToRenewal- DateDiff((ThisItem.DteofCQ),Today(),Days)

I'm also in need of a formula that will read the Gallery 'CQTitle' column in the Gallery below and if 'CQTitle' does not contain 'Firefighter I' or Firefighter II' the Toggle control should default to 'false'.

Toggle Control (Default):

CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified" && CQTitle <> ECMmberCQTitleHtmlTxtFld_1.HtmlText ))

[FabianAckeret]

Hi @Anonymous 

Colors should be changed through the appropriate properties if possible. In your case you add it directly as HTML in the Text property. Instead, you could make use of the Color property of your label. To achieve a different color, follow these steps:

1. Change your current formula to 

"<sb>Days to Renewal: </b>" & LookUp(DaystoRenewalCollection,ClassTitles=ThisItem.CQTitle).DaysToRenewal- DateDiff((ThisItem.DteofCQ),Today(),Days)​

2. Go to the Color property and add the following formula:

If(
LookUp(DaystoRenewalCollection,ClassTitles=ThisItem.CQTitle).DaysToRenewal- DateDiff((ThisItem.DteofCQ),Today(),Days) >= 0, Color.Blue, Color.Red
)

As for your second question, I understood that if at least ONE item in your gallery includes 'Firefighter I' or 'Firefighter II' the toggle should default to false. To do that, add the following to the Default property of your Toggle control:

IsBlank(LookUp(ECMmbrCQSmryGallery_1.AllItems, <your field name> = "Firefighter I" || '<your field name>' = "Firefighter II"))

I hope this helps.

---

Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

[Anonymous]

Thank you for the color recommendation.

On the Toggle control - the additional condition is  - if 'Firefighter I' or 'Firefighter II' DO NOT appear in the Gallery 'Cert/Qual Title'(CQTitle) the Toggle control should default to 'false'. I need this condition to be added to the existing formula below:

CountRows(ECMmbrCQSmryGallery_1.AllItems) = CountRows(Filter(ECMmbrCQSmryGallery_1.AllItems, ComplianceLabel_1.Text = "Compliant" || ComplianceLabel_1.Text = "Certified" && CQTitle <> ECMmberCQTitleHtmlTxtFld_1.HtmlText ))

[FabianAckeret]

Something like this, you mean?

CountRows(ECMmbrCQSmryGallery_1.AllItems) = 
CountRows(
	Filter(
		ECMmbrCQSmryGallery_1.AllItems, 
		ComplianceLabel_1.Text = "Compliant" || 
		<your field name> <> "Firefighter I" && '<your field name>' <> "Firefighter II",
		ComplianceLabel_1.Text = "Certified" && CQTitle <> ECMmberCQTitleHtmlTxtFld_1.HtmlText 
	)
)

I didn't try it out though - might not work as PowerApps will interpret your OR and AND conditions differently than expected. Perhaps, you could start to wrap up your conditions with AND() and OR():

CountRows(ECMmbrCQSmryGallery_1.AllItems) = 
CountRows(
	Filter(
		ECMmbrCQSmryGallery_1.AllItems, 
		OR(
			ComplianceLabel_1.Text = "Compliant",
			<your field name> <> "Firefighter I" && '<your field name>' <> "Firefighter II")),
			ComplianceLabel_1.Text = "Certified" && CQTitle <> ECMmberCQTitleHtmlTxtFld_1.HtmlText
		) 
	)
)

---

Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

[Anonymous]

I updated to this:

CountRows(ECMmbrCQSmryGallery_1.AllItems) =
CountRows(
Filter(
ECMmbrCQSmryGallery_1.AllItems,
ComplianceLabel_1.Text = "Compliant" ||
IsBlank(LookUp(ECMmbrCQSmryGallery_1.AllItems, CQTitle = "Firefighter I" ||
CQTitle = "Firefighter II")) ||
ComplianceLabel_1.Text = "Certified" && CQTitle <> ECMmberCQTitleHtmlTxtFld_1.HtmlText
)
)

It is still not responding. In the screenshot the Toggle should be 'Inactive', because neither the title 'Firefighter I' nor 'Firefighter II' appear in the Gallery.

[FabianAckeret]

Hi @Anonymous 

I don't think I completely understood your requirements yet. 

Could you let me know WHEN you'd like to toggle and when not based on what fields INSIDE and OUTSIDE the gallery?

I understood the following:

Title	Inside/Outside gallery
ComplianceLabel_1.Text	outside
CQTitle	inside
ECMmberCQTitleHtmlTxtFld_1	outside

If:

• ComplianceLabel_1.Text = "Compliant" OR
• CQTitle = "Firefighter I" OR
• CQTitle = "Firefighter II" OR
• ComplianceLabel_1.Text = "Certified" AND CQTitle <> ECMmberCQTitleHtmlTxtFld_1.HtmlText

If so, just use the following formula. Otherwise, try to swap the true/false at the end of the if condition.

If(
  OR(
	ComplianceLabel_1.Text = "Compliant",
	IsBlank(LookUp(ECMmbrCQSmryGallery_1.AllItems, CQTitle = "Firefighter I" || CQTitle = "Firefighter II")),
	ComplianceLabel_1.Text = "Certified" && IsBlank(LookUp(ECMmbrCQSmryGallery_1.AllItems, CQTitle <> ECMmberCQTitleHtmlTxtFld_1.HtmlText))
  )
)

Feel free to adjust the conditions to your liking, but with above formula you should be able to get it working.

---

Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

[Anonymous]

You're learning what many others have; I'm an impossible help.  But, I keep trying. 😁

All fields referenced are inside the Gallery/Item.

Gallery: 'ThisItem.CQTitle' (ECMmberCQTitleHtmlTxtFld_1)
"<sb>Cert/Qual Title: </b> <font color=blue>" & ThisItem.CQTitle

Gallery: 'ComplianceLabel_1': (feeds results to 'Compliance Status' field (ECMmbrCQStatHtmlTxtFld_4) field in Gallery.

ECMmberCQTitleHtmlTxtFld_2 = If( LookUp( DaystoRenewalCollection, ClassTitles = ThisItem.CQTitle ).DaysToRenewal = 0, "Certified", ( DateDiff( ThisItem.DteofCQ, Today(), Days ) > LookUp( DaystoRenewalCollection, ClassTitles=ThisItem.CQTitle ).DaysToRenewal )|| Value(ThisItem.DteofCQ)=0, "Noncompliant", "Compliant" ) 

Gallery: 'ECMmbrCQStatHtmlTxtFld_4': Displays result of ComplainceLabel_1 formula.
"Compliance Status: "&If(ComplianceLabel_1.Text="Noncompliant","<font Color=Red>"," <font Color=BLUE>") & ComplianceLabel_1.Text

[FabianAckeret]

Hi @Anonymous 

If you want to want to check each column in a gallery against a condition, you need to have those values in your data source.

You can't have a condition that refers to the actual controls directly (e.g. ComplianceLabel_1.Text). Unless you have only 1 value in that gallery :-).

You'll need to make use of ThisItem.<column> in your conditions. So, I'd recommend you to get all the values you need into the data source of your gallery.

---

Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

[Anonymous]

Yes, sir; every value I seek is a column available in the SP list/collection (data source).

I have been given more than 800 .pdf certs or quals files, that have various naming conventions. I am trying to figure out how to best introduce these files into the new Power Apps database so that when the Member pulls up their profile the documents will show in the Gallery.

I will, more than likely, have to convert all the files to an image file. In the meantime, I can get the files (image or .pdf) to show up in an independent Gallery based on the library as a data source. However, if I try to display these same files/images in the Image control of my main Gallery (tied to the MembersCertsandQualsList), the images will not display.

All of the fields that would make up the name of a 'CQTitle' file in the SP library are available to the Gallery, from the list/collection. 

UnqID, CQTitle, CQYr are all columns in the MemberCertsandQualsList/Collection.

There are other fields in the Gallery I added, but they are not columns in the 'list/collection', and are methods one forum person recommended to use to provide data without relying on calculated columns, current date, and other coding and delegation impediment familiar to and through SP.

I don't have a problem with it (or doing any of this is SP and Automate, if that's more efficient). I am trying to reduce as many impediments to data access.

Example: The formula in 'ComplianceLabel_1' is what is populating 'ECMmbrCQStatHtmlTxtFld_4' (or, 'Compliance Status' in the User Interface/Gallery)

If( LookUp( DaystoRenewalCollection, ClassTitles = ThisItem.CQTitle ).DaysToRenewal = 0, "Certified", ( DateDiff( ThisItem.DteofCQ, Today(), Days ) > LookUp( DaystoRenewalCollection, ClassTitles=ThisItem.CQTitle ).DaysToRenewal )|| Value(ThisItem.DteofCQ)=0, "Noncompliant", "Compliant" )

I believe all the fields required to achieve the goal are present, accounted for, and populated with data. What do I do now?