Oke, in this case you can use to formulas two easy filter the datasource: Search() and Filter
Search
Search you can use when you use a textinput to add a part off the string you want to filter on. In the search filter you can specify the columns (this can be multiple) which has to be evaluated based on the input string. Formula:
Search(Datasource, Textimput.Text, column1, column2, etc)
Filter
Filter you can best use when you want to filter a dataset on a int or specific fullstring (with fullstring I mean that the string to filter is complete and can be matched by the column you want to filter). Filter is great on filtering relationship in a sql database or when you have a choices list where the choices are limited. Practically you wil use a Filter when you use a combobox/list/dropdown control, where the output is a unique number.
With a Filter you can filter on more conditions, but for each condition you add a seperate line (where Search takes in one textstring an eveluate more columns). Filters we can build so the return records are blank when the combobox is blank or to show al when the combobox is blank.
Show no record on blank combobox
Filter(Datasource
,relationship1ID = Combobox1.selected.ID
,relationship2ID = Combobox2.selected.ID
,etc.
)
Show all record on blank
Filter(Datasource
,IsBlank(Combobox1.selected.ID) || relationship1ID = Combobox1.selected.ID
,IsBlank(Combobox2.selected.ID) || relationship2ID = Combobox2.selected.ID
,etc.
)
To go into the second option (Show all records): How the filter works is that it evalutes every condition line and passes the record if the condition line is true. So if the combobox has no selection the IsBlank() part is true. If there is a selection the IsBlank() part is false and the second part off the condition line is evaluated. Is there a match then the second part is true. Hope this makes sense.
Search and Filter
We can also combine both in one formula.
Search(Filter(Datasource
,IsBlank(Combobox1.selected.ID) || relationship1ID = Combobox1.selected.ID
,IsBlank(Combobox2.selected.ID) || relationship2ID = Combobox2.selected.ID
,etc.
)
,Textinput.Text
,Column1
,Column2,
,etc.
)
What's great is that both formula's are delegated to the server (offcourse when the evaluation syntax is build correctly), so the return collection can be up to 2000 records if needed.
Bonus
In this example we talk about a combobox or textinput field. This is great but just as easy we can use a variable in our Filter or Search. Because you can pass variables to the screen in the navigation() formula (in the context part) this is super easy to build. Do like:
Navigate(ToScreen, Cover, {Variable:value you want to put in the variable})
Then on the hidden property off the ToScreen you reset the variable to blank. Like:
UpdateContext({Variable:Blank()})
If you then go to the screen for another part off you're app and don't add a variable in the navigate() formula. So like:
Navigate(ToScreen,Cover)
The datasource is not filtered. This because you have added the following syntax to your filter:
,IsBlank(Variable) || VariableID = Variable
This way you can create re-usable screen, which can adept to the user, his role and his privileges. I have a playlist with several short video's on how to do this and posted this on youtube. If you want to see this follow this link: PowerApp - Re-Use Screens
