cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Multiple Form Responses into One SP List Lookup Column

I have a Form that collects the following information:

  • TestName
  • Option (single choice)
    • Option 1
    • Option 2
  • Category (single choice)
    • Category 1
    • Category 2

 

Trying to create a flow that places the option AND category responses into one lookup column in a SP list.

TestName          Tags

Test01                Option 1, Category 2

 

Here's how my flow is setup:

  • When a new response is submitted
  • Get response details
  • Create item
    • Site Address: SPurl
    • List Name: Name of List
    • TestName: (dynamic) TestName
    • Tags Id - 1: (dynamic) Option
    • Tags Id - 2: (dynamic) Category

Everything saves proper, but when I run the flow here is the error I get:

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

 

I've tried these expressions with no success:

  • replace(string(outputs('Get_response_details')?['body/r1e6d366ea60a464c8866df9b6ac5270e']')),'"', '')

  • replace(replace(replace(body('Get_response_details')?['r1e6d366ea60a464c8866df9b6ac5270e'],'","','|'), '["',''), '"]','')

  • int(outputs('Get_response_details')?['body/r1e6d366ea60a464c8866df9b6ac5270e'])

There are a lot of solutions for splitting multi-choice form responses into single-line SP list columns, but I can't find anything about placing Form responses into a lookup field.  I could really use some help 🙂

3 REPLIES 3
Microsoft
Microsoft

Hi @millersmasters 

 

Actually, when you want to fill in a lookup column in a list (namely the secondary list whose lookup item gets its content from primary list) in sharepoint using Power Automate, you will have to fill in the item ID of primary list. So the error says you need to fill in an integer in the lookup column in ‘Create item’.

 

Also, if the content you want to fill in the lookup column doesn’t match with any value of the relating column from the primary list. You cannot fill in the lookup column. The content you can fill in a lookup item must match with one of the values of the relating column from the primary list.

 

Could you tell me whether the content you want to fill in match with one of the values of the relating column from the primary list? If they match with each other, then it’s possible to fill in a lookup column.

 

The following steps are for your reference:

 

The primary list (test1) and secondary list (test2):

2.23-1.PNG2.23-2.PNG

 

The whole flow is as below:

2.23-3.PNG

 

The detailed steps for ‘composes’:

2.23-5.PNG

 

The detailed step for ‘Get items’:

2.23-4.PNG

The ‘Filter Query’ above:

event eq '@{concat(outputs('Compose'),'-',outputs('Compose_2'))}'

 

For more info about concat(): Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

The detailed step for ‘Apply to each’:

2.23-6.PNG

 

The result of the flow in secondary list (test2) is:

2.23-7.PNG

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-yiwenxie-msft for your response!  The content I want to fill in from the Form IS a perfect match to items listed in the primary list.

Here is the error I am getting and I don't know enough about filter queries to know what to replace "event" with in the "Get items" step.

Column 'event' does not exist. It may have been deleted by another user.
clientRequestId: da41b677-5cf1-4b90-b176-bfbc1baf0ec4
serviceRequestId: da41b677-5cf1-4b90-b176-bfbc1baf0ec4

Microsoft
Microsoft

Hi @millersmasters 

 

In 'Filter Query', the 'event' is my column field name. You can find it in URL of the column as below:

2.24-6.PNG

 

In my case, even the column display name is 'option-category', actually this column's field name is 'event'.

So you can find your column field name as above to fit in your case.

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (64,709)