cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonMeadows
Resolver I
Resolver I

Saving new record with Patch function

I have a function for generating a customer code that checks all existing codes and increments the highest by 1 to create a new one.

This is the formula:

Patch(newCust,ThisItem,{company: newCompany.Text});
Clear(codeCount);
ForAll(
    Search(
        '[LV1_DB].[Customer]',
        Left(First(newCust).company,4),
        "AccountCode"
    ),
    Collect(
        codeCount,
        {
            Value: Value(
                Right(
                    AccountCode,
                    2
                )
            )
        }
    )
);
ForAll(
    Search(
        '[dbo].[tblCust]',
        Left(First(newCust).company,4),
        "Customer_code"
    ),
    Collect(
        codeCount,
        {
            Value: Value(
                Right(
                    Customer_code,
                    2
                )
            )
        }
    )
);
Patch(
    newCust,
    ThisItem,
    {
        code: Concatenate(
            Upper(
                Left(ThisItem.company,4)
            ),
            Text(
                Max(
                    codeCount,
                    Value
                )+1,
                "[$-en-US]00"
            )
        )
    }
)

If I have both sections of the formula run on the OnChanged event of the newCompany field it creates a new record in my newCust collection.

 

If I split the formula as above, put the first part on the OnChanged event of the newCompany field and the second part on a button it patches the current record as it should.

 

This formula is running within the context of a Gallery on a collection that would normally only have one record.

I am not using the Default() function so why would it be creating new records?

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @SimonMeadows

I have to admit I did not work through all the details of your post, however here is what I learned about the Defaults(MySource) function and its combined use with Patch().

 

1) The Defaults function will put out a new call to the data source each time it is used, so if you are patching multiple records this impacts app performance negatively (for example within a ForAll loop).  In order to avoid this, I normally create a collection like this:

ClearCollect(MySourceDefault, Defaults(MySource))

I normally do this as part of the OnStart of the first screen.  Then I can use Patch(MySource, First(MySourceDefault), etc. etc.) instead of the usual Patch(MySource, Defaults(MySource), etc. etc.)

 

2) Actually, what follows from 1 above is that you do not even need to call the Defaults function at all unless you really have some explicit default values.  The following for example works with Azure SQL DB to create a new record where the table's primary key is ID

Patch(MySource, {ID: Blank()}, etc. etc.)

I am not sure this helps, but if for some reason ThisItem returns a record which has Blank() as the value of the primary key field(s), then a new record will be created.

 

Hope this helps.

View solution in original post

2 REPLIES 2
SimonMeadows
Resolver I
Resolver I

Ok, so im half answering my own question but it would be good to know why it makes a difference.

 

If I change all references from 'ThisItem' to 'First(newCust)' the formula operates as expected.

 

Why, in the gallery, if im in the context of the first record in the newCust collection would there be any difference between the reference to 'ThisItem' and and the reference to 'First(newCust)'?

Shouldn't they be the same record reference?

Hi @SimonMeadows

I have to admit I did not work through all the details of your post, however here is what I learned about the Defaults(MySource) function and its combined use with Patch().

 

1) The Defaults function will put out a new call to the data source each time it is used, so if you are patching multiple records this impacts app performance negatively (for example within a ForAll loop).  In order to avoid this, I normally create a collection like this:

ClearCollect(MySourceDefault, Defaults(MySource))

I normally do this as part of the OnStart of the first screen.  Then I can use Patch(MySource, First(MySourceDefault), etc. etc.) instead of the usual Patch(MySource, Defaults(MySource), etc. etc.)

 

2) Actually, what follows from 1 above is that you do not even need to call the Defaults function at all unless you really have some explicit default values.  The following for example works with Azure SQL DB to create a new record where the table's primary key is ID

Patch(MySource, {ID: Blank()}, etc. etc.)

I am not sure this helps, but if for some reason ThisItem returns a record which has Blank() as the value of the primary key field(s), then a new record will be created.

 

Hope this helps.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,851)