Create Item failing because of Lookup field

[DTooley]

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.

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

[AlanPs1]

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

[ScottShearer · Accepted solution]

@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

[DTooley]

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?

[AlanPs1]

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

[DTooley]

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]

After many hours of digging , 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'])