How to populate a SharePoint lookup column with the Item ID of another list

[Anonymous]

When a new item is created in a SharePoint list, I need to reference it to another list via that list's Item ID.

I have a collection that returns just one single value, which is that Item ID.

I get that value with 
First(colPM.Result).Result and display it in a TextInput control.

However, I can't find a way to get the Update function to work for that DataCard.

Using the Text property of the TextInput I get:

If I use the collection, same here:

Using just First(colPM.Result) returns a record, but it won't update the field with the ID number (it just is blank after submitting the form). Any suggestions what to try next? Thanks in advance.

[Nogueira1306]

First(colPM).Result

Does it gives you the same error?

Plus. If you need to update an item you can use Patch to update

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

[Anonymous]

@Nogueira1306 many thanks for the prompt answer, but no, that returns a number, not a record.

Why does it expect a record? Having a closer look I realized that the column I attempt to populate with the ID of the related item is a lookup column. So I will need {ID: x, Value: y} in my collection record, or somehow split it into ID and Value. Will give it a try and let you know the outcome.

[Anonymous]

@Nogueira1306 I gave it a try with different flavours of 

{
Value: First(colPM.Result),
ID: First(colPM).Result,
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"
}

but to no avail. No errors, the entries in the SharePoint list are created, but without the lookup column being populated, it's blank.

[Nogueira1306]

If result is a LookUp, it will return the id, name and every field. Try:

First(colPM).Result.ID

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

[Anonymous]

@Nogueira1306 I removed the Distinct() clause for the colPM collection (which returned just the ID), so it is now

ClearCollect(colPM,
            Filter(
            'Project Managers', 'Project Manager Name'.Email = User().Email));

Now instead of just the result (the number 97) I get a record, where the ID is 97.

There is no such thing as First(colPM).Result, there is First(colPM).ID though, which as well returns 97 as the pmID for the selected record in the collection. So now, how do I update the lookup column with this ID? To my understanding, I need to supply an ID and a Value. Assuming the list is 0 indexed with value 1 for ID, I tried the following for the update property of the pmID data card

{ID: First(colPM).ID - 1,
Value: First(colPM).ID,
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}

Again no error, item is created, but pmID field is blank. I tried with and without the -1 for the index, and with and without the @odata.type clause. What am I missing?

[Nogueira1306]

ClearCollect(colPM,
            LookUp(
            'Project Managers', 'Project Manager Name'.Email = User().Email).ID);

Does It work??

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

[WarrenBelz]

Hi @Anonymous ,

What type of field are you trying to update? It is obviously not Text. If is is a Choice column you need

{Value:TextInput1.Text}

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous]

@Nogueira1306 that returns a record with all fields blank except 'Value', not even ID is there.

I tried with 

ClearCollect(colPM,
            LookUp(
            'Project Managers', 'Project Manager Name'.Email = User().Email, ID);

but with the same result, a number in the value field. Now I need to update a SharePoint lookup column with that number, but the update requires a record, not a number. How to I construct that record so it updates the lookup field?

[Anonymous · Accepted solution]

@Nogueira1306 I finally figured it out. 

ClearCollect(colPM,
            Filter(
            'Project Managers', 'Project Manager Name'.Email = User().Email).ID);

We were actually very close, my mistake was to use ID:  instead of Id: in the update property of the lookup column's data card: 

Once corrected, it works

{
    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", 
    Id: First(colPM).ID, 
    Value: First(colPM).ID
}