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

[LiamP]

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.

[v-siky-msft · Accepted solution]

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.