Creating Client-Specific Sequential Numbering from Form Responses

Microsoft Forms is a fantastic way to capture data from internal and external sources. One of the really powerful things about creating Group Forms is that the results are saved to a cloud Excel file in the group's document library.

One of our clients needed some support in extending this Excel file with an approval status and sequential invoice number. (For specific reasons, they didn't want to use a SharePoint list at this time).

Here's how we did it.

First, we create a simple watcher to look for when a new form is submitted by one of their staff.

We found that you need to put in a do until to refresh the table and wait until the workflow picks up that the new row is in Excel. So we set a Do until action waiting until a row with the response ID matched the Row ID in Excel.

Once found, we wanted to list all the rows in the Excel file. Again, through testing, we found that we needed to refresh the grab of the attached table to get all rows.

We used a filter array to get only the rows that matched the Brand (Customer Name)

We then needed to count the number of items in the array. This is how we can understand what the next invoice number needs to be.

length(variables('CountArray'))

The above two actions return an integer value of the number of rows pick up.

We then needed to do some formatting to get the invoice number in the format 00001

e.g. 5 digits long with leading zeros.

length(variables('varInvoiceFormat'))

substring(variables('varInvoiceFormat'),sub(variables('varLength'),5),5)

Once doing the above, we get the result we wanted... e.g. 00003

We can now update the Excel file with the latest information. We set the invoice number to be the Customer Name - Invoice Number

We haven't shown the approval stage here. But we did include a link back to the response in Microsoft Forms by using the below.

sub(triggerOutputs()?['body/resourceData/responseId'],1)

And that's it! Now each customer has a sequential invoice number for each form submission.

Here are the results in the Excel table attached to the Microsoft Form