Patch Modification problems

[Anonymous]

Hello,

I've been having problems regarding the code below.

What the code is supposed to do is look into the SharePoint List 'Service Catalog Customer Data' and check if there is a record in it where it has the same Year, Customer, Country and Question values (acting as a composite key).

If there are no record of that particular combination then it should just create a new record using the values specified.

If there is a record of that particular combination then it should update it with the same values specified.

This is then looped through a number of times depending on how many questions are in a separate list (currently in my test data there are 10 questions).

My code adds all the new records as expected. However it will add a new record, instead of modifying one, if a record with the same composite key exists in the SharePoint List. E.g. if there is one record in the SharePoint after this code runs there will be 11 instead of 10 and the original record will be unchanged

The only thing I can think off is that the If Statement Logical Test is always saying true - so it always uses the Defaults () for the Patch. However I don't know why it would be always true if there is a record that already exists filling those parameters.

All of the fields of the composite key are single line of text in the SharePoint list so it's not an issue of data types (at least I don't think it would be).

Could someone help me and explain what's going wrong in my code?

ForAll(
    'Service Catalog Questions',
    Patch(
        'Service Catalog Customer Data',
        If(
            IsBlank(
                LookUp(
                    'Service Catalog Customer Data',
                    Year = Year_Dropdown.SelectedText.Value & Customer = Customer_Dropdown.SelectedText.Value & 'Country (Country0)' = Country_Dropdown.SelectedText.Value & Question = ThisRecord.Question
                )
            ),
            Defaults('Service Catalog Customer Data'),
            LookUp(
                'Service Catalog Customer Data',
                Year = Year_Dropdown.SelectedText.Value & Customer = Customer_Dropdown.SelectedText.Value & 'Country (Country0)' = Country_Dropdown.SelectedText.Value & Question = ThisRecord.Question
            )
        ),
        {
            Year: Year_Dropdown.SelectedText.Value,
            Customer: Customer_Dropdown.SelectedText.Value,
            'Country (Country0)': Country_Dropdown.SelectedText.Value,
            Question: ThisRecord.Question,
            'Total Gross Revenue': Int(DataCardValue4.Text),
            'Total Net Revenue': Int(DataCardValue5.Text),
            'TTL Whs Cost': Int(DataCardValue6.Text),
            'TTL Trans Cost': Int(DataCardValue7.Text),
            'TTL Other Cost': Int(DataCardValue8.Text),
            'TTL Cost of Running Promotions': Int(DataCardValue9.Text),
            'TTL In Invoice Discounts': Int(DataCardValue10.Text),
            'TTL Off Invoice Discounts': Int(DataCardValue11.Text)
        }
    )
);

[Nogueira1306]

ForAll(
    'Service Catalog Questions',
        If(
            IsBlank(
                LookUp(
                    'Service Catalog Customer Data',
                    Year = Year_Dropdown.SelectedText.Value & Customer = Customer_Dropdown.SelectedText.Value & 'Country (Country0)' = Country_Dropdown.SelectedText.Value & Question = ThisRecord.Question
                )
            ),
             Patch(
                    'Service Catalog Customer Data',
                     Defaults('Service Catalog Customer Data'),
{
            Year: Year_Dropdown.SelectedText.Value,
            Customer: Customer_Dropdown.SelectedText.Value,
            'Country (Country0)': Country_Dropdown.SelectedText.Value,
            Question: ThisRecord.Question,
            'Total Gross Revenue': Int(DataCardValue4.Text),
            'Total Net Revenue': Int(DataCardValue5.Text),
            'TTL Whs Cost': Int(DataCardValue6.Text),
            'TTL Trans Cost': Int(DataCardValue7.Text),
            'TTL Other Cost': Int(DataCardValue8.Text),
            'TTL Cost of Running Promotions': Int(DataCardValue9.Text),
            'TTL In Invoice Discounts': Int(DataCardValue10.Text),
            'TTL Off Invoice Discounts': Int(DataCardValue11.Text)
        }
),
Patch(
                    'Service Catalog Customer Data',
                     LookUp(
                    'Service Catalog Customer Data',
                     Year = Year_Dropdown.SelectedText.Value & Customer = Customer_Dropdown.SelectedText.Value & 'Country (Country0)' = Country_Dropdown.SelectedText.Value & Question = ThisRecord.Question
            ),
        {
            Year: Year_Dropdown.SelectedText.Value,
            Customer: Customer_Dropdown.SelectedText.Value,
            'Country (Country0)': Country_Dropdown.SelectedText.Value,
            Question: ThisRecord.Question,
            'Total Gross Revenue': Int(DataCardValue4.Text),
            'Total Net Revenue': Int(DataCardValue5.Text),
            'TTL Whs Cost': Int(DataCardValue6.Text),
            'TTL Trans Cost': Int(DataCardValue7.Text),
            'TTL Other Cost': Int(DataCardValue8.Text),
            'TTL Cost of Running Promotions': Int(DataCardValue9.Text),
            'TTL In Invoice Discounts': Int(DataCardValue10.Text),
            'TTL Off Invoice Discounts': Int(DataCardValue11.Text)
        }
    )
);

[RandyHayes · Accepted solution]

@Anonymous 

Quite a few things in your formula:

1) Your ForAll is backward.  You are using it as a ForLoop like in development - which PowerApps is not.  ForAll is a function that returns a table of records based on your source table and record definition.  Your ForAll is STILL going to go through that process and will generate a potentially large table based on the Patch you have in it that will then ultimately be discarded as you are not using it...this is a big hit on performance!

2) You are using the SelectedText property of your controls - this is a Deprecated property that should be avoided.  Instead you should be using the .Selected.column  (for the sake of the suggested formula below, I will assume Value as the column name)

3) You are specifically converting text numbers into an Integer (no decimal values).  You have things like Cost which is commonly currency and commonly would have decimal values, so I would suggest (and have altered in the suggestion below) using the Value function instead of Int.\

4) You are using a single ampersand in your Lookup formula.  This will concatenate values together.  I am pretty sure you wanted to "AND" the criteria, in which case, you need a double ampersand &&

Please consider changing your Formula to the following:

Patch(
    'Service Catalog Customer Data',
    ForAll('Service Catalog Questions' As _item,
        {
            ID: LookUp(
                    'Service Catalog Customer Data',
                    Year = Year_Dropdown.Selected.Value && 
                    Customer = Customer_Dropdown.Selected.Value && 
                    'Country (Country0)' = Country_Dropdown.Selected.Value && 
                    Question = _item.Question,
                    ID
                ),
            Year: Year_Dropdown.Selected.Value,
            Customer: Customer_Dropdown.Selected.Value,
            'Country (Country0)': Country_Dropdown.Selected.Value,
            Question: ThisRecord.Question,
            'Total Gross Revenue': Value(DataCardValue4.Text),
            'Total Net Revenue': Value(DataCardValue5.Text),
            'TTL Whs Cost': Value(DataCardValue6.Text),
            'TTL Trans Cost': Value(DataCardValue7.Text),
            'TTL Other Cost': Value(DataCardValue8.Text),
            'TTL Cost of Running Promotions': Value(DataCardValue9.Text),
            'TTL In Invoice Discounts': Value(DataCardValue10.Text),
            'TTL Off Invoice Discounts': Value(DataCardValue11.Text)
        }
    )
);

In the formula above, the ForAll will iterate over the 'Service Catalog Questions' table.  For each record in that, it will LookUp the record that matches the dropdowns for Year, Customer and Country and then the Question that is in the current record of the ForAll source - which is now name with the As operator as "_item".

The ONLY thing we care about for that record is the ID.  If it is found, then ID will be a valid ID.  If it is not found, then the ID will be blank.

The rest of the record definition for the records returned from the ForAll continues as you had before, with the exception of the corrections for the SelecteText and the Int functions.

At the end of the ForAll, there will be a table returned.  That entire table will be passed to the Patch function.

Patch is smart!!

It can take an entire table to work on rather than having to instantiate itself over and over (when the ForAll is used wrong).

That table is coming from the ForAll - as that is what a ForAll does!

PLUS, patch is smart enough to look at the primary key value to determine what to do.  IN your case, the ID is the primary key, SO, if the ID has a value, then Patch will update the record with that ID and the record provided with the updates. 

IF the ID is Blank, then Patch will create a new record with the provided record.

That is all you need. 

This will be much more performant and easier to maintain and deal with.

I hope this is helpful for you.

[Anonymous]

Thank you so much!

Aside from a couple minor changes like datatypes and the selected columns which you mentioned it worked perfectly 🙂