Multiple Forms choice questions to populate Lookup columns in Sharepoint

[rosellyn67]

I am trying to create a system where users submit a maintenance work request through Microsoft Forms, then a Power Automate Flow populates a SharePoint list with those responses. I then want to have those work requests visible in a section of a Power App, but that can wait until I've figured out the first part.

Here are the issues I am running into:

I am creating this for a multi-venue institution, so in order to know specifically where something is broken, users need to select Venue -> Gallery -> Exhibit. In my SharePoint data I have multiple lists: One called Venues, then one called Galleries which has a lookup column pulling from Venues to populate the variable Venue, then one called ExhibitList with a lookup column that is populated from Galleries for the variable Gallery. When a user submits a work request, I want them to only see the galleries for the venue they selected, so I created a series of branching questions (Choice type questions) in Forms. If you select Venue 1, then it brings you to a question with only the choices for galleries in that venue. Select Gallery 1, you get a question for exhibits in that gallery.

In my SharePoint list where I want all of the work requests to go (it is called Requests), I have lookup columns for Venue, Gallery, Exhibit that point back to my original lists. There are also columns for title and description of the problem, etc. When I am creating a Flow through Power Automate to populate the list from the form, however, I am running into two problems:

Because of the branching questions system I have in the form, I have multiple questions whose answers need to go into the same column in SharePoint. In my Create Items step (I used the Flow template for putting a forms response into SharePoint), when I put the dynamic content for multiple questions into the same item line, I get a flow check error to fix the invalid expression for the input parameters. Not knowing how to fix that, I temporarily removed Gallery 2 from the line to continue testing the flow.

The second problem has to do with the lookup columns having a different data type than the Forms input (I believe). The flow fails and I get a run error that says;

The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/Venue/Id' is required to be of type 'Integer/int64'. The runtime value '"Gallery A"' to be converted doesn't have the expected format 'Integer/int64'.

I don't know why the Venue lookup column is an Integer type in the first place, so don't know how to change that, not sure how to make the types match. Or is part of the issue that my Forms question is a Choice type?

I am also wondering if perhaps I don't need my columns (Venue, Gallery, Exhibit) in the Requests list to be lookup columns, as they would be populated with the answers from my Choice questions in Forms. But I want to eventually integrate it all into my Power App where you can select a specific exhibit, view any info on its parts, and also view any associated work requests. So I wasn't sure if keeping them as lookup columns would be a benefit later. Also, if I do need to change them to single line text, I was having difficulty figuring out how to do that in SharePoint (column type appears to be locked when I try to edit it).

Here is my Request list column data:

Thank you for any advice! I am new to Power platform so I would really appreciate clear step-by-step instructions for things that might be obvious for those with more experience 🙂