cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Creating an App using SQL Database

Hi,

I am building an App using SQL database, i have the following issues:-

1. I Have a Vendor Table,Vendor Category table , a vendor can have Multi categories (need 3 screens , browse - edit - new), the issue here is assigning Muti records in one screen , lookup and search for the Category name and not Category ID , lookup and search for Vednor Name and Not Vendor ID , there is an issue also with edit screen , it is showing the Field ID (Category ID , Vendor ID which user can not understand)
2. The same Vendor is linked to Multi Sub Categories (the issue here is assigning Muti choice to the same Vendor and be able to edit them and delete them if needed - Category here also should show only Categories assigned to Vendor in Point # 1 above)

3. I am using data table to show the records entered by a user in a parent and Child tables (user fills in the header info first then enter the details which can be more than one entry (repeated entries) , the issue here also is Data table is read only and I can not chage fields like Category ID to show Category name instead. 

Any suggestions and help will be really appreciated.

 

Regards

 

5 REPLIES 5
Anonymous
Not applicable

Re: Creating an App using SQL Database

Hi @Anonymous

 

I suggest you create SQL Views that already merge your tables and querying the Views in PowerApps as it will save you from making multiple calls to your SQL database.

 

I would avoid using Data Table as it has some issues and would use Galleries that are styled like tables.

 

I have a bit of trouble understanding your issues. Maybe some pictures of what you want to achieve could help.

Anonymous
Not applicable

Re: Creating an App using SQL Database

Hi @Anonymous

 

Thanks for your reply.

 

My requirement is: 

 

1. Vendor Table (Vendir ID, name, E-mail,type) 

2. Category Table(Category ID,Name)

A vendor can have Muti Categories (Let's say Category1,Category2,Category3 Etc....)

I need the user to Search for A vendor And assign Mutil categories to him (Entry Screen) , and be able to edit them and delete them also (View and edit Screen)

 

Regards

 

Anonymous
Not applicable

Re: Creating an App using SQL Database

Hi @Anonymous

 

You need a third table if Categories can have multiple Vendors. Something Like:

Vendor Category Table

CREATE TABLE VendorCategoryTable (
   VendorID int NOT NULL FOREIGN KEY REFERENCES Vendor(VendorID),
   CategoryID int NOT NULL FOREIGN KEY REFERENCES Category(CategoryID),
   CONSTRAINT PK_VendorCategoryTable PRIMARY KEY (VendorID,CategoryID)
);

*However, if Category can only be assigned to one Vendor then you need to add VendorID as Foreign Key in Category table.

 

So now you have your three SQL tables Vendor, Category, and VendorCategoryTable.

 

Create an SQL View

Vendor Category SQL View 

CREATE VIEW VendorCategoryView AS SELECT Vendor.VendorID, Vendor.Name, Vendor.Email, Vendor.Type, Category.CategoryID, Category.Name FROM Vendor INNER JOIN VendorCategoryTable ON Vendor.VendorID = VendorCategoryTable.VendorID INNER JOIN Category ON VendorCategoryTable.CategoryID = Category.CategoryID ;

Now you can go ahead and create your app.

 

In the App first Gallery will display all your Vendors

FirstGallery.Items

'[dbo].[Vendor]'

Your second gallery will display all categories belonging to a vendor

SecondGallery.Items

Filter(
  '[dbo].[VendorCategoryView]',
  VendorID = FirstGallery.Selected.VendorID
)

Now from here you can also have buttons:

1. To remove category from vendor (Use Remove() function on VendorCategoryTable)

2. To add categoty to vendor from Category table (Use Patch() function on VendorCategoryTable with VendorID and CategoryID)

3. To create a new category in Category table (Use Patch() function Category Table)

 

 

 

Anonymous
Not applicable

Re: Creating an App using SQL Database

Hi @Anonymous

Thanks for your reply and sorry for late reply.

I am done with first screen (Display screen in which user can select the Vendor and see all categories assigned to him below in a gallery)

 

But still I have problem with Assignning new categories to a Vendor and Edit and delete screen also , can you please give me more time and explain how to make the screen (you mentioned only the functions to be used to submit the data back to SQL database which is very good but I need the screen for the user to interact)

 

Thanks in advance and really appreciate your response .

 

Rregards

 

seadude
Level 10

Re: Creating an App using SQL Database

Thank you for the write up on SQL Views. Do you have more PowerApp-specific examples you can share?

 

Specifically:

 

  • How do you model these Views ahead of time to include in your apps?
  • I'm familiar with creating Entity-Relationship Diagrams to express tables, their columns, and PK/FK relationships, but the concept of "views" and how to use them in PowerApps is new to me.

 

Thank you for your expertise!