Patch "collection" message after switching to SQL Azure View

[RobRoy]

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:

Thanks,

Rob

[timl]

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]

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]

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

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?

[Meneghino]

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.

[timl]

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]

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?

[timl]

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]

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.

[RobRoy]

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