How to update contacts based on a matching field?

Version 1

    Long story short the company I work for is using Goldmine and is looking for a way to update 400 or so "existing contacts.  WE have a matching field. We want to pull data into a specific field." The data to be imported resides in a excel file.

    They fired the person who was basically in charge of this and as the guy with "Foxpro" and "Excel" experience I was tasked with finding a solution. I have little experience with SQL but am getting more familiar with Query's, so any help is appreciated.

    Thanks!

     

    There are long and short answers to your question.

     

    For starters, what is the "matching field" you have in GoldMine?  That will determine a lot as to how much of a long vs. short answer you may get.

     

    (I'm short on time, so simply cannot elaborate further at the moment.)

     

    From my co-worker: "D-QBACCTNUM is the field that currently matches the QB Account Number field."

     

    Sorry for the run around, but that simply doesn't help me.  That's not an "out of the box" field name that I recognize.  So, it could potentially be one of several places.

     

    What I need to know is whether that's in a CUSTOM field (e.g. name starts with U and resides in the CONTACT2 table) or whether it's possibly one of the KEY fields, that reside in CONTACT1 (e.g. KEY1, KEY2, etc.).

     

    The reality here is what determines whether it's a long or short answer to the original question.

     

    It sounds like your D-QBACCTNUM field is a custom/user-defined field.  Here are some general steps to update existing contacts based on a matching field.

     

     

    NOTE: It is highly recommended that a SQL backup be made of your GoldMine database before proceeding.  You are making broad changes to the database that can not be undone without a backup.

    For this process, an Excel spreadsheet will need to be converted and saved as a .csv file with the file name being no more than 9 characters long.

     

    Please refer to the GMPE Administrator Guide for more information regarding importing and exporting data.

     

    1 - Click File >> Import and Export >> Import Contact records

    2 - On first screen of GoldMine Import Wizard select Import a new file and CSV file, Next>

    3 - Browse to .csv file (if you do not see the file where you think it is make sure to select Files of type "All Files (*.*)" in the open file dialogue), Open>> Next

    4 - Click Options button, clear data from Field Delimiter text box, OK Next>

    5 -  Map fields as desired

    6 - "Click Profile Options button" and check the options to "ignore first record in import file"

    7 - Select the "Match-Field" that will be used to match the imported contact records to existing contacts in GoldMine that are to be updated

    Note: Match Fields are limited to Contact, Company, Accountno,  Last Name, Phone1, Zip, City, Key1, Key2, Key3. Key4, and Key5

    - OK > Next

    8 -  Select the radio button that says "Preview Result"

    9 - Scroll through several records using the top "next" option, when you are satisfied click the bottom > Next

    10 - Select "Overwrite the existing GoldMine record" and Import matching records only"

    - Selecting "Import empty fields" will update a populated field in GoldMine with a blank entry if the spreadsheet value is empty

    - Next

    9 -  Select yes or no and give the profile a name or not > Next

    10 -  Finish

     

    Key points to remember in data preparation are:

    1 - Column headers should be less than or equal to 9 characters.

    2 - There should not be any comas in the source data (You are doing a CSV [Comma Separated Value] file so if GM encounters a comma in the data, the next data after this is shifted to the next field.

    3 - There should not be special characters in the source data.

    4 - The file name should be saved as xxxx.csv where xxx represents the file name and it should be 9 characters or less.

    5 - Columns of data that are not being imported need to be eliminated from the source data.

     

    To the original poster,

     

    This in Jason's response is key:

     

    Note: Match Fields are limited to Contact, Company, Accountno,  Last Name, Phone1, Zip, City, Key1, Key2, Key3. Key4, and Key5

     

    That is why I was asking specifically about the match field.  If it's not one of the above fields (thus my question about it possibly being a key field), then GoldMine's import is probably NOT the tool to use (otherwise, you really have to jump through hoops, massaging existing GM data before and after the import, etc.).  This lack of GoldMine being able to match on ANY existing field (including custom fields) is a MAJOR shortcoming to this indigenous import feature.

     

    This is entirely do-able, but your company will most-likely get the most bang for their buck by hiring a consultant to take care of it.  Both I and John Neighbors are GoldMine data consultants well-suited to accomplish this.

     

    Doug is spot on.  When reading your original post, had I been tasked with doing this, my quick approach would have been:

     

    1) Determine if the matching field was one that GoldMine can match on (per Jason's reply).

     

    2) If so, then save the Excel data into an acceptable format and then use GoldMine's own import to knock it out.

     

    3) If not, then import the Excel data into SQL Server directly (using SQL Server Mgmt Studio) and knock out the desired data update at the SQL level. (If the GoldMine is a syncing install, then I would have to make the updates "sync-aware" as well, which is very doable.)

     

    All of this is presuming this is a one-time update.  If this update needs to happen regularly, then there are ways to set that up as well, but would take extra effort than just what is noted above.

     

    Thus, my original comments about both long and short answers. ;-)

     

    Hey guys,

    I really appreciate all the help/posts/suggestions.

    I did some searching and may have found an old Query that might do the trick...probably how it was done before. Plz tell me what you think:

    Query:

    "Update contact2

    set contact2.Usem3 =c2.memo

    FROM GOLDMINE.DBO.contact2 C1 JOIN GOLDMINE.DBO.match6 C2 ON C2.Account =C1.udqbacct

    where C1.udqbacct = C2.account  "

     

    Example of data used to update records:

     

    NameQBACCTNUMLast Seminar 3
    Abbott, Roger1056OSD - DR - Indy 5/13-15/2016
    Abdul-Majid, J46524OSD - DR - Indy 5/13-15/2016
    Adelman Jr., Edward45076ACLS recert - DR - ATL - 8/21/2016

     

    As long as you get the source data into the match6 table appropriately, it looks legitimate.

     

    I agree with Doug, wholeheartedly.  You just have to dot your I's and cross your T's in regards to making sure the STRUCTURE of the match6 table is as it should be (based on what it was before).

     

    Also, as long as this update doesn't need to be sync-aware, as well.

     

    Good luck!

     

    Sorry, still a n00b...what is this "Sync-Aware" you speak of?

    (Is this something that gives one the ability to update a table while people are logged in to GM without corrupting the data or ?)

     

    Also, here is the stru of match6:

     

    GoldMine supports the concept of having remote, undocked "users" and remote "sites" with copies of GoldMine and distributed data.  In those cases, all data changes need to be "sync-aware" so that GoldMine knows to "send out" those data changes the next time a remoter user or site "syncs" with your GoldMine.

     

    If you do NOT have any undocked users or remote sites, then I suspect you can ignore my sync-aware comments.

     

    At first glance, it appears the structure of match6 is simple enough.  As long as the data in "memo" will "fit" in contact2.Usem3, I suspect you should be good.

     

    I would caution that you'd want to review the contents of that table before deciding to import it.  You might find that it contains records you don't want to import today...  Or maybe it contains 5 copies of each record or.. or.. ?  The possibilities are endless.

     

    Proceed with caution, I guess is what I'm saying.

     

    Fredline,

     

    Both John and Doug have excellent information all the way down the line here.  As has been said please be careful with how you handle your data.  It sounds like hiring a GoldMine Partner may be in your best interest.

     

    One other approach here to consider as none of us know your data.  It it possible to create a filter or group containing these records?  If it is then a Global Replace is another consideration.

     

    This document was generated from the following discussion: How to update contacts based on a matching field?