Open Forum

Expand all | Collapse all

GP Customer Numbers

  • 1.  GP Customer Numbers

    Posted May 04, 2020 12:44 PM
    I am looking to investigate the steps involved for using the "Customer PO Number" field in GP Sales Orders as Customer Numbers to be tracked in GP, using the "Customer Maintenance > Customer Combiner and Modifier" modules. #GPCustomerID #SalesOrders

    My organization currently does not use any form of Customer Number tracking.​​

    ------------------------------
    Matthew Raffio
    Granite Telecommunications
    Quincy MA
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: GP Customer Numbers

    TOP CONTRIBUTOR
    Posted May 04, 2020 12:54 PM
    Matthew,
    I am not sure I am completely following your question, but are you trying to use a single customer like "DEFAULT" and every order uses that as the customer ID and then you are going to track the customer ID in the Customer PO field?  If that is the case this would not be an issue.  The only thing to think about is making sure you have the same characters every time if you are trying to use this in reporting.  This is a standard practice for customer who don't really use customers in GP I do this a lot with customers who do online orders.
    Hope this helps.
    Tanya

    ------------------------------
    Tanya Henderson
    Senior Consultant
    S2 Technology
    Park City UT
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: GP Customer Numbers

    Posted May 06, 2020 03:06 PM
    Hi @Tanya Henderson, thank you for your input here.  I am looking to convert and/or combine the data from the Customer PO field and the Comment 1 notes section in the Sales Orders to Customer ID's through Customer Maintenance.  Does this make sense?




    ------------------------------
    Matthew Raffio
    Granite Telecommunications
    Quincy MA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: GP Customer Numbers

    TOP CONTRIBUTOR
    Posted May 06, 2020 03:19 PM
    Matthew,
    What I believe you are trying to do is take the Customer PO and Comment 1 when they have a Sales Order and create a customer by combining those two fields.  I assume you are integrating these either with Integration Manager or SC.  You could do this, but remember if you import into an ID field in GP it can only be 15 characters long and must be in all UPPER case.  I know you could do this with SC and IM both, but you will have to add scripts or in the case of SC a calculated field.
    If you need help I am happy to do so.
    Tanya

    ------------------------------
    Tanya Henderson
    Senior Consultant
    S2 Technology
    Park City UT
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: GP Customer Numbers

    Posted May 07, 2020 10:43 AM
    Hi @Tanya Henderson
    I kind of thought this could be bulk implemented through​ IM.  Could you assist with this?

    I will loop in my points from the discussion board with @Samantha Higdon

     The end goal is to use Customer Card on ongoing basis
    a. Note, the data in the Customer PO field [within the Sales Orders] are in 8 digit numeric form that represent account numbers for our customers.  So, each "Account Number" in an assigned Sales Order can get invoiced out based on the line items in a Sales Order.
    i. My company wants to convert the Customer PO data into real Customer ID's (by using the Customer Module).
    ii.  The reason we are doing this is because we are integrating another ERP platform (i.e., SalesPad).

    ------------------------------
    Matthew Raffio
    Granite Telecommunications
    Quincy MA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: GP Customer Numbers

    TOP CONTRIBUTOR
    Posted May 04, 2020 03:29 PM

    Hi @Matthew Raffio,

    Tanya hit the nail on the head with her post regarding how to use the Customer PO number field when you don't use formal customer tracking.

    My comment is more oriented to your request as it pertains to the Customer Combiner/Modifier tool.  That tool is designed for use in systems where formal customer tracking is used via Customer Numbers.
        1.) Combiner feature -If I set up ABC Bakery as ABCBAKE and then another person sets up ABC Bakery as BAKERYABC, the Customer Combiner/Modifier tool lets you go in and combine the two individual customers​ into one and updates all references in SQL to the newly combined customer.

    2.) Modifier feature - if I set ABC Bakery up as ACBBAKE instead of ABCBAKE, the modifier feature will let me change the Customer Number and then go through and update all references in SQL accordingly.


    It's a super handy tool but only if you're formally tracking customers using Customer Numbers via the Customer Card.

    Thanks,


    Samantha 



    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: GP Customer Numbers

    Posted May 06, 2020 03:11 PM
    Hi @Samantha Higdon, I appreciate your thoughts.  Unfortunately, my company only uses the Customer PO field in the Sales Order module as a basis for our customer tracking.  The Customer Card module is not used.  Is there a way to take the data from the Customer PO field and Comment 1 section in the GP sales order to convert into the Customer ID through the Customer Card module?

    My company is looking to use Customer PO data as GP Customer IDs, by also trying to capture the address information from the Comment 1 section.

    -Matt​

    ------------------------------
    Matthew Raffio
    Granite Telecommunications
    Quincy MA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: GP Customer Numbers

    TOP CONTRIBUTOR
    Posted May 06, 2020 03:25 PM

    Hi @Matthew Raffio,

    I've taken a moment to review your replies to myself and @Tanya Henderson.  I think I may have a better handle on your current state, but let's confirm.

    Current State:
    1.) You do not use the Customer Maintenance Card available via Sales>>Cards>>Customer.
    2.) You DO currently utilize the Customer PO Number field on a Sales Transaction Entry to record some customer information an ID or name.
    3.)  You DO use Comment 1 from somewhere on the Sales Transaction Entry window to record some information - it sounds like address from your reply.​


    The short answer is - I have thoughts on how you could make the conversion happen, but my suggestions on approach will vary based on what the true end goal is.  With regards to your future state, can you confirm which of the below (if any) is the ultimate goal of the future state?
    1.) Begin using the Customer Maintenance Card on an ongoing basis where all sales transactions are assigned to the real customer (hence converting to real customer tracking in GP).

    2.) Continue tracking using the Customer PO Number and Comment 1, but the goal is to have this "converted" customer information appear somewhere in the database for display purposes (i.e. change how the sales invoice prints) or reporting (i.e. show me everything that Customer X has bought in the last year).

    3.) Continue tracking using Customer PO Number and Comment 1, but the goal is really just to have a customer list that can be used for mail, email, phone marketing, etc.  It's essentially for reporting and contact purposes but does not impact the sales or accounting functions.


    If it's none of the above, could you perhaps outline your true end goal in a bit more detail so we can help find possible solutions?

    Thanks,

    Samantha 



    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: GP Customer Numbers

    Posted May 07, 2020 10:39 AM
    Hi @Samantha Higdon

    1. Your understanding of current state is on point. ​
    2. The end goal is to use Customer Card on ongoing basis (goal no.1 on your list).
    a. Note, the data in the Customer PO field [within the Sales Orders] are in 8 digit numeric form that represent account numbers for our customers.  So, each "Account Number" in an assigned Sales Order can get invoiced out based on the line items in a Sales Order.
    i. My company wants to convert the Customer PO data into real Customer ID's (by using the Customer Module).
    ii.  The reason we are doing this is because we are integrating another ERP platform (i.e., SalesPad).

    Thank you very much for your assistance
    -Matt


    ------------------------------
    Matthew Raffio
    Granite Telecommunications
    Quincy MA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: GP Customer Numbers

    TOP CONTRIBUTOR
    Posted May 07, 2020 11:01 AM

    Hi @Matthew Raffio,

    Thanks so much for confirming and the additional information!  Very helpful.  I think @Tanya Henderson has given some really solid advice about some of the tools to use to consider the move itself (Integration Manager, SmartConnect, etc.).  I have a few additional options to consider for moving that data, but let's first talk about preparing the data for the move itself.

    Preparing the Data:
    1.) I would recommend running either a SmartList or SQL script (depending on your comfort level with either or both of those options and other resources available at your company).  The smartlist or SQL script should contain at minimum the Customer PO number and the Comment 1.  It should also include any other customer oriented data from the sales order that you might want to convert into the customer maintenance (Contact name, phone number, etc.)

    2.) Scrub the data - Specifically you'd be looking for data integrity issues that often happen when fields are free form no matter how hard we try to avoid it.  Things to be aware of include but are not limited to:
            A.) Alpha characters.  You say they are 8 digit numeric, but if an alpha character is included, GP would require that character to be
                 uppercase/capitalized as Tanya mentions.  You'd want to fix that either in the raw data or account for it in whatever method you use to get the data
                 into the system.
            B.) "Extra" characters or missing characters- if it should only be 8 digits and there are a few that are say 7 digits or 9....was there a transition?  a
                   missing character?  An extra character?  Any combination of those could cause extra customers to be created (i.e. 1 with 8 digits and 1 with the
                   same 8 digits plus that pesky 9th digit) or inconsistencies in the customer numbering sequence or hierarchy.
            C.) Multiple addresses - customer addresses may have changed over time depending how long this method has been in place or they may have
                  multiple ship to locations.  Some thought would need to be applied by the appropriate folks to decide if only the most recent address is used or if
                  you create 1 customer with multiple address ID's (i.e. an ship to location in Fort Wayne, Dallas, New York City, etc.)
           D.) Defunct Customers - This is very optional, but if you have a customer who ordered from you one time ten years ago, do you want to
                 include them?
           E.) Duplicate Customers - since you're basing it on sales orders, likely customers will appear more than once if they are repeat customers.  For the
                 smoothest implementation, I would recommend removing duplicate entries so Customer A only appears one time.  By no means is this required,
                 but it pares down on your errors or "duplicate customer" warnings when you integrate which I find makes it easier to troubleshoot the valid errors by
                 limiting the false positives so to speak.


    Once we've scrubbed our data and got a really solid raw data set, then the question is how do we make the conversion happen?  I'm thinking you'd like to do a bulk implementation -i.e. import at one time as opposed to setting them up manually?  If so, then there are several options.
    1.) Smart Connect - Tanya Henderson provided some insight in that.
    2.) Integration Manager - Tanya gave some information on this as well.
    3.) Excel Replicator - by @John Arnold.  It's based on a import macro but uses excel instead of mail merge.
    4.) Mail Merge Macro - also based on the concept of an import macro, but it uses the mail merge process.

    I like different things about each of the options to be honest.  Options 3 and 4 are quick and easy if the import you're doing is fairly clean (limited duplicates, fairly consistent data points, etc.) -plus both can be used at no purchase cost. I wouldn't consider it a truly "automated" solution as you fire both off manually, and I wouldn't necessarily recommend it as a long term solution if you wanted to repeatedly use the integration over and over and over because of the manual nature of it.

    If you think you'd want to do this integration several times over (i.e. 6 months down the road, then a year, etc.) or if you think your company has the opportunity to do other integrations then I would probably recommend considering Integration Manager or SmartConnect.

    Another thing to ponder since you mentioned it is the integration with SalesPad - do you know how that data handoff is going to happen?  If it's still being worked out, it's another case point for something like SmartConnect which could then be leveraged for the customer conversion and the ongoing data exchange between Salespad and GP.

    Does that make sense?  If you have questions, please just let me know.

    Thanks,

    Samantha

    ​​

    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: GP Customer Numbers

    Posted May 07, 2020 12:30 PM
    Quick follow up question @Samantha Higdon my organization has over 200,000 address records and Customer PO.  What affect does this level of volume have ​on GP when running the integration?

    ------------------------------
    Matthew Raffio
    Granite Telecommunications
    Quincy MA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: GP Customer Numbers

    TOP CONTRIBUTOR
    Posted May 07, 2020 12:54 PM

    Great follow up question @Matthew Raffio and definitely one to consider when picking the right tool for the job!

    Whew- that is NOT a small set of data.  The affect on GP in terms of performance will vary a bit based on the tool used.  In any case, let's just plan for it taking substantially longer than if you were planning to integrate say 5,000 records.

    Personally, with that volume of data, I'd recommend SmartConnect for a couple of reasons.
    1.) The macro approaches (mail merge and replicator) require the user to be logged into GP to run and requires that they be doing NOTHING else.  Not in a sales order in GP, not in a word document outside of GP, etc.  It also requires that the computer not go to sleep - if it does, the macro stops because the window isn't active- you'd have to sit at the machine making sure the computer doesn't go to sleep until it finishes.  Long story short, these tools are best for short integrations with a more modest volume​ of data.

    2.) Integration Manager also requires that you be logged into GP and prevents you from doing other things in GP.  You'd have a license tied up for hours (a lot of hours assuming the 200,000 are purely unique records).

    SmartConnect has the following advantages in a large volume scenario:
    1.) It can be run without relying on a user being logged into GP.  That means you can go about your normal business while the integration is running or without tying up a GP license if number of licenses available is an issue.

    2.) It can be scheduled.  If you want to avoid performance slowdown for other users, you can schedule the integration to run in off hours, over a weekend, etc. where you can check on it as you like, but it won't cause slowdown for other users trying to use GP.

    Does that all make sense?

    Thanks,

    Samantha 



    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: GP Customer Numbers

    TOP CONTRIBUTOR
    Posted May 07, 2020 11:25 AM
    Matthew,

    SalesPad is not an ERP system.  SalesPad is an enhancement to GP.  GP is the ERP.

    For example:  when you create a customer or an order in SalesPad, the data is stored in the same GP tables that the customers and orders are stored when a user enters them in the GP interface.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 14.  RE: GP Customer Numbers

    TOP CONTRIBUTOR
    Posted May 07, 2020 10:38 AM
    Matthew,

    Here is an approach you may want to consider.  I've seen this used in a number of retail scenarios.

    If you're needing to capture the contact information:
    Have a single customer ID (card).  A customer can have an unlimited number of addresses associated to it.  And, each must have a unique ID.  For each order, enter the address as a customer address.  You could use the PO number or a sequential number as the address ID.

    If you're not interested in the contact information:
    Simply use the single customer and enter the customer PO number on the order header.

    I hope this helps.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 15.  RE: GP Customer Numbers

    Posted May 07, 2020 12:37 PM
    That's what I had in mind @Chris Donnelly, using the Customer PO Number as the unique identifier in Sales Pad.  And the associated address would auto-populate.

    It turns out this will be more applicable for future Sales Orders/Customer PO Numbers rather than historical orders @Samantha Higdon.
    ​​​​

    ------------------------------
    Matthew Raffio
    Granite Telecommunications
    Quincy MA
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role