Open Forum

Expand all | Collapse all

SQL Tables for Budget Information

  • 1.  SQL Tables for Budget Information

    Posted 19 days ago
    Our CFO has requested several changes be made to our current 2020 budget.  This will involve quite a few revisions in GP, completing these manually would be extremely time consuming.  I would like to make the changes using SQL tables, but I am not sure which tables to pull.  Would anyone be able to share those table names with me?

    We are currently on Dynamics GP 2018 and SQL version 2017.

    Thank you -

    #Budgeting, #Accounting, #Finance, #GeneralLedger​​

    ------------------------------
    Patty Baker-Palenik
    Finance and Accounting Manager
    Hospice of Marion County, Inc.
    Ocala FL
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: SQL Tables for Budget Information

    GPUG ALL STAR
    Posted 19 days ago
    The tables are GL00200 and GL00201, but I would recommend going through the UI and then deleting the versions not accepted.

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

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

    Academy - Online Interactive Learning from Experts


  • 3.  RE: SQL Tables for Budget Information

    TOP CONTRIBUTOR
    Posted 19 days ago

    Hi Patty,
    Jo has given you some good advice -- better to stay out of the SQL table and do it through the User Interface.

    We download our budgets from Excel and upload changes all the time -- perhaps this would be a good time for you to make use of that option.

    To modify an existing budget:

    Path:   Financial > Cards > Budgets > Open > choose "using Excel" from the dropdown list

      • The Budget File will open as an Excel spreadsheet - showing all acct numbers, descriptions, and monthly budgets.
      • Consider this your original.
      • Make a duplicate copy (call it Revision 2 or something) to another tab in that worksheet (so you can maintain the original)
      • Make all your changes in Excel on the Revision 2 worksheet; save in Excel

    • Upload the Revision 2 worksheet  -- use the path below
    Financial > Cards > Budgets > Excel > choose from dropdown list: "Import from Excel"

    A dialog box will appear for the "Budget Wizard" for Excel
    • Choose "next"
    • What type do you want to import?  Choose "new" -- "next"
    • Give the new budget a name (e.g.  20-21REV2) and description (e.g. 2020-21 Revision 2)
    • Choose fiscal year (I assume you're on a 6/30/20 Fiscal Year end)
    • Select the Date Range (choose your fiscal year 2020, for example)
    • "next"

    The next dialog box asks where your Excel file is for Revision 2 --
    • browse to that workbook file
    • indicate which worksheet within the workbook has your Revision 2 figures on it.
    Please remember to make a copy of your original so you can go back to it if the process doesn't work for you.  But we have had very good luck doing it this way.  Holler if you have questions.

    Maureen




    Academy - Online Interactive Learning from Experts


  • 4.  RE: SQL Tables for Budget Information

    TOP CONTRIBUTOR
    Posted 18 days ago
    Victoria Yudin provides everything you need to create an Excel refreshable report for your budget.
    You can use the same view to create a budget SmartList favorite.

    ------------------------------
    Lou Spevack
    American Council on Education
    Washington DC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: SQL Tables for Budget Information

    TOP CONTRIBUTOR
    Posted 8 days ago
    IMHO, since the SQL budget tables are simple, and since they do not consist of posted transactions, there is no real danger in updating them through SQL directly.  If you have information in SQL for the year already, I would copy the table contents into Excel to use as a backup.

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Paradise GP Consulting, Inc.
    Sunrise FL
    ------------------------------

    Academy - Online Interactive Learning from Experts


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