Form Entry saves inputs to separate list

[scubacamper]

This is my first App.  I've finally been able to make it successful but now I'm wondering:  Is there a way to have the form entries saved to a separate list (or table)?

At this point, I have 2 lists I'm pulling from:

• List A:  "Packaging Supply Types"
  • Shows main categories of supply types for cascading item selection
• List B:  "Packaging Supplies"
  • Shows all the column headings for each type of Supply (including corresponding Types, to join with List A)
  • In my app, only one of these fields is "editable" - the "Quantity On-Hand" field.  The rest are limited to choices made on primary screens.

When the form is submitted, it's adding records to List B.  Can I set the app to save the records to another list or table somewhere?  I want to be able to keep List B as a "reference" chart (for the cascading item selections) instead of a growing database.

I'm using SharePoint Online for this.

[rbrooks66]

I have a form with reference lists tied to SQL tables for Location, Category, SubCategory, Client, and Charity.  Each of those tables exist in an Azure SQL DB in the cloud.  I then used a Stored Procedure (using Power Automate) to write the data out to a staging table in the same database.  Here is a link to how to use a Stored Procedure: https://www.mssqltips.com/sqlservertip/6857/call-sql-server-stored-procedure-from-power-apps/

I then process the staging table before adding it to my main table.

[zmorek]

Sure, use the OnSuccess() property of your form to add a Patch() statement; you'll just need a way to associate the two lists (unique ID column). Your OnSuccess() property will look something like:

Patch('Your Separate List Name',

LookUp('Your Separate List Name', IDColumnToMatchListB = FormName.LastSubmit.ID,

{ColumnNameToUpdateA: DataCardValue.Text, ColumnNameToUpdateB: DataCardValue2.Text}

)

)

Your patch statement will likely change a bit based on whatever the data card values are, but that's the basic idea.

[scubacamper]

oops, I meant to mention that - I do have a power automate step which collects any new record from the List and adds it to a separate Table within an Excel workbook.

[scubacamper]

@zmorek 

If my OnSuccess field already has a function in it (navigate()), I can append this field with the ";" symbol, correct?

Secondly, before I update the OnSuccess field with the Patch() language, what is the best way to set up the "final destination" workbook (the one which will house the form entries)?  I was envisioning this as an excel workbook, but will it have to be a List? Table? Dataverse?

[scubacamper]

I added the Patch script (see below), with partial success:

The app is saving the new form entries to my new List ("Packaging Supplies Usage"), but it's recording the 'count' entries as zeroes (Qty On Hand, Quantity Received).

I went to look at the original List ("Packaging Supplies") and it's still saving the form entries, but including the 'count' entries with the values that were entered within the form.

Here is the script for the 'Count Entry' form:

Navigate(SuccessScreen,ScreenTransition.Cover);
Patch(
'Packaging Supplies Usage',
Defaults('Packaging Supplies Usage'),
{

Material_x0020_Type:MaterialTypeGallery.Selected.Title,
Title:ItemsListItem.Selected.Title,
PartNo_x002e_:ItemsListItem.Selected.PartNo_x002e_,
UOM:ItemsListItem.Selected.UOM,
QtyperUOM:ItemsListItem.Selected.QtyperUOM,
PrimaryVendor:ItemsListItem.Selected.PrimaryVendor,
QtyOn_x002d_Hand:Value(DataCardValue25.Text),
QuantityReceived:Value(DataCardValue1.Text),
CountedBy_x003a_:DataCardValue12.Text

})

How can I get the patch to pull the form input values as well as the other "selected" values?

EDIT:  The form entry control is still assigned to the original Data Source (List: Packaging Supplies).