Answered

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

(0) Share

Report

Posted on 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

I have the same question (0)

All responses (6)

Answers (1)

v-jefferni on at

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,

Was this reply helpful? Yes No
steshepherd on at

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?

Was this reply helpful? Yes No
Verified answer

v-jefferni on at

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,

Was this reply helpful? Yes No
steshepherd on at

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

Was this reply helpful? Yes No
v-jefferni on at

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,

Was this reply helpful? Yes No
steshepherd on at

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.

Was this reply helpful? Yes No