Compare Two Entities, Write New Records

[SeanHenderson]

Hi,

I have employee data on Training Courses in CDS entities. I want a Flow that triggers when the Position lookup on Employee is changed. I want it to compare the Position Requirements entity for the new Position with the current Details entity for the employee to see which new courses are needed. So an employee has 4 courses, the new position needs 6 courses, 4 of which carry over from the old position, so the 2 additional courses need to be created as outstanding records in the employee Details.

I tried listing the Position Requirements for the new position and listing the Employee Details for the employee. Then I created a condition that says "If Course in 'list requirements' is equal to Course in 'list details' then Create New Details record."

The problem is that this is double counting. So I have an employee with details on 4 Courses. Her new position has requirements for 6 courses, 4 of which she already has. With the Flow as I have it now, it creates 24 Details records. What it should do is keep the original 4 Details records and create 2 new ones for the courses she doesn't have.

Does that make sense? Please help me out with this one, it's been a real problem for me.

Thank you

[ManishJain · Accepted solution]

Hi @SeanHenderson ,

Please use following steps :

1. Create a blank array to hold values which do not match 

2. Use Apply to each loop and select either of value items as input. 

3. Inside the Apply to each add filter array and add another value as input.

4. Use condition to check if the filter array returned result , if value is returned do nothing else add this column id to array created in step 1. 

5. Now the array contains values which are new , so use Apply to each loop and use this array as input 

6. Filter second array ( containing 6 values) with value and use the output of this filter array body to insert new record.

Thanks

[SeanHenderson]

I'm having trouble following your steps, I haven't worked with arrays much before. Can you please show me an example?

[ManishJain]

Hi,

Here are the steps which I have done for your example: , to make it simple I have declared two arrays containing 1,2,3,4 and 1,2,3,4,5,6 values in these.

Thanks

[SeanHenderson]

Step 3 is what I'm struggling to understand. 

Bear in mind, the two entities I'm comparing, Training Details and Training Requirements, are totally different entities. They only share a single common column, called Course. It's the Course values that are in one and not the other that I want to extract.

Where in your filter array does it compare the two Course columns?

[ManishJain]

Hi @SeanHenderson ,

In Apply to each loop input should be Training Requirements and there is no need to use inner loop for filtering. Use Filter array and use Current Training and then filter based on course which is what you have done . 

If you do not have common field how are you comparing then ?

Thanks

[SeanHenderson]

Course is the common field.

I was able to get it to work with a little effort, thank you for your help!