I'm hoping that someone can help me with a problem concerning forms and workflow. I have been asked to create a form that is used to assess risk for certain activities, each with 3 levels, scoring from 1 to 6. The form would then continue into further sections, requiring additional detail around these risks and mitigation.
I started building the form. Most of it is fairly straightforward, but there are parts that require repeating sections. Normally, this wouldn't concern me because I would create a workflow to run on submission, which will process the all the XML chunks and format them as HTML in the list view.
However, the customer needs to be able to report on the various risks and each of their scores. They also need the list view to be filtered so that they can show how many locations have x number risks and with scores of a particular value or above etc. There is also a need to be able to export the list to Excel for other teams to use outside of the organisation. The repeating section data is not suitable for any of the usual SharePoint list view filtering or grouping methods.
I planned to create 2 lists and 2 form content types. List and form A captures the basic header info and the first repeating section. List and form B captures all the other risk mitigation and planning recommendations, but also contains a reference to items in list A. But I'm struggling to find a workable way of ending up with all the collected data preferrably in one primary list, that has list item values that can be used to filter or group. I need to try to avoid using mult-line text boxes because of this.
Can I appeal to the collective expertise here and ask what is the best way to try and achieve this please? Below is an example of the form I have, showing the initial part containing the repeating section.