inserts in data grid for parent child tables

[padma]

gallery  data grid is using child table(skill details),if the insert happens in the parent tables(skill ,employee) the associated child table records(all the new skills for new employee )should automatically update to default values ,else the data already table  in child table is to show up (skill details).

and drop down selection in gallery  data grid to show based on parent table column in SQL server connection.

[v-siky-msft]

@padma 

"the associated child table records(all the new skills for new employee )should automatically update to default values ,else the data already table  in child table is to show up (skill details)."

What does this sentence mean?

Could you share more information with your app and requirement? Could you make an example of it?

Sik

[padma]

The app scenario is employees should be able to update (edit)their skills

There are parent tables like

Employee

Employee ID       Employee Name

1                             aaa

2                             bbb                    

Rating

Rating ID             Rating Description

0                               No

1                              good

2                              perfect

3                              well versed

Skill

Skill ID   Category             Skill

1             Technical             .net

2             Technical             java

3             Soft Skills             management

4             Soft Skills             speaking

And child table Skill Details

And child table Skill Details

Skill Matrix ID    Employee ID       Skill ID     Rating ID                              

       1-1                          1                      1                    0                                   

       1-2                         1                       2                    2               

      1-3                          1                       3                    2                 

      1-4                         1                        4                    3                

      2-1                         2                        1                    1                 

     2-2                          2                        2                   1               

    2-3                          2                        3                   0      

   2-4                            2                      4                   0   

Gallery is containing the details of child table Skill Details

Filter('[dbo].[Skill Details]','Employee ID'=Employee.Selected.'Employee ID')

If a new skill is added in skill table in a category in skill table,

The Skill Details details gallery should automatically give the corresponding rating as zero (default rating)for new skill,and employee should be able to edit it accordingly.

If the new employee is added in employee table all the given skills show show the rating as zero (default rating value ) and and  new employee should be able to edit rating based on his skill.

else the previous details already present in Skill Details to show up and employee should be able to edit the rating drop down.

Screen shot for employee=1 be like the attached

Gallery should get skills from skill table(so that gallery will contain new skills also)

[v-siky-msft]

@padma 

I assume you use EditForm to creat new record, then you can set the following codes to OnSuccess of form.

1. If a new skill is added

ForAll(Employee, Patch('Skill Details',Default('Skill Details'), {'Employee ID': 'Employee ID', 'Skill ID': Form1.LastSubmit.'Skill ID','Skill Matrix ID':  'Employee ID' &"-" & Form1.LastSubmit.'Skill ID'}))

2. If a new employee is added

ForAll(Skill, Patch('Skill Details',Default('Skill Details'), {'Employee ID': Form1.LastSubmit.'Employee ID', 'Skill ID': 'Skill ID','Skill Matrix ID':  Form1.LastSubmit.'Employee ID' &"-" & 'Skill ID'}))

Sik

[padma]

Thanks for your prompt response.

I was trying to use the given formulas,after i click the save button,it is showing me,the item has already created on server

my app is not forms related,new skills or employee will be added in the data base tables in sql server.

app will show the details of the only the employee who will be updating his skills,if it is new employee the default rating for the skills has to be  zero,employee should be able to edit his skill any time and save it.same should be the scenario with new skill.i was able to save the data already present in child table,but i a facing issues in getting new skills for the employee data already present and to get the default values for new employee to be zero.

my save option is

ForAll(Gallery1.AllItems,Patch('[dbo].[Skilldetails]', {Skill_x0020_Matrix_x0020_ID: Skill_x0020_Matrix_x0020_ID},{Rating_x0020_ID:Value(Dropdown1.Selected.Rating_x0020_ID)}))

and my gallery filter is

Filter('[dbo].[SkillMatrix]','Employee ID'=Gallery3.Selected.'Employee ID')

I need save option to work for new skills and new employees.

[v-siky-msft · Accepted solution]

@padma 

Why I assume you work with a Form is that we need a trigger to create new records for 'Skill Details'. Since your employee and skill are created on SQL server, the workaround would be more complex, the Skill Details table don't have records related to these new records, so we need to add a condition at the start of app, if the employee or skill is new(not in Skill Details table), create these related records.

Put the following codes to OnStart property.

ForAll(Employee, If(Not('Employee ID' in ' Skill Details'. 'Employee ID'), Collect(NewEmp,'Employee ID'));
ForAll(Skill, If(Not('Skill ID' in ' Skill Details'. 'Skill ID'), Collect(NewSkill,'Skill ID'));
// check if there is any employee and skill is new, if it is new, save its ID to collection.
If(!IsEmpty(NewEmp), ForAll( NewEmp,
ForAll(Skill, Patch('Skill Details',Default('Skill Details'), {'Employee ID': NewEmp[@'Employee ID'], 'Skill ID': Skill[@'Skill ID'],'Skill Matrix ID': NewEmp[@'Employee ID'] &"-" & Skill[@'Skill ID']}))
));
If(!IsEmpty(NewSkill), ForAll(NewSkill,
ForAll(Employee, Patch('Skill Details',Default('Skill Details'), {'Employee ID': Employee[@'Employee ID'], 'Skill ID': NewSkill[@'Skill ID'],'Skill Matrix ID': Employee[@'Employee ID'] &"-" & NewSkill[@'Skill ID']}))
))
// if there is any new employee or skill, create related record for Skill Detail table.

It will automatically run each time you start the app, if there is any new employee or skill, it will generate related skill details records automatically.

Try it, if there is anything unclear, please feel free to reply.

Sik

[padma]

Hi Sik,This code is working fine for new employee-new skills are updated automatically,but for already existing employee new skills are not getting updated automatically,Thanks once again for immediate response.

[v-siky-msft]

@padma 

Please try this, I change the logic, first to generate records with existing Skill for New employees, and then generate records with all employee for new Skills.

ForAll(Employee, If(Not('Employee ID' in ' Skill Details'. 'Employee ID'), Collect(NewEmp,'Employee ID')));
ForAll(Skill, If('Skill ID' in ' Skill Details'. 'Skill ID', Collect(ExistingSkill,'Skill ID'), Collect(NewSkill,'Skill ID')));
If(!IsEmpty(NewEmp), ForAll( NewEmp,
ForAll(Filter(Skill,'Skill ID' in ExistingSkill.'Skill ID' ), Patch('Skill Details',Default('Skill Details'), {'Employee ID': NewEmp[@'Employee ID'], 'Skill ID': Skill[@'Skill ID'],'Skill Matrix ID': NewEmp[@'Employee ID'] &"-" & Skill[@'Skill ID']}))
));

If(!IsEmpty(NewSkill), ForAll(NewSkill,
ForAll(Employee, Patch('Skill Details',Default('Skill Details'), {'Employee ID': Employee[@'Employee ID'], 'Skill ID': NewSkill[@'Skill ID'],'Skill Matrix ID': Employee[@'Employee ID'] &"-" & NewSkill[@'Skill ID']}))
))

Sik

[padma]

Hi Sik,you code for new skill for existing employees is also working,i need to login to powerapps again and check,once the my database tables are added with new skill.

[padma]

Hi Sik,your code for patch with defaults is working fine, but I facing the below warning error for Violation of PRIMARY KEY

Violation of PRIMARY KEY constraint . Cannot insert duplicate key in object ''Skill Details'. The duplicate key value is (9-1).

primary key is Skill Matrix ID,can you please help me on this