Answered

Patching a column containing a lookup to a SharePoint list ID

(0) Share

Report

Posted on by RL-26030740-0

Hi all

I am really struggling, having read various posts and watched various videos, I believe my code is correct, but not working and I am wondering if there is a strange SharePoint edge case here. I have restarted Chrome, removed and reconnected my data sources etc. etc.

I have 3 SharePoint lists: activities, projects and people. In my activities list, I use the ID of the projects table as my projectID and the ID of the people table as my personID, alongside various other fields. I have a gallery with editable fields which displays records filtered by date range and person. I use Patch functions - "Add Activity" adds a blank row which needs to be populated and "Save changes" iterates through the gallery where I have a Save toggle set to Yes (as per April Dunham's excellent video https://youtu.be/mj4mKS1hS7M?feature=shared).

To simplify the issue, I eventually hardcoded values into my "Add Activity" button OnSelect code:

Patch(

    activities,
    Defaults(activities),
    {
        personID:{
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpanded.Reference",
            Id: 25,
            Value: "25"
        },
        projectID: {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpanded.Reference",
            Id: 309,
            Value: "309"
        },
        WeekCommencing: ddWeekCommencing.Selected.Value,
        action: "",
        duration: 1
    }
);
Refresh(activities);
 
Both reference IDs are valid (entries exist). I have also tried replacing Value with the lookup for the title field (which is the person name) like this: 
LookUp(projects, ID = 309).Title (this works as a label)
 
I do not have any errors in the code, or at runtime, but the projectID and personID are never added:
 
Does anyone know what is happening here? I'd be grateful for any help or suggestions.
 
Regards
RL

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Power Platform 1919 2,217 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi, Can you please try:
something like this (this works with Dataverse, so I assume this works with share point also -- happy to be corrected).
Patch( activities, Defaults(activities), { personID:LookUp(persons,ID=25), projectID: LookUp(projects,ID=309), WeekCommencing: ddWeekCommencing.Selected.Value, action: "", duration: 1 } ); Refresh(activities);

Was this reply helpful? Yes No
Arun K Rajan 25 on at

Like (0)

Report
Copy link

Link copied!
HI,

In your Patch i can see you have used

"#Microsoft.Azure.Connectors.SharePoint.SPListExpanded.Reference"but can you please try with out the dot before References like this"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"

because if the type dosent match exactly it will drops the lookups payload and the item get created with out the lookup value.

try the below code and as a best practice sets value to the lookups display text

Patch(
activities,
Defaults(activities),
{
personID: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: 25,
Value: LookUp(people, ID = 25).Title // or whatever column your lookup shows
},
projectID: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: 309,
Value: LookUp(projects, ID = 309).Title
},
WeekCommencing: ddWeekCommencing.Selected.Value,
action: "",
duration: 1
}
);

and if it is a multi select try this.

projectID: Table(
{
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: 309,
Value: LookUp(projects, ID = 309).Title
}
)

Try this and let me know if you need any support.

1 people found this reply helpful.

Was this reply helpful? Yes No
RL-26030740-0 13 on at

Like (0)

Report
Copy link

Link copied!

Hi Power Apps 1919

Thanks - I get the following:

I originally had the values being entered via a dropdown in my gallery (items=projects), and I then tried a combobox ("more stable") to provide a record and finally resorted to the hardcoded values.

Was this reply helpful? Yes No
RL-26030740-0 13 on at

Like (0)

Report
Copy link

Link copied!

Hi Arun K Rajan

Thanks for the reply. I have tried this (I found the post you refer to) and it is still the same. I thought the Lookup for the Value would be a good idea, but it has not helped. The same Patch code is under the Save button where I have used he dropdown values, and has the same issue:

ForAll(

Filter(

galleryActivities.AllItems,

toggleSaveNeeded.Checked = true

),

Patch(

activities,

ThisRecord,

{

personID: {

Id: 25,

Value: ddPerson.Selected.fullname

},

projectID: {

Id: ddProject.Selected.ID,

Value: ddProject.Selected.project

},

action: txtAction.Value,

duration: numDuration.Value

}

)

)

Thanks

Ray

Was this reply helpful? Yes No
Verified answer

RL-26030740-0 13 on at

Like (0)

Report
Copy link

Link copied!

Hi all

I have come back after 4 hours off site and it now works. No code changes required... Seems like something was not working properly earlier, but it concerns me that this can happen. Here are the entries from earlier which suddenly have the personID and projectID populated.

I have further simplified the Patch to this:

Patch(

activities,

Defaults(activities),

{

personID: {

Id: 25,

Value: LookUp(people, ID = 25).Title

},

projectID: {

Id: 309,

Value: LookUp(projects, ID = 309).Title

},

WeekCommencing: ddWeekCommencing.Selected.Value,

action: "",

duration: 1

}

);

Refresh(activities);

and my Save button Patch is this:

ForAll(

Filter(

galleryActivities.AllItems,

toggleSaveNeeded.Checked = true

),

Patch(

activities,

ThisRecord,

{

personID: {

Id: ddPerson.Selected.ID,

Value: ddPerson.Selected.fullname

},

projectID: {

Id: ddProject.Selected.ID,

Value: ddProject.Selected.project

},

action: txtAction.Value,

duration: numDuration.Value

}

)

)

Both these now work, when failing yesterday and this morning. I'm unsure what has changed to make this happen...

Thanks for the suggestions!

Was this reply helpful? Yes No