Open Forum

Expand all | Collapse all

A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

  • 1.  A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 05, 2019 02:01 PM
    Dear Collaborators,

    This might be a bit long... but bear with me. It'll be worth a Leslie Vail badge to you! (Past winners include: @Amber Bell, @Kristen Hosman, @Steve Endow, @Tom Monfre, @Tammy Chavez​​​​​​, and @Leslie Vail herself!)

    Our Accounting Manager, @Holly Kostechka, wants to set up Fixed Allocation Accounts for all accounts in the Cost of Goods Sold ​​category. That's 84 base account numbers in CoGS.

    Madam Accounting Manager wants to have two separate allocations as well: one set that allocates to 10 of our Departments, the other that allocates to just 3 Departments. So that would be 84 * 2 = 168 new Allocation accounts with the associated distributions in each, for a total of 1,092 distribution lines.

    Fortunately, the distributions for allocation method 1 are all the same; ditto for allocation method 2. That is, allocation method 1 distributes the amounts by fixed percentages across 10 departments using the same set of percentages for each of the 84 Allocation accounts using that method. Similarly, allocation method 2 distributes the amounts by fixed percentages across 3 departments for each of its 84 Allocation accounts.

    The question: which tool do we use to enter all of those 168 Allocation accounts and their associated distributions?

    • Integration Manager
    • Mass Modify
    • GP Macros with Word Mail Merge

    More importantly: how would we set this up? I presume that we'd use an Excel workbook for the raw data. I'm just having trouble picturing how the individual distribution lists would be filled for each of the new allocation accounts.

    Thank you for reading through this. I hope that I've been clear. Here's a miniature of the five types of Leslie Vail badges that have been awarded! You could be the next recipient!



    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 06, 2019 02:59 AM
    @Steve Erbach

    How about a quick GP Power Tools script. You already have GP Power Tools.

    You can add an interface or even make it trigger automatically.

    David

    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 06, 2019 08:22 AM
    @David Musgrave,

    I figured that it could be done w/GPPT... but you're talking about a combination of Dex and SQL code, yes? Not really an option, if that's the case, since I don't write Dex. I can read it OK -- as in translating a Dex data handling routine into straight SQL​ -- but writing? After taking your pre-Summit GPPT course, of course I could! But I'm not a developer anymore, and my company won't foot the bill for development courses.

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    TOP CONTRIBUTOR
    Posted Jun 06, 2019 08:12 AM
    @Steve Erbach,

    I haven't ever used Dave's solution, so I ​am going to propose an alternate solution (which would apply to users who do not own Power Tools). There is already an integration for Fixed Allocation accounts. It is pretty straightforward and you can use excel to develop your input file. I would be happy to show you how to set it up. I recently uploaded a set of over 100 different Fixed Allocation Accounts for a client in just a few minutes. Each one only had a few lines to it, but having 84 distribution accounts in one allocation account will be a first for me!

    ------------------------------
    Barbara Gavron
    Atlantic Beach / Jacksonville FL
    904-536-0129
    ------------------------------

    Conference-GPUG_200x200


  • 5.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 06, 2019 08:26 AM
    @Barbara Gavron,

    That is extremely generous of you, Barbara! Let's chat off-line, shall we?

    If this works out, not only will you receive a (coveted) Leslie Vail badge, but I'll make a blog post out of it!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 6.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 07, 2019 12:54 PM
    My friend, @Barbara Gavron, conducted a quick GoToAssist meeting with me this morning. During that meeting she constructed a new integration template in Integration Manager that will do the trick for our 1,092​ Fixed Allocation distribution lines we need to add to our Chart of Accounts.

    Therefore, I am pleased to report that Ms. Gavron is the latest recipient of the coveted Gold
    @Leslie Vail Award!

    Awarded to Barbara Gavron

    Congratulations, Barbara, and thank you for the help!

    I will be posting a blog about the steps needed to construct the integration using the Integration Manager on the TWO database.

    Sincerely,​​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 7.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    SILVER CONTRIBUTOR
    Posted Jun 06, 2019 11:16 AM
    ​Hi @Steve Erbach

    I think the first questions to ask are always; What do you already have created/setup that you can use? And What tool are you most familiar with?

    My favorite tool is VBA so that is my go to. I use the code below to import the accounts when creating a new company.  Our COA is the same for all companies except for the first segment which represents the store (restaurant).  So it is very easy to export all of the accounts from a Smart list to Excel, change the first segment and save as a csv. Then just import it using the routine.

    For this case it will obviously have to be a 2 step process. 1) Create any accounts that don't already exist for the distributions.  2) Create the Fixed Allocation Accounts.

    You can easily try this code in a test environment by adding the Account Maintenance form and all fields to VBA. Then paste in the code and make sure all field references are working.  You may have to teak a bit for your setup but it should pretty much work as is.  We only have a couple of Fixed Allocation accounts and have always just set them up  by hand.  So you would have to create similar code for the Fixed Allocation form.  I can help with that if needed because sometimes dealing with a grid can be tricking in VBA.

    Just a qualifier; I know that there are many other (and some probably better) ways to do this now but I was using this method long before I had access the newer tools, so I just stick with what I know.  Give me a shout off-line if you like.

    'use this routine to import chart of accounts from a file

    'uncomment all code then start by clicking on user defined 2

    'DON'T FORGET TO SET THE STORE NUMBER

    'YOU MUST FIRST MAKE THE ACCOUNT CATEGORIES MATCH THE EXPORTED STORE

    'Dim acctnum As String, Dept As String, desc As String, posttype As String, typbal As String, cat As String


    'Private Sub UserDefined2_AfterGotFocus()

    '   AccountMaintenance.Clear.Value = 1

    '   Open "S:\stores\153\GPCOA.csv" For Input As #1

    '   DoEvents

     

    '   Do While EOF(1) = False

    '       Input #1, acctnum, Dept, desc, posttype, typbal, cat

    '       AccountMaintenance.Account.Value = "153-" & acctnum & "-" & Dept

    '       DoEvents

    '       AccountMaintenance.Description.Focus

    '       DoEvents

    '      AccountMaintenance.Description.Value = desc

    '       AccountMaintenance.Category.Value = RTrim(cat)

    '       If posttype = "Balance Sheet" Then

    '           AccountMaintenance.PostingType.Value = 0

    '       ElseIf posttype = "Profit and Loss" Then

    '           AccountMaintenance.PostingType.Value = 1

    '       Else

    '           MsgBox ("Posting Type is wrong")

    '       End If

    '       If typbal = "Debit" Then

    '           AccountMaintenance.TypicalBalance.Value = 0

    '       ElseIf typbal = "Credit" Then

    '           AccountMaintenance.TypicalBalance.Value = 1

    '       Else

    '           MsgBox ("Typical Balance is wrong")

    '       End If

    '       DoEvents

    '       AccountMaintenance.Save = 1

    '       DoEvents

    '   Loop

    '   Close

    'End Sub

    Happy Coding!​

    ------------------------------
    Jeff Martin
    Director of IT
    DeRosa Corporation
    Wauwatosa WI
    ------------------------------

    Conference-GPUG_200x200


  • 8.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 06, 2019 03:56 PM
    @Jeff Martin,

    I hadn't considered using VB... but I can certainly see why you would use it. I've done VB coding sporadically over the past couple o' decades... but these days it's limited to the occasional Excel VBA module.

    I'm going to try Barbara Gavron's method first in our Test company... but I'll keep an eye on your method, too.

    Thank you for chiming in, fellow Wisconsinite!

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 9.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 12, 2019 08:51 AM
    @Steve Erbach

    Early in my consulting career I did this very project for one of my clients in Atlanta not too many years ago - same thing - allocation accounts to be used to properly split expenses between several departments.

    I would caution - they abandoned this entire functionality after a few months because the ability to drill in to see exactly what happened to the accounts was a bit of a nightmare.

    In GP after you code to the allocation that code sends it out to the correct account as per how you set it up.  However, in older versions of GP when you drill into the payable later you are not able to see, quickly, exactly how it allocated out - and custom reporting was required for this.​

    I would suggest doing a few tests to see if this will work for them with how they research and report, etc.

    PS:  They needed over 400,000 allocation accounts, so, yes, IM will work to create them...LOL

    ------------------------------

    ------------------------------

    Conference-GPUG_200x200


  • 10.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 12, 2019 09:11 PM
    @Jo deRuiter,

    >>  in older versions of GP when you drill into the payable later you are not able to see, quickly, exactly how it allocated out - and custom reporting was required for this.​ <<​

    Well, our version is 2016 R2, so I think we're OK! I haven't been able to help our Accounting Manager do the comparatively small number of allocation distributions ("small" compared to your 400,000! Yikes!) yet because I'm in Cleveland, site of the late earthquake, learning how to become an ISO 9001 auditor! I know, right?

    Thanks for the independent confirmation about Integration Manager. Would you mind looking over the blog post I'll be making (Real Soon Now!) on how to do this before I post it?

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 11.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    TOP CONTRIBUTOR
    Posted Jun 12, 2019 10:52 PM
    Hey @Steve Erbach
    From Jo's standpoint of "they abandoned it later because they couldn't drill back", we had a customer who wanted to do a bunch of allocations but wanted to be able to look at the initial account and see what the info before it was allocated.

    We built them some SmartConnect maps that reached into GP and took the balances in specific accounts and then allocated them out to the other accounts.  Wonder if this would save you a lot of allocation acct setup and maintenance.  Thinking if there is good if/then logic behind which accounts get allocated how then you could do that easily with SQL scripting in SmartConnect.

    Just an extra thought :)



    ------------------------------
    Windi Epperson
    President/GP Senior Consultant
    Advanced Integrators, Inc.
    norman OK
    405-946-1774 ext. 102
    ------------------------------

    Conference-GPUG_200x200


  • 12.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    GPUG ALL STAR
    Posted Jun 17, 2019 09:50 AM
    @Windi Epperson,

    When I first read your post I was in Cleveland in ISO Auditor mode. Now that I'm back at work I believe that I understand better what you're saying.

    If I understand you correctly, you're saying that SmartConnect could allocate existing account balances to another set of accounts using distribution rules without setting up the allocation accounts themselves. Is that correct?

    Regarding SmartConnect, I'd dearly like to acquire the broader suite of eOne products besides just SmartList Builder.

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 22-Mar-2019
    ------------------------------

    Conference-GPUG_200x200


  • 13.  RE: A (dare I say 'coveted?') LESLIE VAIL badge to whomever can help us!

    TOP CONTRIBUTOR
    Posted Jun 17, 2019 10:59 AM
    @Steve Erbach
    You got it - do allocations w/o all the allocation accounts.  My hope would be that you could setup one or two "smart" maps with if/then logic in them.  Maybe one map that allocates to 3 accts and one that allocates to 10.  Then the users could pick which to run depending on the need.
    Or if the logic in them can be pinned down fairly precisely, you could schedule the maps to run daily, weekly, monthly.

    Oh the things you can do with SmartConnect! :)​

    ------------------------------
    Windi Epperson
    President/GP Senior Consultant
    Advanced Integrators, Inc.
    norman OK
    405-946-1774 ext. 102
    ------------------------------

    Conference-GPUG_200x200


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