cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DTooley
Level: Powered On

Create Item failing because of Lookup field

 

I am archiving items from one list (Source) to another list (Archive) using the create item action.  Both lists are exact copies of each other. 

 

The create item fails because of a lookup column.  When the Confidence Score ID field is left blank, the create works.  If I select the field, then Source items where the Confidence Score is empty, cause the create item to error out .   Items with something in the field, will create an Archive item.

 

Lookup field.png

 

I receive this error message:

{  "error": {
    "code": 502,
    "source": "flow-apim-msmanaged-na-northcentralus-01.azure-apim.net",
    "clientRequestId": "b0db328d-394c-4606-9c42-11eeedb1ec02",
    "message": "BadGateway",
    "innerError": {
      "status": 500,
      "message": "Error converting value {null} to type 'System.Int64'. Path 'Id', line 2, position 13.\r\nclientRequestId: b0db328d-394c-4606-9c42-11eeedb1ec02\r\nserviceRequestId: 8a36c99e-b098-8000-1de4-6cee65952193"    }  } }

 

Any thoughts on why this is happening and what can be done so that an item is written regardless of Confidence Score having data or not.

 

Thanks!!!

Dave

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Create Item failing because of Lookup field

@DTooley :

I was able to reproduce your error when there was nothing selected in the lookup column.  I haven't yet figured out a way to handle sending a null value.

 

My work around is to add an additional lookup value to the looup list.  I added a value caleed "Nothing Selected" and the ID of this item is 4.   I successfully used the following expression when setting the value of a lookup column (called LookupCol) in the create item action:

if(equals(body('Get_item')?['LookupCol']?['Id'], null),4,body('Get_item')?['LookupCol']?['Id'])

 

The expression sets the value to 4 if the lookup column is empty in the original list.  Otherwise, the ID of the lookup column entry is used.

 

If this works for you, please mark your post as Solved.

If you like my solution, please give it a Thumbs Up.

 

Scott

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
6 REPLIES 6
Super User
Super User

Re: Create Item failing because of Lookup field

Hi @DTooley , thank you for your post.

Your error states: "Error converting value {null} to type 'System.Int64'"

I would say "Confidence Score ID" is either null, or needs wrapping in int() perhaps. 

Assuming it is not null (it may be, I can't tell), all "numbers" are interpereted as a string unless told otherwise so I'd suggest you wrap the dynamic content for "Confidence Score ID" within int() as an expression.

 

If you have found my post helpful, please mark thumbs up.

Any other questions, just ask.

Thanks, Alan


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Super User
Super User

Re: Create Item failing because of Lookup field

@DTooley :

I was able to reproduce your error when there was nothing selected in the lookup column.  I haven't yet figured out a way to handle sending a null value.

 

My work around is to add an additional lookup value to the looup list.  I added a value caleed "Nothing Selected" and the ID of this item is 4.   I successfully used the following expression when setting the value of a lookup column (called LookupCol) in the create item action:

if(equals(body('Get_item')?['LookupCol']?['Id'], null),4,body('Get_item')?['LookupCol']?['Id'])

 

The expression sets the value to 4 if the lookup column is empty in the original list.  Otherwise, the ID of the lookup column entry is used.

 

If this works for you, please mark your post as Solved.

If you like my solution, please give it a Thumbs Up.

 

Scott

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
DTooley
Level: Powered On

Re: Create Item failing because of Lookup field

AlanPs1,

Thank you for responding.  I wish I could report that your solution works.  When I attempt to create the expression using int(), the field I need is not available.  Note the see more option is not there either.

 

Or am I not doing this correctly?

 

Conf score ID.png

Super User
Super User

Re: Create Item failing because of Lookup field

Hi @DTooley .

In direct answer to your last question,

I can't be certain this is the solution but if it were I doing this, I would remove int() temporarily, re-add the "field you need" and use peek code under elipses (...). you will be able to locate the name of the value and copy and paste it, or hover over it and take a note. Once you have it copied, then paste it into int() ... perhaps.

 

Although ... Scott has said "My work around is to add an additional lookup value to the looup list". I would follow what he has offered on this one as he says it works for him.

He has added the expression for you to copy and paste also so pleasse try that (either) and let us know how you have gotten on please.

 

Thanks, Alan


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


DTooley
Level: Powered On

Re: Create Item failing because of Lookup field

Scott,

Thank you for your alternate solution.  I have not tried it myself as the user I was assisting, chose to leave the field blank in the recieving list.  It makes sense for their unique sitution.

 

However, this is an ongoing issue that will affect others.  I am confident the solution will work and I will try it out as soon as I can.  In the meantime, I will mark it as a solution.

FrancisL
Level: Powered On

Re: Create Item failing because of Lookup field

After many hours of digging Smiley Frustrated , I finally found that putting a zero works to set null. If only Microsoft would help us in these matters, it would save us so much time.
 
Simply remove quotes and replace with a 0.
 
if(equals(body('Get_item')?['Commodity']?['Id'],null),0,body('Get_item')?['Commodity']?['Id'])

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

thirdImage

New Flow Community Board!

Check out the new Microsoft Flow Community Blog Topic Suggestion board!

fourthImage

Australia Summit

Travel to Melbourne and network with thousands of peers!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 77 members 4,331 guests
Please welcome our newest community members: