cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DTooley
Advocate III
Advocate III

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
ScottShearer
Super User
Super User

@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
AlanPs1
Resident Rockstar
Resident Rockstar

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!


ScottShearer
Super User
Super User

@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

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

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!


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.

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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (3,446)