Create A Collection of Specific Values in a Record

(1) Share

Report

Posted on by PowerAutomatic

Hi, On an app's gallery button within an item, how do I create a collection of the selected record where the value is = "Open" across multiple columns with choices of "Open" and "Closed"?

Datasource is SharePoint with multiple columns, several of which are of the same choices of Open or Closed. Example: Columns 1-7 are Choice = Open or Closed, but other columns are other choices, text, date. etc.

The end result should be that when a user selects a specific record in the gallery (or gal_Test.selected) by pressing the "View Open Columns" button, then they should see what Columns have the value of "Open" in either a text label, another gallery, or a form.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

CarlosFigueira on at

Like (2)

Report
You can use an expression similar to the following:

ClearCollect( OpenColumnsCollection, Filter( [ If(ThisItem.Column1.Value = "Open", "Column1"), If(ThisItem.Column2.Value = "Open", "Column2"), If(ThisItem.Column3.Value = "Open", "Column3"), If(ThisItem.Column4.Value = "Open", "Column4") ], Not IsBlank(Value)))

In each of the If calls, if the column value is not open, then the result will be blank, so filtering those out should give you a collection with only the open ones.

Hope this helps!

Was this reply helpful? Yes No
PowerAutomatic 43 on at

Like (1)

Report

Thank you Carlos. It almost works. I can't figure out this problem with it not accepting Value at the end of the table. I look up the collection, there is the table, I click on the table and there is the value, but power apps does not accept it. And of course, if Value is removed, it says can't compare table to text. I've tried variations based on your suggestion but no luck. It must be just one more thing to change or add. Maybe rename column or {Value: something something} but IDK. BTW, that column name is in the collection table, which matches the SharePoint field name in the URL. It won't accept the friendly column name in the code.

Was this reply helpful? Yes No
CarlosFigueira on at

Like (1)

Report

How is col_OpenAudits being created in your example? I don't understand exactly what the expression <<If(col_OpenAudits.HM_..._Finding.Value, "Open", "HM...Finding")>> is trying to do.

Was this reply helpful? Yes No
PowerAutomatic 43 on at

Like (1)

Report

Hi,

Thanks for staying with me on this. It is created via LookUp. Maybe that's the problem.

ClearCollect(col_OpenAudits, LookUp('Audit Questions_HM',AuditName.Value = gal_Audits.Selected.'File Name'));

Was this reply helpful? Yes No
CarlosFigueira on at

Like (3)

Report
In this case, col_OpenAudits will have a single element (the record representing the selected item from the gallery); is that what you wanted to do? If that's the case, and assuming that 'Audit Questions_HM' is the SP list that has multiple choice columns with 'Open'/'Closed' values, then you can get the list of open columns using some expression like this one:

ClearCollect( OpenColumnsCollection, Filter( [ If(First(col_OpenAudits).Column1.Value = "Open", "Column1"), If(First(col_OpenAudits).Column2.Value = "Open", "Column2"), If(First(col_OpenAudits).Column3.Value = "Open", "Column3"), If(First(col_OpenAudits).Column4.Value = "Open", "Column4") ], Not IsBlank(Value)))

Where 'Column1', 'Column2', 'Column3', 'Column4' will be the names of your columns.

Hope this helps!

Was this reply helpful? Yes No
PowerAutomatic 43 on at

Like (1)

Report

Carlos, Super quick fix. Thank you!

Was this reply helpful? Yes No