Look Ups are integrations we get often. A Look Up is a set of fields in an OpenWater form that queries an outside source ( SalesForce, a Google Sheet, a CSV, etc), to autofill information for the user.
In this article, we'll go over the steps for setting up an Integrations Hub Look Up with a CSV.
1. Get Started with Integrations Hub.
2. Add a new Look Up connector from the Integrations Hub dashboard.
3. You will be brought to a page with different Look Up options. Select CSV.
4. You will now be shown the below screen. On the Autocomplete tab, you can add a Name (which can be anything- i.e. "Test Lookup"), and upload the CSV Autocomplete URL. You will click on “Select File” next to the CSV Autocomplete URL field, and select the CSV file that the Look Up will be utilizing from your computer.
5. You will now notice that columns from your CSV upload have now populated under the "Fields" section.
6. We will now add the Label, located in the Autocomplete settings to the left of the Fields section. The Label is what will display in the search results for the Look Up. For our example, I'm going to add First Name and Last Name - however, this feature comes in handy when your client wants their Look Up results to be "First and Last Name- Company" or any other additional fields to distinguish their results from one another.
The easiest way to add these is copying the field from the Fields section and then pasting them in the order you'd like in the Label field.
7. Next, in the same Autocomplete settings, we will add the Value. The Value is used in the background to get and prefill the needed fields. You can select any value that makes the most sense for your needs. In our case, I'm going to select "Employee ID" from the selection dropdown menu.
8. We will now add a Rule located under the Filter Settings. A Rule determines the search queries shown to you from the CSV you uploaded.
For our Rule, we will select First Name from the dropdown options (these options are the same as the Fields section). Then select Like (this means "contains"), and leave the last field as Query.
Example:
If we say "Name is like {query}" that means it's looking for any names similar to what the user typed in the search result. We could for example setup a more complex rule:
"Name" is like "{query}"
and
"Company" =! "OpenWater"
and
"Degree" == "Highschool" or "College"
This would filter results to be only those that have a name like the search, "OpenWater" as their company column, and "Highschool" or "College" as their degree column.
9. Now toggle to the Prefill Tab. You'll see a section called Prefill Settings. We will now add a Prefill Item. In this case, First Name and Last Name.
10. Select Add Prefill Item then Add Submission from the dropdown options.
11. Now you will see 2 fields populate- Alias and Value Field. The Alias is where you add the alias we use in the OpenWater form for the particular field we're wanting to connect. The Value Field is where you add the field we want to call from the uploaded CSV (and connect to that alias we just mentioned).
If you have more than one alias to add, click "Add Field" to populate additional alias rows. Once you add all of your table aliases, click Save at the top left of the page.
*** Not sure where to find/ what an alias is? Check out this article.
12. Now, let's test. Click Test on the top left of your screen, next to the Save button. You will now see the below popup appear. Type in a common letter, in this case for a First Name, and then select any result at random.
13. After you select a result, you should see something like the below, showing all successful fields for the result selected. Ensure that all fields you added to the Table Alias section are populating. If they are, your test has been successful.
14. Last, we will add the Autocomplete URL and Prefill URL to your Look Up form on your OpenWater instance. You can find these at the top of both the Autocomplete tab and the Prefill tab.
15. Once you have your Autocomplete URL and Prefill URL, navigate to your program form that will house the lookup. Next, select the field where the Look Up will occur and notice that the field settings will populate on the left-hand side. In these settings, check off the "Autocomplete from third party" and "Set field values from third party" options.
Below those checkboxes, you will see 2 open fields for URLs. Paste your Autocomplete URL into the field titled "Autocomplete from third party URL". Then paste your Prefill URL into the field titled "Set field values from third party URL".
Then save your form.
16. You have now completed building a Look Up in Integrations Hub from a CSV File.