cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnthonyDo
Helper III
Helper III

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

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.

View solution in original post

7 REPLIES 7
PG_WorXz10
Community Champion
Community Champion

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.

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

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

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.

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

@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?

AnthonyDo_0-1630574440234.png

 

 

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)
            }
        )
    )
);

 

Hi @AnthonyDo ,

 

Instead of in please try = 

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

If i change the in to =:

 

AnthonyDo_0-1630580134963.png

 

WarrenBelz
Super User
Super User

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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,231)