Need help with a FORALL loop in a PATCH statement

[EdHansberry]

Here is my code:

ForAll(
    Filter(
        galASNLineDetails.AllItems,
        chkbxSelectASNDetail.Value = true
    ),
    Patch(
        '[dbo].[tblAppASNLogDetails]',
        Defaults('[dbo].[tblAppASNLogDetails]'),
        {
            ASNNumber: gvarASNRecord.ASNNumber,
            VendorNumber: gvarVendorNumber,
            PONumber: ThisRecord.lblASNDetailOrderNo.Text,
            POLineNumber: Value(ThisRecord.lblASNDetailLineNo.Text),
            QuantityShipping: Value(ThisRecord.lblASNDetailQtyRemaining.Text),
            ItemNumber: ThisRecord.lblASNDetailItemNo.Text,
            ItemDescription: LookUp(
                colOpenPOLineMetaData,
                'Item Number' = ThisRecord.'Item Number',
                Description
            ),
            TotalCube: LookUp(
                colOpenPOLineMetaData,
                'Item Number' = ThisRecord.'Item Number',
                Cube
            ) * ThisRecord.qty_remaining,
            TotalWeight: LookUp(
                colOpenPOLineMetaData,
                'Item Number' = ThisRecord.'Item Number',
                Weight
            ) * ThisRecord.qty_remaining,
            HTSCode: LookUp(
                colOpenPOLineMetaData,
                'Item Number' = ThisRecord.'Item Number',
                'HTS Code'
            ),
            DutyRate: LookUp(
                colOpenPOLineMetaData,
                'Item Number' = ThisRecord.'Item Number',
                'Duty Rate'
            ) * 100,
            DestinationLocation: "MAN",
            ShipToCode: gvarPortCode
            //Note: Below patches didn't reliably capture Data. As of 4/12/2021 useing above more verbose code.
            //ItemDescription: ThisRecord.lblASNDetailDescription.Text,
            //TotalCube: Value(ThisRecord.lblASNDetailCube.Text),
            //TotalWeight: Value(ThisRecord.lblASNDetailWeight.Text),
            //HTSCode: ThisRecord.lblASNDetailHTSCode.Text,
            //DutyRate: Value(ThisRecord.lblASNDetailDutyRate.Text) * 100
        }
    )
)

It is correctly looping through checked items in galASNLineDetails.

However, only the item number and other items that have the field and a value are being correclty being patched - the line that says below for example:

ItemNumber: ThisRecord.lblASNDetailItemNo.Text,
            

None of the ones that need to look up info from a collection are though.

ItemDescription:
   LookUp(
                colOpenPOLineMetaData,
                'Item Number' = ThisRecord.'Item Number',
                Description
            ),

Is not doing the lookup correctly. I cannot figure out how to see what "ThisRecord.'Item Number'" is returning for the match. I tried this:

            ItemDescription: LookUp(
                colOpenPOLineMetaData,
                'Item Number' = ThisRecord.lblASNDetailItemNo.Text,
                Description
            ),

But that returns an error at the = sign saying "these types cannot be compared. Text, Error." So that is returning an error there, even though in the section above it is fine. 

I'll be honest too. I don't know why ThisRecord.'Item Number' is working in the syntax. I didn't name any fields in the gallery, but autocomplete is suggesting it.

What I want it to do is to go through the colOpenPOLineData collection and look up the appropriate value for each Item Number that I've checked. 

You'll notice the code at the bottom has been commented out because getting the data directly from the gallery labels wasn't reliable.

Any suggestions on where I've gone wrong?

[EdHansberry]

FWIW, I've tried With() as below, but it wont' take it. Not sure you can use With with Patch as the function)

With(
   {Item: ThisRecord.lblASNDetailItemNo.Text},
   Patch(...)
)

[RandyHayes · Accepted solution]

@EdHansberry 

Please consider changing your Formula to the following:

Collect('[dbo].[tblAppASNLogDetails]',
    ForAll(
        Filter(galASNLineDetails.AllItems, chkbxSelectASNDetail.Value) As _item,
        With({_poMD: LookUp(colOpenPOLineMetaData, 'Item Number' = _item.'Item Number')},

            {
                ASNNumber: gvarASNRecord.ASNNumber,
                VendorNumber: gvarVendorNumber,
                PONumber: _item.lblASNDetailOrderNo.Text,
                POLineNumber: Value(_item.lblASNDetailLineNo.Text),
                QuantityShipping: Value(_item.lblASNDetailQtyRemaining.Text),
                ItemNumber: _item.lblASNDetailItemNo.Text,
                ItemDescription: _poMD.Description,
                TotalCube: _poMD.Cube * _item.qty_remaining,
                TotalWeight: _poMD.Weight * ThisRecord.qty_remaining,
                HTSCode: _poMD.'HTS Code',
                DutyRate: _poMD.'Duty Rate' * 100,
                DestinationLocation: "MAN",
                ShipToCode: gvarPortCode
            }
        )
    )
)

This formula will utilize the ForAll correctly and will also reduce the 5 lookup calls to just one for each record.

This should also perform much faster.

Avoid using the ThisRecord function unless you know EXACTLY what record ThisRecord will refer to.  In your context, the ThisRecord was referring to multiple records - not the one you thought it would be. ThisRecord always refers to the left-most data function.  So, the ThisRecord in your LookUps were referring to the LookUp record, not the table record of your Gallery.

Also, you'll not that this is all feeding into a Collect function.  This will create the records in your table properly.

I hope this is helpful for you.

[EdHansberry]

Wow... very different. So you are saying I can use COLLECT to put data into my SQL table and not need to use PATCH?

I'm trying this out now.

[RandyHayes]

@EdHansberry 

Yes, Collect will, in the formula provided, take a table of records and send them all to the SQL datasource.  

Although PowerApps still internally does a row-by-row action, it is much faster than trying to use a ForAll as a For/Loop (which it's not) and instigating the Patch statement on each iteration.  ForAll returns a table of records, so, ForAll will do its thing first and create a table to then sent to Collect.  In your original, not only were you iterating and then performing two data functions (Patch and Defaults) against the datasource, but you were also performing 5 more data functions with each lookup (even though it is against internal collection memory).  So...kind of slow in other words.

As a note, Collect can be used to create new records in your datasource. If you do not provide a primary key in your records to the Collect function, then all is good.  If you do, it will fail.

You can also use Patch in the same way.  Except, if you provide the Primary key value in any record in the table, it will update. If you don't provide one, it will create.  So, if you provide Blank() (null) on the primary key, then it will create a record.  In your case, you are not providing a primary key in your records, so those will all be "creates".

Since you were only concerned with creating, I chose Collect as the function to use.  Either will work in the formula I provided.

[EdHansberry]

I had no clue. This is the final code - just a few tweaks to some field names you couldn't possibly have known.

Collect('[dbo].[tblAppASNLogDetails]',
    ForAll(
        Filter(galASNLineDetails.AllItems, chkbxSelectASNDetail.Value) As _item,
        With({_poMD: LookUp(colOpenPOLineMetaData, 'Item Number' = _item.item_no)},

            {
                ASNNumber: gvarASNRecord.ASNNumber,
                VendorNumber: gvarVendorNumber,
                PONumber: _item.lblASNDetailOrderNo.Text,
                POLineNumber: Value(_item.lblASNDetailLineNo.Text),
                QuantityShipping: Value(_item.lblASNDetailQtyRemaining.Text),
                ItemNumber: _item.lblASNDetailItemNo.Text,
                ItemDescription: _poMD.Description,
                TotalCube: _poMD.Cube * _item.qty_remaining,
                TotalWeight: _poMD.Weight * _item.qty_remaining,
                HTSCode: _poMD.'HTS Code',
                DutyRate: _poMD.'Duty Rate' * 100,
                DestinationLocation: "MAN",
                ShipToCode: gvarPortCode
            }
        )
    )
)

Thanks!

This is why I should stick to Power BI. My brain isn't big enough to handle all of the DAX and Power Query knowledge and then cram in advanced Power Apps. 😀

[RandyHayes]

@EdHansberry 

Yes, the combination of syntaxes and languages etc is a bit overwhelming sometimes 🤑

Glad it is working for you now.

[EdHansberry]

Thanks. Yeah, this is a 100% create operation, and the primary key field is one of those in SQL that auto-increments each record number for me. I never touch that field. Makes life easier.

[RandyHayes]

@EdHansberry 

Yes, you cannot specify a primary key value when you create a record - ever!, but if you supply an existing primary key value and when you do, it will perform an update on that corresponding record.