This is a public Forum  publicRSS

Forum post

    Paul Simon
    Merge Data From Two Similar TablesAnswered
    Forum post posted October 17, 2010 by Paul Simon, last edited February 9, 2012 
    1102 Views, 2 Comments
    Title:
    Merge Data From Two Similar Tables
    Post:

    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.

    Thanks.

    Best Answer

    PhilModJunk

    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.

    Answer

     

    • PhilModJunk

      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.

    • Paul Simon

      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).