- Basic Set Up
- File Configuration
- Link the Data Destination to Forms
- Fallback Locations
- Testing and Troubleshooting
The Microsoft Sharepoint Custom List Data Destination creates or updates rows in a custom list based on the data in a form submission. It uses the Office 365 Connection to connect to your Sharepoint Online.
This destination allows you to:
- Dynamically sort submitted data into different custom lists in your Sharepoint Online
- Map selected data from ProntoForms into specific columns in a custom list
- Automatically create new custom lists or columns as needed, based on a form submission
- and set rules to decide whether the destination should create a new row of data in a list, or update an existing row.
All data destinations automatically back up data collected in the field. They reduce the need to visit the web portal by making submitted forms available through the services you already use. Different documents can also be sent to different services with a single form submission. Read here for more information on data destinations.
Custom Lists are a part of Sharepoint Online in the Office 365 suite of tools and services. Custom Lists are a spreadsheet/data table used for hosting structured information in the cloud. Custom Lists are also available as a data source in ProntoForms, allowing for two-way integration, as well as the ability to update a data source using a form's submissions.
- Create the data destination following the instructions here.
- Destination Basics: Name the destination. Optionally choose to auto-link forms and documents to this destination.
- Add Filtering: With a filter set up, form submissions will only be sent through the destination if the filter is met. Read more here.
- Connection: Set up an Office 365 Connection or choose an existing connection.
- Set up the File Configuration tab as discussed below.
This tab is where you choose:
- which custom lists the data from form submissions will be added to
- which fields from your mobile forms will be sent to which columns in those lists
- the rules that determine if a row in a list should be created, or if an existing row should be updated
The site prefix is the part of your site path that tells us where the custom list you're trying to send form submission data to is, relative to the main site. There are three types it could be:
- / : The Sharepoint URL, e.g. https://companyname.sharepoint.com, aka the root site.
- /sites/: A sub-site to the main company Sharepoint, e.g. https://companyname.sharepoint.com/sites/SubSiteName
- /teams/: A team site created for a specific group of people on the main company Sharepoint, e.g. https://companyname.sharepoint.com/teams/TeamName
If you want to add or update data in an existing custom list, the List Path tells ProntoForms both exactly where the custom list is, and what the name of it is. If you want to create new lists when forms are submitted, the List Path tells Prontoforms exactly where to create the list and what to name it.
Use Data Record Expression Language to route different data into different custom lists.
- Health & Safety/Monthly Site Inspections
- This list path would send data from all form submissions to a custom list named "Monthly Site Inspections."
- Health & Safety/%f
- This list path would send data from different forms into different lists (%f uses the form name as the custom list name). For example, submissions for a form named "Monthly Inspection" would get sorted into a custom list named "Monthly Inspection," while submissions for a form named "Annual Inspection" would get sorted into a list named "Annual Inspection."
- Health & Safety/%a[Site Name]
- This list path would send data into different lists based on the "Site Name" provided in the form submission. If the form was submitted for "ABC Excavation Project", the data would go into a list with that name.
- %a[Project]/%a[Site Name]
- This list path would send data into different sites/subsites based on the "Project" provided in the form submission, and then into different lists within that site/subsite based on the "Site Name."
If the custom list does not exist yet, it will be created in the specified location. However, sites and subsites cannot be created by the data destination, so they must already exist. Please note, you do not have to add /List/ to your URL, unlike the Custom List Data Source.
Determining the List Path in Sharepoint Online
This section details how you find and enter the file path when setting up the Sharepoint Custom List Destination. You can find the URL for your list in your browser address bar, or in the Settings listing, as shown below.
Choose which files from your submissions to include as attachments in the custom list. List the questions by unique IDs, separating each ID with a comma.
Applicable question types are: Image, Sketchpad, Audio, File Attachment, or Signature.
Control which fields/columns in your Sharepoint Custom List should receive which answers from your forms. Each row you add to the table below maps form answers to a column in your list.
When mapping custom list fields, ensure that the data type chosen in the ProntoForms questions match the corresponding Custom List field's designated data type. For example, if mapping a currency value to the corresponding field, ensure you have selected the currency value when building the ProntoForms question.
Note: The data destination will always create a "ProntoForms ID" column in your list, and will fill it with the submission's ID (link). You do not need to configure this, and we do not recommend deleting or clearing this column in your list.
Custom List Field
This is the name of the column in your Microsoft custom list that you would like to fill with data from your form submissions.
If the column doesn't exist yet in your custom list, the data destination will add it after the first submission. It will be created with a "string" data type, which you can manually change later.
This is the data from ProntoForms that you would like to send to the custom list field. Type in static text, or use Data Record Expression Language (link) to reference answers from your form.
If the column already exists, ensure that the data you will be sending matches the data type of the column, or else the data destination will fail.
Unique Key (Using Submissions to Update Existing Rows)
Your unique key controls how the data destination decides if it should create a new row in your custom list, or update an existing row in your custom list.
This feature is useful if your form submissions should update the status of existing items -- like modifying an existing inspection, updating the status of a job, or changing the current location of a piece of equipment. This is commonly used to update a data source that is used in a form.
Important Information about Updating Custom Lists
If a row in your custom list is updated by the destination, the entire row in your list will be replaced. That can mean that data will be cleared.
- If a field in your custom list row has a value, but is updated by a submission that has a different value for that field, that field will be replaced with the newest value.
- If a field in your custom list row has a value, but is updated by a submission where this value is missing, that column will be cleared.
When to use a Unique Key
- If new form submissions should only ever create new rows in your list, do not choose a unique key.
- If new form submissions should update matching rows in your list (if a match exists), choose a unique key.
Configuring a Unique Key
The unique key tells the data destination what to look for in order to check if a new form submission matches an existing row in your custom list.
For example, in the above screenshot we marked both "Project Number" and "Site Name" as the unique key. This means that when a new form is submitted, the data destination will check if an existing row has a Project Number and Site Name that match the same fields in the new submission.
- If neither or only some of the unique key fields match, the destination will just create a new row in your list.
- If all unique key fields match, the matching row will be updated with the data from the new submission.
- If all unique key fields match multiple rows in your custom list, only the first matching row will be updated, not all matching rows.
Because of #3 above, it's essential that you build a unique key that is guaranteed to be unique in your custom list, otherwise it may update an incorrect row. Good fields to include in your unique key are identifiers like work order numbers, account ID's, Equipment ID's, etc.
Create vs. Update: Example Scenario
Below is an example of an existing custom list, and recall that the unique key is a combination of "Project Number" and "Site Name."
|Row #||Project Number||Site Name||Site Address||Inspection Score|
|1||123||Site ABC||123 Elm Street||95|
|2||456||Site DEF||456 Maple Lane||60|
|3||789||Site HIJ||789 Hemlock Drive||75|
- If I submitted a form that had the Project Number "234" and the Site Name "Site XYZ", a new row would be created in the list for this submission.
- If I submitted a form that had the Project Number "123" and the Site Name "Site DEF", a new row would be created in the list for this submission.
- If I submitted a form that had the Project Number "456" and the Site Name "Site DEF", Row # 2 would be updated with this submission, and no new row would be created.
Some scenarios other than a brand new form submission could trigger a row to be updated.
- Data Destinations sometimes need to be re-executed (link) for troubleshooting/support purposes, which re-sends the same data to your custom list.
- If you enable "submission editing" (link) on your forms, the data destination will send the new information each time the submission is edited, and overwrite the old submission.
If you have no unique key specified, these scenarios are handled by ProntoForms automatically. The record's ID in ProntoForms is used as the unique key, so that a form submission can only update itself in your custom list. If you specify a unique key, you are overriding this behaviour and are responsible for making sure these functions will still work.
If you have a unique key, submission editing is enabled on your forms, and you do not want a new row for each "edit", take care in creating your unique key, and use fields that are unlikely to be modified after the initial submission.
In order for the destination to send any submitted form data, it must first be connected to the form. This is done in the Data Destinations tab of the form builder.
If your site or subsite is incorrect but the Connection is functional, ProntoForms will still send the form submission to your Sharepoint site. If you cannot find a form submission in the target folder or document library, please:
- Check your file path to ensure the file prefix and folder expression are both correct.
- Check the following locations:
- the root site or base sharepoint URL (/) if a site is not found or provided.
- Documents (Shared%20Documents) if a document library is not found or provided.
Data destinations should be configured and tested carefully before using them in production. This is key to ensuring that ProntoForms data is received correctly. Please consult the recommendations for testing and troubleshooting data destinations.
We especially recommend in-depth testing if you have a unique key configured to allow updates to happen. If configured incorrectly, you may accidentally replace the wrong information.