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

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
timl
Super User
Super User

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.

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.

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
Community Champion
Community Champion

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.

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.

 

 

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?

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
Community Champion
Community Champion

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.

@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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,793)