Dynamic filtering is a way for you to choose a record from another sheet faster by filtering the sheet based on what you have entered on the sheet that you're entering. Similar to Ragic's cascaded selections, the list of records that you can choose can be based on what you entered on another field.
To create a dynamic filter, navigate to your form page's design mode, and from Form Tools, click on the gear icon next to the related sheet name under Link & Load.
This will show you the setting to create a dynamic filter. Select a field value on the sheet that you're linking from, and match it depending on your requirements with the value or criteria that is going to be filtered from the sheet you're linking to. You can set multiple conditions by clicking on the + sign below the configuration, or enter a custom criteria with the pen tool next on the right.
In our example below, we want to offer VIP services depending on the customers' loyalty program. The service request form can only select customers that belong to a certain loyalty program so we set the "VIP Club Membership" field on the Customers sheet to be filtered by the service type offered on the Service Request field.
After a Service Type is selected, only the customers that belong to the related VIP club are displayed.
These dynamic filter rules can be set on fields that are linked as a subtable as well.
In our example below, the sales order form can only display products in the subtable that is linked from the product sheet that are currently running under a sales promotion. This promotion date range is set on the products sheet, and we check to see if the current date is between the start date and end date. This allows you to set promotion dates beforehand for products.
The subtable in below example is linked with the "Menu" sheet. By applying "does not contain" criteria to the "Allergic to" field, the system will only display choices of meals that do not contain the allergens typed in that field.
You can also apply "contains" criteria to filter entries that contain certain values.
In some cases, some fields in your entries may have empty values. To filter out entries with empty values, you can use the parameter "$EMPTY_VALUE".