Patching a collection when gallery item is a SUM formula - error

[Fernanda]

Hello!

I need some help... I am working on an Inventory app which works offline (based on collections).

When Stock Review begins, we upload the database (sharepoint) to a collection and after it is over, the collection is uploaded (patch) to the Sharepoint List.

We have a few Inventory Areas. Within that Inventory Area, there are several Warehouses. And each warehouse has different chemicals.

For example:

Company 1 has 3 inventory areas (A, B, C)

Inventory Area A has 10 warehouses (1, 2, 3, and so on)

Warehouse 1 has 4 chemicals (a, b, c, etc)

My main database (colBaseSP) has Inventory Area as main column (Title). This is the first part. We go out to do the physical review and enter the data in the device.

The second part is data processing. Once the review is over and all data is in, I have to filter by Inventory Area and get the sum of each chemical. This data will be uploaded later to a different Sharepoint List (colBaseTD), where the chemical is the main column (title).

To achieve this, what I did was creating a blank Gallery (items property is the first part data collection "colBaseSP"), filtered by a dropdown (title distinct value), and also bring back the distinct value of the chemicals listed for that Inventory Area. I added a label where the text value is a formula. This way, I can see the subtotal of each chemical for the selected inventory area. I am pretty sure there is a smarter way to do this part, but this is all I could think of to make it work

Sum(Filter(colBaseSP;Dropdown3.Selected.Result = Title;ThisItem.Result = 'CÓDIGO MATERIAL');Consumo)

Here comes my problem.

I need to add those values to my second data base (colBaseTD). I created a button to do the patching, but I keep getting errors. My main error is that I am not reading a number but a table (only tested one gallery item first). And I am at a dead end, because I have no idea how to fix it. Maybe my whole approach is wrong, but it was the only way I could find to get what I needed.

Patch(colBaseTD;Defaults(colBaseTD);{Consumo: Gallery3.AllItems.Label5})

Can you help me?
Thank in advance!!

[WarrenBelz]

Hi @Fernanda ,

If you are Patching a single value from inside the Gallery

Patch(
   colBaseTD;
   Defaults(colBaseTD);
   {Consumo: Value(ThisItem.Label5.Text)}
)

from outside the gallery

Patch(
   colBaseTD;
   Defaults(colBaseTD);
   {Consumo: Value(Gallery3.Selected.Label5.Text)}
)

all of the gallery from outside

Patch(
   colBaseTD;
   ForAll(
      Gallery3.AllItems As aPatch,
      {Consumo: Value(aPatch.Label5.Text)}
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Fernanda]

Hello Warren!

Thank you for the quick reply!!

I tried your solution, but it is still not working.

For the 3rd solution (All items), I have the following issues:

1. I cannot longer find "Consumo" column inside of my collection

2. And the database isn't valid

even though, both collections are created

Your formula for the single value from outside the gallery worked, but of course, it only patched 1 value.

Thank you for your help!!!

[WarrenBelz]

Hi @Fernanda ,

You actually need this

Collect(
   colBaseTD;
   ForAll(
      Gallery3.AllItems As aPatch,
      {Consumo: Value(aPatch.Label5.Text)}
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Fernanda]

Hello Again

My collections colBaseTD and colBaseSP, when databases are uploaded, already have data, this is why I was using patch.

Maybe I built my collection in a wrong way?

My first database (colBaseSP) comes from a SP list that already has data (inventory areas, warehouses and chemicals info), and the second one (colBaseTD) also comes from a SP list with data (areas and chemicals detail). Both databases are uploaded when I click on a button, to select the specific Zone.

ClearCollect(
    colBaseSP;
    Filter(
        'Inventario Quimicos parte 1';
        Dd_ZonaInventario.SelectedText.Value = 'Nombre Inventario'.Value
    )
);;
SaveData(
    colBaseSP;
    "InventarioEnEquipo"
);;
ClearCollect(
    colBaseTD;
    Filter(
        'Inventario Quimicos parte 2';
        Dd_ZonaInventario.SelectedText.Value = 'Zona Inventario'
    )
);;
SaveData(
    colBaseTD;
    "TratamientoEnEquipo"
)

This, so far has worked fine for my data patching in the first collection (colBaseSP), as you can see, when I enter data for "Consumo", it is correctly patched.

In the gallery I showed before, items property filters colBaseSP, because I need to filter by warehouse and also show subtotals for each chemical. In the image above, "Cal Apagada" repeats in at least 10 different warehouses for that selected inventory area. So, what I did was build a blank gallery, filter by a dropdown (to select the inventory area) and also to only show the chemicals distinct value (to show only once all the chemicals considered for that inventory area)

To get the subtotal of each chemical, i used a SUM formula, doing the filter by dropdown selected, and also filtering by chemical ID (Código Material), that way, selecting the specific column, I can get the subtotal. And it works (at least, visually)

Sum(
    Filter(
        colBaseSP;
        Dropdown3.Selected.Result = Title;
        ThisItem.Result = 'CÓDIGO MATERIAL'
    );
    Consumo
)

And that is the data I need to patch in my second collection (colBaseTD). 

Last night after trying your solution I had made a mistake and didn't add a lookup to find the right row to patch, but still, I cleared the collection to test your formula, but it still didn't work.

So, I am not sure what I am doing wrong... is it the collection?? maybe my idea to distinct value the gallery with the sum formula is wrong and it won't be read by the patching formula? 

Thank you for your patience! I am not an expert, most of what I know I have learnt from this forum and youtube, and I have been reading a lot and watching many videos looking for a way to fix it or find a workaround, but I haven't found it so far

[WarrenBelz]

Hi @Fernanda ,

Collect adds new records to an existing collection - is that not what you are doing here  ?

[Fernanda]

Hi @WarrenBelz !!

No, I am patching a collection 🙂

Today, I was working on it, re reading the solutions you wrote, trying to figure it out, and I think I found the solution! At least, it worked and it patched the value. 

Patch(
    colBaseTD;
    LookUp(
        Filter(
            colBaseTD;
            Dropdown3.Selected.Result = ALMACÉN
        );
        Label3.Text = Title
    );
    {Consumo: Value(Label5.Text)}
)

Now, I have to test it with all of the rest of the data, but so far, it seems to be the way to go 🙂

I am curious about something though.... is "As" a way to start a variable of some sort?? I had not seen it written in a formula before

Thanks a lot for your patience and help!! 

[WarrenBelz]

@Fernanda ,

We were on different paths here - if you look back, you were using Defaults(Collection), so I assumed you were writing new records, but I am wondering why you are using both LookUp and Filter when you only want one record

Patch(
    colBaseTD;
    LookUp(
       colBaseTD;
       Dropdown3.Selected.Result = ALMACÉN &&
        Label3.Text = Title
    );
    {Consumo: Value(Label5.Text)}
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Fernanda]

Hi @WarrenBelz 

I see that now!, before you mentioned it, I was not aware that Defaults() was to create new records... my mistakes began there 😛

"Consumo" is not the only column I have to patch. I was only using one at first for testing, but I am patching 9 different columns.

Thanks again for you help!! learnt a lot from this 🙂