Patching values to a SharePoint list from text inputs in a gallery

[BillKern]

Hello,

I have an app that updates a SharePoint list with values for current inventory, and quantities that will be arriving on Tuesday, Thursday, and Sunday. Here's a screenshot of what it looks like:

The hint text displays what is in the SharePoint list currently. You can go through and enter values into each of the columns and rows until you've got everything entered. Once you're finished you click the send button on the top right and what it's supposed to do (and has been doing for over a year) is add up the number of items that are being updated and then a popup comes up letting you know how many items. I do this by adding the updated items to a collection. Here's the code for that button:

// Patches the updated inventory values to the InvUpdate collection
ForAll(
    ItemGrid_2.AllItems,
    If(
        
        !IsBlank(Qty.Text) || !IsBlank(QtyTues.Text) || !IsBlank(QtyThurs.Text) || !IsBlank(QtySun.Text),
        Patch(
            InvUpdate,
            LookUp(
                InventoryTable,
                Value(StockNumber_2.Text) = Value(Title)
            ),
            {
                Updated: true,
                NewQty: Value(Qty.Text),
                Tue: Value(QtyTues.Text),
                Thu: Value(QtyThurs.Text),
                Sun: Value(QtySun.Text)
            }
        )
    )
);


// Checks if there are any items to be updated
If((CountRows(Filter(InvUpdate, Updated)))= 0, UpdateContext({showPopup5:true}), UpdateContext({showPopup3:true}))

The problem I'm having is that out of nowhere this stopped working. I made zero changes to the code and now every time I go to update the inventory list only 1 item gets added to the new InvUpdate collection. 

Any thoughts?

[RandyHayes]

@BillKern 

Please consider changing your Formula to the following:

// Patches the updated inventory values to the InvUpdate collection

Patch(InvUpdate,
    ForAll(
        Filter(ItemGrid_2.AllItems, !IsBlank(Qty.Text) || !IsBlank(QtyTues.Text) || !IsBlank(QtyThurs.Text) || !IsBlank(QtySun.Text)) As _item,
        {ID: _item.ID,
         Updated: true,
         NewQty: Value(Qty.Text),
         Tue: Value(QtyTues.Text),
         Thu: Value(QtyThurs.Text),
         Sun: Value(QtySun.Text)
        }
    )
)

// Checks if there are any items to be updated
If((CountRows(Filter(InvUpdate, Updated)))= 0, UpdateContext({showPopup5:true}), UpdateContext({showPopup3:true}))

There should be some logic in the Value functions to determine if the TextInput is blank or now - otherwise performing a Value on an empty text will produce a runtime error.

I hope this is helpful for you.

[BillKern]

@RandyHayes I'm now getting an error saying "The data source supplied to the function is not valid"

[RandyHayes]

@BillKern 

Is InvUpdate your datasource?  

[BillKern]

@RandyHayes InvUpdate is the collection that is being added to, the data source for the gallery is InventoryTable

[RandyHayes]

@BillKern 

What purpose does the Collection serve in this?  You can use the InventoryTable directly in the formula provided to complete the operation in one step without the duplicate information.

Is there something else that the collection is used for - other than the If statement in your formula.

Which can be changed to this:

// Patches the updated inventory values to the InvUpdate collection

Patch(InventoryTable,
    ForAll(
        Filter(ItemGrid_2.AllItems, !IsBlank(Qty.Text) || !IsBlank(QtyTues.Text) || !IsBlank(QtyThurs.Text) || !IsBlank(QtySun.Text)) As _item,
        {ID: _item.ID,
         Updated: true,
         NewQty: Value(Qty.Text),
         Tue: Value(QtyTues.Text),
         Thu: Value(QtyThurs.Text),
         Sun: Value(QtySun.Text)
        }
    )
)

// Checks if there are any items to be updated
If(
    CountRows(
        Filter(ItemGrid_2.AllItems, !IsBlank(Qty.Text) || !IsBlank(QtyTues.Text) || !IsBlank(QtyThurs.Text) || !IsBlank(QtySun.Text)
        )
    )= 0, 
    UpdateContext({showPopup5:true}), 
    UpdateContext({showPopup3:true})
)

Your Gallery already is a collection of items, this is why I am asking what the additional duplicate collection is for.

[BillKern]

@RandyHayes When I click the send button it populates the InvUpdated collection with any item that I intend to update. Then a popup shows up to let you know how many items are in the collection so you know how many items are about to be updated. When you then click the update button it runs this code to patch the data from the InvUpdate collection to the InventoryTable:

// Patches the new inventories from the InvUpdate collection to the InventoryTable
ForAll(
    RenameColumns(
        Filter(
            InvUpdate,
            Updated
        ),
        "Title",
        "ETitle"
    ),
    Patch(
        InventoryTable,
        LookUp(
            InventoryTable,
            Title = ETitle
        ),
        {
            OnHand: NewQty,
            TuesArrv: Tue,
            ThurArrv: Thu,
            SunArrv: Sun
        }
    )
);

I have it set up this way because I didn't know a better way to do it at the time, I was learning PowerApps as I went. I can try splitting up what you posted so the countrows is the display on the popup and the InventoryTable patch pulls directly from the gallery instead of the collection. 

[RandyHayes]

@BillKern 

Yes, the collection is a waste in this case because you're not really creating an in memory database (or at least you don't need to as it is already in your Gallery).

So, in this case, I would change your original formula to the following:

If(
    CountRows(
        Filter(ItemGrid_2.AllItems, !IsBlank(Qty.Text) || !IsBlank(QtyTues.Text) || !IsBlank(QtyThurs.Text) || !IsBlank(QtySun.Text)
        )
    )= 0, 
    UpdateContext({showPopup5:true}), 
    UpdateContext({showPopup3:true})
)

This will then show your popup.

Then on the OnSelect of the update button:

// Patches the updated inventory values to the datasource
Patch(InventoryTable,
    ForAll(
        Filter(ItemGrid_2.AllItems, !IsBlank(Qty.Text) || !IsBlank(QtyTues.Text) || !IsBlank(QtyThurs.Text) || !IsBlank(QtySun.Text)) As _item,
        {ID: _item.ID,
         OnHand: IfError(Value(Qty.Text),0),
         TueArrv: IfError(Value(QtyTues.Text),0),
         ThuArrv: IfError(Value(QtyThurs.Text),0),
         SunArrv: IfError(Value(QtySun.Text),0)
        }
    )
)

A few things to note:

1 - Your ForAll's were backward - you were using them as For/Loops, they are not.  They are functions in PowerApps that produce tables.  You can use a table as an argument for your Patch.  This will help with performance in your updates.

2 - You are using the Title as your identifier for the record to update.  Typically, the process is to utilize the primary key (ID in the case of SharePoint) to update a record.  The formula I provided above will use the ID as it comes from your Gallery.

3 - I have added the IfError logic in the formula because if any of the Text inputs are empty in your gallery (which based on the filter for the gallery items, some can be) it will cause a runtime error.  The IfError will try to evaluate the value and if none, then it will return 0

Finally, just a little tip on OR's in your first formula, you can use a technique like this to make writing your Formula a little quicker:

If(
    CountRows(
        Filter(ItemGrid_2.AllItems, 
            !IsBlank(Qty.Text & QtyTues.Text & QtyThurs.Text & QtySun.Text)
        )
    )= 0, 
    UpdateContext({showPopup5:true}), 
    UpdateContext({showPopup3:true})
)

This will concatenate all the text input values together and then evaluate the IsBlank on that.  If any one of them is not blank, then IsBlank will return false (and thus true with your not operator).  If all of them are blank, then IsBlank will return true (and thus false with your not operator).

[BillKern]

@RandyHayes Thanks for the advice there! I've changed the first formula and that works, but I'm getting an error for the second. 

"Invalid argument type (Table). Expecting a record value instead."

[RandyHayes]

@BillKern 

That usually indicates that there is some column name that is not correct.

Check your column names in the record structure of the ForAll and make sure they are accurate...and the Real names of the columns.  If you've changed the names on the columns in the list, it will need to be the real name, not the changed name.