cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
07GT
Frequent Visitor

ForAll Patch Help

Hello,

 

Here's my scenario: I have a table (Table1) with a list of Asset Classes and their Costs, and a second table (Table2) which has a list of assets, and their class. I'd like to be able to patch the cost of the asset class from table 1 to all the corresponding assets in table 2.

 

Table1

Class_NameClass_Cost
Class A10
Class B20
Class C30

 

Table2

Asset_NameAsset_ClassAsset_Cost

AAA1

Class A?
AAA2Class A

?

BBB1Class B?
CCC1Class C?
CCC2Class C?
CCC3Class C?

 

My initial thought was to use the following:

 

Forall(Table2,    Patch(Table2, Lookup(Table1, Class_Name = Asset_Class, Class_Cost)   )

I figured out I can't use the same table in the forall and patch. Could someone help me out on the syntax here?

 

Any thoughts would be appreciated, thanks!

 

Jason

11 REPLIES 11
WarrenBelz
Super User III
Super User III

Hi @07GT ,

Try this

With(
   {
      wT2:Table2,
      wT1:Table1
   },
   ForAll(
      wT2 As aTable,
      Patch(
         Table2,
         {ID:aTable.ID},
         {
            Asset_Cost:
            Lookup(
               wT1,
               Class_Name = aTable.Asset_Class
            ).Class_Cost
         )
      )
   )
)

 

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.

GarethPrisk
Super User II
Super User II

Patches can be

  • Creates, to add new records
  • Updates, to existing records

Which are you intending to do? Presumably, it's an update and this is CDS.

 

You can take another approach, and use a shadow/copy collection to do the ForAll and an UpdateIf. Example:

// Temporary Collection, to loop - a copy of the collection to update
ClearCollect(
    colUpdate,
    colTable2
);
// For all of those records
ForAll(
    colUpdate As varUpdate, // Aliased to help disambiguate
    UpdateIf(
        colTable2,
        colTable2[@tblId] = varUpdate.tblId, // Match record in actual table, with copied collection
        {
            tblLookUp: LookUp(
                colTable1, // Table to lookup into
                varUpdate.tblValue = colTable1[@tblId], // Match records
                tblValue // Value to use
            )
        }
    )
)
HenryARPhillips
Continued Contributor
Continued Contributor

Hi,

 

I don't feel happy with this but in the limited time I had this does seem to work. The reason I did an !Blank is because the UpdateIf was expecting a Boolean at that point

 

HenryARPhillips_0-1615235976854.png

HenryARPhillips_1-1615236939284.png

 

RandyHayes
Super User III
Super User III

@07GT 

Don't waste your ForAll output!!!  ForAll creates tables...it is not a programing For/Loop

Please consider changing your Logic to the following:

Patch(Table2 As _item, 
    ForAll(
        Patch(_list, {Asset_Cost: Lookup(Table1, Class_Name = _item.Asset_Class, Class_Cost)})
    )
)

I hope this is helpful for you. 

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

@07GT ,

Seeing the whole gang is here, @RandyHayes 's approach is similar to mine, but cut down a bit (using an As statement rather than a With() statement). The reason I included both tables in the With() statement is that the Relational LookUp back to Table 1 is not Delegable.

07GT
Frequent Visitor

Thanks all for your quick replies,

 

@RandyHayes can you help me understand where you get the "_list"  data source from in your Patch function? Should this not be Table2 ? Or _item ?

 

@WarrenBelz  I'm getting an error with this part the code:  {ID: wt2.ID}

Its telling me the ID doesn't match expected type text, Found type Table. Any thoughts?

 

@GarethPrisk  and @HenryARPhillips  I'll look through your solutions here right away. 

 

thanks!

RandyHayes
Super User III
Super User III

@07GT 

Oops, sorry...not sure how I had this in there.  Should have been this:

Patch(Table2 As _item, 
    ForAll(
        Patch(_item, {Asset_Cost: Lookup(Table1, Class_Name = _item.Asset_Class, Class_Cost)})
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

@07GT ,

I will step out here - your ID columns should both be numeric, but I have made a small change that may assist (see below in my original post)

RandyHayes
Super User III
Super User III

@WarrenBelz 

Stick around for the fun Warren!! 😁

 

Hey, I believe the issue in your formula was the you were trying to pass a table of ID's to the ID column.

Your formula should have been this:

With
   {
      wT2:Table2,
      wT1:Table1
   },
   ForAll(
      wT2,
      Patch(
         Table2 As _t2,
         {ID: _t2.ID},
         {
            Asset_Cost:
            Lookup(
               wT1,
               Class_Name = _t2.Asset_Class
            ).Class_Cost
         )
      )
   )
)

 

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (57,392)