Update if record exist, patch if record doesn't exist

[AnthonyDo]

Hi guys,

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

This is how i check if the records do exist in datasource by a filter:

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

This is my code and commented with 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
            )
        ),
    //if the records exist, patch them with the same records or update the values that did change (order could 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 yet patch the record
        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

[PG_WorXz10]

Hi @AnthonyDo ,

Something like below ?

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
            )
        ),
    //if the records exist, patch them with the same records or update the values that did change (order could change)
        UpdateIf(
		'Custom Entity [dbo].[PostcodeRange]s',
		ID = colEditMakelaarsToAdd.ID,
		{
		 MakelaarId: MakelaarIdNew,
                Order: sortnummer,
                PostcodeEinde: Value(TextInput2_1.Text),
                PostcodeStart: Value(TextInput1_1.Text)
		}
		),
    //if the records don't exist yet patch the record
        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)
            }
        )
    )
)

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.

[AnthonyDo]

Hi @PG_WorXz10 , 

I changed your code to: (i cant do "id=coleditmakelaartoadd.id" it gives me error "text cant be compared to table")

Therefor i changed "=" to "in"

The code patches all the records again, ending up in duplicates

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=sortnummer
            )
        ),
    //if the records exist, patch them with the same records or update the values that did change (order could change)
        UpdateIf(
		'Custom Entity [dbo].[PostcodeRange]s',
		MakelaarId in colEditMakelaarsToAdd.MakelaarIdNew&&PostcodeStart=postcodeStart,
		{
		        MakelaarId: MakelaarIdNew,
                Order: sortnummer,
                PostcodeEinde: Value(TextInput2_1.Text),
                PostcodeStart: Value(TextInput1_1.Text)
		}
		),
    //if the records don't exist yet patch the record
        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

[PG_WorXz10]

Hi @AnthonyDo 

Please try below code once. 

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 ThisRecord.MakelaarIdNew
            )
        ),
    //if the records exist, patch them with the same records or update the values that did change (order could change)
          UpdateIf(
		'Custom Entity [dbo].[PostcodeRange]s',
		MakelaarId = ThisRecord.MakelaarIdNew &&  PostcodeStart=ThisRecord.postcodeStart,
		{
		        MakelaarId: MakelaarIdNew,
                Order: sortnummer,
                PostcodeEinde: Value(TextInput2_1.Text),
                PostcodeStart: Value(TextInput1_1.Text)
		}
		),
    //if the records don't exist yet patch the record
        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)
            }
        )
    )
)

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.

[AnthonyDo]

@PG_WorXz10 

Trying out your code it results in a error. Could this be because editing a record isn't supported yet on virtual tables?

orAll(
    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 ThisRecord.MakelaarId
            )
        ),
    //if the records exist, patch them with the same records or update the values that did change (order could change)
          UpdateIf(
		'Custom Entity [dbo].[PostcodeRange]s',
		MakelaarId = ThisRecord.MakelaarId &&  PostcodeStart=ThisRecord.PostcodeStart,
		{
		        MakelaarId: MakelaarIdNew,
                Order: sortnummer,
                PostcodeEinde: Value(TextInput2_1.Text),
                PostcodeStart: Value(TextInput1_1.Text)
		}
		),
    //if the records don't exist yet patch the record
        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)
            }
        )
    )
);

[PG_WorXz10]

Hi @AnthonyDo ,

Instead of in please try = 

[AnthonyDo]

If i change the in to =:

[WarrenBelz · Accepted solution]

Hi @AnthonyDo ,

Try this

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

This is assuming the first two fields are inside the collection and the Text Boxes are on the screen.

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.