Skip import of duplicate records (and merging) according to latest note. (#2)

Isnt that a case of removing the old EmployeeID's that no longer exist in OrientationNotes (or Employeestbl.ID where skipImport = '1') ?

Because orientation Notes have been moved to the rectified / resolved / corrected Employeestbl.ID - I would have thought that entry would already be in Orienttions....

Or, is Notestbl from previous question a different table to OrientationNotes ?

And therefore a similar albeit entirely different set of tables ?

In which case, could there be a risk of multiple / different / conflicting skipimport flagging on employeestbl ?

edit : you did say :Instead of having Notes table, we have the following two tables. so there is no Notestbl ?

Hi Mark,

Or, is Notestbl from previous question a different table to OrientationNotes ?

Correct.
Thanks,
Ben

To clarify a bit.

The process is very similar, with two differences outlined above.

1- OrientationNotes are related to Orientations, which are related to Employees table.
2- The extra step #3 that needs to be done in case an orientation record exists already for that Emp/Fac.

Let me know if more clarification needed.

Thanks,
Ben

See attached.

Thanks,
Ben
Untitled.png

Hmmmm....  A lot more *interesting*

1) It is possible to have a few Employeestbl.ID for an email
2) It is possible to have a few EmployeeID for different FacilityID
3) It is possible to have Notes for each of 2 above

So, it is possible to have different Employeestbl.ID with the same Email - one for faciityID 'n' and one for facilityID 'n+' both legitimately existing in orientations because it satisfies the unique index.

Is the objective still to have one unique Employeestbl.ID per email ? In which case, what will be the determining factor as to which Orientations.EmployeeID wins ?

Hi,

Is the objective still to have one unique Employeestbl.ID per email ? In which case, what will be the determining factor as to which Orientations.EmployeeID wins ?

Yes this is the case.

So basically we need to look at the OrientationNotes table which employee has the latest note (just the way you did in the other post for notes table, with one difference, here you have to join the Orientations table as well to determine to which employee this notes belongs..).

At the second step, we only need to update the Orientations table, to assign it to the main employee, once thats done, all related notes to that orientation should automatically be under that employee.

However while running the second step, we may anticipate an error saying not all records were successful, as it would create duplicate in unique index for EMP/FAC.

And therefore there must be a third step to handle those records.
So for example if we have 2 records in Orientations table
ID        FacilityID     EmployeeID
1                       100                      15
2                       100                      25
And we want combine those two employees 15 & 25 into one, (since they have same email)
Instead of changing the Orientation record from 15 to 25 (in case 25 has the latest note).
We change all records in OrientationNotes table that OrientationID is 1 to 2.

Does that make sense?

Thanks,
Ben

Yep, that makes sense - thanks

Hi Mark,

So can I count on you for this one as well?

Thanks,
Ben

Yep, will be tied up with dumb things I gotta do for a few hours, and will get stuck into it on my return...

Great!

Thanks,
Ben

Hi Mark,

As my manager is eager to get this done ASAP.
Wondering when is your avail?
perhaps Par1 I can try copy from the other one, if that makes it easier/faster..

Thanks,
Ben

Hi,

Three part process

1) Flag EmployeesTbl
2) Update OrientationNotes to point to the correct OrientationsID
3) Delete Orientations with Skipped Employee.ID

#1, agree.
Re #2 & #3, I'm not fully getting it, why are you performing an update to ALL OrientationNotes, as from my understanding for most of them it would be only necessary to update the Orientations table, and switch over the orientation to the right employee, this would automatically bring along all notes associated with them.
While for those that have already a Orientation in place with that facility, it would be necessary to update the OrientationNotes table, and determine which is the right Orientation to transfer the records to..(The one with EmployeeID = MainEmployee SkipImport = 0, and FacilityID = current orientation.facilityid)

Thanks,
Ben

1) it isnt for all orientationnotes, just those where the employeeid in orientations will be changing where an existing employeeid already exists in orientations for that facilityid.

2) it is possible that there is an orientationID pointing to an orientations.ID where the employeeID already exists and therefore that orientationID becomes redundant (it cannot be updated to the corrected employeeid - unique constraint prevents it) which means the orientationID is no longer relevant and has to point to the correct (existing) row.

Hi,
Got you!
Testing it..
Thanks,
Ben

Hi Mark,

Preliminary tests had showed its working, was waiting for users to confirm, however that doesn't look to be so fast, so I'm closing it.

Thank you very much!
Ben

Hi Mark,
Something is missing from this request.
However I will open a new one, let me know if you are now avail..?
btw, I also have a couple of these tasks
Thanks,
Ben

>> btw, I also have a couple of these tasks

 :) out and about on site today, but do have some time available overnight + tomorrow  (my time GMT + 10:00)

Hi,
See following
https://www.experts-exchange.com/questions/29099802/Skip-import-of-duplicate-records-and-merging-according-to-latest-note-3.html#questionAdd

Thanks,
Ben