Power Apps(Pages) Issue with connection and working with sql database connected table with foreign key

[JacoMathew]

Hi All,

I am trying to create an app with Power pages. I have a set up an azure sql database with table that have primary key and foreign key. I was able to make the connection to the solution. I have tables in the following format :
Employees table :
Id, FirstName, LastName, EmpType, Status ....

EmployeeSkill Table :
Id, EmployeeId, Skill .....

I created views with an additional column called DVID. DVID in Employees is a concatenate of (Id,FirstName,LastName). DVID in EmployeesSkill is a concatenate of (Id,EmployeeId,Skill). I created this DVID column as while loading the table, we have to set a "Primary field". I have read that and also found out that this has to be unique. As I cannot select Primary Key Id, I created DVID as the combination would be unique. 

As per the instruction on one of the videos, I deleted the EmployeeId column from the table, created a relationship (many to one) between EmployeeSkill and Employees and set the Primary name as EmployeeId. This worked and I was able to see the correct option in the drop down of the form. However, as soon as I submit the form. I get an error but the data is getting saved in the EmployeeSkill table. Also I noticed that once I create the relationship, the table in the solution stops showing the records. Also the list that I had added to the page doesn't show data. 

I am not sure if I am doing any of the steps wrong. Any suggestions and advice on how to setup the connections correctly to be able to save and view the records correctly. 

Thanks in advance,

Jaco

[MissingLink]

You don't mention which video you have been following, so I could be giving information that differs.

You are aware that you need a PRIMARY KEY when patching (Add, Edit, Delete) records in a canvas Power App.
And you already have these. They are your 'ID' fields that exist in the SQL table.

Just a few tips here
I find it useful to name the ID fields to include the name of the table. So instead of calling them all just 'ID', I call them 'ID_Employee' or 'ID_Skills'. I Also prefer to put the 'ID_' in front of the table name. This makes it easier to find fields and follow the logic later.

Also - Think about your use of the singular/plural when naming your tables. This is a hotly debated topic - so use your preference. In the case of you Skill related table, I prefer the plural because EmployeeSkill implies that each Employee only has one skill.

And finally, If you are want your user to only use a finite list of skills, then I am guessing that you will probably want to be able to choose a skill from a drop down list rather than allowing your users to type whatever they like into a free-text box. So you might also want to create a 'SkillList' table.

Anyhow, lets get back to your SQL tables.
I am presuming that the 'Id' field in each SQL table was created something like this ?

TABLE CREATION
CREATE TABLE Employees (
ID_Employee INT IDENTITY(1,1) PRIMARY KEY
FirstName ...( blah blah blah
)

and

CREATE TABLE EmployeeSkills (
ID_Skills INT IDENTITY(1,1) PRIMARY KEY
Skill Varchar(255) NOT NULL, --Think about making this a foreign key to a Skills Table
ID_Employee INT,
CONSTRAINT FK_EmpID FOREIGN KEY (ID_Employee) REFERENCES Employees(ID_Employee)
)

FYI:
The 'IDENTITY(1,1)' part tells SQL to start from the number 1 and add a count of 1 to each record as it is created. So in effect, each ID be a unique number that is on each record.

The 'PRIMARY KEY' tells SQL that the ID field should be treated as the primary key for the table.
NOTE: It is possible to create a primary key based on multiple fields. (Similar to what you have tried to do - but it is NOT necessary in your case.)

FOREIGN CONTRAINTS
In the EmployeeSkills table, The ID_Employee is the foreign key because it is a reference to the Employees table.

However, whether you include the line:
'CONSTRAINT FK_EmpID FOREIGN KEY (ID_Employee) REFERENCES Employees(ID_Employee)'
is up to you.

If you include the line, it establishes the relationship between the two tables in SQL which invokes some rules that you need to be aware of.

The EmployeesSkills table is now dependent on Employees table.
The net result is that
1. You will not be able to add a record to the EmployeeSkills tables that has an ID_Employee number which is NOT in the Employees Table.
2. You will not be able to delete the Employees table without breaking the dependency (foreign key reference).
3. Depending on whether you have turned on referential integrity or not, If you delete an Employee, then the Skills record will either be deleted, or it will become an 'orphan' (IE: It will be linked to an Employee that no longer exists - This could mess with reports that you write later.)

For more info see: https://www.w3schools.com/sql/sql_foreignkey.asp

POWER APP.
To use the above setup in your Power App.
On any screen that shows the EmployeeSkills info, change the ID_Employee field to a DropDown list.
Default value = Thisitem.ID_Employee
Text to be displayed is the ThisItem.EmployeeName &" "& ThisItem.EmployeeSurname

I hope that helps you get started.

[JacoMathew]

Thanks for the detailed explanation. Appreciate it but I believe you missed that the solution I am asking is for Power Pages/Portal. I am not new to database development but I am new to Power pages development.  The video that actually helped was this by @nickdoelman. The issue I am facing is after I delete the column and create the relation, the table data stops showing. I also get the error in the Power pages when I submit the form. However, when I check the database table, the records is inserted. The list that is connected also doesn't show the records.

[JacoMathew · Accepted solution]

Hi All,

Closing this topic as I have resolved the issue. The issue was Id doesn't work in Power pages virtual table. I had to create GUID and use that as the primary key while making the connection.

Thanks