Filter on a data source with a dynamic number of filters

[shashikara]

In my app, users can select a number of different text terms to add them as filters.  Then the user can search a data source based on the filters selected.  The data source is a CDS table called RegInv, and the results are stored in a collection called ResultsList.

The user's filters are stored in a collection called UserFilters with the columns FilterType and FilterText.  FilterType tells me which column in the CDS source to filter on, and FilterText is the text to search for in that column.

I've figured out how to apply the filters to get the results that match ANY of the filters the user chose, but I need to filter based on ALL of them (meaning the results should match all of the filters the user selected, not just any of them).

Here is my formula for filtering on ANY.  Thoughts on how to write a formula to require that ALL the filters match?

ForAll(
	UserFilters,
	Collect(
		ResultsList,
		Switch(
			FilterType,
			"Issues",
			Search(
				RegInvData,
				FilterText,
				"cr023_issues"
			),
			"Technologies",
			Search(
				RegInvData,
				FilterText,
				"cr023_technologytagstext"
			)
		)
	)
);

As a side note - I tried dynamically storing the column name in the UserFilters collection, but I can't seem to reference a variable as the column to search in a Search formula.  When I convert it to a Filter formula and add the variable as the column (FilterText in FilterColumn), I get a delegation warning.  I'm fine doing it with a Switch as above, but curious if this is possible without having a delegation issue.

[WarrenBelz]

Hi @shashikara ,

The below is only syntax guidance as I cannot see your data or structure

ForAll(
   UserFilters,
   Collect(
      Filter(
         ResultsList,
         (
            FilterType = "Issues" &&
            Search(
               RegInvData,
               FilterText,
               "cr023_issues"
            )
         )&&
         (
            FilterType = "Technologies" &&
            Search(
               RegInvData,
               FilterText,
               "cr023_technologytagstext"
            )
         )
      )
   )
)

It is a bit hard to answer the delegation question without seeing the actual code and error.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @shashikara ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[shashikara]

Hey Warren! Thanks for the helpful response! 

Unfortunately, I think I did a poor job of explaining the data structures.  Let me provide some more information.

I have two collections:

- RegInvData, a large table with lots of reference information.

- UserFilters, a table containing information about each of the filters the user has selected.

I want to user these two to make a third collection:

- ResultsList, a table containing the rows in RegInvData that match ALL of the filters the user selected.

So the goal is to let the user select any number of filters then click "Search".  The app navigates to a screen with a gallery containing the records from RegInvData that match ALL of the filters the user selected (i.e. ResultsList).

Right now, I have only figured out how to return results that match ANY of the user's filters (which was the formula I pasted in my original post).  I'm asking for guidance on how to filter/search RegInvData to return records that match ALL of the filters listed in UserFilters.

RegInvData and ResultsList have this same structure as follows:

UserFilters has the following structure:

[shashikara]

Hey Warren, thank you for the helpful suggestion! Unfortunately, I don't think I explained my data structures and the problem that well, so let me try to clarify.

I have two tables:

- RegInvData, a CDS table with lots of reference data.

- UserFilters, a collection containing information about each of the filters the user has selected (one filter per row).

The goal here is to let the user select any number of filters, then click "Search".  This navigates the user to a results screen containing a gallery of results for them to view/interact with.  The data in the gallery is the collection ResultsList, which should contain only the rows in RegInvData that match ALL of the filters the user has selected.

Right now, I've only figured out how to make ResultsList contain the rows in RegInvData that match ANY of the filters the user selected.  This is the formula I put in my original post.  What I'm trying to figure out how to do is have ResultsList only contain the data that matches ALL the user-selected filters.

RegInvData and ResultsList have the same table structure as follows:

UserFilters has the following structure:

[WarrenBelz]

Hi @shashikara ,

A bit like the first post, it is better to guide you then help you with code correction.

What you need to do is get two collections with the same field names and then join (Collect) then into a third. There are other ways, but this is the simplest. 

Collect them both and use RenameColumns where necessary to change one set of names to match the other and then you can join them together. As I mentioned, happy to help once you have the structure required. Please post your code and field names in Text rather than images.

[WarrenBelz]

Hi @shashikara ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @shashikara ,

I am following up this post to see if it can be closed as you obtained the result you were looking for.

Please click Accept as solution if my post helped you solve your issue as this will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.