You can automatically fill fields in a form with data from a spreadsheet or submitted responses from another form using the Lookup field
To do this, you will need two forms, the lookup form (the form where you need the data to be displayed) and the source form (the form from which data will be referenced).
- Sign up or sign in to the Formplus application.
- You will need to create the source form, you can also use an existing form as the source form. If you are new to Formplus, here is a guide on creating a form.
- If you will be using/importing data from a spreadsheet ensure that the source form has at least the same number of fields as the number of column data you will like to import, then go ahead to import responses from the spreadsheet into the source form.
- Create the lookup form (or edit an existing form) and ensure to include the lookup field. The lookup form should also include fields you will like to lookup data into. For example, if you will like to look up a customer's address from the source form, the lookup form should also contain a field for address.
- Configure your lookup field to reference data from the source form by clicking on the Edit icon of the lookup field and following the steps below:
Setting up Autofill Lookup
- Select the lookup type, the default selected option is the 'Basic Lookup'. Click the dropdown to select the 'Autofill Lookup' option as that is what we need in this case.
- Select the type of field you will like your lookup field to be. The options available for the Autofill lookup type are the -Short text, Number, Basic select, Radio, and Email field types. What you will select here is dependent on what type of data you expect your respondents to input/select to trigger the lookup field.
- Change the default lookup field label to your preferred text just as you rename other form field labels.
- Depending on the selected field type in 2 above (not available for Basic Select field type), you may able to add a placeholder text to the lookup field
- You can enter a help text to communicate additional information to your respondents
- Depending on the selected field type in 2 above (not available for Basic Select field type), you may able to add a default value for the lookup field
- Select the form which should serve as the source form. This is the form you will be referencing data from.
- Once you select a source form, another dropdown will be displayed to select the field from the source form to be used in triggering the autofilling. The field to be selected from the source form should have the data you are expecting your respondents to input in the lookup field i.e. Whatever a respondent inputs/selects on the lookup field will be used as the search term/criteria to search the selected field here on the source form for any matches
- This is the section to map fields from the source form to fields on the lookup (current) form, this is a very important part of setting up the lookup field as this is how the lookup field knows the other fields on the form it will need to populate with data from the source form. Once a match is found from the search described above, any fields mapped here will be automatically filled with information from the source form. To map fields, you will select a field in the dropdown from the lookup/current form being edited and map it to another selected field from the source form. Only fields supported to be auto-filled will be listed in the dropdown for the current form. The supported fields are: Short Text, Long Text, Labelled Text, Number, Email, Basic Select, Website, and Radio field.
- Click the 'Match Another Field' button to map another field on the lookup form to the source form as described above
- Option to make the field required. It is recommended that lookup fields are required as if data is not entered on the lookup field, other fields that have been mapped to be auto-filled will not be populated
- Click the 'Save' button to save your changes to the lookup field. If your lookup field setup is incomplete, there will be an indicator/warning message beside the field to let you know the 'Lookup setup is incomplete'
An example of an autofill lookup mapping is a Leave Request Form (lookup form) referencing data from an Employee Information Form (source form). When an employee joins an organisation, they fill out the employee information form with all the required details. Later on, when they need to fill another form say a Leave Request form, they do not need to enter all their details again. A lookup field will be added to Leave Request Form and the Employee ID can be used as the triggering field, then other information from the source form such as Employee Name, Employee Email, e.t.c. can be mapped. Once the employee enters their Employee ID on the Leave Request Form, every other matched detail on the form will automatically be displayed on the lookup form
The autofill lookup field type is case sensitive. This means it needs to be ensured that the data your respondents' input in the lookup field is in the exact case as it is on the source form. Our recommendations to avoid issues that may come with this include:
- When possible, use 'Basic Select' as the field type for the lookup field if the data to be inputted in the lookup field is not personal data/information. For example, if store information is to be looked up and the store name is the lookup/triggering field, since the store name is not a personal information, using the basic select field type means all the store names from the source form will be displayed in a dropdown and your respondents can select any store name without having to type in the data themselves.
- When possible, use numeric values such as IDs, phone numbers, as the lookup field, this eliminates the need to type in characters. If the ID to be used contains a constant character prefix, you can include the character prefix as a default value
- Before importing data, ensure all the data in the column you will be using as the triggering field are all in the same case you will expect them to be inputted.
- Add a placeholder or help text to the lookup field on the lookup form and the triggering field on the source form (if it is an active form), to let your respondents know the format in which they are to input text. For example, you can include an help-text that says 'Please type in your email in lowercase'
You can read this article to learn about the other things you can use the Lookup field for.