AnsweredAssumed Answered

Merging imported records with multiple duplicate pairs

Question asked by BenjaminDestrempes on Jul 26, 2014
Latest reply on Jul 27, 2014 by BenjaminDestrempes

Title

Merging imported records with multiple duplicate pairs

Post

     Hi,
      
     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:
      
Date Monday
Hours Monday
Date Tuesday
Hours Tuesday
     [...]
Date Sunday
Hours Sunday
      
     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!

Outcomes