Makeen transform after being used for some time may accumulate lots of data in the database. This may impact performance, specifically in cases where submitted data is required to be viewed on a mobile device. These are special types of forms where the same form is being used to submit multiple times, each time with added information or more filled fields. It is also possible that the same form is filled by multiple users therefore form submitted by one user needs to be seen by another user on its mobile device. This requires downloading of data from server to other user devices.
However, downloading all data on a mobile device will result in inefficient utilization of resources therefore it is important to download on a mobile device only data that is relevant in order to save space. Relevant data may be the last 100 records or data of last one week or so and data older than that may not be required. One way of downloading specific data is to define filter at connector level which is explained here. However, defining a filter at the connector level is static and will download data at the time of loading the app.
In order to provide better user experience to the customer, filter can be defined at form level which enables to apply different filter on the same entity based on the form requirement. Also, there are 2 modes of filters that can be applied on a form:
1) Dynamic
2) Static
The first mode where the filter is dynamic it is possible to define a filter as key-value pairs on the UI in which the key is a DB column and value can be any of the following:
a) Simple string
b) Control value
c) Column value of another entity
Options b and c make this mode dynamic, each one of the above will be explained with an example below in UI walkthrough.
The second approach where the filter is static is the one in which part of the database query can be written that runs at the database.
Below is the UI walkthrough, these are the most common steps for defining filters in both modes, the difference will be highlighted at the appropriate step.
1) Login to Studio
2) On the left menu select Pages & Forms
3) On the page listing click a page
4) Form designer will open, drag a button from right
5) Click on the button and on the right side select Action/Triggers
6) click on +Action
7) Select Button click from drop-down
8) Click save and then click on + sign
9) From the drop-down select GetData
10) Click on Data tab
11) From the drop-down select Entity
12) From the drop-down select the entity which is basically the DB table
13) To define filters press the + sign
14) After pressing the plus sign following will be visible
Here you can define only one filter or you can press the - sign to remove the filter.
To define the mode 1 filter mention the DB column in the left-hand box and in the right-hand box define the expression as explained above, few examples are below.
a) Simple string comparison
The left-hand side box contains the column name of the table selected in step 12 and the right-hand side box has value that needs to be matched and is enclosed in single quotes.
b) Control value
The left-hand side box again contains the column name of the table selected and the right-hand side is accessing one of the controls that are on the form. To access any control on the form Controls keyword is used with square brackets and inside the brackets need to mention the identifier that is set for that control in properties. The identifier is mentioned in single quotes inside the square brackets.
c) Other entity column value
The expression on the right side is Entity['getdata'].Find(["ID"]=1)["textbox1"] in which getdata is the DB table, ID is one of the columns of this table, and textbox1 is another column of the same table. Entity is the keyword used to access a table, Find is another keyword that compares a column to a particular value which in this case is 1. This expression reads that from table getdata fetch the row having ID 1 and then fetch the value of the column textbox1 from the row. This value which will be the result of the evaluation of expression will then be passed on as filter. For more info about writing expressions read articles on defining expressions in binding.
The above-explained mode can be written in a very complex way by combining other clauses using & and | operators including control values and data from columns of other entities. The limitation of this mode is that whatever the result is of the right-hand side box it is always matched equally to the left-hand side value. Meaning, it is not possible to define a filter, e.g. if there is a quantity column in a table then we cannot define a filter that fetches rows greater than or less than a particular quantity value. However, we can fetch the exact quantity like 100, so all the rows that have exactly 100 as the quantity will be fetched.
The other mode of filters
The text in the left-hand box is a keyword where which will always be the value to use this mode. The text in the right-hand box is department = 'technology' which is part of the database query. Similar kinds of queries are defined in the connector level filter. However, this does not allow control values and column value of another entity to be embedded in the filter, rather only simple strings can be part of it. This option is similar to the filter defined at the connector level explained here.
The advantage of this mode is that this is like row DB query and unlike mode 1 all kinds of operators less than, greater than, equal to etc. can be defined on DB columns to filter rows for fetching from DB. However, this one has a limitation that while defining filters form data cannot be referenced meaning any control value cannot be passed in this filter based on which DB rows filtering can be done.