Answered

Patch a collection to Sharepoint, where one of the columns is a Lookup

Like (0) Share

Report

Posted on 21 Sep 2023 12:14:50 by steshepherd

I'm struggling to work this one out. I've looked at how to patch to Sharepoint where there is a lookup column in the list. But I can't add the code into my patch, as I'm using ShowColumns. Let me explain

I've got a 'form' using the Gallery method, so a new item being added will create a blank record in the Sharepoint list. When fields are updated, this saves the information to a collection.

I'm creating the collection from Thisitem - so it stores all values from the Sharepoint list in the collection. So the Lookup column is saved as a table in the Collection.

When I come to Patch to Sharepoint, I am using ShowColumns - but the column I want to patch is a table, so I can't use

"Request ID"

I can't say "Request ID.Value"

I can't say

Request ID:{
 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
 Id:Lookup the Id,
 Value:Lookup the value
 }

So how to Patch the lookup column in Sharepoint?

Am I making this too complicated?

Thanks

Categories:

General Questions

All responses (6)

Answers (1)

steshepherd on 06 Oct 2023 at 11:14:54

Like (0)

Report

Re: Patch a collection to Sharepoint, where one of the columns is a Lookup

Hi,
Looking at the App again today - that code when I add it to the UpdateIf statement gives me an error:
Invalid argument type. Expecting a Record value, but of a different schema. The function 'UpdateIf' has some invalid arguments.
v-jefferni on 28 Sep 2023 at 02:52:59

Like (0)

Report
Re: Patch a collection to Sharepoint, where one of the columns is a Lookup

Hi @steshepherd ,

You have 'Request ID (From DSARB)' of type record in the collection, but it should be:

'Request ID (From DSARB)': {Id: LookUp(Requests, 'Request ID' = varCurrentRecord.'Request ID','ID'), Value:LookUp(Requests, 'Request ID' = varCurrentRecord.'Request ID','Request ID')}

I didn't see this column in ShowColumns function within Patch. If it's needed, just add this column into ShowColumns function.

Best regards,
steshepherd on 27 Sep 2023 at 10:32:45

Like (0)

Report
Re: Patch a collection to Sharepoint, where one of the columns is a Lookup

Hi,
Is this the patch or the update collection? I need to collect the information to the collection, then patch it
UpdateIf( colTriageDetails, ID = ThisItem.ID, { Integration: dpdIntegration.SelectedText, Database: dpdDatabase.SelectedText, Applications: dpdApps.SelectedText, Infrastructure: dpdInfra.SelectedText, 'Cyber Security': dpdCyber.SelectedText, 'Information Governance': dpdIG.SelectedText, 'Information & BI': dpdInformation.SelectedText, 'Patient Safety Priority': togPatientSafety.Value, 'Significant Financial Impact': togFinancial.Value, 'Regulatory compliance': togCompliance.Value, 'Scheduled plan': togSchedule.Value, 'Fault fix': togFault.Value, 'Prioritisation comment': inpPriorityComments.Text, 'Next Step': dpdNextStep.SelectedText, 'Next Steps Comment': inpNextStepComments.Text, 'Request ID (From DSARB)': {ID: LookUp(Requests, 'Request ID' = varCurrentRecord.'Request ID','ID') Value:LookUp(Requests, 'Request ID' = varCurrentRecord.'Request ID','Request ID')} })
Request ID (From DSARB) is expected a record - how can I give it that?

Once I have this in my collection, I am then just patching using

Patch( 'Request Assessment', ShowColumns( colTriageDetails, "Title", "Request_x0020_Name", "Summary", "Where_x0020_will_x0020_the_x0020", "How_x0020_much_x0020_budget_x002", "CIOApproved_x003f_", "Request_x0020_on_x0020_Plan_x002", "Are_x0020_the_x0020_requirements", "Is_x0020_there_x0020_a_x0020_cle", "ArchitectureImpactRating", "What_x0020_is_x0020_the_x0020_ri", "What_x0020_is_x0020_the_x0020_li", "Advancing_x0020_Our_x0020_Digita", "Collaborating_x0020_With_x0020_O", "Improving_x0020_Quality_x0020_Fo", "Making_x0020_Our_x0020_Money_x00", "Supporting_x0020_Our_x0020_Peopl", "Transforming_x0020_Our_x0020_Ser", "Trust_x0020_Strategy_x0020_Align", "A_x0020_Clear_x0020_Vision", "Simplicity_x0020_First", "Rule_x0020_of_x0020_One", "Invest_x0020_in_x0020_People", "Strategic_x0020_V_x0020_Tactical", "Clinically_x0020_led", "Embrace_x0020_Agile", "Integration_x0020__x0026__x0020_", "Database", "Applications", "Infrastructure", "Cyber_x0020_Security", "Information_x0020_Governance", "Information_x0020__x0026__x0020_", "Patient_x0020_Safety_x0020_Prior", "Significant_x0020_Financial_x002", "Regulatory_x0020_compliance", "Scheduled_x0020_plan", "Fault_x0020_fix", "Prioritisation_x0020_comment", "Next_x0020_Step", "Next_x0020_Steps_x0020_Comment", "ID", "Request_x0020_Status" )
But how would that work if I am patching a record from the collection to a lookup column in Sharepoint?

Thanks
Verified answer

v-jefferni on 22 Sep 2023 at 10:24:41

Like (0)

Report
Re: Patch a collection to Sharepoint, where one of the columns is a Lookup

Hi @steshepherd ,

Nowadays, "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference" is not needed any more when patching a LookUp column of SharePoint list. For a single selection LookUp column, the syntax is:

ColumnName:{Id:LookUpListID, Value:LookUpListColumnValue}

Please post the whole Patch function as well as ShowColumns function you are using in your App, so I can understand what you have done to your App to save values to LookUp column.

Best regards,
steshepherd on 22 Sep 2023 at 07:59:15

Like (0)

Report
Re: Patch a collection to Sharepoint, where one of the columns is a Lookup

Hi,
Thanks for taking the time to reply. I don't think I was clear enough in my explaination
I've got 2 lists in Sharepoint. These lists were created long before I decided to try and make an App. I have Requests and Request Assessments. They are 'related' to each other by having a unique ID - Request ID. This was a manually entered number that I would enter in each List. In the past I could use this to combine queries in PowerBi etc. I also still provide some functionality within Sharepoint where some of the information in the Request Assessments list 'looks up' information from the Requests List - for example I could pull through the overall status of the request. In Sharepoint I did this by creating a lookup column in Request Assessments, where I entered the Request ID (again!) but this time from the Request List - so I could shows Request ID (from Request List), Request Status (from Request List), etc.

Now I've created an App - I can add Requests & Request Assessments, Request IDs are calculated and all works fine...however, to maintain that functionality in Sharepoint, I still need to provide the Request Assessment list a value to enter into the Lookup column.

I can't just patch the Request ID to that column (I tried that initially, but it didn't work). I thought I had to use the
"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"
to patch to sharepoint lookup list? But that doesn't work as I can't put that into my Patch function as I am using ShowColumns

Are you saying I can just use a Lookup function to provide the Value/ID to the lookup column in Sharepoint?

Hope that helps?
v-jefferni on 22 Sep 2023 at 06:32:17

Like (0)

Report

Re: Patch a collection to Sharepoint, where one of the columns is a Lookup

Hi @steshepherd ,

It seems the Request ID column allows multiple selections (since tables in this column). If this is the case, then in the Patch function, syntax of this column could be:

{'Request ID':ForAll(ThisRecord.'Request ID' As RQ,{Id: Id:Lookup the Id, Value:Lookup the value})}

But one thing I want to know is, how do you update this column in collection? If you use a Combo box where Items is Choices(list.'Request ID'), select selections and save them into the collection, it would be easier to patch the collection to data source:

{'Request ID': ThisRecord.'Request ID'}

Best regards,