cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulBendall
Frequent Visitor

Can you Filter() based on an Array?

Is it possible to use the Filter function where you are looking if a value exists with an array of values, for example something like Filter(Table, Value in Array)?

I have an App where a user selects from a dropdown; based on the user selection I lookup a value in a column from a Sharepoint list, the value in itself is a comma delimited string. I've Split the value to create an array and now I need to do another filter/search/lookup to SharePoint to get some text for each of the delimited items. Is this possible or do I need to use ForAll?

To expand with an example:
The dropdown selected by the user returns the text from a SharePoint list such-as: "AB-01, AB-02, AB-05" 
I can split the string using the Split function into an array (or probably more accurately a single column table?)
Now I want to look up the text in a different SharePoint list that is related to AB-01, AB-02, AB-05
So something like Filter the data where column X has a value of AB-01 or AB-02 or AB-05 (but I only know this at runtime)

Thanks in advance

Paul

2 REPLIES 2
PaulD1
Super User
Super User

You may be able to use 'in' but it won't be delegable when used that way.

Below is a snippet of a (very long, hoho - low code?) filter where I am splitting an array 'FilterStatus' which contains a comma separated list of selected statuses the users wishes to see.

I'm allowing for up to 7 statuses to be selected. There is no status 'X' so if only one status is selected (say, 'Open') the predicate becomes:

Status = 'Open' Or Status = 'X' Or Status = 'X' Or Status = 'X' Or Status = 'X' Or Status = 'X' Or Status = 'X' Or Status = 'X'

When constructed this way the filter will delegate.

 

 Status = First(
                Split(
                    FilterStatus,
                    ","
                )
            ).Result Or Status = Coalesce(
                Last(
                    FirstN(
                        Split(
                            FilterStatus,
                            ","
                        ),
                        2
                    )
                ).Result,
                "X"
            ) Or Status = Coalesce(
                Last(
                    FirstN(
                        Split(
                            FilterStatus,
                            ","
                        ),
                        3
                    )
                ).Result,
                "X"
            ) Or Status = Coalesce(
                Last(
                    FirstN(
                        Split(
                            FilterStatus,
                            ","
                        ),
                        4
                    )
                ).Result,
                "X"
            ) Or Status = Coalesce(
                Last(
                    FirstN(
                        Split(
                            FilterStatus,
                            ","
                        ),
                        5
                    )
                ).Result,
                "X"
            ) Or Status = Coalesce(
                Last(
                    FirstN(
                        Split(
                            FilterStatus,
                            ","
                        ),
                        6
                    )
                ).Result,
                "X"
            ) Or Status = Coalesce(
                Last(
                    FirstN(
                        Split(
                            FilterStatus,
                            ","
                        ),
                        7
                    )
                ).Result,
                "X"
            )
        ),

 

brianng2
New Member

I have spent the whole 2 days for this solution. a little bit hard-coded but that works perfectly! greatly appreciated!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (36,294)