2 Replies Latest reply on Oct 26, 2010 10:09 AM by PaulSimon

    Merge Data From Two Similar Tables



      Merge Data From Two Similar Tables


      I have two similar address lists I need to merge to create shipping labels from.

      I've already made sure I have identical field names in each table even though the corresponding fields from each table may be blank. It looks like this right now, with "x" indicating the presence of data:

        Large Signs Small Signs
      BLDG_CD   x
      ODN   x
      IRS x x
      TAC x x
      Street x x
      Floor_Room_Stop x x
      City x x
      ST x x
      Zip x x
      Group Number_Manager x x
      Phone No.   x
      Qty_Large_Signs x  
      Qty_Small Signs   x
      Total_Large_Signs Summary Fld  
      Total_Small_Signs   Summary Fld

      It took some manual copying and pasting to ensure the TAC, IRS, City and Floor_Room_Stop fields all match between the two tables, but it's done. What was provided to me was all over the place as far as consistency goes.

      What I need to do now can't be done with a simple Import Records command. I need to merge the two tables in such a way as to combine all records with the same IRS, TAC, Street, Floor_Room_Stop, City, ST and Zip fields and end up with a master list of unique address with combined Large and Small signs. One catch is that some addresses have different Group Name_Manager entries and they need to be kept separate as they require their own mailing envelopes.

      Any help on this seemingly straightforward query is welcomed and appreciated.


        • 1. Re: Merge Data From Two Similar Tables

          It's possible to setup a relationship that matches values on all these fields.

          LargeSigns::IRS = SmallSigns::IRS AND
          LargeSigns::TAC = SmallSigns::TAC AND
          LargeSigns::Street = SmallSigns Street AND
          //continue this pattern with the othere fields AND
          LargeSigns::Group Number_Manager = SmallSigns::Group Number_Manager

          You can then write a script that steps through either the records in the LargeSigns or SmallSigns table and stops to use some Set FIeld steps to merge the data whenever a matching related record exists.

          When testing such a script, make a back up copy of your file first, so that you can throw out your file and start over if you get data merged incorrectly.

          • 2. Re: Merge Data From Two Similar Tables

            It took a lot of back and forth editing on the initial addresses to match the similar addresses.

            What I did was write one script to refresh the imported data, then another to merge them. Once happy with the performance I just combined them into one script that would wipe out all records from each table and re-import them from a clean copy in another file.

            Then I ran into the discovery that related records don't relate when matching fields are blank. Added more script steps to insert a text placeholder to allow the match, then remove it later.

            I'd then do a search (manually) for duplicate or similar addresses and manually correct them. Royal pain but I couldn't think of any other way than manually editing the original lists I was provided. In the end though I got thanks to your suggestions PMJ.

            I just did some quick weight calculations in excel that I could have easily done in FM also and sorted my packages by weight with a report and export to .csv files for importing into UPS Worldship (another nightmare).