GL Tables


Commonly Used Tables:
GL00100 – Account Master
GL00102 – Account Category Master
GL00105 – Account Index Master [contains concatenated account number]
GL00200 – Budget Master Header
GL00201 – Budget Master Detail
GL10000 – Transaction Work/Unposted (header)
GL10001 – Transaction Work/Unposted (line detail)
GL10110 – Open Year Summary Master
GL10111 – Historical Year Summary Master
GL20000 – Open Year Posted Transactions
GL30000 – Historical Year Transactions
GL40200 – Segment Description Master
DTA10100 – MDA Transaction Groups
DTA10200 – MDA Transaction Codes

Summary Views:
GL01201 – Budget Summary Master View [contains concatenated account number]
GL11110 – Open Year Summary Master
GL11111 – Historical Year Summary Master
[Note: These views show debits, credits and net changes per period per account, they do not show the period balances. The reason to use these and not the corresponding GL10110 and GL10111 tables is that these views also include the Account Description.]

ACCTTYPE (Account Type):
1 – Posting Account
2 – Unit Account
3 – Posting Allocation Account
4 – Unit Allocation Account

PSTNGTYP (Posting Type):
0 – Balance Sheet
1 – Profit and Loss

Active:
0 – Inactive
1 – Active

TPCLBLNC (Typical Balance):
0 – Debit
1 – Credit

FXDORVAR (Fixed or Variable):
1 – Fixed Allocation
2 – Variable Allocation

ACCATNUM (Account Category):
1 – Cash
2 – Short Term Investments
3 – Accounts Receivable
4 – Notes Receivable
5 – Inventory
6 – Work In Process
7 – Prepaid Expenses
8 – Long-Term Investments
9 – Property, Plant and Equipment
10 – Accumulated Depreciation
11 – Intangible Assets
12 – Other Assets
13 – Accounts Payable
14 – Notes Payable
15 – Current Maturities Of Long-Term Debt
16 – Taxes Payable
17 – Interest Payable
18 – Dividends Payable
19 – Leases Payable (Current)
20 – Sinking Fund Payable (Current)
21 – Other Current Liabilities
22 – Long-Term Debt
23 – Common Stock
24 – Preferred Stock
25 – Additional Paid-In Capital – Common
26 – Additional Paid-In Capital – Preferred
27 – Retained Earnings
28 – Treasury Stock
29 – Common Dividends
30 – Preferred Dividends
31 – Sales
32 – Sales Returns And Discounts
33 – Cost Of Goods Sold
34 – Selling Expense
35 – Administrative Expense
36 – Salaries Expense
37 – Other Employee Expenses
38 – Interest Expense
39 – Tax Expense
40 – Depreciation Expense
41 – Income Tax Expense
42 – Other Expenses
43 – Other Income
44 – Charges Not Using Working Capital
45 – Revenues Not Producing Working Capital
46 – Gain/Loss On Asset Disposal
47 – Amortization Of Intangible Assets
48 – Non-Financial Accounts
[Note: Many installations that we have seen have changed the account category names to better fit their chart of accounts. Any categories created in addition to these defaults are marked with a * in GP, even thought they have a valid category number in the GL00102 table.]

DECPLACS (Decimal Places):
[Used for Unit Accounts]
1 – 0
2 – 1
3 – 2

TRXTYPE (Transaction Type) in GL10000:
0 – Standard
1 – Reversing

Series (in GL tables):
1 – All
2 – Financial
3 – Sales
4 – Purchasing
5 – Inventory
6 – Payroll – USA
7 – Project
10 – 3rd Party

DTA_Series (in DTA tables):
2 – Financial
3 – Sales
4 – Purchasing
10 - Invoicing
11 – Sales Order Processing
12 – Purchase Order Processing

BACHFREQ (Batch Frequency):
1 – Single Use
2 – Weekly
3 – Biweekly
4 – Semimonthly
5 – Monthly
6 – Bimonthly
7 – Quarterly
8 – Miscellaneous

PSTGSTUS (Posting Status) in DTA10100:
1 – Work
2 – Open
3 – History
4 – Originating (not in the GL yet)

ACCTENTR (Allow Account Entry) in GL00100:
0 – No (do not allow account entry)
1 – Yes (allow account entry)

General Ledger reporting notes:

  • Use ACTINDX to link account numbers from transactions.
  • GL00100 will have the account name and GL00105 will have the concatenated account number.
  • Unposted transactions are stored in 2 tables (GL10000 and GL10001), whereas posted transactions will be in one table (either GL20000 for open year or GL30000 for closed year).
  • General Ledger SQL views


Last Updated: 04.18.2014

270 Responses to “GL Tables”

  1. Carsten Klausman Reply March 6, 2014 at 7:12 am

    Victoria,

    We are trying to take the GL account posting to produce a P/L enriched with item information from the SOP tables.

    First we are extracting the GL transactions using this SQL (Data set 1):
    SELECT t.ACCOUNTNUM
    ,t.ACTNUMBR_1
    ,t.ACTNUMBR_2
    ,t.ACTNUMBR_3
    ,t.AmountCur
    ,t.BUSINESSUNIT
    ,t.CURNCYID
    ,t.Department
    ,t.ORTRXSRC
    ,t.TRXDATE
    FROM
    (
    SELECT CAST(LTRIM(RTRIM(l.ACTNUMBR_1)) AS NVARCHAR(7)) AS ACTNUMBR_1
    ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2)) AS NVARCHAR(7)) AS ACTNUMBR_2
    ,CAST(LTRIM(RTRIM(l.ACTNUMBR_3)) AS NVARCHAR(7)) AS ACTNUMBR_3
    ,g.trxdate
    ,CAST(g.ORTRXSRC AS NVARCHAR(25)) AS ORTRXSRC
    ,SUM(g.DEBITAMT – g.CRDTAMNT) AS AmountCur
    ,CAST(g.CURNCYID AS NVARCHAR(5)) AS CURNCYID
    ,CAST(LTRIM(RTRIM(l.ACTNUMBR_1)) AS NVARCHAR(10)) AS BUSINESSUNIT
    ,LTRIM(RTRIM(l.ACTNUMBR_3)) AS Department
    ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2)) AS NVARCHAR(10)) AS ACCOUNTNUM
    FROM GL20000 g
    INNER JOIN GL00100 l
    ON g.ACTINDX = l.ACTINDX
    WHERE g.TRXDATE >= ’2011-01-01 00:00:00.000′
    GROUP BY l.ACTNUMBR_1
    ,l.ACTNUMBR_2
    ,l.ACTNUMBR_3
    ,g.trxdate
    ,g.ORTRXSRC
    ,g.CURNCYID

    UNION ALL
    
    SELECT CAST(LTRIM(RTRIM(l.ACTNUMBR_1)) AS NVARCHAR(7)) AS ACTNUMBR_1
        ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2)) AS NVARCHAR(7)) AS ACTNUMBR_2
        ,CAST(LTRIM(RTRIM(l.ACTNUMBR_3)) AS NVARCHAR(7)) AS ACTNUMBR_3
        ,g.trxdate
        ,CAST(g.ORTRXSRC AS NVARCHAR(25)) AS ORTRXSRC 
        ,SUM(g.DEBITAMT - g.CRDTAMNT) AS AmountCur
        ,g.[DATAAREAID]
        ,CAST(g.CURNCYID AS NVARCHAR(5)) AS CURNCYID
        ,CAST(LTRIM(RTRIM(l.ACTNUMBR_1)) AS NVARCHAR(10)) AS BUSINESSUNIT
        ,LTRIM(RTRIM(l.ACTNUMBR_3)) AS Department
        ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2)) AS NVARCHAR(10)) AS ACCOUNTNUM
    FROM GL30000 g
    INNER JOIN GL00100 l
        ON g.ACTINDX = l.ACTINDX
        WHERE g.TRXDATE >= '2011-01-01 00:00:00.000'
    
    
    GROUP BY l.ACTNUMBR_1
        ,l.ACTNUMBR_2
        ,l.ACTNUMBR_3
        ,g.trxdate
        ,g.ORTRXSRC
        ,g.CURNCYID
    

    )t

    Secondly then we are extracting the SOP tables using this Query (Data set 2):

    select CAST(SOP_LINES.ITEMNMBR AS NVARCHAR(50)) AS ITEMNMBR
    ,CAST(LTRIM(RTRIM(COA.ACTNUMBR_1)) AS NVARCHAR(7)) AS ACTNUMBR_1
    ,CAST(LTRIM(RTRIM(COA.ACTNUMBR_2)) AS NVARCHAR(7)) AS ACTNUMBR_2
    ,CAST(LTRIM(RTRIM(COA.ACTNUMBR_3)) AS NVARCHAR(7)) AS ACTNUMBR_3
    ,SOP_LINES.QUANTITY
    ,CAST(SOP_HDR.CUSTNMBR AS NVARCHAR(25)) AS CUSTNMBR
    ,CAST(SOP_HDR.TRXSORCE AS NVARCHAR(25)) AS TRXSORCE
    ,SUM(SOP_LINES.UNITCOST * SOP_LINES.QUANTITY) AS CostAmoutPosted
    ,CASE
    WHEN SOP_HDR.SOPTYPE = ’4′ THEN (CAST(SUM(SOP_LINES.UNITPRCE * SOP_LINES.QUANTITY) AS NUMERIC(38,9)) *-1)
    ELSE CAST(SUM(SOP_LINES.UNITPRCE * SOP_LINES.QUANTITY) AS NUMERIC(38,9)) END AS LineAmount –Returns has to have the sign changed
    ,CAST(SOP_HDR.CURNCYID AS NVARCHAR(5)) AS CURNCYID
    ,SOP_HDR.CCODE
    ,SOP_HDR.GLPOSTDT

    FROM sop30300 SOP_LINES
    INNER JOIN SOP30200 SOP_HDR
    ON SOP_LINES.SOPNUMBE = SOP_HDR.SOPNUMBE
    AND SOP_LINES.SOPTYPE = SOP_HDR.SOPTYPE
    INNER JOIN GL00100 COA
    ON SOP_LINES.SLSINDX = COA.ACTINDX
    WHERE 1=1
    AND SOP_LINES.SOPTYPE IN (3,4) — Only Sales Orders and returns
    AND SOP_hdr.PSTGSTUS = ’2′ –Only posted items
    AND sop_hdr.VOIDSTTS = ’0′ –Exclude voided transactions
    GROUP BY SOP_LINES.ITEMNMBR
    ,coa.ACTNUMBR_1
    ,COA.ACTNUMBR_2
    ,COA.ACTNUMBR_3
    ,SOP_HDR.SOPTYPE
    ,SOP_LINES.QUANTITY
    ,SOP_HDR.CUSTNMBR
    ,SOP_HDR.TRXSORCE
    ,SOP_HDR.CURNCYID
    ,SOP_HDR.CCODE
    ,SOP_HDR.GLPOSTDT

    Thirdly we are outer joining the data set 1 = data set 2 by this key:

    TRXDATE = GLPOSTDT
    ACTNUMBR_1 = ACTNUMBR_1
    ACTNUMBR_2 = ACTNUMBR_2
    ACTNUMBR_3 = ACTNUMBR_3
    ORTRXSRC = TRXSORCE

    In case we are able to join the two data sets we take the “Lineamount” from data set 2 and use that number to calculate the P/L – however it doesn’t add up to if we produce a trial balance based on the GL accounts postings only.

    We were hoping to drill from a GL account to the actual invoice line.

    Is it at all possible to link the GL postings to the SOP tables and in that case what are we doing wrong.

    I hope you can put some light on our little challenge.

    Kind Regards
    Carsten Klausman
    Sr. Businesss Intelligence Specialist
    Pandora Jewellry

    Like this

    • Hi Carsten,

      There are many reasons why this logic may not work, the top few I can think of:

      1. You have transactions in the GL that did not come from the SOP module. You should be able to account for the with how you join your data, but I don’t think ORTRXSRC = TRXSORCE and the other links you have at the bottom are enough. I didn’t go through your code in a lot of detail, but I think you should be linking on (at least) something like SOP30300.SOPNUMBE = GL20000.ORDOCNUM and SOP30300.SOPTYPE = GL20000.ORTRXTYP.
      2. It’s possible that the actual distributions on your SOP transactions are not using the defaulted account numbers that you are using (SOP30300.SLSINDX). If anyone on even one transaction overrides the GL distributions at the summary level, this logic will fall apart. The end result of this is that there is not a definitive link between SOP line items and the GL20000 or GL30000 tables.
      3. Similar to the previous point, but slightly different, so I wanted to separate it out, is that you’re only looking at the quantity * unit price. Both of these might not be what you want to use, however, that will depend greatly on exactly how you are using GP, so it’s very difficult to speak to generically without looking at your data. For example, if you ever use markdowns or trade discounts, these won’t match up.

      So, in summary, depending on exactly how you are entering transactions into GP it may not be possible to link the GL directly to the individual line items like you’re attempting. What I would probably do instead is take several passes through the data, first get the GL detail, then ‘expand’ each GL detail that came from SOP into what line items and accounts/amounts that contains. You will need some error checking here to make sure that the totals match what’s in the GL and if they do not, at least show a message or come up with some way to show that on your report.

      I hope that helps.

      -Victoria

      Like this

  2. HI Victoria,

    Which field in GL20000 table is the date the user entered the data into the system? TRXDATE, DEX_ROW_TS or ORPSTDDT]

    Like this

    • Hi Kimberly,

      I am not sure that you can always definitively get the date something was entered. ORPSTDDT stores the date the originating transaction was posted – not entered. DEX_ROW_TS stored the date and time the latest change was made to the transaction. TRXDATE is the General Ledger Posting Date.

      -Victoria

      Like this

  3. Victoria,
    I am trying to run

    select * from GL00100
    where ACTINDX not in
    (select distinct ACTINDX from GL10001
    union
    select distinct ACTINDX from GL20000
    union
    select distinct ACTINDX from GL30000)

    to find the accounts not used on any GL transactions, but it returns

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘GL00100′

    Please help! Thank you!

    Like this

    • LK,

      Sounds like you may be running your query against the wrong database – this will only work when you run it against the GP company database. :-)

      -Victoria

      Like this

      • Thank you Victoria. Can you please clarify what you mean by “GP company database”? I am using SSMS 2008 to run the query in the DYNAMICS database on the server where Dynamics GP was installed.

        Thank you!

        Like this

        • LK,

          Each company you have set up in Dynamics GP is a separate SQL Server database. The DYNAMICS database (also called the ‘system database’) holds overall/shared settings, including users, security and multicurrency. All the other data, including accounts and transactions, will be in the individual database for each company. You can see a list of your companies in the SY01500 table in the DYNAMICS database – the INTERID column in that table will hold the SQL Server database name for each company.

          -Victoria

          Like this

  4. hello mam

    i want to join table [GL30000] ,[GL20000],[GL00100],can u please tell the relationship between these table

    i am trying to filter through trxdate from GL30000 table

    Like this

  5. Thanks for the reply Victoria.
    Also If I want to link PM table (open/posted transactions) to GL, what are the fields I should join on? I eventually want to pull out the related account number to the transactions.

    Thanks again.

    Like this

  6. Hi Victoria,

    Could you please tell me which column in GL table refers to Cost Center.

    Like this

  7. I’ve found this info very helpful Victoria, so thanks for making it available.

    I’m extracting data from GP and found that I got different results if I inner joined the transactions to GL00100 vs GL00105 on the ACTINDX field. I expected the two account master tables to be equivalent in terms of account index values, but apparently they weren’t. Is it common that these two tables get out of sync with each other? Is one of the account master tables more “reliable” than the other?

    Thx
    Darren

    Like this

    • Hi Darren,

      That does not sound right. These 2 tables should have the same number of accounts, with the same account indexes. Can you run the following queries against your data and see what they come back with?

      select * from GL00100 
      where ACTINDX not in 
      (select ACTINDX from GL00105)
      
      select * from GL00105 
      where ACTINDX not in 
      (select ACTINDX from GL00100)
      

      -Victoria

      Like this

  8. Hi Victoria, I have a question about joining GL and PM tables. I’ve seen your recommendation that suggests joining DocType to ORTrxTyp and VChrNmbr to ORCtrNum, but in my instance, that join doesn’t work. What I’m looking for is VendorID and POrdNmbr for a GL transaction. I’ve found the PO # in PM30200 and the GL Trx# in GL20000. How do I equate them? Thanks for your help! This is a great site!

    Like this

    • Hi Bill,

      Are you saying you want to show the PO number for the GL transactions coming from payables transactions? If so, there are two modules that these could be coming from – PM and POP – and you would probably want to code for these separately (unless you’re only using one or the other). But if really depends on what you’re looking to report on. It may help if you give me a little bit more detail on what you’re looking to do… :-)

      -Victoria

      Like this

      • Yes. Only for payables. Sorry.

        I didn’t consider the POP tables….I will see what I find there.

        This is for a report that shows detailed account transactions by fiscal period. Essentially, I need to give the vendor and po # where possible.

        Bill

        Like this

        • Bill,

          If you’re only looking at PM tables, you can do something like the following:

          select g.*, p.PORDNMBR 
          from GL20000 g
          left outer join 
          (select DOCTYPE, VCHRNMBR, PORDNMBR
           from PM20000
           union 
           select DOCTYPE, VCHRNMBR, PORDNMBR
           from PM30200) p
           on g.ORTRXTYP = p.DOCTYPE and g.ORCTRNUM = p.VCHRNMBR
          

          -Victoria

          Like this

          • Thanks for the query. It does work. But, any idea why most of the results of POrdNmbr are either null or blank? Less than 1 percent have a valid PO#.

            Like this

          • Hi Bill,

            The query does not limit itself to only payables transactions, I just gave you something to get you started. Also, nothing except an invoice will have a PO number in payables, and then only a subset of them is typical to see PO numbers for in most companies. If you’re finding a lot of PM invoices do not have PO numbers and you are expecting them to, then you will want to talk to your accounting department about how these are being entered. Hope that helps.

            -Victoria

            Like this

  9. How do we relate our GL2000 table with the chart of accounts and their categories?

    We have 13,757 posted transactions in GL2000 and when we try to relate that table to the chart of accounts GL00100 and GL40200 so that we can group by our categories , we end up with 25,000+ records.

    Victoria, how can we create a SQL query that would Show us all the GL2000 accounts but also give us the flexibility to group them by our categories in the GL40200 table?

    Like this

    • Karry,

      Account Categories are in GL00102. Segment names are in GL40200. Below is a script to show all the entries in GL20000 and their category names:

      select c.ACCATDSC, g.* 
      from GL20000 g -- GL
      inner join GL00100 a --accounts 
      	 on g.ACTINDX = a.ACTINDX
      inner join GL00102 c --categories
      	 on a.ACCATNUM = c.ACCATNUM
      

      -Victoria

      Like this

  10. Hi Victoria,

    Im a newbie to this and find your tables very helpful. Im trying to find current balance and Beginning balance for GL Accounts (GLPGL). I couldnt find any columns which directly lists the, Can you please tell me where to find or how to calculate them.

    Thanks,
    Chev

    Like this

    • Chev,

      You can’t find this because GP does not store current or beginning balances in tables. You would need to calculate them.

      -Victoria

      Like this

      • Thankou Victoria..

        Like this

      • Could you please tell me if there is any straight forward way to calculate those balances??

        Like this

        • Chev,

          This gets more complicated if you have multiple open years, but you can try something like this:

          select 
          g.YEAR1 [Year],
          n.ACTNUMST Account_Number,
          g.MNACSGMT Main_Account_Segment, 
          case g.ACTIVE when 1 then '' 
          	  else 'X' end Inactive,
          g.ACTDESCR [Description],
          sum(case g.PERIODID when 0 then g.PERDBLNC 
          	 else 0 end) Beginning_Balance,
          sum(case g.PERIODID when 0 then 0 
          	 else g.DEBITAMT end) Debit,
          sum(case g.PERIODID when 0 then 0 
          	 else g.CRDTAMNT end) Credit,
          sum(case g.PERIODID when 0 then 0 
          	 else g.PERDBLNC end) Net_Change,
          sum(PERDBLNC) Ending_Balance
          from GL11110 g
          inner join GL00105 n
          on g.ACTINDX = n.ACTINDX 
          where g.ACCTTYPE = 1
          group by g.YEAR1, g.MNACSGMT, g.ACTDESCR, 
          g.ACTIVE, n.ACTNUMST
          

          -Victoria

          Like this

    • How about GL10110 (open year) and GL10111 (closed years)?

      These tables have balances by year and period.

      Like this

  11. Victoria,

    I’ve looked throught these threads and cannot find the answer I’m looking for. I’m hoping you can help. I’ve been asked to write a report that shows all GL Entries posted after 5:00 PM on the close date for the closing period.

    I looked at GL20000.Dex_Row_TS thinking that it would have only been updated when a record was entered, but found that it is apparently updated at other times. I cannot use the GL20000.TrxDate because it is the “posted” date and not the date entered (much less time).

    Do you know of a reliable method in the Great Plains SQL database for pulling GL records by entered date/time?

    Like this

    • Cat,

      I am not aware of anything in GP that will track the correct time – the Dex_Row_TS is as close as you will get, but it is not always accurate, as you have seen. You can get the date the transaction was posted in ORPSTDDT – that may be the best you can do. If this is important for the future, you may want to add a trigger to track this in a separate table.

      -Victoria

      Like this

      • Thank you so much for your speedy response.

        I will see if they will approve the trigger and custom table, then write the report using that.

        I appreciate you,

        Cat

        Like this

  12. once posted is the JE reversing date tracked aywhere else I am working on a user request to identify entries as reversing with the reversing date
    using series=2 and ortrxtyp=1 i can id as reversing but don’t see the rev date in the gl20000 or gl30000

    Like this

    • Mark,

      From looking through our data, it looks like the reversing transaction will have the same journal entry number, but the RCTRXSEQ will be 1 (whereas for the original entry it will be 0). Also the reversing transactions will have a TRXSORCE starting with ‘GLREV’.

      Hope that helps.
      -Victoria

      Like this

  13. Hi Victoria,

    I apologize if the answer is in here already. When I ran year end close and reconcile for a couple databases (we have 47 databases with our instance of GP 10.0), the periodid 0 in their GL10110 and GL10111 tables are missing. All the rest of the databases are fine, it is just for a couple that were all setup at the same time (do I have a trigger accidentally set to clear this – if so, where would it be?). I have deleted both tables and rebuilt them but the starting numbers are missing. There are no missing entries in GL20000 or GL30000, all the BBF entries are there. These BBF are what populates periodid 0. Is there a way to fix this other than creating them manually?

    Like this

    • Mark,

      I have not seen this happen before. Does this mean that you have no beginning balances for the balance sheets accounts in these companies? Are you able to restore and re-run the year end close? If not, you may want to talk to GP support for help with this.

      -Victoria

      Like this

  14. Hello,

    Our finance department has many reoccurring General Journal entries. However, it does not appear that great plains creates entries in the GL 10000 table like it does if you just create a standard General Journal entry. How does Great Plains handle the General Journal Entries that are reoccurring? I do see a handful of entries just sitting in the GL 10000 table as if they are a unposted batch. Do Reoccurring transactions always remain as an open Batch and can only be posted?

    Why I ask is that it states that the GL 10000 account is the work file for all General Journal transactions. However, we never see any transactions getting created in this work file if they arte set as reoccurring transactions.

    What is the workflow through the tables for General Journal entries?

    Like this

    • Hi Larry,

      Unlike posted GL entries, unposted GL entries are stored in 2 tables: GL10000 has the header information and GL10001 has the detail/line items. Recurring entries work very much like other GL entries and any saved recurring GL entry will be in both of these tables. We have about 5 of these in our database, and I can see them in there alongside the non-recurring entries. In the GL10000 table the RCRNGTRX field will be 1 for any transactions that are recurring.

      Another way to look for these would be to check the SY00500 table – this will contain all unposted batches. Since unposted recurring transactions must be in a batch, you should see them in this table with a BACHFREQ of something other than 1 (single use) and a SERIES of 2 (financial).

      If you’re not seeing anything like that, I would suspect that what your users are calling ‘reocurring’ may not actually be using the recurring entry functionality in GP.

      -Victoria

      Like this

  15. Hi Victoria,

    I have been going to your site a fair bit recently as I am starting to do more reporting work with GP. I am still learning the tables and their relationships so hoping you can help.

    I want to develop a query where I can start with g/l postings for a specific account number and then join their a/p voucher details (if they exist) and then the notes from the SY03900 table.

    I am having difficulty finding the primary key(s) to use on GL20000 and PM32000. I tried voucher number, but got some odd results. I also tried the original source, but that is a batch reference.

    What can I use to grab the GL20000 records for an account and pull the related A/P details?

    thx,
    Doug

    Like this

  16. Hi Victoria, I need your help. Is there a SQL view/script that will give me all GL accounts with no historical or current activity. I’d like to take the results and delete those unused accounts; is there also a script to do a mass delete within SQL that you would recommend?

    Like this

    • Hi Ana,

      You can use the following code to find the accounts not used on any GL transactions:

      select * from GL00100 
      where ACTINDX not in 
      (select distinct ACTINDX from GL10001
       union 
       select distinct ACTINDX from GL20000
       union 
       select distinct ACTINDX from GL30000) 
      

      However, this will not identify any accounts used on setup windows for items, customers, vendors, etc. Or any accounts on unposted transactions in the subledgers. To do a mass delete from SQL, you would really want to make sure those accounts are not used ANYWHERE else. Otherwise users will get errors when the system tries to use those accounts and they are not longer valid.

      Like this

  17. Hi Victoria,

    I just stumbled across your blog and have found more info here about GP than all other places combined, I was thinking there wasn’t much out there!

    Anyways, I am a bit new go GP and am groping around. I am trying to link items purchased in SOP30300 to the GL distributions (in GL2000?). I see items and their components in IV00104, and the GL ACTINDX column in IV00101, but that looks like the setup rules for an item. I am more interested in getting from the sale to the GLs the money ended up in.

    Thanks in advance for any help! I hope I am being clear, please let me know if clarification is needed to make sense of this!

    Like this

    • Hi Warren,

      The account distributions that go to the General Ledger when an SOP transaction is posted are not stored on a line item basis and SOP30300 is the line item detail…so it may not be possible to always definitively get this at the line item level. If you’re ok with looking at the GL distributions at the SOP transaction level, they are stored in the SOP10102 table. In case you need the distribution types, are listed on my SOP tables page. Hope that helps. If you have more specific questions, let me know.

      -Victoria

      Like this

  18. Hi Victoria
    What a great site, thank you so much for providing this. I am new to GP and this has really helped to guide me through the database :).

    I have a quick question. Is it possible to link the GL transactions to the POP tables to return the Item and PO number(s) which correspond to the GL Transaction? I have tried various combinations, but as the invoices created are one Receipt to many PO’s I am struggling to get a correct match.

    Many thanks.

    Like this

    • Hi Jubline,

      Thank you for your kind words.

      It may be possible to link the GL transactions on the POP tables and the item(s)/PO number(s), however, you might not be able to do it by amount. And you will have to somehow manage multiple results…so depending on exactly what you want to show this may be possible, but certainly not easy. Part of the issue is what you mention – one receipt could represent multiple PO’s. Another part would be that items are not necessarily linked to GL distributions on the POP transactions because the GL distributions are stored in summary, not by line item or corresponding PO number.

      If you can narrow your scope to only include certain types of GL (or POP) transactions that may be easier. What specifically you are trying to accomplish with your reporting?

      -Victoria

      Like this

      • Hi Victoria
        Many thanks for such a speedy response – it’s so kind of you.

        I am trying to link 2 types of GL transactions to their corresponding PO number and Item numbers so that we can follow PPV (Purchase price Variance) through the system.

        The issue seems to be that whilst most of the goods received may have a one-to-one relationship with the PRGN number, at the point that the GL Invoice is raised and a Purchase Invoice put onto the system, the GL Line PRGN has many PO’s and items.

        The only natural key between GL and POP seems to be the PRGN number. I am currently linking the GLTransactionView to GL00105 on ACTINDX and then to POP30310:
        GLTransaction_View.ORDOCNUM = POP30310.POPRCTNM
        then to POP 10500:
        GLTransaction_View.ORDOCNUM = POP10500.POPRCTNM

        This is quite possibly incorrect… I got my idea from picking the POP_PPV view apart and looking at the tables used there and then trying to link them into the GL view.

        Any light that you could shed on this would be really gratefully received. Thanks so much again for all your help :)

        Justine

        ps – I think I’m trying to do something similar to Mark (below):

        Victoria, Regarding this comment made earlier and your response: Typically (and definitely in this case) GP posts in aggregate – if you have 5 lines on a receipt that all go to the same account, you will not see 5 separate GL distributions for the same account in the same transaction, you will see one line combining them. So there is nothing to link a GL entry or part of it to a particular line number on a receipt.
        Do you know if there is a way t post the detail of the receipt line items to the GL without them posting in aggregate. If not, what would you suggest to get the detail of these line items along with purchasing to balance to G/L.

        Like this

        • Jubline,

          First thing that jumps out at me is that since both POP30310 and POP10500 hold multiple transaction types, at the very least you would want to add a link on the transaction type.

          I am not familiar with ‘POP_PPV view’ and ‘the PRGN number’, what are those?

          One suggestion that is more of a setup thing, but that might make tracking PPV easier is to set up GP so that it posts the PPV to a separate account (or group of accounts). That way you only have to check the accounts where PPV goes, not all transactions/accounts.

          But realistically, even if this type of reporting can be done (and I am still not convinced that it can), I am not sure I can ‘generically’ help with this, it may require someone actually looking at your data to help you with it.

          -Victoria

          Like this

          • Hi Victoria

            Apologies for not sending you all the correct information. PRGN numbers are the receipt numbers:
            POPRCTNM on the POP tables and then ORDOCNUM (and possibly ORCTRNUM on the GL side).

            The PPV view is possibly something written in-house? It just returns all of the PPV transactions.

            I shall try adding a link on the Transaction Type between POP30310 and POP10500 and see if that helps me.

            Thanks so much for all of the suggestions. I will do as you suggest and see if we can get someone to take a look at the data here and make some suggestions.

            Thanks again, I really appreciate your assistance. :)

            Jubline

            Like this

    • Have you looked at joining the INVINDX field in POP10100 to the ACTINDX in GL00100? This is how I can get GL transactions down to the PO info.

      Like this

  19. Hi Victoria,

    Let me know why we get this error : Input variable contains a duplicate journal entry number in General Leder in GL10000 GL20000 .

    This is my one of the client facing this error. Can you tell me how can i solve this…

    Like this

    • Kavitha,

      That does not sound like a typical GP error to me, at least not one that I have seen before. If you have a customization or 3rd party product involved, I would ask them about this.

      -Victoria

      Like this

      • victoria,
        It does not involved any 3rd Party product.But for customization …first i am not clear with this. can you explain about customization.

        I have used same DLL file to import excel file into my GP .
        Let me know if you need any farther info…. for giving me help

        Like this

        • Kavitha,

          As far as I know importing into GP using a “DLL file” is not something that is supported or out-of-the-box. So I would think that is a custom process. The error message sounds like you are trying to import a journal entry number that already exists. If that is not the case, I would probably recommend starting with the developer of this DLL for getting help.

          -Victoria

          Like this

          • Yes your correct victoria, I have used same dll file in two diff systems…i have imported successfully.

            Myself only develop this DLL file. I have used this SP taGetNextJournalEntry to finding next journal number…

            i am getting correct next journal entry number. But i am bit confusing why in my client system not increment correctly..

            Like this

  20. Hi Victoria,

    I hope all is well. We have an issue. Our client was in the process of closing the year for his company. He ran through the year end closing procedures. After closing the year I noticed that there were a bunch of transactions that were not posted. I opened the fiscal period and the user posted the remaining batches. After doing so I noticed in our report that the opening balance for an income account was not zero. The closing balance was carried forward to the opening balance of the current year. Where can I confirm the opening balance for an account in Great Plains? Is there a way to fix this? Thanks.

    Like this

  21. Hi Victoria,
    Not sure if this question is related to this place. Im a DBA and just trying to help my colleagues. I hope the below can explain well what we are looking for:

    The situation is that we show entries to the Sales Tax liability account that show what our system thinks we should be paying to the State. But, when we prepare the tax return we discover differences; always. This is either due to us not catching something on the tax return or our GP system is coding some kind of sale/sales incorrectly as a taxable sale.

    Question:
    Is there any kind of report that can be run that shows which accounts in our system are set up as taxable?
    May be if it is possible we go through the transactions of the accounts that are charged with taxes?

    Thanks,
    Sameer

    Like this

    • Hi Sameer,

      I do not believe there is just one report you can run that would help you determine what is causing the discrepancies you’re seeing. I would recommend working with your GP partner to go through your GP setup as well as the reports/windows you are looking at currently that show the discrepancies to determine what is causing them. While it could be something simple, it could also be a lot of other things and it is really impossible to say without looking at your system.

      -Victoria

      Like this

      • Thank you for quick reply,
        Lets assume if you have system in front, what things you’ll check.

        Like this

        • Sameer,

          I am not sure what ‘Lets assume if you have system in front’ means, but again, there is no way to answer this without seeing the reports you are looking at and going over your actual data. For all I know you have General Ledger entries updating the sales tax liability account directly. There is no system setting to check for that – this would require understanding where the data for those entries is coming from and why it is being entered directly into the General Ledger.

          -Victoria

          Like this

  22. Running GP2010 SP1 with Binary Stream Multi Entity Management.

    Like this

    • Jeff,

      I don’t think this was something not working in SP 1 of GP 2010, but you’d have to look through the list of fixes for SP 2 to determine for sure. It’s also possible that the MEM product is replacing that window (as it does with many windows) and their version of this window does not have the same functionality as the GP version. If MEM is replacing this window, you could possibly test this by temporarily disabling the modified window or the MEM product…or talking to Binary Stream support to check with them.

      -Victoria

      Like this

  23. Is there an action that keeps the sort order? The right side of the lookup screen keeps returning to ‘Type’ as in posting or unit etc. rather than staying Main Segment.

    Like this

  24. Victoria,

    Is there a way to change the Account Lookups sort to run by a specifc segment rather than the out of the box sort which is ‘Type’ AFter you change to a segment, lets say main segment for example how do you lock that choice in so that it comes up with the sort every time you open the accounts lookup screen?

    Like this

  25. Hi Victoria,
    First of all thank you for blogging extensively to keep all of the rest of us sane… :)

    My question involves GL transactions. Here is the scenario: we took a client live in Dynamics 2010 September of this year. Shortly prior to golive we imported all of their GL transactions for 2012 and posted them. Within a few days we realized that the data was corrupt and included a bunch of batches that were commitments and budgets. (The budgets and commits had been posted to the GL as actual transactions)

    The obvious solution is to back those batches out of the GL – there are only a few thousand of them – but I was wondering what would happen if we simply over-wrote GL20000 with the correct data for pre-September, and then just imported the transactions from Sept forward onward. If all of the current JE numbers were the same, would we maintain data integrity? We wouldn’t want to lose drill-down and audit trails.

    This is probably a stupid question – but you never know!

    Kind Regards,

    Sean

    Like this

    • Hi Sean,

      I would strongly recommend NOT doing this directly in the tables. There are a lot of related tables and you’re going to spend a lot of time testing, checking and fixing all of them. My recommendation would be import correcting entries.

      -Victoria

      Like this

  26. I am looking for a table that actually has the general ledger account balances – not the period change – for each period. I am working with GL10110 and GL10111. Is there any other table that just gives balances or am I stuck having to calculate this info?

    Like this

  27. Thanks Victoria

    I will have to buy the book. I think it will defintely be worth it.

    Regards
    Nish

    Like this

  28. Hi Victoria,

    I need some urgent help from you. We are in the process of moving a compnay over from pastel Account to Dynamics GP 2010. I need to import the COA together with three years of historical data. Please could you advise on the best process to follow in this regard.

    Regards
    Nish

    Like this

  29. Hi Victoria,

    Good Day! Could you please help me in fixing this error we are about to post a batch coming from Sales Transaction window in Financial Batches window but this error appear ” you can’t post tranasction in General Ledger while multicurrency for the Financial Series being valued”

    Thanks,

    Myrna

    Like this

    • Hi Myrna,

      Sounds like there is a revaluation process started, you cannot post batches until it is completed. If no one has the revaluation window open, it’s possible there is a stuck process in SQL. To clear it, make sure all your users are out of GP, then run the following in SQL:

      delete from DYNAMICS..ACTIVITY
      delete from DYNAMICS..SY00800
      delete from DYNAMICS..SY00801
      delete from tempdb..DEX_LOCK
      delete from tempdb..DEX_SESSION
      

      -Victoria

      Like this

  30. Victoria
    Thank you so much – really appreciate your help. You’ve saved me hours of frustration. I’m sure I speak for everyone when I say we’re all so glad to have found you.

    Like this

  31. Hi Victoria
    I’ve just discovered your blog & am really impressed & grateful. I’m wondering if you can help, I am in IT & have been asked to work with our Accoiunting Dept so am trying to get my head around GP. I am trying to pull together the following information:
    Transaction Date
    Business Unit
    Department
    Natural Account
    Amount
    Financial Year
    Financial Period

    Can you tell me how I can link the GL00100, GL00105, GL2000 & GL30000 in order to pull this information together? I can link the GL00100+GL00105 tables but don’t know how to link them to the GL20000+GL30000 tables.
    KR
    Amanda

    Like this

    • Hi Amanda,

      Some of these things are specific to your business (like Business Unit, Department, etc.), so it’s difficult to answer specifically. In general, you can link GL20000 or GL30000 to the GL00100 and GL00105 tables by the ACTINDX (account index). Each General Ledger account has an index that is used in most tables that you can use to simplify linking. There are some tables that have multiple account indexes with different columns names, but all the tables you listed will have just one – ACTINDX. Hope that helps.

      -Victoria

      Like this

  32. GL Variable Account Maitenance SQL Script for SSRS edit list
    Please provide input and thoughts:
    In a SOX environment getting accountants access to Maintenance screens to view variable account maintenance is not straight forward so creating a SSRS edit list for variable account maintenace seems a possible options. Those of you with more under the hood SQL experience with GP2010 any thoughts on elements missing or an extra garnishment needed? Please reply:

    /****** Variable Account Maintenance: Group Insurance ******/
    /****** I was focusing on one variable account you might have others or want all. ******/
    USE [TEMPDB]
    if object_id(‘#GLVAMSTR’) is not null drop table #GLVAMSTR
    if object_id(‘#GLUAMSTR’) is not null drop table #GLUAMSTR
    USE [NREG]
    SELECT DISTINCT
    RTRIM(GLS.ACTNUMST) AS VAR_ACT,
    GLV.ACTDESCR AS VAR_ACTDISC,
    RTRIM(GS.ACTNUMST) AS DIST_ACCT,
    GL.ACTDESCR AS DIST_ACDISC,
    DIST_ACTIVE = CASE WHEN GL.ACTIVE = 1 THEN ‘Active’ WHEN GL.ACTIVE = 0 THEN ‘Inactive’ ELSE ” END,
    VA.BDNINDX
    INTO #GLVAMSTR
    FROM GL00104 AS VA
    INNER JOIN GL00100 AS GL ON VA.DSTINDX = GL.ACTINDX
    INNER JOIN GL00105 AS GS ON GL.ACTINDX = GS.ACTINDX
    LEFT OUTER JOIN GL00100 AS GLV ON VA.ACTINDX = GLV.ACTINDX AND GLV.ACTINDX= 17918 — I was focusing on one variable account you might have others or want all.
    LEFT OUTER JOIN GL00105 AS GLS ON VA.ACTINDX = GLS.ACTINDX AND GLS.ACTINDX= 17918 — I was focusing on one variable account you might have others or want all.
    WHERE GLS.ACTNUMST IS NOT NULL

    SELECT
    UA.ACTINDX AS UAGLINDX,
    RTRIM(GS.ACTNUMST) AS UNIT_ACCT,
    GL.ACTDESCR AS UNT_ACTDISC,
    [YEAR] = CASE WHEN UA.YEAR1 IS NULL THEN 0000 ELSE UA.YEAR1 END,
    PERIOD = CASE WHEN UA.PERIODID IS NULL THEN 0000 ELSE UA.PERIODID END,
    AMOUNT = CASE WHEN UA.PERDBLNC IS NULL THEN 0.00 ELSE UA.PERDBLNC END,
    UNT_ACTIVE = CASE WHEN GL.ACTIVE = 1 THEN ‘Active’ WHEN GL.ACTIVE = 0 THEN ‘Inactive’ ELSE ” END
    INTO #GLUAMSTR
    FROM GL10110 AS UA
    INNER JOIN GL00100 AS GL ON UA.ACTINDX = GL.ACTINDX
    INNER JOIN GL00105 AS GS ON GL.ACTINDX = GS.ACTINDX
    WHERE UA.ACTINDX IN (Select BDNINDX FROM #GLVAMSTR)

    SELECT
    a.VAR_ACT, VAR_ACTDISC, DIST_ACCT, DIST_ACDISC, DIST_ACTIVE, UNIT_ACCT, UNT_ACTDISC, UNT_ACTIVE, [YEAR], PERIOD, AMOUNT
    FROM #GLVAMSTR a
    inner join #GLUAMSTR b ON a.BDNINDX = b.UAGLINDX
    ORDER BY a.VAR_ACT, YEAR, DIST_ACCT, UNIT_ACCT, PERIOD

    Like this

  33. Victoria -

    Your blog is one of the first sites I come to when trying to figure out an issue with Dynamics GP. I have always found it EXTREMELY useful in my Dynamics GP travels.

    I do have a question about the GL10001 table while researching an issue with MDA. How is the DTA_GL_Status column populated? I currently have a problem where MDA records are “disappearing” from the DTA10100 and DTA10200 tables. In the GL10001 table I noticed its been populated with a status of 0 or 32, but I don’t know what these mean. Do you happen to know?

    Many Thanks,
    Sean

    Like this

    • Hi Sean,

      Thank you very much for the kind words!

      I cannot find any reference for the MDA (DTA) tables in the GP SDK, or anywhere else. I submitted a question to Microsoft asking if anything like that is available. In the meantime, I did find this about the field you are asking about: http://support.microsoft.com/kb/857570. Also found another comment on a partner forum saying that the DTA_GL_Status will be 32 if a transaction is coming from a subledger. In our data, I see most of the transactions with 0 coming from Intercompany transactions, Quick Journal transactions or transactions like the year end close – basically transactions that do not work with MDA.

      In any case though, my gut feeling is that transactions “disappearing” from the DTA10100 and DTA10200 tables would not be caused by what is in that field. Although, just to make sure I understand, when you say “disappearing” – are they there at one point, then not there anymore later? Or do they never make it in there to begin with?

      -Victoria

      Like this

      • Thank you for the info! I also found the kb you noted. It didn’t really help answer what I’m trying to track down.

        Here’s the steps of what’s happening.
        1. Information from ADP is delivered
        2. Accountant imports the data into the Financial >> General Journal window via Tools >> Integrate >> Import from ADP which he then links to a spreadsheet to import the data.
        3. Because we’re using MDA (albeit probably not correctly) he executes a Macro (generated from VBA code in a spreadsheet) to input the MDA information.

        Once he’s done this I can validate the transactions in the GL10000, GL100001, DTA10100 and DTA10200. When he deletes the batch, it deletes the transaction correctly from the GL10000 and GL10001 tables and the DTA10100/200 tables but it deletes extra records from the MDA tables. So before he executed this transaction, I validated 1061 records in the DTA10200 table, after he did the import, I had 1078 which looked correct. When he deleted the batch, I expected to have 1061, but instead had 941, so it deleted more than just the MDA records associated with the transaction.

        Same thing happens when he posts. He can post a transaction with MDA items and it will post correctly, when he posts another transaction with MDA items, it will delete the previous MDA information. It’s the strangest thing I’ve ever seen!

        I tried using the Support Debugging Tool to fire off an email in the event of a record deletion from those tables, but never get it to fire correctly. I ran a SQL trace this morning and can see it calling several sp’s relating to the process (gldeletebatch, dtavalidatestats, etc.) but trying to figure out “why” the extra records are being deleted is becoming quite daunting.

        Any thoughts where I might start with this? The good thing is this is easily reproduced in our test environment which is helpful so it’s not a random occurrence.

        Like this

        • Hi Sean,

          Thanks for the additional detail. That definitely sounds like there is something going wrong and again, I would not expect the value in the DTA_GL_Status to be the answer. You have 2 ‘processes’ that are not quite out-of-the-box, I would start testing by first eliminating them, then adding them back one at a time.

          For example, start by entering a sample transaction manually (doesn’t have to be the entire transaction you normally import, just a small portion), add the MDA information manually – do you still have the same results when deleting or posting? If not, then do an Import from ADP and then enter the MDA information manually – do you have the same results when deleting or posting?

          If that still does not help track down the problem, I would recommend working either with your GP Partner if they know the GP tables well, or Microsoft GP Support. Exiting records being deleted is a pretty serious issue, and I would want to track down what is causing it as soon as possible.

          -Victoria

          Like this

  34. Victoria, do you know where the Source Document APL comes from in GL20000? I know it has something to do with applying payments but I don’t know what steps or through which window these entries are created.

    Thanks

    Like this

    • Hi Peter,

      APL stands for Apply Receivables Documents. While not recommended, it is possible to change the source codes, but you can check them for your company by going to Microsoft Dynamics GP | Tools | Setup | Posting | Source Documents.

      -Victoria

      Like this

      • PS. Typically APL postings are generated when you apply a receivables payment, return or credit to an invoice after posting using the Apply Sales Documents window and there is a GL change that needs to be recorded. For example, a discount or writeoff is taken or there is an exchange rate difference.

        -Victoria

        Like this

  35. VIctoria,
    Thanks for the website. It is so useful! I am using GP with Crystal Reports and do not see a GL11110 or GL11111 on my table list. Do you know why this is or how to fix it? Thanks in advance for your help.

    Deeann

    Like this

    • Hi Deeann,

      Very glad to hear my blog is helpful! GL11110 and GL11111 are views, not tables. So if you are in Crystal and you’re only looking at tables, you will not see them listed. Quick way to finding these in the list when you are looking at the data connections in Crystal:

      • Right click on your connection and choose Options
      • Select Views under Data Explorer and deselect everything else (or, if you are looking for all GL tables and views, leave Tables and Views both checked, then in the Table name LIKE box type in GL% - that will give you both tables and views starting with ‘GL’)
      • Click Ok
      • Right click on your connection again and choose Refresh
      • Next time you open your connection you will see a different list, based on what you have selected

      Hope that helps
      -Victoria

      Like this

  36. Is there a GP 2010 table where we can see the Batch Number for financial transactions that have already been posted? I am doing a data query from GP10000 for unposted transactions, would like something similar for posted transactions. Any help would be appreicated!

    Like this

  37. Thanks Victoria! They had told me to use the PERIOD BALANCE field but when it came up negative they said the balance was wrong. But as you said I think it is just the way they entered the information.

    Like this

  38. Victoria,
    Please excuse my GP ignorance. Someone asked me to write a report giving the balance on a line of credit.
    Wouldnt the credit amount field be equal to the total credit avaliable. And wouldnt the debit amount field be the amount used of the credit line?

    Like this

    • Hi Jerome,

      No problem at all – I was answering your question from an accounting (and GP) perspective. Accounting (and GP) does not know what you are actually putting in a particular General Ledger account. It could be cash, it could be sales, it could be an expense, or…a line of credit. No matter what, the balance is always Debits – Credits. That does not change.

      What does change is how the accounts are used. For example, Cash accounts increase on the Debit side and decrease on the Credit side. So a Debit balance for a cash account means a positive cash balance. However, Sales accounts increase on the Credit side and decrease on the Debit side. So a negative balance for a sales account actually means positive sales. None of this is GP specific – this is strictly accounting.

      Unfortunately, since I do not know exactly how your line of credit entries are booked, it’s really impossible to answer your question any more specifically. I can tell you what I think, or how I might enter these, but that would just be wasting your time – you need to get this knowledge from your accounting folks. So…if you are not too familiar with accounting and specifically how this account is being used in your GP, whoever has asked you to create this report may need to give you a little more help/guidance than simply asking for a balance of an account.

      Hope that helps explain things just a little better.
      -Victoria

      Like this

  39. In the GL11110 my period balance is -6000 but my Credit Amnt is 20000 and my Debit amnt is 14000. Is this correct? shoudnt my Period Balance = Credit amt – Debit amt ?

    Like this

    • Hi Jerome,

      Credits are negative and debits are positive. And if you’re subtracting the 2, it’s always Debit – Credit. So your period balance is 14,000 – 20,000 = -6,000.

      -Victoria

      Like this

  40. Hai victoria, here is an issue: I ran the Check Links yesterday. Now, users(inlcluding powerusers) cannot see / access / view / created any GL accounts. However, after logging in as the sa, they are definitely all there. I run checklinks of system and finance…they show no error…do you know where the problem is…?

    Like this

    • Fazil,

      I have not seen Check Links do anything like this, and while nothing is impossible, I would consider that pretty unlikely. Is it possible that security settings and/or user permissions got changed?

      -Victoria

      Like this

  41. Victoria, Regarding this comment made earlier and your response: Typically (and definitely in this case) GP posts in aggregate – if you have 5 lines on a receipt that all go to the same account, you will not see 5 separate GL distributions for the same account in the same transaction, you will see one line combining them. So there is nothing to link a GL entry or part of it to a particular line number on a receipt.

    Do you know if there is a way t post the detail of the receipt line items to the GL without them posting in aggregate. If not, what would you suggest to get the detail of these line items along with purchasing to balance to G/L.

    Like this

    • Hi Mark,

      I am not aware of any setting to change the posting behavior for POP receipts. The only options would be to post receipts of one item at a time. This will not break anything, you can still enter invoices in aggregate, but it would certainly take a lot longer to process receipts if you have a lot of items on receipts.

      Looking at if from the reporting side, you could possibility use the POP accounts and distributions from POP30310 and POP30390, but you would need to do some testing to make sure this works for you data.

      However, if you’re looking to do this because you’re having trouble reconciling the Inventory module or Payables module to GL, then I am not sure this is the way to go because POP only has a subset of what goes into Inventory and Payables. So you would really want to compare to Inventory and Payables tables, but even then doing this manually using tables is very complicated.

      If this is a recurring issue, you might want to consider The Closer.

      -Victoria

      Like this

    • No, new users also cannt see the accounts… here is a similar issue i found from internet… http://msgroups.net/microsoft.public.greatplains/cannot-view-enter-any-gl-account/83605#replyForm…. but no solution in this.

      Like this

  42. I am pulling a transaction detail from the GL10000 and 100001 tabls but want to also see (in seperate fields) the segment names from the GL40200 table. How do I link them together and pull into my excel reports. I dont know what fileds to lik together between the the Trx tables/GL00100/gl00105/gl40200 tables to make that work. Any thoughts?

    Like this

    • Patrick,

      You can link from GL10001 to GL00100 on ACTINDX. Once you’ve done that, you an link from GL00100 to GL40200 based on the segment number and value, for example:

      For the first segment:

      GL00100.ACTNUMBR_1 = GL40200.SGMNTID and GL40200.SGMTNUMB = 1
      

      For the second segment:

      GL00100.ACTNUMBR_2 = GL40200.SGMNTID and GL40200.SGMTNUMB = 2
      

      -Victoria

      Like this

  43. Hi Victoria,
    I created a SmartList using Segment Description Master to get listing of all Segments. Is there a way to get the User ID (who created) and the Create Date

    Thanks,

    Khan

    Like this

  44. Victoria,

    Thanks for previously answering my questions, very helpful. I have identified several DEBITS in the RM20101 & RM20201 tables that I am unable to identify its corresponding SOP Invoice record. The APTODCNM = ‘DBT00XXXXX’ and the ‘APFRDCNM = ‘CRT00XXXXX’. I am not understanding what exactly this debits are related to? Are they debiting credits previously attached? (I have confirmed in this particular system prefix DBT & CRT are infact debits & credits and confirmed using RMDTYPAL field). Any insight into this matter would greatly be appreciated.

    - Dave

    Like this

    • Dave,

      There is no requirement that a debit or a credit memo has to ‘relate’ to an invoice. So, this is not a GP question so much as it is a question for your accounting department. Give them the specific debit and credit numbers and ask them. If they say…’oh, this goes with SOP invoice XXXXX’, ask them how they know this – is there something they have entered into GP that tells them this? If so, where is it?

      -Victoria

      Like this

  45. Hi Victoria,

    First of all, Love your Blog! Very informative! Am learning a lot here and able to adapt quickly to GP because of it.

    I have a question re:GP2010, I noticed that there are the GL00100F1 to F4 tables. I wonder what these are for. I am familiar with the GL00100 tables but when I try to research what these “filters” tables are for, I only find their definition as “filters” and nothing else describing what they are for.

    This is out of curiosity.

    Thanks! and More Power!

    Like this

    • Hi Peter,

      Thanks for your kind words!

      It’s a good question, as I have not seen this documented anywhere. I am not sure what those tables are for. I suspect they were added for some performance gains and are used by certain processes in GP as needed. I have not checked them often, but have never seen them have any data in any of the GP installs I have worked on. For more information, consider posting your question on the GP Community Forum to see if anyone can answer this.

      -Victoria

      Like this

  46. Hi Victoria… I’m one month new to Dynamics and stepped into an existing problem.

    We are running Dynamics GP 10 with SP 3 + Hotfixes.

    Historically the “Unapplied Amount Total” in the Inquiry -> Sales -> Receivables Summary screen has always matched the “Period Balance Total” in the Inquiry -> Financial -> Summary screen for our AR GL. About 14 months ago the Period Balance Total amount was over by $19.74. This difference has increased three more times over the past 14 months by similar amounts. I have been tasked with locating the transactions that caused these differences and/or explaining why the two amounts no longer balance.

    I have been researching table definitions and looking at scripts but feel I am just spinning my wheels. The only employee with Dynamics experience left abruptly about two years ago and since then employees have been doing things by rote, with little to no routine maintenance being performed.

    Any assistance on where to start would be greatly appreciated!

    Thank you.
    David

    Like this

    • David,

      Have you tried using the Reconcile to GL feature for this? (GP | Tools | Routines | Financial | Reconcile to GL) Sometimes this works really well, other times not so much. But I would start with that to see if it helps track down the issues.

      -Victoria

      Like this

      • Hi Victoria… I have tried running the Reconcile to GL feature, but it crashes with a SQL Server error of:

        Violation of PRIMARY KEY constraint ‘PK##1752157′. Cannot insert duplicate key in object’dbo.##1752157′.

        I have researched the error but have yet to find a workable solution. There was apparently a KB952380 article with a query to fix the error but I am unable to locate it.

        Thank you.

        David

        Like this

        • Hi David,

          I cannot access that KB article either, but other KB articles with similar errors do not seem very helpful, either. Let’s tackle this from a different angle. First I have a question about the data you are comparing. I am not sure that I would use the Receivables Summary Inquiry window to compare to your General Ledger balance because if users are in the system entering and posting transactions as you are doing this, you are going to be chasing a moving target.

          What I typically do as a start to trying to track stuff like this down is establish the real balances I am trying to match up historically, so I can see where they started to have an issue and to narrow down the data I need to search through. I do this by printing the Receivables Historical Aged Trial Balance (HATB) report using the GL Posting Date option and compare that to the GL balances. I would also recommend checking period ends, not current dates. So for example, if your periods are calendar months, print the HATB as of 10/31/11 and compare that to the GL period balance as of 10/31/11. Are those off? If so, go back until you find where they started to be off. Make a list of both balances in Excel and calculate the differences. This will help establish where you need to look more closely.

          Another consideration I want to mention – unless something small and easy to find is causing differences, tracking down a $20 difference may not be worth a lot of time and effort. If your differences are consistently that small and if they go back and forth ($20 one month, -$20 another month, etc.) this may be just timing differences in some voids and not really an issue worth spending time on. I don’t know the size of your typical AR balance, but for most companies $20 is a pretty insignificant percentage of the total. I just wanted to mention this to add a little perspective. That said, I always make sure that our books reconcile to the penny, but that’s because the resource I am using for tracking this stuff down is me and this is what I do for a living and know it very well, and frankly, it’s not very common that our subledgers don’t reconcile to the penny anyway. :-)

          -Victoria

          Like this

          • Hi Victoria… Thank you for the detailed information. From what I understand, they have a process where they validate the totals on the Receivables Summary Inquiry window and the General Ledger at the end of the day, when no one is else is in the system. Apparently the totals balanced to the penny until one day last year.

            Since they balance daily, they know that the initial imbalance ocurred between 9/12/201 and 9/14/2010. I found one record in RM10101 with a debit amount that equals the balance difference, but do not know how to validate if this is the transaction in question.

            Thank you.

            David

            Like this

            • Hi David,

              Are the accounting folks not able to look at the transaction associated with this distribution record you found and see what the issue is? It’s pretty difficult to guess without looking at it. The typical issues are dates or account numbers, but it could be something else also.

              -Victoria

              Like this

              • If I can add a suggestion – take a look at the transactions (if not too many) created between the dates you noted and see if any of the distributions have an incorrect account going against your transaction type of RECV. It has been my experience that an out-of-balance is caused by users over-writing the RECV account with an account *other than* the control account that should be pre-populated on that transaction line. Good Luck!

                Like this

                • Hi Kristie… It looks like we have over 13,000 transactions for those three days. If you can tell me know which tables/fields I should be looking at I could probably create a SQL query to narrow things down.

                  Thank you!

                  David

                  Like this

                  • Again – merely a suggestion, no guarantees.

                    Here’s what I use to find the transactions entered incorrectly for Receivables:
                    SELECT * FROM RM30301
                    WHERE DSTINDX ***
                    and DISTTYPE = 3

                    *** = Receivables Control Account found by querying the GL00100 table
                    DISTTYPE 3 is for the RECV transaction type I mentioned earler

                    Victoria – if this is not the correct way for me to use your forum, please do not hestitate to let me know – I love this site though because so many times it has helped me out of some ruts :)

                    Like this

                    • Hi Kristie,

                      It’s all good – I really appreciate you chiming in!

                      -Victoria

                      Like this

                    • I think the reply form may have stripped out some of the code from Kristie’s reply, it does that to me all the time. Here is what the query should look like:

                      SELECT * FROM RM30301
                      WHERE DSTINDX <> ***
                      and DISTTYPE = 3
                      

                      You could also search for the reverse condition:

                      SELECT * FROM RM30301
                      WHERE DSTINDX = ***
                      and DISTTYPE <> 3
                      

                      -Victoria

                      Like this

                    • Thanks Kristie! The query did locate two transactions, but one was a sale and one was a return so they should cancel each other out.

                      I am again going to recommend they post an entry to adjust for these small differences. Many individuals have spent too much time trying to track this down.

                      Thanks again!!

                      David

                      Like this

              • Thanks Victoria… Unfortunately the accounting folks are do not fully understand the processes and are relying on me to correct issues that have existed for months/years. Since I’m an IT person and don’t have an accounting background, I need explanations on why things are done and how they work, which they cannot supply. I think I have convinced them that the time and effort to track down the discrepancies is not worth it.

                I now have two additional issues to research and don’t know where to start.

                Thanks again!

                David

                Like this

                • David,

                  Consider talking to your GP Partner to see if they can help you bridge that gap between accounting and IT. Maybe you can have them do some training/troubleshooting for you that will help you learn GP better if you are going to be expected to support the accounting department in this way.

                  -Victoria

                  Like this

                  • Hi Victoria… It just happened again. This time we can trace it to something that was posted on 12/27/2011. The Receivables Summary Inquiry was $10 lower than the GL Summary Inquiry for the AR cash account. Both summary windows were viewed with no one else in the system.

                    Is there any way to identify the detailed transactions behind each of these summary windows to locate the offending record(s)?

                    Thank you.

                    David

                    Like this

                    • Hi David,

                      I would use the queries from the previous posts here. I just fixed them to show the code properly. However, based on our prior conversation, you may need to talk to your GP partner or GP support to help you look at the results and determine what is actually causing this. I would suspect there is a customer that has an account number not set up properly, but beyond that, without looking at it I cannot tell you more.

                      -Victoria

                      Like this

                    • David, have your accounting folks looked at the default AR accounts assigned to any of your customers. That would automatically overwrite the “normal” AR account and could cause the issue that you’re seeing here. It would self correct over time if the customer record is changed.

                      Like this

                    • Hi, I’m also an IT guy currently helping out the Accounts team with the same error. From what I have worked out so far this seems to be a temp table which is built to hold enquiry data from multiple tables. Work, Open, and History for RM Transactions in my case. I have narrowed my error to a transaction that exists in both RM20101 (RM Open File) and RM30101 (RM History File) with the same DOCNUMBR. It would seem the process left the RM20101 record when it should have removed it during posting. I could just remove the record but instinct tells me not to touch it. I have run reconcile and check links but no errors found. Has anyone solved this issue yet?

                      Like this

                    • Hi Chris,

                      I am always very conscious of data integrity and doing anything directly inside the GP tables unless I am 100% certain. Because of that, I feel it would be irresponsible to simply say that you should delete what looks like a duplicate record in RM20101 without someone who understands the GP tables well looking at it. From what you are describing, most likely that is the solution, but it also may be that the record in RM30101 is not completely correct and/or other tables may need to be checked to make sure everything matches up, for example RM00401, RM20201, RM30201, RM10101 and RM30301. It may be best to ask for someone to look at this with you – maybe your GP partner or GP Support.

                      -Victoria

                      Like this

      • Hi Victoria,

        I am using GP 10.0, trying to get some data from AP module to GL transactions.

        Kindly please advise how I can link AP details to GL transactions.

        Any column can be linked or there is other step for it.

        Thanks, Murad

        Like this

        • Murad,

          You should be able to link from the GL tables to the payables tables using the ‘originating’ columns. ORMSTRNM will link to the vendor ID, ORDOCNUM will link the document number, ORTRXTYP will link to the document type, and ORCTRNUM will link to the voucher number.

          -Victoria

          Like this

  47. Victoria,

    Do you know which table hold Fiscal periods?

    Thanks,
    Hishma

    Like this

  48. Hello. Just found your site. Love it! I am a bit new to GP so any help is appreciated.
    We are running GP10.

    In the canned Metric “Gross Profit for the Past 12 Months”, the field for December 2010 is blank. All others are populating automatically. The literature I’ve found points to GL20000, and it appears to have data for that month…any ideas?

    Thanks,

    Rose

    Like this

    • Hi Rose,

      I am not 100% sure, but I seem to remember there being a bug that was causing this behavior. Unfortunately, I am not finding anything about this on the internet. :-( I personally am not crazy about this particular metric because it is dependent on account categories all being correct and many companies do not pay attention to that when creating new GL accounts. Here is some more information on that: http://www.vaidy-dyngp.com/2010/08/gross-profit-metrics-daouds-article.html.

      I would recommend posting your question on the GP Community Forum to see if others have seen this and can help you find the cause.

      -Victoria

      Like this

      • Hi Victoria.

        Thanks for the fast reply. I used the information at vaidy-dyngp.com and found the data is there, per the smart lists referenced to check the values in the metric. Very cool exercise but still have the 0 data in our Dec 2010 bucket on the metrics graph, even though it’s there when I do the smart lists. Let me know if you hear anything else about a bug.

        Thanks,

        Rose

        Like this

  49. Hi Victoria,
    Do you know if the GL Account Rollup has a corresponding SQL table where all Account Rollups that were set up in GP are stored? Is there a way to set up the Account Rollup Inquiry to pull GL Balances from multiple databases? Or even maybe by using the Smartlist Builder? Thanks!
    Lynor

    Like this

    • Hi Lynor,

      Account Rollups have a bunch of related tables:
      SE000200 Account Rollups Account Segment Index
      SE000300 Account Rollups Options Header
      SE000301 Account Rollups Options
      SE000401 Account Rollups Account Period Detail
      SE00400 Account Rollups Account Detail
      SE465546 Account Rollups Calculated Column
      SE810000 Account Rollups Account List
      SE90001 Account Rollups Account List Accelerator
      SE988977 Account Rollups Options Columns

      There is no way to pull multiple databases into one Account Rollup, to do that I would usually use FRx. Yes, you can accomplish this with SmartList Builder, but it may be somewhat of a challenge to code this, as GP does not actually store balances, only net changes, so if you needed to see period balances, you would have to code that.

      -Victoria

      Like this

  50. Victoria,

    Do you know where the Sales Line Item Distributions are held? I am looking for the individual line item distributions and I cannot seem to find it in Resource Descriptions.

    Ron

    Like this

  51. Bab,

    Depending on your posting settings and a number of other factors, transactions that have been posted in payables may now be sitting in the GL waiting to be posted. They would be in the GL10000 and GL10001 tables at this stage. Only once the GL transactions are posted do they move to GL20000. Budgets are in GL00201, but you can also use view GL01201 to report on them. Hope that helps.

    -Victoria

    Like this

  52. Hi Victoria

    When we enter a sales order and apply a payment from a credit card. Is there an entry mane in the GL at that time?

    Thanks
    Vic

    Like this

    • Hi Vic,

      Yes, there should be a GL entry resulting from that.

      -Victoria

      Like this

      • Victoria

        I searched the GL10000 and GL10001 tables for the amount or any sogn of it and found nothing. Any other places I should look? Also it is still an SO it has not been transfered to invoice yet , does that matter??

        Thanks
        Vic

        Like this

        • Hi Vic
          it wil only be in GL10000 GL10001 when you post the invoice in SOP and you just will see that journal in GL10000 GL10001 after you post SOP when your posting options is unchecked “post through GL” in setup / posting /

          hope that helps

          Like this

  53. Hi Victoria,

    I recently had an upgrade to GP 2010. Prior to the upgrade to 2010, I was able to change the description of an invoice within the GL. The path was:

    Inquiry -> Financial -> Detail or History Detail -> And then click on the line I wanted to change

    Next, I’d go to:

    Extras -> Additional -> Maintain GL Ref ->
    Then I’d change the description/detail of that invoice

    I don’t see the “Extras” option any longer and I haven’t been able to find a way to do this. Is it still possible to perform this change? I appreciate your help.

    Thanks,
    Pooja Shah

    Like this

    • Hi Pooja,

      Sounds like you had a customization prior to the upgrade, as what you are describing is not possible out-of-the-box in GP. Customizations need to be upgraded separately when you upgrade GP, so you need to talk to whoever created that customization for you originally and see if they can upgrade it for you.

      -Victoria

      Like this

  54. Hi Victoria

    We are looking for a view that shows ALL gl transactions payables, receivables, bank transactions and journal entries.

    Do you have anything like this???

    Thanks
    Vic

    Like this

  55. Hi Victoria,

    How do I know which transactions in GL20000 are included in the period of GL10110? Looks like I can’t use TRAXDATE and ORPSTDDT to determine if they fall in the month period in GL10110.

    Many thanks,

    gaganiaro

    Like this

    • Gaganiaro,

      Usually you can simply use the TRXDATE, however, there are exceptions. The few I can think of off the top of my head are stuff like beginning balances brought forward, adjusting periods that overlap and non-calendar month fiscal periods.

      You should be able to use the OPENYEAR and TRXDATE fields in GL20000 together with the fiscal period setup in the company to determine the corresponding period in GL10110.

      I would absolutely NOT use ORPSTDDT – that is the actual date something was posted and has nothing to do with GL dates whatsoever.

      -Victoria

      Like this

  56. Hi Victoria
    Please i got a problem with some journals posted in GL20000.
    I got a sales invoice (date february 2011)
    that was posted on feb 1st 2011.
    but that transaction generated 8 journal entries in gl2000
    JRNENTRY 8 is the correct distribution of the sales invoice and TRXDATE = feb 1st 2011
    but JRNENTRY 1 to 7 are in 30th april 2010, 31th may 2010, 30th june 2010, 31th july 2010, 31th august 2010, 31th december 2010 and 31th january 2011.
    And all these journals distributions are very small ammounts using inventory and cost of goods accounts
    JRNENTRY=8 has ORGNTSRC = SLSTE00003908 AND SERIES=3 AND ORTRXTYP=3
    AND JRNENTRY 1 to 7 has ORGNTSRC = GLTRX088013 AND SERIES=5 AND ORTRXTYP=0

    I dont know why these journals were automatically created. The problem is that they have changed the data of 2010 (they didnt close 2010 periods)

    Could you help me please?
    is there a way to setup posting that not generate this journals?

    Thanks a lot

    Like this

    • FAC,

      It sounds to me like journal entries 1 through 7 were actually generated by the POP invoices for adjustments to inventory cost. Are they hitting Inventory and Cost of Goods accounts? Usually the REFRENCE for these transactions will be ‘SALES’ and then the POP receipt number. So SALES11069 will be created from POP receipt # 11069.

      I think that the dates will be the original dates of the inventory receipts, but I would have to test to confirm that. I don’t believe there is a way to stop this posting from happening. This is why it is important to look at the posting reports that come out when transactions are posted. And of course, to close periods that should not be posted to, as you mention. This would have gotten caught in Batch Recovery, so the dates could be changed as needed.

      -Victoria

      Like this

      • Thanks Victoria

        those items quantities are integrated by Inventory Transactions (adjustments) by a third party manufacturing software.
        The Sales invoice number is 002-002949
        and the REFRENCE for journal entries 1 to 7 are IVT002-002949, IVA002-002949, VENTA002-002949 all of them with the sales invoice number at the end.
        So i cannot find which inventory adjustments they are referring to.

        The one thing i cannot understand is why they are hitting Inventory and Cost of Goods accounts? I mean why a sales invoice has to change a cost of good account?

        Is there way to view a posting report again?

        Thanks a lot
        I really appreciate your help.

        Like this

        • FAC,

          IVT is an inventory transfer, IVA is an inventory adjustment, but I have not seen VENTA before…

          What is the inventory valuation method? That might make a difference in finding these…if it is FIFO and this invoice was just posted, you should be able to see what the last cost layers sold were.

          Most, but not all posting reports can be reprinted. For Sales, you can go to Reports | Sales | Posting Journals…but I believe what you’re looking for is the Cost Variance Journal, which is not something I see as available for reprinting.

          I would recommend posting this on the GP forums to see if you get some more folks responding who have seen these…I have only seen them coming from the POP module, not from sales.

          -Victoria

          Like this

  57. Hi Victoria ,
    I have a journal entry in GL20000. It has,
    SOURCDOC : GJ .
    REFRENCE : SALESC123555
    BCHSOURC : GL_Normal
    SERIES : 5.
    BACHNUMB :GLTRX441006
    I tried tracing this journal in Inquiry , but it shows a message “Transaction History does not exist for this transaction”. In GL10000, some journals are available for ORTRXSRC : GLTRX441006 .(same as GL20000′s BACHNUMB) and these journal’s SOURCDOC,BCHSOURC ,SERIES are same as mentioned above . I dont know for which scenario and why these journals are created . Please help.
    Thanks in advance.

    Like this

    • Hi Divya,

      This sounds like an inventory cost adjustment that got posted directly to the GL from a POP invoice. I would expect ‘C123555′ to be your POP Receipt Number for the invoice. The typical scenario is something like this:

      1. Post receipt of inventory.
      2. Sell the inventory and post the invoice.
      3. Enter the vendor invoice for the inventory, match it to the receipt and change the price.

      The difference in the inventory cost for what was already sold will be posted directly to the GL in a transaction like you are describing.

      Hope that helps,
      -Victoria

      Like this

  58. Hi Victoria,
    We are trying to perform the year closing for one of our entities and we receive a message that says the retained earnings account is inactive. I have checked table GL00100 in SQL and all of the retained earnings accounts are coded with a 1. I also looked at the account itself under the mastercard records and they are coded as active. Have you seen this message before?

    Like this

    • Yolanda,

      I have not seen this before. Are you closing to just one Retained Earnings account? Or do you have ‘Close to Divisional Account Segments’ checked?

      Have you recently made any changes to your accounts? Have you tried running Check Links or Reconcile on the Financial series?

      -Victoria

      Like this

  59. Hi victoria,

    I am running GP10 SP3 + Hotfix (standard modules + Analytical Accounting)

    As a test i have performed a year end in Fabrikam.

    after year end i verified the BBAL details between

    Summary inquiry Screen and Detail inquiry…

    the date do not match why is it so… but for earlier years it matches Exactly.

    Example:

    Cash Operating Account : 000-1100-00
    BBAL
    Debit : 1529714.84
    Credit : 921077.53

    on clicking details of the same BBal Period

    Detail inquiry screen displays followings

    Cash Operating Account : 000-1100-00
    Year: 2015
    Date: 31-12-2014 to 31-12-2014
    JV No : 3460
    Debit : 608637.31
    Credit : 0.00

    Can you let me know why this is not matching?????

    Same is the case with my live company tooo….

    Thanks
    Akram

    Like this

    • Akram,

      This may just be an issue with hoe GP displays/stores the data. Just to make sure I understand, can you please let me know the following:

      1. Go to Inquiry | Financial | History Summary and bring up the account and the last closed year (2014 in your example), what is the Period Balance (last column) on the LAST line?

      2. Go to Inquiry | Financial | History Summary and bring up the same account for the following year (2015 in your example), what is the Period Balance (last column) on the FIRST line?

      -Victoria

      Like this

      • Hi Victoria,

        Thanks for instant reply

        1. Go to Inquiry | Financial | History Summary and bring up the account and the last closed year (2014 in your example), what is the Period Balance (last column) on the LAST line?

        2. Go to Inquiry | Financial | Current Year Summary Screen Summary and bring up the same account for the following year (2015 in your example), what is the Period Balance (last column) on the FIRST line?

        The period Balance are same on both screen with last line matching the first line…

        608637.31 USD

        In live testing scenario…
        The trail balance before and after matches completely, also the retain earning accounts balance is correct as per acct dept.

        its only that this two screen do not match each other.

        Is there any specific reason for this….

        -Akram

        Like this

        • Hi Akram,

          This may be because you posted additional transactions to the closed year after the year was closed. Or you may be looking at the representation of how GP stores the year-end closing entry. Without seeing your data or at least some screen shots, it is difficult to tell exactly what the reason is for what you are seeing. However, if the ending period balance for the closed year matches the beginning period balance for the new year, there should be no issue.

          -Victoria

          Like this

          • can i attach the screen shot here in this forums…

            as its fabrikam… i would like you to see this….

            Like this

            • Akram,

              You would have to post the pictures somewhere else and then put a link to them here. I would also caution you about using Fabrikam for intensive testing – it has data that is not quite clean and I have seen issues in the past with using Fabrikam to test year-end closing specifically. If you are only seeing an issue in Fabrikam, then it may be related to how the data was imported in there. It is usually better to test with a copy of your own data.

              -Victoria

              Like this

              • Case 1. ( Not matching case)

                Case 2: Summary Matching only with Historical Details

                Here you go…..

                same is the case with my live company too….

                i need to give my Finance dept a reason why this is happening and they still can continue as it will not hamper their work.

                it happens only for BBF not for any other periods 1 to 12….

                -Akram.

                Like this

              • Hello Victoria,

                Here are the cases both screen shot…

                Hope you can help me out in this…

                http://www.megaupload.com/?d=2EF0W9FI

                Like this

                • Akram,

                  I am seeing this in some cases in our live data as well. Again, this is not an issue. It is simply how GP is displaying this. The only thing that matters on the beginning balance line is the period balance. If this is a real issue for the accounting department, I would recommend talking to GP Support to see if they can explain how this is getting populated.

                  -Victoria

                  Like this

  60. Hi Victoria

    Hope I am in the correct forum.

    Accounting posted and invoice last month as 12/01/2011 instead of 12/1/2010. My cfo would like to now change the posting dat to 01/01/2011. Can that be done?

    Thanks
    Vic

    Like this

  61. Hi Victoria

    My Controller needs to see all open balances with a term of CC (Credit Card for us)

    Will any of your views show me that?

    Thanks
    Vic

    Like this

  62. Hi Victoria,

    I want to see if you can please give me your input. I need to post few transactions in June ,July Aug and Sept however, we closed the period and all the statements has been done. Can I still post trx to June? If so what are the best way to open the priod and post the transactions and close them again?

    Anything else needs to be done after posting the transaction? like Reconcile to GL ?

    Thank you,

    Like this

    • maybe i can help with this
      did you close the period of 2010 in Config / Company / Fiscal Periods by checking the month u wanted to close?
      If so, if you want to open that period u just uncheck that month and post ur trx and close it again
      no reconcile needed
      the only problem would be if u already printed legal reports from those closed periods, data will change and u will have legal problems (well in my country we would)
      i hope thats what u wanted

      Like this

  63. Hi ,
    I have encountered the MDA issue while posting.
    I have verified GL10001 and DTA10300 tables. I found in there is one line in GL1001 table Orgseqnum field did not match with DTA10300 Seqnumbr filed.
    How the GL10001 Orgseqnum get updated..? any logic or setup table behind that..?
    I request you to give some more information on MDA analysis and GL10001 Orgseqnum details..

    Like this

    • Vigneshwaran,

      Since this sounds like a possible issue with your data, I would recommend contacting GP Support to make sure you get the most complete information and support specific to your situation.

      -Victoria

      Like this

  64. Hi Victoria,

    Sorry I forgot to mention the impacted tables.

    This are the impacted tables:

    SY00500

    Like this

  65. This site is really helpful. I would like to ask some concerns on GP 7.5:

    The 1st problem is this, one of are users is about to post a IVADJ-B batch however, the batch was automatically marked to posting prior to triggering the actual POST button. Can this be possible?

    Next, we ran the audit trail to check and found out that there was a IVADJ-A batch ran that was completed and finished successfully which immedeatly followed the posting of IVADJ-B. We do not know how this happened considering that the end-user did not trigger the POST button. We also checked that the IVADJ-B was has “marked” (MKDTOPOST) status ’1′ and batch status (BCHSTTUS) ’3′.

    Could you help us and explain what happened to this? Thank you

    Like this

    • Hello,

      As you probably know, GP 7.5 had not been supported for a long time (more detail here), and I believe that there have been improvements in batch posting error handling in newer version of GP. It’s also been a pretty long time since I have worked on GP 7.5, however, most of the concepts should be the same.

      BCHSTTUS = 3 means that the batch is still receiving transactions. When the batch is receiving transactions, the system will not allow anyone else to post it, so that may be why the marked to post status is 1. I have seen this happen when there is a problem, interruption or error during posting and the system will need manual intervention to be able to complete posting. Take a look at the Batch Recovery and Master Posting windows to see if you can still see the original batches and if there are any messages or statuses associated with them there.

      -Victoria

      Like this

      • Hello Victoria,

        Thanks for your help. We were able to checked your resolution and fully understand how this scenario behave.

        Cheers,
        Freedom

        Like this

  66. I am trying to find out if anyone has ever experienced losing their detail journal entry lines from within a batch. The batchID, number of transactions, as well as the dollar amount of the entire batch remains in tact, but the details within that batch have disappeared. When I check the GL10000 table, all is as it should be, but when querying for the details within the batch in the GL10001 table, the distribution lines are not there. I ran Financial Checklinks in hopes it would resync everything, but no luck. Our biggest concern is with the intercompany journal entries which originated from other companies, the details are lost in some of those batches as well as others. My apologies if this is not the right way to get this issue posted, but I could not find where to go to create a new post. Thank you in advance for any help.

    Like this

    • Kristie,

      Sounds like something went really wrong somewhere…I have not encountered this before. The only thing I can think of is trying to restore from a backup if you have the ability to go back to when the data may have been there.

      -Victoria

      Like this

      • Thanks Victoria – from what we could tell there was some sort of hiccup with this particular database and at the same time the hourly transaction log was not getting generated, the details within the journal entries were mysteriously disappearing. We were able to recover back to the last hourly transaction log on to a test database and replicate the details over to our production database, but we still lost about two hours with of journals between the last good hourly log and the next one. So, my lesson learned, for your larger databases, monitor hourly logs, and if possible, create an alert if there’s a gap larger than 1.25 hours between logs. Our outsourcing partner thinks this may have all happened because of lack of space on the hardware where backups are stored until they’re moved to tape. Just sharing what I can in hopes others can avoid the fiasco we went through to recover what we could.

        Like this

  67. Hi,

    I am wondering if there an actual field in Dynamics that holds a cumulative GL account balance for each account balance as opposed to adding period balances together?

    Thanks

    Like this

  68. Hi Victoria

    I was wondering the post date and time of a transaction, in ORPSTDDT only stores the date but no time
    then i thought the DEX_ROW_TS field in GL20000 was the post and date time, but that field showed me todays date about an hour ago, im just curious what data stores in DEX_ROW_TS and why that field has today’s date, original post date was months ago.

    Thanks a lot and pardon my english
    you are the best

    Like this

    • Hi FAC,

      To my knowledge, the ORPSTDDT is the ‘Originating Posting Date’, not ‘original’. Slight difference, but it is important, as in GP ‘originating’ means where something came from. So in this case, the Originating Posting Date is the date the transaction was originally posted in the subledger it came from. For example, if you are looking at a GL transaction that was created by a Payables Invoice, this date will show when the Payables Invoice was posted. All ‘old’ date fields in GP will only have a date, no time, even though in SQL they are a datetime field. DEX_ROW_TS is a new field introduced in GP 10.0 and in this case will hold the date and time the record was created in the GL20000 table, so when it was actually posted in the General Ledger. Depending on your posting settings and many other factors, this date could be different from the Originating Posting Date. Also, the time in the DEX_ROW_TS field is show in UTC (Coordinated Universal Time), not the time on your SQL Server.

      Hope that helps.
      -Victoria

      Like this

  69. Hi Victoria

    Is there any thread in this blog that describes about Analytical Accounting tables, structure etc.., as available for RM/PM/GL/CM etc..,

    Rgds..Sreekumar

    Like this

  70. A user has contacted me after running the Distribution History Detail – Payables Managment report that an account came up “not found.” I have determined that in PM30200 the field for CHEKBKID is blank though that does not seem to be consistant filled or blank. Any suggestion on where to start tracing this back to the missing account?

    Thanks,

    Allen McEuin

    Like this

    • Allen,

      Typically an account not found would make me think that there is something wrong with the account index on the transaction. I would find the transaction this came up for in the PM30600 and look at all the account indexes for it, then look those account indexes up in GL00100 – are they all there?

      -Victoria

      Like this

  71. Victoria,

    I am attempting to link the GL20000 table to inventory items from receipts or po’s and am having difficulty because this table does not seem to hold the original po or receipt line number. Any suggestions?

    Like this

    • Bill,

      Typically (and definitely in this case) GP posts in aggregate – if you have 5 lines on a receipt that all go to the same account, you will not see 5 separate GL distributions for the same account in the same transaction, you will see one line combining them. So there is nothing to link a GL entry or part of it to a particular line number on a receipt. PO’s don’t get posted to the GL, so you’d only be able to link to a receipt and then from there back to a PO.

      -Victoria

      Like this

  72. Dear Victoria,

    First, thanks for this space.
    I am working with analitical accounting, and I need to join the GL with AAG tables, but i have been troubles with it because i was doing it using sqncline and origsecnum that works in the most of the cases but it does not for a few ones.
    So, how can i have something like

    select *
    from GL00100
    where ACTINDX not in
    (select ACTINDX
    from GL20000
    where TRXDATE between
    DATEADD(yy,-1,GETDATE()) and GETDATE()
    union
    select ACTINDX
    from GL30000
    where TRXDATE between
    DATEADD(yy,-1,GETDATE()) and GETDATE())

    -Victoria

    but with aag tables.
    Hope you can help me, thank you.

    Like this

    • Hi Marcia,

      I am sorry, I do not work with Analytical Accounting, so I am not a good resource for this. I would recommend posting your question on one of the GP newsgroups to see if anyone there can help you.

      -Victoria

      Like this

  73. Hi Victoria

    The table explanations for all of the modules are great. Very useful ones.

    I am trying if I can delete or mark inactive all the GL accounts which has not been used for last one year. It would so helpful for me if you can suggest any query for the process.

    thanks
    Jubith

    Like this

    • Jubith,

      I would not recommend deleting accounts from the database, as that can break all sorts of things in GP. Even changing them to inactive might not be the best idea, so definitely test this thoroughly and make backups. However, if you’re simply looking to come up with a list of these accounts, here is a query that would help:

      select *  
      from GL00100 
      where ACTINDX not in 
      (select ACTINDX 
       from GL20000 
       where TRXDATE between 
       DATEADD(yy,-1,GETDATE()) and GETDATE()
      union
       select ACTINDX 
       from GL30000 
       where TRXDATE between 
       DATEADD(yy,-1,GETDATE()) and GETDATE())
      

      -Victoria

      Like this

      • Thanks for the reply and query. I would also not opt for deleting the records from GP, rather I would tick out the option ‘Allow Account Entry’.

        If I filter the sub query with DISTINCT ACTINDX, it runs faster.

        thanks.

        *Jubith

        Like this

  74. Hi Victoria,

    Thank you for all of your advice and help for the Dynamics GP Community, truly appreciate it!

    I was wondering if you could assist me or shed some light on an issue we have. We just upgraded to GP 10.0 to GP 9.0. The upgrade went fine, and during the test upgrade we posted some test invoices to see if everything was going alright.

    However, being new to GP myself, I did a mistake that I only removed these posted invoices from table RM20101… without realizing that the test invoices put numbers into the GL and Invoice Registries.

    Is there a way to completely delete these records from our GP tables? I see that I could delete the GL from GL20000 table; however is this going to affect anything else, as in the GL account balances in GL10100 or etc?

    Thanks in advanced for your help! Greatly appreciate it.

    -Elizabeth

    Like this

    • Hi Elisabeth,

      Unfortunately you did something that I would not recommend doing. :-( Taking posted transactions out of a table is a subledger is going to cause all sorts of havoc. Depending on the details of those transactions, there could be dozens of tables that got updated. Some of them are summary tables which add to totals every time a transaction gets posted, so there is no easy way to cleanly remove selected transactions from them. If it’s possible, I would recommend putting back the rows you have deleted or restoring from a backup and then cleaning these up from the GP user interface by either voiding them or entering returns/credit memos.

      -Victoria

      Like this

      • Victoria, thanks for the quick answer.

        I agree with you and I think we’re going to put back the rows I deleted and let’s see if we could just void these invoices. I’ll let you know how it goes.

        Thanks for your advice!

        Like this

  75. Hi Victoria
    thanks for all your so fast replies you are very helpfull and the best.
    Well i am going to bother you with another problem (sorry)

    Someone screw up the post configuration and today all the posting from every module were not generating journal entries (cause the “post directly to GL” was not checked)

    well after i killed that guy ;) i am trying to fix this huge mistake,
    restore the database to the yesterday’s backup is not an option cause there are a lot of transactions and users will not do all the transactions again,

    so i was thinking to make a query of all PM, RM …. posted transactions that are not posted in GL20000 (well all transactions of today)
    something like this (ie. PM20000 table)

    SELECT VCHRNMBR from PM20000 WHERE VCHRNMBR NOT IN ( SELECT ORCTRNUM FROM GL20000 )

    then i get the distributions from PM10100 of that documents and then make a journal entry for every VCHRNMBR and upload it to GP (through Integration Manager) and then post them.

    is this correct? or there is another way to do that?

    thanks a lot Victoria

    Like this

    • FAC,

      Yes, your approach sounds like it will work. You may want to enlist the help of GP Support or your partner to ensure that all transactions are captured. There may be quite a lot of tables and information to wade through.

      Also, since it may be very difficult to ‘link’ the GL transactions to the subledgers after the fact, another option may be to gather the totals of the distributions and create a General Ledger transaction with just the totals.

      Finally, take access to the Posting Setup window away from all users.

      -Victoria

      Like this

  76. Victoria,

    I am trying to find the details from GL. I have Jrnl No.
    and Audit code (Starts wiht GLTrx….) How do I get the detail info? Can you please see if you can help me out?
    -Jim

    Like this

  77. Hello Victoria,

    I need your help in above listed Commonly Used Tables GL Tabels for GL Reports.
    WIll you please let me know about Tables series of DTA. what data has been stored in DTA tables? Whats stands for MDA or please let me know what is MDA Transactions?

    Like this

  78. Any suggestions on how to get started on a Crystal report that shows me actual vs. budget, current month and year to date. I use FRx for this for financials, but there is a need for external locations to track their spending and I thought Crystal on Citrix would be the only way to go for my cash strapped company. This way managers could log in, run the Crystal report and see exactly where they are with their spending… and not bother me for the info! :)

    Thanks and happy holidays all!

    Gordon

    Like this

    • Gordon,

      If you’re only looking to report on P&L accounts, this should be fairly straightforward. I would start with the following views/tables:

      • GL11110 – Open Year Summary Master
      • GL01201 – Budget Summary Master View (or GL00201 – Budget Master for multiyear budgets)

      -Victoria

      Like this

      • Victoria,

        That worked out really well, thanks. I am on my way.

        Basically, I have the report grouped by GL account and in the details are the actuals and budget amounts for each period. However, for some reason, if the actual for a given period is 0.00, but there is a budget amount, it will not display the record. This is problematic because it is not calculating the correct total for each account. I need it to display period balances that are 0.00.

        Any thoughts on why this is happening or more importantly, how to correct it?

        Thanks and regards,

        Gordon

        Like this

        • Gordon,

          You probably need to have the GL account master (GL00100) as your first table, so that you capture all the accounts, then link in GL11110 and GL01201.

          -Victoria

          Like this

          • OK, that table is in the mix. I allowed it to Auto-Link the tables since my linking is weak. In looking over the links it feels compelled to link all 4 tables (I added GL00105 for the ACTNUMST field) by each one of the ACTNUMBR_1, …2,… 3, and so on to ACTNUMBR_10.

            Is that overkill? Should I try to do the linking myself?

            Thanks,

            Gordon

            Like this

            • That is absolutely overkill. The only linking you need is on the ACTINDX which will be present in every one of those tables and then possibly on the period ID’s, depending on how you are coding that in your report. Use left outer joins on the table to be safe.

              -Victoria

              Like this

              • OK, that didn’t pick up the missing amounts.

                I am actually trying to get this done right with one company (as I know you have advocated in a previous email from a few months back), but not in a view but just with the database itself.

                So, now I am going to try to create the view (one company only) to see if that will get me to where I want to be.

                In setting it up, I don’t see that I even need GL00100 as I can get the ACTDESCR from GL11110, so I removed that table. When I run the query, the results are not what I want, so my linking must be wrong.

                Using GL11110 and GL01201, how can I get the results to give me the period balance and budget amount just once for each account for each period?

                Thanks.

                Gordon

                Like this

                • Gordon,

                  There are many different ways to do this. Here is one option:
                  select n.actnumst [Account],
                  a.actdescr [Name],
                  gl.periodid Period,
                  coalesce(gl.perdblnc,0) Actual,
                  coalesce(b.budgetamt,0) Budget
                  from gl00105 n
                  inner join gl00100 a
                  on n.actindx = a.actindx
                  left outer join gl11110 gl
                  on gl.actindx = n.actindx
                  and gl.year1 = 2009
                  left outer join gl01201 b
                  on b.actindx = n.actindx
                  and b.budgetid = '2009' --change to yours
                  and b.periodid = gl.periodid

                  -Victoria

                  Like this

                  • Gordon, actually, this might be better:

                    SELECT n.ACTNUMST Account,
                    d.ACTDESCR Name,
                    a.PERIODID Period,
                    SUM(CASE a.AmtType
                    WHEN 'Actual'
                    THEN Amt ELSE 0
                    END) Actual_Amt,
                    SUM(CASE a.AmtType
                    WHEN 'Budget'
                    THEN Amt ELSE 0
                    END) Budget_Amt
                    FROM
                    (SELECT ACTINDX, PERIODID,
                    BUDGETAMT Amt, AmtType = 'Budget'
                    FROM GL01201
                    WHERE BUDGETID = '2009' -- change
                    UNION ALL
                    SELECT ACTINDX, PERIODID,
                    PERDBLNC Amt, AmtType = 'Actual'
                    FROM GL11110
                    WHERE YEAR1 = 2009 -- change
                    ) a
                    LEFT OUTER JOIN GL00100 d
                    ON a.ACTINDX = d.ACTINDX
                    LEFT OUTER JOIN GL00105 n
                    ON a.ACTINDX = n.ACTINDX
                    GROUP BY n.ACTNUMST,
                    d.ACTDESCR, a.PERIODID

                    -Victoria

                    Like this

                    • Thanks Victoria, I will give this a try and let you know how I make out.

                      Happy New Year!

                      Gordon

                      Like this

                    • Victoria,

                      Preliminary results are in and so far things are looking good, really good.

                      I’ll keep you posted.

                      Thanks so much.

                      Regards,

                      Gordon

                      Like this

                    • Victoria,

                      I’ve successfully created this report for one company and have now made it so that it reports data from multiple companies.

                      However, with one company, it was easy to just use the Select Expert to give it a range of GL accounts (the Income Statement accounts), but with multiple companies, that range in the Select Expert won’t work for all.

                      Would I have to enter the account number range in the SQL view or could I somehow set up the Select Expert at the report level to give me only the Income Statement accounts?

                      Thanks and regards,

                      Gordon

                      Like this

                    • Gordon,

                      My 2 cents: if you’re always only going to want the Income Statement accounts (or P&L accounts as GP calls them), then do this in SQL – if you have a lot of data this will speed up processing. You could limit your selection on GL00100.PSTNGTYP = 1. On the other hand, if you think you might need to see all the accounts in a future version of this report, then bring in the GL00100.PSTNGTYP column so that you can use that in the record selection formula in Crystal.

                      -Victoria

                      Like this

                    • Victoria,

                      That did it! Thanks. I think I have everything I need on this report.

                      Thanks so much for your assistance.

                      Regards,

                      Gordon

                      Like this

    • Hello Victoria,

      Tons of Congrats for MVP.. I need your help in above listed Commonly Used Tables GL Tabels for GL Reports.
      WIll you please let me know about Tables series of DTA. what data has been stored in DTA tables? Whats stands for MDA or please let me know what is MDA Transactions?

      Like this

      • Sanjay,

        MDA stands for Multidimensional Analysis, a GP module that allows tracking of additional information for GL accounts. Similar to Analytical Accounting, but much simpler. If your company is not using MDA there is no reason for you to ever need the DTA tables for your reporting.

        -Victoria

        Like this

  79. How can i find out what tables the smartlist under financial->account transactions uses

    Like this

    • Larry,

      There is a manual called IG.pdf on disk 2 of the GP 9.0 installation that lists the tables in the out of the box SmartLists. For Account Transactions, here is the list:

      GL10001 – GL_TRX_LINE_WORK
      GL20000 – GL_YTD_TRX_OPEN
      GL30000 – GL_Account_TRX_HIST
      MC40200 – MC_Currency_SETP
      GL00100 – GL_Account_MSTR
      GL10000 – GL_TRX_HDR_WORK

      -Victoria

      Like this

    • Larry,

      There’s a View called ‘AccountTransactions’.
      You can check it.

      Erik

      Like this

  80. Hi Victoria,

    I have a client that is looking to find the table name used after an inventory roll and before Reval so he can create a Crystal Report.. Can you tell me the name of the table he can use?

    Thanks,
    Mellisa

    Like this

    • Hi Mellisa,

      Most likely there is more than one table involved. I am not quite sure what you mean by ‘inventory roll’, but in general I think your client may need to give you some more detailed specifications for the report they are looking to create for you to help them find the appropriate tables. Sorry not to gave a quick answer.

      -Victoria

      Like this

    • If you are referring to having manufacturing, then the table you probably want is ICIV0323.

      Like this

  81. Thanks for your summary of tables. It is very helpful. I was wondering if you can touch on adding custom tables to GP. In GP 9, there are only 4 user defined fields. Because GL accounts have multiple dimensions, it would be great if one could add an additional table for user defined fields.

    It would be even better if you could update the tables in the account maintenance window.

    Thanks for your thoughts,
    Joe

    Like this

    • Joe,

      Can you elaborate a little on what you’re looking to track in these additional dimensions? There are some tools available that might help, like Extender, MDA and AA, but whether any of them can be useful in your situation would depend on the specific need.

      -Victoria

      Like this

  82. I would like to have a query for recievables management to get the following information –

    Invoice number
    GL Post Date
    Counterparty Name
    Amount Due
    Cash Due Date
    Date Paid
    Amount Paid
    Description Field
    Days Past Due

    Can you provide me the query?

    Like this

  83. Hi Victoria,

    The table explanations for all of the modules are great…I reference it all the time.

    I was wondering if you could assist me or shed some light on an issue we have. Somehow, a PO was created and several of the items were marked as “Non-Inventory” even thought they are Inventory Items. The PO was received and posted. Now, our General Ledger has a total for this PO that does not match the Sub Ledger because the sub-ledger only included the Inventory Items from the PO. Any thoughts or suggestions on how to correct/proceed?

    Thanks so much.

    Brent

    Like this

    • Hi Brent,

      If the GL is correct and these items are still in your physical inventory, then I would adjust the inventory subledger by entering an inventory increase adjustment transaction and not posting it to the GL. Hope that helps.

      -Victoria

      Like this

  84. Victoria

    Thank you for the advice. I had a notion that it would use the General ledger. Having never used MGP before I find myself in the proverbial deep end. I have sent an email to the users to determine how they use MGP for their asset management.

    I am from the other side of the world in Cape Town South Africa. I am a business analyst doing data analyst work on an EDW project. The client is in Central Africa.

    Just to make my job a little more difficult, the DB I have is the structure only, no data…

    Johannes

    Like this

  85. Victoria

    I certainly gained some insight from your website. I have a very difficult task at hand. I have to do Source to Stage and Source to Model mapping from the MGP database. What makes it difficult is the fact that I do not have an MGP system to work with. I have to map straight from the tables and column headers.

    I virtually have to find out what tables are utilized for a specific report and then work out what field refers to a specific report measure.

    Therefor your refrences to the table names and where they fit in, is helping me. I am however stuck with assets. The database that I have, does not have the FA***** tables. What other tables can be used for assets?

    Regards
    Johannes

    Like this

    • Johannes,

      If the database you have does not have FA tables, the Fixed Assets module is not being used, so assets are being tracked some other way. You would need to talk to the users to find out how assets are being tracked and where. The two most common methods I have seen when not using the Fixed Assets module are: (1) setting up a General Ledger account for each asset or (2) tracking asset details outside of GP, typically in Excel, and only entering summary transactions into GP’s General Ledger.

      -Victoria

      Like this

  86. Victoria,

    Thanks again for this helpful information. I’m currently using SBF and I do not have a GL11110 or GL11111 tables. (We will be updating later this year!) Do you know of a way to link account descriptions to the GL tables? For example, I have tried many ways to link GL00100 to GL20000 or GL30000 with no success.

    I am attempting to pull the expenses broken out by category, instead of just CGS. Do you know of a way to do this? Thanks!

    - Will

    Like this

  87. Victoria,

    I just discovered your site today while writing some custom report queries for Dynamics GP. This information is very helpful.

    I have a background in database design, SQL, Java, Linux, sys-admin and Windows-Linux interop but I don’t know much about the internals of Dynamics. With recent developments here at work, I’m now forced to learn about Dynamics. So far, it’s been interesting and you have shortened the learning curve somewhat with the information you provide on your blog.

    Cheers,
    Joe

    Like this

  88. thankx

    Like this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,358 other followers

%d bloggers like this: