cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MartinN1
Frequent Visitor

Collecting data with patch result

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. 

MartinN1_0-1639402056676.png

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

7 REPLIES 7
MartinN1
Frequent Visitor

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
Super User
Super User

 

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
Frequent Visitor

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

MartinN1_0-1639404613865.png

 

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
Frequent Visitor

Hi Stalin,

The first database is correct:

MartinN1_0-1639405778477.png

 

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

MartinN1_1-1639405811749.png

 

DB:

MartinN1_2-1639405834209.png

 

 

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
Frequent Visitor

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,787)