Using a single Form to create new Excel documents each time its completed

[Carr2012]

Hi,

I am creating an onboarding form for my clients and am trying to get my form to create a new excel document upon each completed form.

The Excel doc has multiple tabs which also contain multiple tables, which I am also struggling to make fill properly (I am currently using 'Switch' in my flow.

Any guidance or assistance would be great.

thanks

[rzaneti]

Hi @Carr2012 ,

Could you please share an image from your current flow design? 

[Carr2012]

My assumption is that I have to many options in the Switch 'On' box, but I am unsure what to select as the different sheets are not dictated by answer, but rather by the detail required.

[rzaneti]

Hi @Carr2012 ,

The 'Switch' action is used as an 'IF' (imagine an 'Excel' formula 'IF'), but with different outcomes depending on the value of a variable. For example, if you have a variable called 'Quarter' that stores a number corresponding to the quarters of an year, you can use a switch to return the following outcomes:

- If 'Quarter' equals to 1, then 'Jan-Mar'

- If 'Quarter' equals to 2, then 'Apr-Jun'

- If 'Quarter' equals to 3, then 'Jul-Sep'

- If 'Quarter' equals to 4, then 'Oct-Dec'

Based on your description, you will use a 'Switch' only if one of the Form questions answer results in different ways to populate your Excel tables. If your condition to define which tables must be populated depends on more than one question answer, the flow design will become more complex, and a single 'Switch' may not be enough to handle it. 

Could you please provide some clarification about your process to populate these tables? Which questions are relevant to determine the tables to be populated? 

[Carr2012]

It sounds like I am using the wrong process in that case.

My Form is made up of 4 sections, 

I have a dynamic Excel document which has 4 sheets to mirror the 4 sections.

Within the 4 excel sheets are between 3 & 4 tables per sheet.

I am wanting my form to auto populate the answers in my Excel doc.

Additionally, I would like each completed form to create its own Excel document with the relevant sheets within it.

It is the latter point that I am most interested in an answer for.

Hopefully that makes it a little clearer.

Thanks

[rzaneti]

Hi @Carr2012 ,

Linking Form sections and Excel tables

Are these sections 'conditionally displayed' to the user? For example, let's assume that your company provides 4 different types of services (A, B, C and D) and the user, in one of the first Form questions, needs to check the services that they intend to contract. If the services B and C were checked, the Form will redirect them to fill the respect sections only, skipping the sections "A" and "B". 

If this is the case, you can use the 'Condition' action, one per each section, to identify which tables must be populated. Instead of insert all of section questions in your 'Condition' statement, you can include only one question and test if it is empty. If it is not empty, the user necessarily entered in that Form section, so the respective table must be populated. 

Creating an Excel file copy from a template

This task will be easier than populate the tables 🙂

First, add a 'Get File content' action (from either SharePoint or OneDrive connector, depending on where your template is stored) and search for your Excel template in 'File Identifier' input: 

Then insert a 'Create file' action (from either SharePoint or OneDrive connector, depending on where your want to save it) and specify the folder where you want to save it. In file name, make sure to include the file extension (.xlsx, for example). A good practice is also to include a unique name for the file; in the example below, I'm including user name, extracted from the Form submission. In 'File Content' input, insert the 'File content' dynamic content from 'Get file content' action.

Let me know if it works from your end or if you have any additional questions!

[Carr2012]

HI,

Thanks for the above.

I have found a workaround for the different sheets and got that working well.

I have followed the guidance for creating a copy file, however this keeps all of the original data in it too.

Is there a way for the created copy to only contain the new data.?

i.e. client 1 fills out the form and it creates an excel doc with the responses, then client 2 fills out the form and that creates a new excel doc with the new answers only

Thanks

[rzaneti · Accepted solution]

Hi @Carr2012 ,

To achieve it, you just need to change the order of your actions: first copy the Excel file and then populate it (I believe that you are currently populating the original file instead of the new one).

It will add a complexity related to the tables: as the file doesn't exist yet, Power Automate won't be able to automatically identify the existing tables and its columns. So if you take this approach, you may need to

1. Fetch all tables existing in the copied Excel file (you can achieve it with 'Get tables' action)
2. Include some 'Filter array' actions in these tables (filter it by name), in order to identify each one of them
3. When populating each of the tables with the 'Add row to a table' actions, you may use an object notation, where the column name will be an object key and the column value will be the data to be inserted. You can refer to the notation presented on the 'Update row' action from the accepted answer of this post: https://powerusers.microsoft.com/t5/Building-Flows/Update-a-Row-in-File-with-a-changing-name/m-p/236...  

Let me know if you need more clarification on the instructions above!!