Patch from Collection to SharePoint List only working partially (1 of 3 fields patching, 2 not patching)

[AchalDesai]

Hi All,

I work in an ecommerce company and trying to create an app to manage our Lost Shipment Claims process.

I have a gallery connected to a SharePoint List. The aim of this gallery is to display current status of a lost shipment and take 3 inputs back to the SharePoint list which are given by the user. As seen in the image below, the users can filter the gallery based on the search boxes provided. The goal is to then take input on the 3 field - Credit Currency, Credit Amount, Update Carrier Status and patch it to the SharePoint List. The problem I am having is that the patch is working partially. The Update Carrier Status value gets correctly patched but the values of Credit Currency and Credit Amount dont.

The Credit Currency field is a Combo Box, Credit Amount field is a text input box and the Update Carrier Status is a normal dropdown.

The way it is setup is when a user makes changes to these fields, the updates are collected in a collection. Then these new values are patched in the SharePoint list from the Submit Button.

Formulas in the 3 fields and the Submit button are as bellow

1. Credit Currency Combobox -  OnChange Property Formula
Collect(
    CarSelectedGalItems,
    {
        'Eshop Package Reference': ThisItem.'Eshop Package Reference',
        clnCreditCurrency: Car_galCmbxCreditCurrency.Selected.Result
    }
)

2. Credit Amount Text Input -  OnChange Property Formula
Collect(
    CarSelectedGalItems,
    {
        'Eshop Package Reference': ThisItem.'Eshop Package Reference',
        clnCreditCurrency: Car_galCmbxCreditCurrency.Selected.Result
    }
)

3. Update Carrier Status Dropdown-  OnChange Property Formula
Collect(
    CarSelectedGalItems,
    {
        'Eshop Package Reference': ThisItem.'Eshop Package Reference',
        CarNewStatus: Car_drpgalUpdateOpsStatus_1.Selected.Value
    }
)

4. Submit Button - OnSelect Property Formula
ForAll(
    RenameColumns(
        CarSelectedGalItems,
        "EshopPackageReference",
        "EshopPkgRefs"
    ),
    Patch(
        LostPackagesDB,
        First(
            Filter(
                LostPackagesDB,
                'Eshop Package Reference' = EshopPkgRefs
            )
        ),
        {
            'Carrier Status': {Value: CarNewStatus},
            'Credit Currency': clnCreditCurrency,
            'Credit-Amount': clnCreditAmount
        }
    )

 The problem I am having is that the Submit Button formula shows no error. But the patch only works for the Update Carrier Status field but not the Credit Currency and Credit Amount fields. 

I have tried all combinations of formulas like Value(), or {Value:}, but nothing works.

I checked the collection as well and the updated values do populate in the collection but the patch still doesn't work. A sample record in the collection is seen below.

AS seen in the image of the collection, all the values are being collected properly.

I have also tried converting these values into record, table, text, value, etc. thinking it might be a data type issue.

However, I can't figure out why the Carrier Status gets patched but the Credit Currency and Credit Amount doesn't.

I am hoping you guys can help me figure it out.

[StalinPonnusamy]

Hi @AchalDesai 

The problem with this approach is one-row overwriting other row values. We have 2 possible solutions based on your needs.

Option 1: (Write to Sharepoint immediately when some changes)

1. Credit Currency Combobox -  OnChange Property Formula
Patch(
    SPList, LookUp(SPList, 'Eshop Package Reference': ThisItem.'Eshop Package Reference')
    {
        clnCreditCurrency: Car_galCmbxCreditCurrency.Selected.Result
    }
)

2. Credit Amount Text Input -  OnChange Property Formula
Patch(
    SPList, LookUp(SPList, 'Eshop Package Reference': ThisItem.'Eshop Package Reference')
    {
        clnCreditCurrency: Car_galCmbxCreditCurrency.Selected.Result
    }
)

3. Update Carrier Status Dropdown-  OnChange Property Formula
Patch(
    SPList, LookUp(SPList, 'Eshop Package Reference': ThisItem.'Eshop Package Reference')
    {
        CarNewStatus: Car_drpgalUpdateOpsStatus_1.Selected.Value
    }
)

Option 2: Add to collection and update to Sharepoint. This solution is to update the existing collection instead of creating a new one. You always have only one row for each ID with all 3 values. 

• And always update all the fields

Set OnChange of the text and Onchange of Dropdown and onChange of Combo to

With(
    {
        Item: LookUp(
            CarSelectedGalItems,
            'Eshop Package Reference': ThisItem.'Eshop Package Reference'
        )
    },
    Patch(
        CarSelectedGalItems,
        Coalesce(
            Item,
            Defaults(CarSelectedGalItems)
        ),
        {
            'Eshop Package Reference': ThisItem.'Eshop Package Reference',
            'Credit-Amount': CreditAmount.Text,
            clnCreditCurrency: Car_galCmbxCreditCurrency.Selected.Result,
            CarNewStatus: Car_drpgalUpdateOpsStatus_1.Selected.Value
        }
    )
)

In this Case, you always have only one row for any unique.

Thanks, Stalin
Blog - Learn To Illuminate Blog
YouTube - Learn To Illuminate Videos

[AchalDesai]

Hi @StalinPonnusamy 

Thanks you for taking the time.

I believe that Option 2 is the best method. Because it is possible that the user might change the same field multiple times if wrong item gets selected from the dropdowns. Then the patch would have to keep running that many more times slowing the app down.

So I tried your formula for option 2 on the Credit Currency combobox. However, I am getting the below error. I am not so familiar with the With formula and Variables on powerapps so having a hard time to debug this error.

[AchalDesai]

Hi @StalinPonnusamy,

Took some time but managed to figure it out.

Just tiny things, like column names and control names.

Below is the working formula for anyone else who comes looking.

With(
    {
        Item: LookUp(
            CarSelectedGalItems,
            'Eshop Package Reference' = ThisItem.'Eshop Package Reference'
        )
    },
    Patch(
        CarSelectedGalItems,
        Coalesce(
            Item,
            Defaults(CarSelectedGalItems)
        ),
        {   'Eshop Package Reference': ThisItem.'Eshop Package Reference',
            'Credit-Amount': Car_galtxtCreditValue.Text,
            'Credit Currency': Car_galCmbxCreditCurrency.Selected.Result,
            CarNewStatus: Car_drpgalUpdateOpsStatus_1.Selected.Value
        }
    )
)

And then changed the main Collection to SharePoint List Patch function on the submit button  as below.

ForAll(
    RenameColumns(
        CarSelectedGalItems,
        "EshopPackageReference",
        "EshopPkgRefs","CreditCurrency","clnCreditCurrency","CreditAmount","clnCreditAmount"
    ),
    Patch(
        LostPackagesDB,
        First(
            Filter(
                LostPackagesDB,
                'Eshop Package Reference' = EshopPkgRefs
            )
        ),
        {   'Credit-Amount': clnCreditAmount,
            'Credit Currency': clnCreditCurrency,
            'Carrier Status': {Value: CarNewStatus}
        }
    )
)

[StalinPonnusamy]

Good. I was able to help.

Thanks, Stalin
Blog - Learn To Illuminate Blog
YouTube - Learn To Illuminate Videos

[AchalDesai]

Hi @StalinPonnusamy,

I implemented the solution that you suggested and it worked for that bit (i.e. all changes and selections are patched to SharePoint without overwriting each other in that session)

But I now see after more testing that the core problem remains. If I already have data in all 3 columns in SharePoint. Then even if 1 of the dropdowns in the gallery is changed, the patch results in overwriting the original values of the other 2 columns with blank.

I understand that this is happening because we are collecting and patching all 3 columns each time.

However, this is actually causing problems in the overall functionality of the App.

Is there a way to collect and patch only the values that change in the dropdowns and not touch the ones that exist in SharePoint if they have not been updated/changed in PowerApp?

Putting in some images below that will help understand the problem.

Already existing values in the 3 columns on SharePoint.

Operations Status is updated through PowerApp

Values overwritten for 'Lost at Hub' and 'Hub Name' columns on SharePoint even though the were not updated on PowerApp.

Required Result