Modify / Add Data from an SQL View Using EditForms

[YongJaco]

Hello! I'm still pretty new with PowerApps and would like to know the best way to edit / add data in an SQL view. Basically, I have a canvass app connected to an SQL DB. The DB has multiple tables and consolidated to a view. If I want to add / edit the view, I need to edit the underlying table (let's call it master table) where it gets the data from. I used an edit form with master table as the source.

Is lookup function an effective way to do this? I tried patterning my lookup with Shane Young's tutorial video but just couldn't make it work and I always get the message "This formula uses scope, which is not presently supported for evaluation." I don't think it would be that straight forward since I want to edit the master table so in the editform, the datacard values should show it's corresponding value from the view and not the master table.

[eka24]

In Powerapps, you cannot edit SQL view and therefore cannot connect it to an editform.

Rather connect the underlying Tables to the Editform. You can connect the view to a Gallery to view the information.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[YongJaco]

Hello! That's what I have set up, sorry if my initial description wasn't clear. What I have designed is when I select from the data table, it will show all the row's details (from SQL view). But when I edit, on the edit form (which uses an underlying table as source), I want to see the values from the view instead of the underlying table. For instance, my SQL view shows a product name but the SQL table source shows it's product code. This product code is then related to another table which contains the product name matching the product code.

[eka24]

What is the name of the Table that connects the editform. 

What is the name of the view that connects the Datatable.

What colum Link the two tables

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[YongJaco]

What is the name of the Table that connects the editform. -- ClientProductUse

What is the name of the view that connects the Datatable. -- ClientProductUseView

What colum Link the two tables -- ClientProductUseID from ClientProductUse table matches with ClientProductUseViewID.

[eka24]

My understanding is that when you select from the Datatable, it should show corresponding in the edit form.

So on the Item property of the form Put:

Filter(ClientProductUse,ClientProductUseID=DatatableName.Selected.ClientProductUseView)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[YongJaco]

Yes, the datatable would show its details in the form but since I used a different source form the form, it's just blank on all fields. I tried the function below but it did not give the desired result. Will try patch this time and see if that let's me to execute the idea.

[eka24]

Please are you saying it didn't show the Data in the form?

Can you give a screenshot..

Maybe I don't understand exactly what you want to do

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[YongJaco]

Hi! Thanks so much for the patience. I appreciate I might not be explaining things too well since I'm still new to this. Unfortunately, I cannot take a screenshot of the powerapps and db as they I am working on a copy of actual data. I'll explain here as much as I can using some screenshots I made.

1. This is how the datatable looks like. It shows the actual product name and uses the view.

2. Then this is the editform. The value it will display is a product ID.

Another table manages the relation between the product ID and product name. In the DB, the ID is set to INT while name is VARCHAR. We programmed the view in SQL to display names based on product IDs from editform source table.

So basically, I should pass the value of the selected item in the datatable to the editform. However, the editform then should display the productname selected from the datatable instead of the productID based on the Products database. Then when the form is submitted, instead of product name, it would just pass the ID back to the editform's source table since the variable holding it in the DB is an INT. The view will then adjust accordingly.

Apologies for the very long explanation but I hope I made it more clear 🙏

[YongJaco · Accepted solution]

Apologies for not getting back on this thread. I decided to use PATCH() to update our database.