2 Replies Latest reply on Jul 27, 2014 4:09 PM by BenjaminDestrempes

    Merging imported records with multiple duplicate pairs

    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!

        • 1. Re: Merging imported records with multiple duplicate pairs
          philmodjunk

               A script can do this, but I do not see any advantage to you that would be accomplished by the merge. You can sort to group the records with duplicate values and then use a pair of nested loops to loop through the group with a given duplicate value in order to merge as the inner loop and an outer loop that loops through the entire found set.

               But it's far easier to keep the records separate and use a summary report with a sub summary part and summary fields to display the data as a "merged" row of data. By deleting the body layout part from such a layout, you get one row of data for each merged group.

          • 2. Re: Merging imported records with multiple duplicate pairs
            BenjaminDestrempes

                 I forgot to mention that the data was displayed using a portal in another layout. Each entry has a fk_timesheet_id used to display the info on a Timesheet layout. Would it be possible to achieve the same results wihout a summary report? Or would it be possible to format a summary report to display the data from both layouts? I have never used a summary report before.

                 Thanks.