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:

General Questions

All responses (6)

Answers (0)

PowerAutomatic 33 on at

Like (1)

Report

Create A Collection of Specific Values in a Record

Carlos, Super quick fix. Thank you!
CarlosFigueira on at

Like (3)

Report
Create A Collection of Specific Values in a Record

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!
PowerAutomatic 33 on at

Like (1)

Report

Create A Collection of Specific Values in a Record

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'));
CarlosFigueira on at

Like (1)

Report

Create A Collection of Specific Values in a Record

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.
PowerAutomatic 33 on at

Like (1)

Report

Create A Collection of Specific Values in a Record

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.
Suggested answer

CarlosFigueira on at

Like (2)

Report
Create A Collection of Specific Values in a Record

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!