cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Phineas
Post Partisan
Post Partisan

Gallery and Toggle Relationship

I have a Toggle and a Gallery.

 

The Toggle is triggerd by the contents of the Gallery.

 

If there are no 'Noncompliant' condtions in the Gallery and if all of the required documents are present in the Gallery the member is "Compliant". 

 

I also need to apply a date test to one of the documents; if the difference between Today () and date of award of the document is < 1 then 'Noncompliant'; or, if Date of Award + 730 > Today().

 

Gallery fields =

Title; DateofAward; ComplianceStatus

 

(CountRows(Filter(Gallery.AllItems, StartsWith(Field.Text, "Title1"))) >0 && 

(CountRows(Filter(Gallery.AllItems, StartsWith(Field.Text, "Title2"))) >0 && 

(CountRows(Filter(Gallery.AllItems, StartsWith(Field.Text, "Title3"))) >0 && 

(CountRows(Filter(Gallery.AllItems, StartsWith(Field.Text, "Title4"))) >0 && 

(CountRows(Filter(Gallery.AllItems, StartsWith(Field.Text, "Title5"))) >0 &&  (DateofAward.Text) + 730 > Today() ) )

 

If all the documents are present and the date associated with 'Title5' is within parameters the member is 'Compliant' and Toggle1 will default to 'true' in no 'false'.

7 REPLIES 7
KvB1
Solution Specialist
Solution Specialist

What is your question? ^^

Toggle measure member compliance with true/false based on the content of the qualifications Gallery.

 

I need two conditions.

 

The member must have ALL four titles  (Title1 - Title4) in the Gallery Title column, and the Gallery must have NO 'Noncompliant' findings in the Gallery ComplianceStatus for each of those items based on the date of issuee of the item, 'Title4' the difference between Today and the 'date of issue' of Title4 CAN NOT BE greater than 730 days, or the Title4 the member is 'Noncompliant' (the Title will still say Certified; the title is good, the member is just no longer compliant because after 2 yrs the member needs Title4 & Title5 (see below), if Title4 is to old and the member doesn't have a valid and compliant Title5 (see below) the toggle should default to 'false'. 

 

Or

 

The member must have one of each title (this time Title1 - Title5) in the Gallery, and the date of issue of  the item titled 'Title5' must be greater than Today, or the title is 'Noncompliant' and the toggle should default to 'false'.

KvB1
Solution Specialist
Solution Specialist

Man thats a lot of conditions ^^ 

 

Since you are working in a gallery, I would try to break down your conditions. For example, create a lable to see if the Title is compliant or not based on the date of issue.

That way you can filter the gallery on the Compliance status as well. And do 

CountRows(
	LookUp(
		Gallery.AllItems,
		StartsWith(
			Field.Text,
			"Title1"
		)
		And
		ComplianceStatus.Text="Compliant"
	)
)

You can then combine these to figure out whether Titles1, 2, 3 and 5 are compliant, etc

Thanks!

 

I already have a condition that say check Gallery.AllItems 'Noncompliant' >0.

 

I got that part covered. However, there times when ALL 'documents' could be complaint, but an additional document would be necessary based on the date of one of the other documents.

 

Which is why I designed the condition the way I did.

 

If Title 1 - 4 are present in the Gallery and there are no 'Noncomplaint' conditions member is "Compliant".

 

If Title 1 - 4 are present BUT the date of award of Title 4 occured MORE THAN 730 days ago (based on Today), then Title 5 is required, and it's date of award of Title 4 occured MORE THAN 730 days ago.

 

I've got all the 'is the document present' and 'are ALL "Compliant' part straight.

 

I just need to figure out how to write -

 

(CountRows(Filter(Gallery.AllItems, StartsWith(Field.Text, "Title5"))) >0 &&  (The difference between date of award associated Title5 and Today() is >0 )

 

 

KvB1
Solution Specialist
Solution Specialist

Oh okay,

LookUp(
	Gallery.AllItems,
	Field.Text="Title5"
).DateOfAward.Text>Today()

This will look for the record in your gallery that has "Title5" in the Text property of the Field control, and return true if the Text property of the DateOfAward control in that record is larger than Today()

KvB1
Solution Specialist
Solution Specialist

Actually, your formula:

(CountRows(Filter(Gallery.AllItems, StartsWith(Field.Text, "Title5"))) >0 &&  (The difference between date of award associated Title5 and Today() is >0 )

 

Can just be replaced by the formula in my previous post since if the record for Title5 doesn't exist, the thing I wrote will return false as well

 

 

Enter this just as you show it (with updated Gallery and Field Name)

LookUp(
	Gallery.AllItems,
	Field.Text="Title5"
).DateOfAward.Text>Today()

     *Error associated with '.DateofAward.Text' says "Invalid argument type. Expecting a Number
                                                      value"

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,828)