I need insert two data entry in two coluns, but split data semi comma separate.

[AdamLincoln]

Hi guys,

I'm try insert two data entry in two colunms, but this work fine to one columns, i not know make work for two coluns.

Code that work fine one colunm:

ForAll(
        Trim(Split(Reserva.Text;";"));
Patch(FaturamentoHotel;
        Defaults(FaturamentoHotel);
        {'Hora de início':Now();
        'Hora de conclusão':Now();
        Email: User().Email;
        Nome:User().FullName;
        Reserva:Result}))

 
My Target:
Data Entry:

Target for data entry:

[RandyHayes · Accepted solution]

@AdamLincoln 

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.  It 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 - especially when trying to Patch.

 Please consider changing your Formula to the following:

With({_usr: User();
      _reserva: Split(Trim(Reserva.Text); ";");
      _valorParceiro: Split(Trim(ValorParceiro.Text); ";")
     };

    Patch(FaturamentoHotel;
        ForAll(Sequence(CountRows(_reserva));
            {'Hora de início':Now();
             'Hora de conclusão':Now();
             Email: _usr.Email;
             Nome: _usr.FullName;
             Reserva: Last(FirstN(_reserva, Value)).Result;
            'Valor Parceiro': Last(FirstN(_valorParceiro, Value)).Result
            }
        )
    )
)

Also, your User() function is expensive (performance), so best to only perform it once.

The above formula will create a record in your datasource for each split and include the associated item from the second split.

I hope this is helpful for you.

[sopatte]

Your code formatted for readability:

ForAll(
    Trim(Split(Reserva.Text;";"));
    Patch(FaturamentoHotel;
        Defaults(FaturamentoHotel);
        {
            'Hora de início':Now();
            'Hora de conclusão':Now();
            Email: User().Email;
            Nome:User().FullName;
            Reserva:Result
        }
    )
)

 The problem is you are splitting your Reserva Text field, but you need to find the corresponding text in Valor Parcerio. This is tricky because you need the corresponding index. One way you could do it is:

With({
        ReservaTable:Trim(Split(Reserva.Text;";"));
        ValorParcerioTable:Trim(Split(Reserva.Text;";"));
    };
    ForAll(Sequence(CountA(ReservaTable));
        Patch(FaturamentoHotel;
            Defaults(FaturamentoHotel);
            {
                'Hora de início':Now();
                'Hora de conclusão':Now();
                Email: User().Email;
                Nome:User().FullName;
                Reserva:Last(FirstN(ReservaTable,Value).Result;
                'Valor Parcerio':Last(FirstN(ValorParcerioTable,Value).Result
            }
        )
    )
)

I'm not entirely sure about where you will need ".Result" and ".Value" but you should get code suggestions while typing to help you figure that out.

[RandyHayes]

@sopatte 

Keeping in mind that your ForAll is still used backward and performance will suffer from using a Patch in a ForAll when trying to use it as a ForLoop - that it's not!

You have to realize that ForAll is a function that creates a Table.  It will perform that action internally whether you use it correctly or not.  As well, Patch will return a record.  These two things in mind tell you that, since the record definition is not specified in the ForAll formula you provided, then your resulting table will be a table of rows for each iteration (in this case of the split) that will have a record with a Value column that will have the results of the Patch.

So, in the case of a couple of records, the performance hit might be minimal, but the performance starts to decrease sharply for the more records that you have as the ForAll will still (regardless of use) create a table to return that has all of the results of all of the patches.

When used properly, with a formula such as I had suggested - Patch(datasource, ForAll(iterations, {record Definition}))

Then the Patch will only take the overhead of instantiating once.  It is then provided with a table of all of the records to patch.  So, it will not then have the additional overhead of retrieving each record as it patches in order to populate a ForAll table.

The Defaults function is not needed.  Patch is smart enough to know that a primary key is not provided, so it will produce a new record.  Should a primary key exist, Patch is smart enough to then lookup and update that record.  Even a combination of records returned from the ForAll (those with primary and those without), Patch will create and update as needed.

[sopatte]

@RandyHayes 
I didn't see your response. I was composing my response while you (apparently) were composing yours.

One comment though. We touched on this before. The documentation of ForAll does state, "If the return value from ForAll is not used, which is often the case with data modification functions, then the return value will not be created and there are no resource or order concerns." I think that indicates it can be used like a for loop without quite as much concern as you attribute. That said, it makes sense that a single call to Patch is better than multiple calls to Patch, and in general I have no doubt that your insight is greater than mine, and that your suggestions are (a) worth considering and (b) in fact, better than mine. I had no idea, for example, that patch was "smart enough" to do the things you mentioned, and particularly I look forward to not using Defaults() so much.

Peace.

[AdamLincoln]

Thanks so much Randy, you solution work perfectly.

[AdamLincoln]

Thanks so much for you help sopatte.

[RandyHayes]

@sopatte 

Biggest issue with using the ForAll like a ForLoop is that it implies a level of "development".  PowerApps is not a development platform.  It is formulas...just like Excel, because it was modeled around Excel.

So, I personally push people away from ForAll like a loop, because it is too common to try to bring "bad development" habits into PowerApps.  So, best to avoid them and use the functions and formulas as they were originally designed.  There ARE times that it has to be used as a for loop...BUT, they should be rare!  

One distinction with the Patch in a ForAll is that it will instantiate the Patch for each action over and over.  On small datasets, perhaps not an issue, but the larger, the more performance hits there are.  This is a very common answer I resolve on this forum often - "Help - My ForAll patch is very slow".  When I provide the proper way to use that with the ForAll as a table, performance becomes a tremendous change.

It is misleading though because, if you watch the monitor tool, you will see the Patch perform for each record, regardless of the way the formula is written.  This is because, technically, the entire table of records cannot be delegated to the source, so each must be handled.  However, the difference in this case is that the Patch function itself instantiates only once.  One once does it have to gather datasource info, and session table info.  So, all of that overhead is removed. 

The other aspect of ForAll when used as the Table function it was designed for, is that it becomes the MOST powerful function in PowerApps because it provides a way to shape data dynamically (not in some behavioral action) in evaluated formulas.  This is HUGE for PowerApps design!

And yes - Patch is smart!  It will know what to do simply based on the primary key.  If the key is blank, it will create a record.  If it has a value, it will update the corresponding record.

The Defaults are helpful for two cases -

1) If you need all of the columns of a record to be part of the return from a Patch.

So - 

Set(record, Patch(dataSource, {Title: "Hi"})) 

This will create a new record in the Datasource because there is no primary key.  The resulting record variable will have ONE column - Title

Set(record, Patch(dataSource, Defaults(DataSource), {Title: "Hi"})) will do the same thing, except the record variable now will contain ALL of the columns of the datasource record.

2) If you actually have defaults!  If there are some columns that need to have specific datasource specified defaults.

Using that in a ForAll is not an issue.  It would just introduce a Patch.

Ex.

Patch(dataSource, ForAll(recordList, Patch(Defaults(dataSource), {column:Values})))

Another BONUS...some functions have little known signatures.  For example, Removing records - this is another place where people complain on performance when they do the following:

ForAll(table As _item, RemoveIf(dataSource, ID=_item.ID))

The formula is better written because of one additional signature to the Remove function - the one where you specify a DataSource and a Table.  In this case, it is looking always for the primary key.

So, the above formula would be written as:

Remove(dataSource, ForAll(table, {ID: ID}))

The ForAll would produce a table of ID's (the primary key in this case).  Remove then knows exactly what to do with that.

However, in the above, the ForAll really isn't needed as the following is equivalent:

Remove(dataSource, table.ID)

I do fear that more and more will adopt trying to use the ForAll as a for loop and cringe when I see the docs try to placate to those that use it as such, because, ultimately yes, you can use it that way, but, there are performance implications and, it really detracts from the way that PowerApps was designed - as a no-code Excel-like app designer.  The more people try to make it "development", the harder things get and the more difficult and complicated things are to maintain and manage.

And, the simplicity of the formulas produced when using it properly are superior to when used the other way around.

I just hope they never allow setting variables or other things in a ForAll as that will completely detract from the purpose.  And as mentioned before, when using the ForAll as intended - it becomes the most powerful function in PowerApps.  When used as a For Loop, it is a "so-so" function, and annoying to many because you can't do certain things in it.

Hopefully that is clear and helpful for you.