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.
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.
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)
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
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)
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 .
Thank you for the write up on SQL Views. Do you have more PowerApp-specific examples you can share?
Thank you for your expertise!