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.
Take a peek at this thread -
You can use calculated values on your form that connect to fields in your repeating section that can count values or sum values based on your needs. For your scenario you will need multiple cv fields and then additional cv fields to sum the value of the value given in the repeating section.
Once you get to the total you want, connect that cv field to a list column that you can use as a filter on your list (if your field is not blank you can show as needed)
I was hoping to avoid having to create lots of additional columns in the main list if I can avoid it. The problem I have is that there are likely to be an increasing number of these activities, and I don't want to have to keep adding new fields and columns to the primary list. I have been looking at the SharePoint list view field, but not sure if there is any interaction with that, or how it will be output to the primary list?
I will read the article from your suggestion to see if there is a way I could adapt it in the meantime.