Patch to duplicate record with children

[NathanC]

Hello everyone,

I have been using Power Apps for a month, and I just spent over a day on this problem, so I'm looking for your guidance with the following problem : I have a 'duplicate' button code that compiles fine, but when I run a test, it only copies the record, and not its children, records from another related table that have a lookup column referencing the former record. It also produces an error, only AFTER I have run the code :                cr203_quantitedebut : The field cr203_quantitedebut is required (I translated the error from French).

Here is my plain code, but maybe you'll prefer to work with the second, edited block :

Patch(
    Documents;
    Defaults(Documents);
    {
        Nom: BrowseGallery1.Selected.Nom & " - Copie";
        DateFin: BrowseGallery1.Selected.DateFin;
        StockNonLivre: BrowseGallery1.Selected.StockNonLivre
    }
);;
ForAll(
    BrowseGallery1.Selected.Diffusions As _pointsDocument;
    Patch(
        Diffusions;
        Defaults(Diffusions);
        {
            NomDocument: LookUp(Documents; Nom = BrowseGallery1.Selected.Nom & " - Copie");
            NomPointDistribution: _pointsDocument.NomPointDistribution;
            QuantiteDebut: _pointsDocument.QuantiteDebut;
            QuantiteRestante: _pointsDocument.QuantiteRestante
        }
    )
)

In order to make the code clearer, here is a simplified and heavily commented version, don't mind the semi-colons, they replace the comma in the French version :

Patch(
    TableA;
    Defaults(TableA);
    {
        Name: take the name of the TableA item selected in the gallery and modify it slightly;    
                                                 // works fine
        EndDate: take the date in the same way;  // works fine
        StillInStock: Same as above              // works fine
    }
);; // Up until now, everything seems to work with no problem, the new record can indeed be found after the test

ForAll(
    BrowseGallery1.Selected.TableBs As _itemB_RelatedToA; //Take the selected TableA item from the gallery, now take its related TableB items and give the records an alias to avoid errors;
    Patch(
        TableB;
        Defaults(TableB);
        {
            TableAName: LookUp(TableA; Name = theNameISpecifiedInTheFirstPatchSeeAbove); // I mistakenly called the column a name, but it references a record and seems to work with no problem when I run some other tests, I'll refactor when everything works

            // Here starts the part where the problem seems to be, but I can't figure it out
            TableCName: _itemB_RelatedToA.TableCName;  // Again, Name but is actually a record. However, I never got this line to work properly even in other tests so I'm suspicious of it
            StartingNumber: _itemB_RelatedToA.StartingNumber; // This is the line where I give a value to the required field from the error, but I'm 90% sure the instruction is correct, I got it to work in other tests. I honestly believe there's an error in the previous instruction, meaning this line is not executed.
            EndingNumber  : _itemB_RelatedToA.EndingNumber; // Probably doesn't even get here, but it's not even a required field anyway
        }
    )
)

My Dataverse for Teams database looks like this :

TableA (Documents)

    |

TableB (PointsDiffusion) : has a column referencing TableA (NomDocument) and one referencing TableC (NomPointDiffusion)

   |

TableC (PointsDistribution)

Thank you for giving me some of your time.

[poweractivate]

Use this @NathanC 

ForAll(
    BrowseGallery1.Selected.Diffusions As _pointsDocument;
    Patch(
        Diffusions;
        Defaults(Diffusions);
        {
            NomDocument: LookUp(Documents; Nom = BrowseGallery1.Selected.Nom).NomDocument  & " - Copie";
            NomPointDistribution: _pointsDocument.NomPointDistribution;
            QuantiteDebut: _pointsDocument.QuantiteDebut;
            QuantiteRestante: _pointsDocument.QuantiteRestante
        }
    )
)

The problem is

//Suppose BrowseGallery1.Selected.Nom is "MYNAME"
//Look up a document called "MYNAME - Copie" and use the likely non-existent record "Blank" as the name of the Document
 NomDocument: LookUp(Documents; Nom = BrowseGallery1.Selected.Nom & " - Copie");

This appears incorrect.

Instead I think you want this:

//Suppose BrowseGallery1.Selected.Nom is "MYNAME"
//Look up a document called "MYNAME" and then use likely existent record "SomeRecord" and then use its "NomDocument" property as the name of the Document, for example "SOMERESULTNOMDOCUMENT", aand the add the suffix " - Copie", so for example, it becomes "SOMERESULTNOMDOCUMENT - Copie"
 NomDocument: LookUp(Documents; Nom = BrowseGallery1.Selected.Nom).NomDocument & " - Copie";

See if it helps @NathanC 

Also for this part

//In the LookUp(...).NomDocument below
//Replace the .NomDocument part at the end with the appropriate column you want to use if needed.

LookUp(Documents; Nom = BrowseGallery1.Selected.Nom).NomDocument

Hope it helps.

[NathanC]

Thank you for your answer !

Unfortunately, I think I misled you because NomDocument is not actually a text column but a lookup column that only takes records. (I created the table when I had just begun working on PowerApps, I only understood my mistake later)

After testing the formula in a separate  label, I confirmed that it actually works - even though the code may be a bit dirty

[RandyHayes · Accepted solution]

@NathanC 

To start, your formula has the ForAll backward. You are trying to use it like a ForLoop in some development language - which PowerApps is not.  ForAll is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance.  The way you have it now will kill performance!

Too often seen in responses, videos, blogs etc...this function used wrong!

There is no need for you to have to (on every iteration) do another data operation to get the record you just created!

Please consider changing your Formula to the following:

With({_record:
    Patch(TableA;
        Defaults(TableA);
        {
            Name: BrowseGallery1.Selected.Nom & " - Copie";
            EndDate: BrowseGallery1.Selected.DateFin;
            StillInStock: BrowseGallery1.Selected.StockNonLivre
        }
    )
    };

    Patch(TableB;
        ForAll(BrowseGallery1.Selected.Diffusions As _pointsDocument;        
        {
            NomDocument: _record;
            NomPointDistribution: _pointsDocument.NomPointDistribution;
            QuantiteDebut: _pointsDocument.QuantiteDebut;
            QuantiteRestante: _pointsDocument.QuantiteRestante
        }
    )
)

I hope this is helpful for you.

[poweractivate]

@NathanC 

Yes you should prefer to make a single Patch where you can as @RandyHayes is doing, so you should try to put the ForAll inside the Patch somehow. This requires you to construct the formula in a more advanced way though, but I think it is worth it. I would suggest you to follow the guidance given by @RandyHayes and see if it works for you.

[RandyHayes]

@poweractivate 

Well...the point being - Patch accepts a table of records to patch.  ForAll is a function that creates tables.  So, the two are perfect together.  

When you try to make ForAll work like a programming for loop, PowerApps needs to instantiate a Patch function over and over again.  This is a load on performance because Patch is "expensive".  If you instantiate it once and provide it with the table of records, it has a very limited overhead.  

It's like going to a grocery store checkout with a load of groceries in your cart.  You wouldn't take one item out, put it on the belt, have it scanned, put it in a bag and then pay for it, and THEN take the next item out of your cart and do the same thing until done.  No, instead you would put all items on the belt, and then process them all and then complete the transaction all at once.

Same concept here.

But in general, using ForAll like a for loop is bad practice!  Not how it was designed and makes one very frustrated when trying to make it work like one.

[NathanC]

Wow, it worked first try.

Thank you @RandyHayes  and @poweractivate  for your help, it was extremely quick and efficient.

[RandyHayes]

@NathanC 

Happy to help!