cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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
Highlighted
Super User III
Super User III

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

View solution in original post

6 REPLIES 6
Highlighted
Resident Rockstar
Resident Rockstar

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!


Highlighted
Super User III
Super User III

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

View solution in original post

Highlighted
Advocate II
Advocate II

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

Highlighted
Resident Rockstar
Resident Rockstar

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!


Highlighted
Advocate II
Advocate II

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.

Highlighted
Helper II
Helper II

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
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

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

Top Solution Authors
Top Kudoed Authors
Users online (4,235)