ForAll Patch Help

[07GT]

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_Name	Class_Cost
Class A	10
Class B	20
Class C	30

Table2

Asset_Name	Asset_Class	Asset_Cost
AAA1	Class A	?
AAA2	Class A	?
BBB1	Class B	?
CCC1	Class C	?
CCC2	Class C	?
CCC3	Class 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

[WarrenBelz]

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]

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
            )
        }
    )
)

[Anonymous]

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

[RandyHayes]

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

[WarrenBelz]

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

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 @Anonymous  I'll look through your solutions here right away. 

thanks!

[RandyHayes]

@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)})
    )
)

[WarrenBelz]

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

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