The Challenge
After watching a lot of youtube videos about PowerApps the creation of a new item in SharePoint list is pretty simple. All I have to do is use the Patch() function. Patch(Employee,Default(Employee), { 'First Name': "Trevor" , ... }). I have used it a lot of time that I have it ingrained in memory, even the colors. In my design phase I've laid out the ERD of my data.
I thought SharePoint List and PowerApps behave like a website. With SQL you can dynamically create new rows in another table. It's not after I came to this part of development that I notice the problem.
As you can see. Each Employee table has 37 Employee Skills table. It's not the same as logging the changes in the Logs table. Whenever I have to create a new employee profile. I also need to make 37 items of "employee skills" and it needs to reference that employee. Patch() is powerful enough to make that happen so I needed to get creative.
The Solution
There are two ways to make this work. One is through PowerApps and the other through PowerAutomate
Solution #1
This solution is only using functions in PowerApps.
1. Patch the employee info. (Will be put in last row)
Patch(Employee,Default(Employee), { *Enter info here* } );
​
2. Retrieve last row in Employee List.
UpdateContext( { EID: Last(Sort(Employee,Descending)).ID } );
3. Use a loop to make all the rows with the appropriate skill and attach the employee ID (EID) to each rows.
ForAll(Sequence(37) As SID, Patch('Employee Skills',Default('Employee Skills'), { Title: LookUp(Skill, ID = SID, "Title") , 'Current Level': 0, 'Target Level': 0, EmployeeID: EID, SkillID: SID }));
Solution #2
The second solution uses PowerAutomate. There are actually three ways to do it (That I thought of).
Version 1: Similar to the way I migrated data from excel to SharePoint list. The difference is instead of getting the data from the excel file, data is passed directly from PowerApps to the flow, where PowerAutomate is the one actually creating all the rows needed.
Version 2: Almost identical to version 1. This version uses a trigger in PowerAutomate to run the flow that will make the accompanying 37 rows in another table. Whenever a new item is created in the "Employee" list then a flow will run that will create the 37 items in "Employee Skills" list.
Version 3: Create the item in Employee list using Patch(). Retrieve the ID of the last row of the Employee list. Run a flow using PowerApps while passing the ID to the flow. The flow will create 37 items in "Employee Skills" list.
Conclusion
Everyone of the solution is a viable option but there are actually two factors that I look for in a solution Speed and Flexibility
-
Speed: How fast will it execute/finish to do the task required.
-
Flexibility: How hard is it to modify and its ability to scale when given larger data sets.
I have tried each of the solutions I came up with, and the solution I chose has the most amount of balance between speed & flexibility. Solution #2 Version 3 was the one I used in my program. It's not the fastest, and not the most scalable but I was fast and scalable enough in addition to being the easiest to modify. That's why I chose it.