One of several choices for maintaining a one-to-many data relationship in a SharePoint list is to use a multiline text column to store the child items in the parent list. The child items can be delimited somehow, likely by a carriage return. This provides one of the simplest ways to have child items related to a main SharePoint list.
One of several choices for maintaining a one-to-many data relationship in a SharePoint list is to use a multiline text column to store the child items in the parent list. The child items can be delimited somehow, likely by a carriage return. This provides one of the simplest ways to have child items related to a main SharePoint list.
The Known Allergies multiline textbox provides a simple way to record line items for the patient record
A disadvantage is that you need a workflow to maintain the list data if you plan on being able to work with the data directly in a SharePoint list outside of the form. Also, the data will still need to be parsed to read it in another workflow or to have aggregate calculations like sums. However, the good news is that the workflow to parse the child item data is considerably simpler than what is required when working with the Nintex Repeating Section control.
An advantage is that a plain-text display of the child items can be easily displayed within a SharePoint view and in a Nintex form.
In my simplest example, I have a Patients list and an Allergies list that will hold the known allergies for each patient. The allergies are stored in a multiline textbox in the Patient list and can be moved to the Allergies list later by a workflow. Be sure to set the multiline text column to plain text to avoid complications in managing what are intended to be simple carriage-return separated items.
The Patient list
How should an item be added? It’s entirely possible to simply type the allergies line-by-line in the textbox. However, you might be concerned that users will enter invalid or non-standard spellings of the items and you want to enforce standardization.
In my example, I’m using a combination of controls to enforce that the entries into the Known Allergies textbox come only from a pre-defined list of values in a drop-down box.
The Allergy dropdown list with standardized values
I’ve used a checkbox with the label “Add” to use as a makeshift “button” with a rule to append the selected allergy to the multiline textbox. Furthermore, I’ve made the multiline textbox disabled so that the known allergies can’t be edited directly.
The form rule for the Add checkbox appends the selected allergy to the multiline textbox
The form rule to append the selected allergy to the list of allergies in the multiline textbox does two things—it appends the single new allergy text to the existing text in the Known Allergies control, and at the same time it unchecks itself. In this way, the checkbox behaves as a button that responds when checked and then resets itself.
The full text of the formula to append the allergy to the existing allergies is as follows:
[Form].[Known Allergies]+[Form].[varCRLF]+[Form].[Allergy]
Notice that a line-feed variable is used to separate the previous entries from the new one. The variable is defined in the form variables tab as the string “\n” which is the escape character for a line feed.
The definition of the varCRLF variable
After the known allergies are recorded and the patient form is submitted, the allergies are listed nicely within the patient list.
Now let’s turn to making a simple workflow to extract the allergies into the Allergies child list. I configured the workflow on the Patients list to run when an item is created or modified. The workflow deletes the existing items related to the patient (by the Patient ID) before adding the allergies entered in the patient form. Using the Regular Expression action to turn the entries into a collection, each allergy is then inserted back into the Allergies list along with the Patient ID. Note that I use a Run If action to avoid adding a blank (I could’ve also accommodated this in the form, but that formula as I used it above doesn’t account for leaving out the line feed for the first allergy).
A workflow to put the known allergies into a separate SharePoint list
As with the formula on the form, the Regular Expression uses “\n” to signify a line feed as the splitting character. Notice how much simpler this workflow is compared to working with the XML from a Repeating Section control.
The configuration of the Regular Expression action used to split the known allergies from the Patient list into a collection
Now we have the allergies in the Allergies list and related back to the Patient list with the Patient ID foreign key! The advantage to having the allergies listed in a related list is that you can now build aggregate reports on your allergy data, for example, the most common allergies among your patient population.
The Allergies list after the workflow has run