cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RobRoy
Level: Powered On

Patch "collection" message after switching to SQL Azure View

First, thank you so much for the ability to use SQL Azure views! Being able to search ALL my records and not just the first 500 is a game-changer.

 

I changed the data source of my primary gallery from a SQL Azure table to a view, and now my "favorites" checkboxes feature is not working. The Patch function now says "The first argument of 'Patch' should be a collection". Here is the checkbox code:

 

Patch('[dbo].[EventsView]',First(Filter('[dbo].[EventsView]', EventFavorite=ThisItem.EventFavorite)),{EventFavorite: If(ThisItem.EventFavorite=true,false,true)})

Does this have something to do with not being able to edit views? Here is the code in context:

 

Patch.jpg


Thanks,


Rob

 

 

11 REPLIES 11
Super User
Super User

Re: Patch "collection" message after switching to SQL Azure View

Hi Rob,

 

Yes, you're right about views not supporting updates. In general, we would add triggers to views to enable insert and update functionality. However, PowerApps doesn't support triggers so this isn't a viable workaround.

 

https://msdn.microsoft.com/en-us/library/def01zh2.aspx

 

To get around this problem, you could still set the items property of your form/gallary control to a SQL view, but modify your patch function so that it patches to the table, rather than the view.

RobRoy
Level: Powered On

Re: Patch "collection" message after switching to SQL Azure View

I did try switching to Patch the table, and PowerApps doesn't complain... but nothing happens on select.

 

Patch('[dbo].[Events]',First(Filter('[dbo].[Events]', EventFavorite=ThisItem.EventFavorite)),{EventFavorite: If(ThisItem.EventFavorite=true,false,true)})

If I manually check the box in the SQL database then the stars fill in as expected.

RobRoy
Level: Powered On

Re: Patch "collection" message after switching to SQL Azure View

I was able to update my SQL table by doing as you suggested and patching the table while still using a view:

 

FavoritesPatch.jpg

 

Although I did have to add a Refresh action to get it it update, even though the source is delegable.

 

Now I am trying the same thing with a standard EditForm but I can't seem to get the syntax correct. The changes do not save for the text box. I haven't even tried changing the date yet. Am I close?

 

EventsPatch.jpg

Meneghino
Level 10

Re: Patch "collection" message after switching to SQL Azure View

HI @RobRoy

Using the First() as the second argument of Patch is a performance killer.

Much better to use {ID: ThisItem.ID} as the second argument, where ID is your primary key column.

This also works where there is a composite primary key.

Please let me know how you get on.

Super User
Super User

Re: Patch "collection" message after switching to SQL Azure View

Hi @RobRoy

 

I suspect this part of your formula might be the cause of your error...

 

{Event: Value(DataCardValue11.Text)}

The Value function converts a string to a number. I suspect your formula is failing when PowerApps attempts to convert the string in your textbox ("I worked extensively on my PowerApps app for the life of me") to a number.

 

 

RobRoy
Level: Powered On

Re: Patch "collection" message after switching to SQL Azure View

Ah, thanks! The Favorites buttons work now using this code:

 

Patch('[dbo].[Events]',{ID:ThisItem.ID},{EventFavorite: If(ThisItem.EventFavorite=true,false,true)});Refresh('[dbo].[EventsView]')

I tried a similar function for the EditForm. It sort of works, using this code:

 

Patch('[dbo].[Events]',{ID:BrowseGallery1.Selected.ID},{Event: DataCardValue11.Text});Back()

I need the Back function because it doesn't return to the DetailForm even though the record is successfully updated. Also, the DetailForm does not refresh and show the changes. I tried including a Refresh function but that just loaded a proceeding records DetailForm.

 

But I have another problem. Because I am using Patch instead of the SubmitForm function that usually goes with the checkmark, any new record I create just overwrites the first ID record (1). The code for adding a new record states:

 

NewForm(EditForm1);Navigate(EditScreen1, ScreenTransition.None)

So it uses the same form. Do I need to create a whole new EditForm for new records?

Super User
Super User

Re: Patch "collection" message after switching to SQL Azure View

Hi @RobRoy

 

I'm glad that your favorites button now works! With regards to your question about adding new records, you can avoid the need to add an entire new form for new records by adding conditional code. For example, you can apply an If statement to your Patch function so that runs only when the form is in edit mode.

 

 

If(EditForm1.Mode = FormMode.Edit,
  Patch('[dbo].[Events]',{ID:BrowseGallery1.Selected.ID},{Event: DataCardValue11.Text})
)

You can apply this same technique to the other parts of your form that want to run only when your form is in new/edit mode.

 

 

Meneghino
Level 10

Re: Patch "collection" message after switching to SQL Azure View

In my experience it is much easier in the end to build a blank canvas app and avoid the use of forms, just build the forms you need from the individual controls.

Highlighted
RobRoy
Level: Powered On

Re: Patch "collection" message after switching to SQL Azure View

@Meneghino That will be the plan once I feel comfortable working with the built-in forms.

 

@timl Clicking the checkbox performed no action using your code, so I tried adding an else statement. But I received an error message stating "The specified column is generated by the server and can't be specified.". I thought it might refer to the fact I didn't include the Date field, which was the only other field (New record = Date and Event). But that wasn't it.

 

If(EditForm1.Mode = FormMode.Edit, Patch('[dbo].[Events]',{ID:BrowseGallery1.Selected.ID},{Event: DataCardValue11.Text}, {EventDate: DataCardValue12}),SubmitForm(EditForm1))

Event.jpg

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,447)