Merging imported records with multiple duplicate pairs
I am trying to merge timesheet records exported from a CVS file.
The CVS layout looks like this:
Task - Date - Hours
Importing the CVS file assigns the hours to a date field that is based on the date's DayOfWeek:
Each line in the CVS file contains data for a single task, date and hour amount. Importing thus creates multiple records with the same task but with different dates and their associated hours:
Record 1: Task: Task 1 - Date Friday: 07/25/2014 - Hours Friday: 5
Record 2: Task: Task 1 - Date Saturday: 07/26/2014 - Hours Saturday: 3
Record 3: Task: Task 2 - Date Saturday: 07/26/2014 - Hours Saturday: 2
I would like to merge all the records belonging to the same task (records 1 and 2) in order to end up with 1 single record for task X, with the dates and hours contained in the previously seperated records:
Record 1: Task: Task 1 - Date Friday: 07/25/2014 - Hours Friday: 5 - Date Saturday: 07/26/2014 - Hours Saturday: 3
Record 2: Task: Task 2 - Date Saturday: 07/26/2014 - Hours Saturday: 2
I am not sure how the script accomplishing this should look like. Looking around, I have found scripts that would merge records where only a single pair of duplicated could exist but in this case, multiple pairs can exist in the same database.
I am aware my example may not be very clear, I can submit demo CVS and FileMaker files if needed. I am using FileMaker Pro Advanced 12 on Windows 7.
Any help will be appreciated!