cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Patch Records to temporary collection, and patch again to sharepoint lookup/person field

Hi all,

 

I've been building up an app based loosely around @Shanescows youtube videos for PowerApps repeating tables (like InfoPath).

My app is similar in that we have the following:

 

  1. Button to create new 'expense' - creates new form, and new collection with columns as required, navigates to next screen

 

 

NewForm(Form1);
ClearCollect(
    ExpenseCollection,
    {
        LineDescription: "",
        LineCost: 0,
        LineCostinclGST: 0,
        LineCostexclGST: 0,
        LineCostGST: 0,
        LineCurrentUser: User().FullName,
        LineApprover: "",
        LineCostCentre: "",
        LineProject: "",
        LineJobRelated: "True",
        LineGSTincl: "True"
    }
);
Navigate(
    NewReimbursement,
    ScreenTransition.Fade
)​

 

 

  • Form (linked to sharepoint list 1) and gallery for individual entries (which patch individually to the collection by a 'commit' button:

 

 

Patch(
    ExpenseCollection,
    ThisItem,
    {
        LineDescription: InputDescription.Text,
        LineCost: Value(InputCost.Text),
        LineApprover: InputApprover.Selected.Mail,
        LineProject: InputProject.Selected.'Project Number',
        LineCostCentre: InputCostCentre.Text,
        LineJobRelated: JobToggle.Value,
        LineGSTincl: GSTToggle.Value,
        LineCostexclGST: Value(AmountExclGST.Text),
        LineCostinclGST: Value(AmountInclGST.Text),
        LineCostGST: Value(GST.Text)
    }
);
Collect(
    ExpenseCollection,
    {
        LineDescription: "",
        LineCost: 0,
        LineCostinclGST: 0,
        LineCostexclGST: 0,
        LineCostGST: 0,
        LineApprover: "",
        LineCostCentre: "",
        LineProject: "",
        LineCurrentUser: User().FullName,
        LineJobRelated: "True",
        LineGSTincl: "True"
    }
)​

 

 

  • Save button which submits Form (SP list 1), and patches collection data to SP list 2

Trouble is, I have included two combo boxes within the gallery that look to external sources for their data. I have an 'Approver' (O365 users) field 'InputApprover' that we're using the 'Mail' component of the record, and a 'Project' field, which references items in another SP list (call this SP list 3).

When patching the data from the gallery to the collection, I am only able to get these to patch as text. When saving (submitting the form and patching the collection to SP list 2), it falls over because it expects a type 'record', but is text.

 

Does anyone have any ideas how to patch a SP record to a collection, and then patch again to SharePoint?

 

Sorry if this is a bit vague - I've been doing this for all of a couple of days.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Patch Records to temporary collection, and patch again to sharepoint lookup/person field

Hi @LiamP ,

 

I think you should post the formulas of patching collection data to SP list 2, so that I can give the most accurate workaround.

 

First, let's simplify the issue!

Two column in you sharepoint list: a person column( InputApprover) and a lookup column(Project). 

You save the text value of two columns, and you want to know to use these two text value to patch to those two columns, right?

 

1.  To patch person column of SharePoint, you have to patch a record which contains "Claims", "Department", "DisplayName", "Email", "JobTitle", "Picture" columns. 

2.  To patch LookUp field of SharePoint,  you have to patch a record containing '@odata.type'Id and Value.

3.  To patch the collection to SP list2, please refer to the following expressions: (just for reference, modify it to fit your app)

 

ForAll(ExpenseCollection,
Patch('SP list 2',Defaults('SP list 2'),
{
InputApprover:
{
Claims:"i:0#.f|membership|" & LineApprover,
Department:"",
DisplayName: "",
Email: "",
JobTitle:"",
Picture:""
},
Project:
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: LookUp('SP list 3', 'Project Number' = LineProject, ID),
Value: LineProject
}
})

 

Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Patch Records to temporary collection, and patch again to sharepoint lookup/person field

Hi @LiamP ,

 

I think you should post the formulas of patching collection data to SP list 2, so that I can give the most accurate workaround.

 

First, let's simplify the issue!

Two column in you sharepoint list: a person column( InputApprover) and a lookup column(Project). 

You save the text value of two columns, and you want to know to use these two text value to patch to those two columns, right?

 

1.  To patch person column of SharePoint, you have to patch a record which contains "Claims", "Department", "DisplayName", "Email", "JobTitle", "Picture" columns. 

2.  To patch LookUp field of SharePoint,  you have to patch a record containing '@odata.type'Id and Value.

3.  To patch the collection to SP list2, please refer to the following expressions: (just for reference, modify it to fit your app)

 

ForAll(ExpenseCollection,
Patch('SP list 2',Defaults('SP list 2'),
{
InputApprover:
{
Claims:"i:0#.f|membership|" & LineApprover,
Department:"",
DisplayName: "",
Email: "",
JobTitle:"",
Picture:""
},
Project:
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: LookUp('SP list 3', 'Project Number' = LineProject, ID),
Value: LineProject
}
})

 

Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,743)