cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EdHansberry
Kudo Collector
Kudo Collector

Need help with a FORALL loop in a PATCH statement

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

8 REPLIES 8
EdHansberry
Kudo Collector
Kudo Collector

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

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

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

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

@EdHansberry 

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

Glad it is working for you now.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,647)