Forall patch if records don't exist, update if records exist

[Anonymous]

Hi guys,

I am trying to check if records inside a collection already exist in my datasource. If they do exist in the datasource i just want to update them with the same values/modified values. If they don't exist i want to patch the new records to the datasource.

This formula below returns me the records inside my collection that already exist in the datasource.

Set(
    varRecord,
    Filter(
        'Custom Entity [dbo].[PostcodeRange]s',
        MakelaarId in colEditMakelaarsToAdd.MakelaarIdNew && PostcodeStart = postcodeStart && Order in colEditMakelaarsToAdd.sortnummer
    )
)

The formula below explains what i am trying to achieve.

ForAll(
    colEditMakelaarsToAdd,
    //check if the records from "coleditMakelaarsToAdd" exist in the datasource "Custom Entity [dbo].[PostcodeRange]s"
    If(
        IsBlank(
            LookUp(
                'Custom Entity [dbo].[PostcodeRange]s',
                MakelaarId in colEditMakelaarsToAdd.MakelaarIdNew && PostcodeStart = postcodeStart && Order in colEditMakelaarsToAdd.sortnummer
            )
        ),
    //if the records exist in the datasource, update them with the same values or update the values that did change
        Patch(
            'Custom Entity [dbo].[PostcodeRange]s',
            Defaults('Custom Entity [dbo].[PostcodeRange]s'),
            {
                MakelaarId: MakelaarIdNew,
                Order: sortnummer,
                PostcodeEinde: Value(TextInput2_1.Text),
                PostcodeStart: Value(TextInput1_1.Text)
            }
        ),
    //if the records don't exist in the datasource, patch the new records
        Patch(
            'Custom Entity [dbo].[PostcodeRange]s',
            Defaults('Custom Entity [dbo].[PostcodeRange]s'),
            {
                MakelaarId: MakelaarIdNew,
                Order: sortnummer,
                PostcodeEinde: Value(TextInput2_1.Text),
                PostcodeStart: Value(TextInput1_1.Text)
            }
        )
    )
);

Best Regards,
Anthony

[zmansuri]

Power Apps Editable Table/Gallery like Excel (Tutorial) - YouTube

In this videos same concept is used except for the ForAll. To update the record "Update" function is used. 

[WarrenBelz]

Hi @Anonymous ,

Add your unique identifier field name where noted

ForAll(
   colEditMakelaarsToAdd As aPatch,
   With(
      {
         wItem:
         LookUp(
            'Custom Entity [dbo].[PostcodeRange]s',
            MakelaarId in colEditMakelaarsToAdd.MakelaarIdNew && 
            PostcodeStart = postcodeStart && 
            Order in colEditMakelaarsToAdd.sortnummer
         ).YourIdentifier
      },      
      Patch(
         'Custom Entity [dbo].[PostcodeRange]s',
         If(
            IsBlank(wItem),
            Defaults('Custom Entity [dbo].[PostcodeRange]s'),
            {YourIdentifier = aPatch.YourIdentifier}
         ),
         {
            MakelaarId: aPatch.MakelaarIdNew,
            Order: aPatch.sortnummer,
            PostcodeEinde: Value(aPatch.TextInput2_1.Text),
            PostcodeStart: Value(aPatch.TextInput1_1.Text)
         }
      )
   )
)

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.

[Anonymous]

Deer @WarrenBelz ,

I tried your code but it wasn't behaving the way it should. It was patching all the records again even if it already exists. So i made some modifications and got my code to work for 1 thing. 

It now only patches the new records to the datasource but it is not modifying existing records when i change the order i put them in. How could i make this achievable?

My code:

ForAll(
        colEditMakelaarsToAdd As aPatch,
        With(
            {
                wItem: LookUp(
                    'Custom Entity [dbo].[PostcodeRange]s',
                    Text(GUID(MakelaarId)) = Text(GUID(aPatch.MakelaarIdNew)) && PostcodeStart = postcodeStart && Order = aPatch.sortnummer
                ).MakelaarId
            },
            If(
                !IsBlank(wItem),
                UpdateIf(
                    'Custom Entity [dbo].[PostcodeRange]s',
                    Text(GUID(MakelaarId)) = Text(GUID(aPatch.MakelaarIdNew)) && PostcodeStart = postcodeStart,
                    {
                        MakelaarId: aPatch.MakelaarIdNew,
                        Order: aPatch.sortnummer,
                        PostcodeEinde: Value(TextInput2_1.Text),
                        PostcodeStart: Value(TextInput1_1.Text)
                    }
                ),
                Patch(
                    'Custom Entity [dbo].[PostcodeRange]s',
                    Defaults('Custom Entity [dbo].[PostcodeRange]s'),
                    {
                        MakelaarId: aPatch.MakelaarIdNew,
                        Order: aPatch.sortnummer,
                        PostcodeEinde: Value(TextInput2_1.Text),
                        PostcodeStart: Value(TextInput1_1.Text)
                    }
                )
            )
        )
    )

Best Regards,

Anthony

[WarrenBelz · Accepted solution]

HI @Anonymous ,

Firstly, I cannot see your data or logic, but the code you have posted

ForAll(
   colEditMakelaarsToAdd As aPatch,
   With(
      {
         wItem: 
         LookUp(
            'Custom Entity [dbo].[PostcodeRange]s',
            Text(GUID(MakelaarId)) = Text(GUID(aPatch.MakelaarIdNew)) && 
            PostcodeStart = postcodeStart && Order = aPatch.sortnummer
         ).MakelaarId
      },
      If(
         !IsBlank(wItem),
         UpdateIf(
            'Custom Entity [dbo].[PostcodeRange]s',
            Text(GUID(MakelaarId)) = Text(GUID(aPatch.MakelaarIdNew)) && 
            PostcodeStart = postcodeStart,
            {
               MakelaarId: aPatch.MakelaarIdNew,
               Order: aPatch.sortnummer,
               PostcodeEinde: Value(TextInput2_1.Text),
               PostcodeStart: Value(TextInput1_1.Text)
            }
         ),
         Patch(
            'Custom Entity [dbo].[PostcodeRange]s',
            Defaults('Custom Entity [dbo].[PostcodeRange]s'),
            {
               MakelaarId: aPatch.MakelaarIdNew,
               Order: aPatch.sortnummer,
               PostcodeEinde: Value(TextInput2_1.Text),
               PostcodeStart: Value(TextInput1_1.Text)
            }
         )
      )
   )
)

assumes a couple of things - this bit

LookUp(
   'Custom Entity [dbo].[PostcodeRange]s',
   Text(GUID(MakelaarId)) = Text(GUID(aPatch.MakelaarIdNew)) && 
   PostcodeStart = postcodeStart && Order = aPatch.sortnummer
).MakelaarId

looks for a field (actually the identifier) in a record with the same unique identifier as the item in the collection you are looping through and will return the field value if is is found. This bit

If(
   !IsBlank(wItem),
   UpdateIf(
      'Custom Entity [dbo].[PostcodeRange]s',
      Text(GUID(MakelaarId)) = Text(GUID(aPatch.MakelaarIdNew)) && 
      PostcodeStart = postcodeStart,
      { . . .  .}

which is different to what I posted Updates all records matching the filter you have posted - why are you using UpdateIf this rather than

If(
   !IsBlank(wItem),
   Patch(
      'Custom Entity [dbo].[PostcodeRange]s',
      {MakelaaarId:aPatch.MakelaaarId},
      { . . .  .}

which would update the existing record found by the unique identifier you have just established exists on the record?

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.

[Anonymous · Accepted solution]

Deer @WarrenBelz ,

It now works the way it should work. This is the code:

ForAll(
        colEditMakelaarsToAdd As aPatch,
        With(
            {
                wItem: LookUp(
                    'Custom Entity [dbo].[PostcodeRange]s',
                    Text(GUID(MakelaarId)) = Text(GUID(aPatch.MakelaarIdNew)) && PostcodeStart = postcodeStart && Order = aPatch.sortnummer
                ).MakelaarId
            },
            If(
                !IsBlank(wItem),
                Patch(
                    'Custom Entity [dbo].[PostcodeRange]s',
                    {MakelaarId: aPatch.MakelaarIdNew}
                ),
                Patch(
                    'Custom Entity [dbo].[PostcodeRange]s',
                    Defaults('Custom Entity [dbo].[PostcodeRange]s'),
                    {
                        MakelaarId: aPatch.MakelaarIdNew,
                        Order: aPatch.sortnummer,
                        PostcodeEinde: Value(TextInput2_1.Text),
                        PostcodeStart: Value(TextInput1_1.Text)
                    }
                )
            )
        )
    )

Can you explain me what this bit does?

Patch(
                    'Custom Entity [dbo].[PostcodeRange]s',
                    {MakelaarId: aPatch.MakelaarIdNew}
                )

 I thought it would only update the existing record's id with this patch function. But it updates the order aswell.

Best Regards and thank you,

Anthony 

[WarrenBelz]

@Anonymous ,

That is simply identifying the record to be patched by matching the unique identifier with the correct item in the collection and then updates the record.