How to filter from an external data source

ABOUT

You can filter queried data in your form to show specific data.  For example, if you select a value in a queried dropdown control, related data can populate another control.  This can be done by having a list of data in a SharePoint list or Google sheet. 

To filter data, you first must query data from an external data source.  In my example, I will be querying a simple employee information list that has two columns; Name and Title.  The idea is that if I select a name, their title will appear in another control (in this a Textbox control).

HOW TO FILTER EXTERNAL DATA

You will need:  One Dropdown control and one Textbox control in your form, and a SharePoint list or Google sheet to query data from.

Step 1. Query your SharePoint list or Google sheet.  In your form, select Data>Query>+DATA SOURCE in the right panel.

In the popup, choose your data source, SharePoint or Google Sheets and click NEXT.

Now, complete the data source wizard.

Name: Enter a name for the data connection (spaces and special characters are not accepted)

AUTHENTICATE:  Authenticate Google account in order to connect to the spreadsheet.

Google information

Spreadsheet URL: Get the shareable link for your Google sheet.  Make sure you go to the Sharing Settings and select Anyone with the link can edit, and copy the link and paste it in the Spreadsheet URL field.  Note: By default, the shareable link will have can view selected, please select the can edit option before continuing.  

Sheet Name: Type the sheet name here (Sheet1)

Query Options:

  • Query data on load: With this option checked, your form will automatically query the spreadsheet data when the form is opened (and the form has connectivity).
  • Offline Cache: If you form is installed and you plan on going offline, this option will pull and save the data in your dropdown for offline use. The dropdown will not update with new data, but it will show data cached when installed.

Configuration: Here you specify what column you want to query from your Google Sheet.  In this example I selected the State field to query, then selected DONE to finish the wizard.

if using SharePoint:

Name: You can create any name you like for the data connector in your form. 2 guidelines for the data connector name: special characters (such as @, #, $, %, &, +) are not supported; the name cannot contain a blank space. Example of names you can use: List_1List_2 etc.

Query from: You can query from either a SharePoint library or a SharePoint list. In this example we are querying from a list so please select SharePoint List.

SharePoint URL: Paste your SharePoint URL here, make sure that it is a SharePoint list URL. 

Authentication: There are 2 authentication methods to select from Standard or Custom. Choosing Standard requires you to add the credentials for your SharePoint server. Custom authentication does not require any credentials.  This setting requires that the Federated Login option to be activated on your SharePoint account. Learn more about Standard and custom SharePoint authentication

Query on load: This option for your form will trigger a query when the mobile user opens it.  This query will prompt the mobile user for credentials to the SharePoint server when the form is opened. Make sure to check this option if you don't have a query button.

Offline Cache: Caching data in your form will improve the performance of your forms when your mobile users are working offline without a connection to your data stores.  The data is cached and can be refreshed by the mobile user manually when they have network connectivity. 

Attach credentials: Attaching user credentials does not prompt the mobile user for credentials. All information will be encrypted in the form.

Configuration: In this section you can select the SharePoint columns that you want to push to your form. If you selected Standard authentication then the columns will be displayed in the table.

If you selected Custom authentication then you have to provide the List id and add the columns manually.

Step 2.  Edit the dropdown data source.  Select your Dropdown control to show it’s Properties, and under Items, select External data source and the EDIT DATA SOURCE button.

In the next window, select your Google sheet or SharePoint list in the Data source dropdown and columns for Value and Display name.  If there are identical values in your column, and you only want to show one of each value in your dropdown, select the Show unique values only checkbox.

The image below shows that I am querying my Google sheet data source and the dropdown in my form will show all unique names to select from.

For this scenario, we can skip Filter data since we will filter on the textbox control instead.  Click SAVE and your dropdown is ready to query.

Step 3.  Set up your textbox to show filtered data.  Select the Textbox control to show it’s Properties and select Logic.  Click the FILED PROPERTIES button to prompt a popup. 

Instead of Default Value, choose Formula Builder: and MORE

Another popup, More Options, will appear to filter the data for the textbox control. 

What the screenshot above means:  I am retrieving the data from the GoogleEmployeeList data source, and I want my textbox to show the employee title.  When a name is selected in my form’s dropdown (dropdown1), the Title will appear in the textbox. 

Data source: Select the data source you are querying.

Field: Select the field column from your data source you want to show in the textbox control.  In my example, I am choosing Title since I want the job title appear after selecting an employee name in my dropdown (dropdown1).

Where: Choose the dropdown field that contains the list, in my case, names (dropdown1).

is equal: Choose the value that will decide the filtered data in your textbox control.  In my case, if a name is selected in the dropdown, the title will appear in my textbox.

Select SAVE in the two popups and your scenario will be ready.

On my device, I chose a name and the title appeared according to the data in my Google Spreadsheet.

If I select any name, the corresponding title will appear

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.