Collecting data with patch result

[MartinN1]

Hello community,

I have no clue how to solve my issue with the collection. I

My code has 2 parts. Patch function is putting general information to my DB, where I got request type, who raised it etc. The second part is, that user can put multiple items based on the requirements, that should be entered into another SQL table (standardization), however I faced here the issue. 

If I use my code below, it will not only create 1 record in my first DB('Customer.Ref_app'), but when user enter e.g. 10 invoices, it will create 10 records each with its unique ID (same on 'Customer.Ref_app_reaging'). 

Collect('Customer.Ref_app_reaging',

             AddColumns(

                       "RequestID",

Patch('Customer.Ref_app',
Defaults('Customer.Ref_app'),
{
Request_ID: Int("101"&(Mid(Last('Customer.Ref_app').Request_ID,4,10) +1))
, Request_type: "Re-aging"
, Customer_Number: TextInput_CustomerNumber_reaging.Text
, Customer_Name: If(varEDW, Label_CustomerNameEDW_reaging.Text, TextInput_CustomerName_reaging.Text)
, Company_Code: If(varEDW, Left(ComboBox_EDW_SalesOrg_reaging.Selected.VKORG,2), ComboBox_LEY_SalesOrg_reaging.Selected.SO)
, Sales_Org: If(varEDW, ComboBox_EDW_SalesOrg_reaging.Selected.VKORG, ComboBox_LEY_SalesOrg_reaging.Selected.SO)
, Dist_channel: If(varEDW, Label_EDW_DistChnnl_reaging.Text, ComboBox_LEY_DistChnnl_reaging.Selected.Value)
, Division: If(varEDW, "00", "")
, FAM_Code: If(varEDW, Label_EDW_FAM_reaging.Text, Label_LEY_FAM_reaging.Text)
, Status: "Waiting for Approval"
, Created: Now()
, Creator: varUser
}
).Request_ID))

Patch is working correctly, however I am not sure, how to enter the data to Customer.Ref_app_reaging with the same ID Patch return + with all items entered by the user. Do you have any idea?

Thank you

[MartinN1]

Clear(Col_Reaging_request);

ForAll(Gallery_Reaging.AllItems,
    Collect(
        Col_Reaging_request,
        {Invoice: TextInput_Invoice_reaging.Text
        ,Amount:  TextInput_Amount_reaging.Text
        ,Currency: ComboBox_Currency_reaging.Selected.Value
        ,Net_due_date: DatePicker_NetDueDate_reaging.SelectedDate
        ,New_due_date: DatePicker_NewDueDate_reaging.SelectedDate
        ,Old_PT: TextInput_OldPT_reaging.Text
        ,New_PT: TextInput_NewPT_reaging.Text}
    )
);

Collect('Customer.Ref_app_reaging', 
        AddColumns('Customer.Ref_app_reaging',
            "RequestID",
            Patch('Customer.Ref_app',
                Defaults('Customer.Ref_app'),
                {
                       Request_ID:          Int("101"&(Mid(Last('Customer.Ref_app').Request_ID,4,10) +1))
                    ,  Request_type:        "Re-aging"
                    ,  Customer_Number:     TextInput_CustomerNumber_reaging.Text
                    ,  Customer_Name:       If(varEDW, Label_CustomerNameEDW_reaging.Text, TextInput_CustomerName_reaging.Text)
                    ,  Company_Code:        If(varEDW, Left(ComboBox_EDW_SalesOrg_reaging.Selected.VKORG,2), ComboBox_LEY_SalesOrg_reaging.Selected.SO)
                    ,  Sales_Org:           If(varEDW, ComboBox_EDW_SalesOrg_reaging.Selected.VKORG, ComboBox_LEY_SalesOrg_reaging.Selected.SO)
                    ,  Dist_channel:        If(varEDW, Label_EDW_DistChnnl_reaging.Text, ComboBox_LEY_DistChnnl_reaging.Selected.Value)
                    ,  Division:            If(varEDW, "00", "")
                    ,  FAM_Code:            If(varEDW, Label_EDW_FAM_reaging.Text, Label_LEY_FAM_reaging.Text)
                    ,  Status:              "Waiting for Approval"
                    ,  Created:             Now()
                    ,  Creator:             varUser
                }
                ).Request_ID));

Adding whole code inc. the collection where I am adding the column

[StalinPonnusamy]

Get the ID from the first master/Main Function and use that new id in second patch function

Set(NewRefAppID,
Patch('Customer.Ref_app',
Defaults('Customer.Ref_app'),
{
Request_ID: Int("101"&(Mid(Last('Customer.Ref_app').Request_ID,4,10) +1))
, Request_type: "Re-aging"
, Customer_Number: TextInput_CustomerNumber_reaging.Text
, Customer_Name: If(varEDW, Label_CustomerNameEDW_reaging.Text, TextInput_CustomerName_reaging.Text)
, Company_Code: If(varEDW, Left(ComboBox_EDW_SalesOrg_reaging.Selected.VKORG,2), ComboBox_LEY_SalesOrg_reaging.Selected.SO)
, Sales_Org: If(varEDW, ComboBox_EDW_SalesOrg_reaging.Selected.VKORG, ComboBox_LEY_SalesOrg_reaging.Selected.SO)
, Dist_channel: If(varEDW, Label_EDW_DistChnnl_reaging.Text, ComboBox_LEY_DistChnnl_reaging.Selected.Value)
, Division: If(varEDW, "00", "")
, FAM_Code: If(varEDW, Label_EDW_FAM_reaging.Text, Label_LEY_FAM_reaging.Text)
, Status: "Waiting for Approval"
, Created: Now()
, Creator: varUser
}
).Request_ID);

//Second Patch Function
Patch('Customer.Ref_app_reaging', 
    ForAll(Gallery1.AllItems As Source, 
        Patch(Source, 
        {
          RequestID: NewRefAppID, //Value from Patch 1
          ColumnName: Value(Source.fieldName.Text)
        }
        )
    )
)

 
Thanks,
Stalin - Learn To Illuminate

[MartinN1]

Hi, I tried the code, but got the error below:

[StalinPonnusamy]

Hi @MartinN1 

Please try this

//Second Patch Function
    ForAll(Gallery1.AllItems, 
        Patch('Customer.Ref_app_reaging',
        Defaults('Customer.Ref_app_reaging'),
        {
          RequestID: NewRefAppID,
          Invoice: TextInput_Invoice_reaging.Text,
          Amount: TextInput_Amount_reaging.Text,
          Currency: ComboBox_Currency_reaging.Selected.Value
        }
        )
    )

Thanks,
Stalin - Learn To Illuminate

[MartinN1]

Hi Stalin,

The first database is correct:

However I enterd 2 items and it uploaded only the last one of them:

DB:

[StalinPonnusamy · Accepted solution]

I'm not sure you are looping Gallery items like

//Second Patch Function
    ForAll(Gallery_Reaging.AllItems as Source, 
        Patch('Customer.Ref_app_reaging',
        Defaults('Customer.Ref_app_reaging'),
        {
          RequestID: NewRefAppID
        ,Invoice: Source.TextInput_Invoice_reaging.Text
        ,Amount:  Source.TextInput_Amount_reaging.Text
        ,Currency: Source.ComboBox_Currency_reaging.Selected.Value
        ,Net_due_date: Source.DatePicker_NetDueDate_reaging.SelectedDate
        ,New_due_date: Source.DatePicker_NewDueDate_reaging.SelectedDate
        ,Old_PT: Source.TextInput_OldPT_reaging.Text
        ,New_PT: Source.TextInput_NewPT_reaging.Text
        }
        )
    )

And side note, If you collect your collection directly from the DataSource i.e  Collect(yourCollection, filter(yourDatasource, ctrieria))

And you are directly working with the schema of the records and not altering them, then you can simply reverse the process to push back to the data source in a patch statement.

i.e.  Patch(yourDataSource, yourCollection)

Any records that do not have primary keys (new records) will be created and any records with a primary key will be updated all based on the contents of the collection.

[MartinN1]

Hi Stalin,

thank you for your help. The issue was not with PowerApp itself, but with the primary key in SQL as I was trying to create multiple primary keys with the same value. I created new column as primary key (just ID) and it work perfectly!

Thank you for your help