PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 07/19/2018 10:25 AM by  blong
GL monthly balances
 3 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Chesca
Programmer/analyst
State
Veteran Member
(1042 points)
Veteran Member
Posts:490


Send Message:

--
07/19/2018 6:39 AM
    I need to dump data from Lawson for budgeting analysis and need help with finding the right data fields/databases to pull from. Is there a Lawson screen/table that stores monthly activity for income statements accounts and ending balances for balance sheet accounts? Any input would be greatly appreciated
    blong
    Private
    Private
    Advanced Member
    (57 points)
    Advanced Member
    Posts:21


    Send Message:

    --
    07/19/2018 7:14 AM
    For actual amounts we use the GLAMOUNTS table joined with the GLCGCPY table:
    DB_BEG_BAL + CR_BEG_BAL gives you the beginning balance.
    DB_AMOUNT_01 + CR_AMOUNT_01 gives you Period 1 balance. Repeat for additional periods

    For actual units we use the GLUNITS table joined with the GLCGCPY table:
    DB_BEG_BAL + CR_BEG_BAL gives you the beginning balance.
    DB_UNITS_01 + CR_UNITS_01 gives you Period 1 balance. Repeat for additional periods

    For budget we use the FBDETAIL table joined with the GLCGCPY table:
    Amount fields:
    DB_BEG_BAL + CR_BEG_BAL gives you the beginning balance.
    DB_AMOUNT_01 + CR_AMOUNT_01 gives you Period 1 balance. Repeat for additional periods.

    Units fields:
    DB_BEG_BAL_UNT + CR_BEG_BAL_UNT gives you the beginning balance.
    DB_UNITS_01 + CR_UNITS_01 gives you Period 1 balance. Repeat for additional periods.

    We use the GLCGCPY (company group) table to pull records for only the companies in a certain group as we have restructured our GL and have historical information related to closed companies. So we only pull for active companies in the company group we are interested in.
    Chesca
    Programmer/analyst
    State
    Veteran Member
    (1042 points)
    Veteran Member
    Posts:490


    Send Message:

    --
    07/19/2018 9:03 AM
    Thank you so much for your prompt response! I will create an SQL or crystal
    blong
    Private
    Private
    Advanced Member
    (57 points)
    Advanced Member
    Posts:21


    Send Message:

    --
    07/19/2018 10:25 AM
    You are welcome!

    If you need the AU and Account descriptions:
    GLNAMES contains the COMPANY, ACCT_UNIT and DESCRIPTION.
    GLCHARTDTL contains the ACCOUNT, SUB_ACCOUNT and ACCOUNT_DESC
    You are not authorized to post a reply.