Update different data sources within a form

[ShadowTech]

Hello,

Scenario:

I have a gallery that has a Collection as the data source which contains a SQL view(read only). I would like to click on a record in the gallery which is bound to the Collection, open an edit form but be able to add SharePoint list columns to it and edit those. These SharePoint list columns correlate to fields in the SQL view.

Is this possible? If so how? If not, any options or work arounds?

Thanks in advance.

[Drrickryp]

Hi @ShadowTech 

You will need to have your SharePoint list as the datasource of the form and your Item property must use a unique field from your SQL table in the SharePoint list. The best approach would be to store the SQL ID as a number field in your SPlist and you could call it sqlID.  

Lookup(SPlist,sqlID=Gallery1.Selected.ID)

[ShadowTech]

@Drrickryp But that would entail matching up every SQL line item id with every record in SharePoint wouldn't it?

[Drrickryp]

@ShadowTech 

How do you currently relate the two tables?

[ShadowTech]

@Drrickryp They SQL View and SharePoint list are completely separate but there is an SO # in both Sources so I can do a lookup for a couple fields and place  in the SQL View gallery. Basically I would like to click on a record in the SQL View gallery and add those SharePoint fields to the edit form so I can update those fields all in one form.

The SQL view is read-only.

Thanks

[Drrickryp]

@ShadowTech 

That should be no problem.  If you are adding data from the SQL table in gallery to your SP list, you would set up the edit form to have the datasource as the SharePoint list.  Assuming the gallery is called Gallery1, the Item property of the form would be Lookup(SPlist,SO#=Gallery1.Selected.SO#). In each of the controls inside the card, you would make the Default property Gallery1.Selected.fieldname. where the fieldname matches the SPlist column.  The field doesn't have to be visible in the gallery.   If it is done correctly, the form should populate with the items in the gallery. I would disable the one that populates from the SO#  or hide it so the users can't change it. 

FYI, in a form, besides the Datasource and Item property, the only important properties are the default property of the control inside the card and the Update property of the card itself.

[ShadowTech]

@Drrickryp Question: The naming convention of the SO# fields are different in each data source but contain the same data. Does it matter or do the fields in both data sources (SQL View & SharePoint List) need to be the same field name?

Thanks

[Drrickryp]

@ShadowTech 

As long as the data types are the same I don't think the names matter.

[ShadowTech]

@Drrickryp Quick question...Is it possible to update multiple line items from a SharePoint list in a single form?

Example:

The SharePoint list has multiple Items with the below columns. Is it possible to update all in the date column that pertain to the value in the SO Number column in a single form?

Thanks in advance.

[Drrickryp]

@ShadowTech 

What you are describing appears to be a one to many relationship between the Order and the OrderDetails (Line Items). Typically, this would require a separate table for the Line Items.  In Powerapps, a single form can only apply to a single record.  I don't think you can do what you want to with a single form.  Possibly you could using a Patch() or UpdateIf() function in the OnSuccess property of the form to populate the Child table based on a formula. However, if all of the line items will have the same date as the order, then there is no need to have it in the line items table as it can be referenced from the orders table.  The screenshot below shows a design for Orders and Order Products where the Products are added to the order.