Company/System Tables


Commonly Used Tables – Company databases:
CO00101 – Document Attachment Master
CO00102 – Document Attachment Reference
SY00500 – Batch Master
SY01200 – Internet Addresses
SY01401 – User Defaults
SY02200 – Posting Journal Destinations
SY02300 – Posting Settings
SY03000 – Shipping Methods Master
SY03100 – Credit Card Master
SY03300 – Payment Terms Master
SY03900 – Record Notes Master
SY04200 – Comment Master
SY40100 – Period Setup
SY40101 – Period Header
TX00101 – Tax Schedule Header Master (header)
TX00102 – Tax Schedule Master (detail)
TX00201 – Tax Detail Master
TX30000 – Tax History

Commonly Used Tables – System (DYNAMICS) database:
ACTIVITY – User Activity
SY003001 – Account Definition Header
SY00302 – Account Definition Detail
SY00800 – Batch Activity
SY00801 – Resource Activity
SY01400 – Users Master
SY01402 – System User Defaults
SY01403 – User Tasks (Reminders)
SY01404 – Customer Reminders Setup
SY01500 – Company Master
SY01990 – Shortcut Bar Master (Navigation Pane Shortcuts)
SY60100 – User Access

Security Tables in GP 10.0 and higher – System (DYNAMICS) database:
SY09000 – Task master
SY09100 – Role master
SY09200 – Alternate or modified form and report ID master
SY10500 – Role assignment master
SY10550 – DEFAULTUSER task ID assignment master
SY10600 – Tasks assignments master
SY10700 – Operations assignments master
SY10750 – DEFAULTUSER task assignment
SY10800 – Alternate or modified form and report ID assignment master

Email Tables (in GP 2010 and higher)
SY04900 – Email Options
SY04901 – Email Messages
SY04902 – Email Series Setup
SY04903 – Email Series Documents
SY04904 – Email Card Setup
SY04905 – Email Card Documents
SY04910 – Email Details
SY04911 – Email Temp
SY04912 – Email Attachment Temp
SY04915 – Email History

Official list of BCHSTTUS (Batch Status) in SY00500:
0 – Available
1 – Marked to Post
2 – Available / Delete
3 – Marked / Receive
4 – Marked
5 – Marked / Print
6 – Marked / Update
7 – Posting Interrupted
8 – Journal Printing Interrupted
9 – Table Updates Interrupted
10 – Recurring Batch Error – Edit Required
11 – Single Use Error – Edit Required
15 – Computer Check Posting Error
110 – Checks Printing
130 – Remittance Processing

Francisco’s list of BCHSTTUS (Batch Status) in SY00500:
[This is a much more comprehensive list posted by Francisco Hillyer in a GP Newsgroup - provided courtesy of e-mail by Robert Cavill - thanks Robert and Francisco!] 
0 – Available
1 – Batch Posting
2 – Batch Being Deleted
3 – Batch Receiving Transactions
4 – Batch Done Posting
5 – Being Printed
6 – Being Updated
7 – Interrupted While Posting
8 – Interrupted While Printing
9 – Interrupted While Updating
10 – Recurring Batch Errors / Transactions Did Not Post
11 – Single Batch Error / Transactions Did Not Post
15 – Error While Posting Computer Checks
20 – Interrupted While Processing Computer Checks
25 – Interrupted While Printing Computer Checks Align
30 – Interrupted While Printing Computer Checks
35 – Interrupted While Printing Computer Checks Align Before Reprint Computer Checks
40 – Interrupted While Voiding Checks
45 – Interrupted While Reprinting Checks
50 – Interrupted While Processing Remittance Report
55 – Interrupted While Processing Remittance Report Align
60 – Interrupted While Printing Remittance Report
100 – Processing Computer Checks
105 – Check Align Being Printed Before Print Checks
110 – Printing Computer Checks
115 – Check Align Being Printed Before Reprint Checks
120 – Voiding Computer Checks
125 – Reprint Computer Checks
130 – Remittance Report
135 – Printing Remittance Align Form
140 – Printing Remittance Form

Series:
1 – All
2 – Financial
3 – Sales
4 – Purchasing
5 – Inventory
6 – Payroll
7 – Project
10 – 3rd Party

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

MODULE1 in Email Tables:
9 – Receivables Management
11 – Sales Order Processing
12 – Purchase Order Processing
19 – Payables Management

EmailDocumentID in Email Tables:
Module 9 – Receivables Management
1 – Invoice
3 – Debit Memo
4 – Finance Charge
5 – Service/Repair
6 – Warranty
7 – Credit Memo
8 – Return

Module 11 – Sales Order Processing
1 – Quote
2 – Order
3 – Invoice
4 – Return
6 – Fulfillment Order

Module 12 – Purchase Order Processing
1 – Purchase Order

Module 19 – Payables Management
6 – Remittance

EmailDocumentFormat in Email Tables:
1 – DOCX
2 – HTML
3 – PDF
4 – XPS


Last Updated: July 20, 2014

161 Responses to “Company/System Tables”

  1. Hi Victoria,

    Is there away to give someone read only status to specific cards in GP13? I would like someone to be able to view bank EFT Remit info but not allow editing.

    Like

    • Damian,

      Dynamics GP does not have any easy way to give someone read only access to a window where you can normally edit information. I typically recommend a report for this type of request – that will show all the information without any ability to edit it.

      -Victoria

      Like

  2. Victoria,
    We have a batch in Master Posting whose status is “Unequal Actual/Control Transactions”. This is an inventory batch. When I look at the Posting Setup for Inventory, no check boxes are checked for “verify number of tx” and “verify batch amounts”. What else might be causing this to occur? Thanks!

    Like

    • Hi Bill,

      Even if you don’t have the verify options checked on the posting setup, if you enter control totals on a batch and they do not equal the actuals, I believe you will see this happen. You can open the batch, clear out the control totals and then you should be able to post the batch.

      -Victoria

      Like

  3. Victoria, thanks for your prompt response. During the first attempt to print these checks, the user did not get a prompt to choose the printer; upon re-printing, GP goes through the process of assigning check numbers, but they do not print and again there is no prompt to choose a printer. I had the one user who ran the check batch exit GP and then cleared TempDB..Dex_Session; Temp.DB..Dex_Lock; SY00800, and SY00801, though this didn’t seem to help.

    Like

    • Hi Ana,

      I am not sure if this is something that we can troubleshoot in blog comments, I think we may need to look at it in your GP to be able to determine what is happening.

      -Victoria

      Like

  4. Hi Victoria, we have a check batch that is stuck with a 110 status; yet no checks have printed. We have tried to reprint; the system goes through the process and still no printed checks.
    1) We are unsure if the checks are printing to some other printer; how can we verify that?
    2) Confirmed the PM10300 contains about 126 checks
    3) Is it a matter of updating the SY00500 table and changing the batch status to 0?

    Any suggestions?

    Like

    • Hi Ana,

      You can certainly try changing the batch status to 0 in SY00500 to see if that would help. I would also try to get all users out of GP and clear the five ‘activity’ tables. You are on GP 2013, so somewhere in the check printing process it should ask you what printer to send the checks to – is it not getting that far? If so, it’s definitely not printing to another printer.

      -Victoria

      Like

  5. Victoria,
    Thanks for your help! I was hesitant to try and forcfully delete anything but now I’ll definitely defer to the experts and call our GP partner. Thanks again!

    Like

  6. Victoria,
    They’re check printing batches. I’ve yet to see if after the batch is deleted in the UI, it’s gone in the SY00500 table. The user went back into GP before I could look. So the process was to reset the batch, have user delete from UI, they exit GP, go back in and the batches are back. I’ll have her delete them again and see if they’re still in the table. If this is the case, what are your thoughts? Thanks!

    Like

    • Dave,

      Actually, check batches are a bit more ‘special’. If it got stuck in the middle of posting after the checks were printed, which is what I am guessing, you will not be able to delete it. If this is the case, I eould recommend working with your GP Partner or Dynamics GP Support to help you with it. While I have fixed a number of these types of issues in the past, there is no one fix that I can recommend, as this always involved examining a number of related tables to determine what was causing the posting problem.

      -Victoria

      Like

  7. Victoria,
    I have a strange issue where 2 batches will not delete. We have reset the batch status and marked to post status to zero in the sy00500 table and within the application they both appear to delete. Problem is, next time this user opens the app the batches reappear. I’m hesitant to just delete them from the table as I assume there is a reason for this behavior. Any ideas? Thanks!

    Like

    • Hi Dave,

      Are you saying that after you change the values in the SY00500 table you then go into the GP application and delete the batch? And then it is not in the list of batches in the UI or in the SY00500 table anymore? But then, later, when a user logs into GP it’s there again? If I misunderstood, can you please elaborate? Also, what kind of batch is it?

      -Victoria

      Like

  8. I have multiple companies in our current installation. Is there an easy way via SQL to find out which users have access to each different company and what roles they are assigned in each company?

    Like

  9. Hi Victoria,
    We are an export company so we do zero rated in our invoices, but now we already have local customer’s so it mean’s that VAT can be included in invoice. Is there need to setup or initialize in GPD so that when we generate invoice it will automatically compute the VAT if it is local costumer and zero rated if export?
    Any suggestions from you would highly appreciated…..Thanks.

    RolanVH

    Like

    • Hi Rolan,

      I believe you should be able to set up Dynamics GP to calculate taxes properly. Taxes in Dynamics GP are calculated based on 3 things: item setup, customer setup and shipping method. I would recommend reviewing the documentation and talking to your Dynamics GP partner if you need help on setting this up properly.

      -Victoria

      Like

  10. Hi Victoria,

    Might seem like a bit of a daft question, but can you advise how the Master_Type field of the SY01200 table is set when a customer record is created? We’ve got a client whose debtor records all have this field set to VEND rather than CUST for their corresponding records in the SY01200 table and not sure how to modify this behaviour from the GP interface.

    Can you please advise?

    Thanks.

    Lev

    Like

    • Lev,

      I do not know the precise logic of how the code sets these, however, it should be something as simple as, ‘if you’re on the customer record, insert CUST’. I have not yet seen these populated incorrectly by GP out-of-the-box, can you add a new customer record and internet information manually in the GP UI and duplicate this behavior?

      -Victoria

      Like

  11. Victoria,

    Have you seen this error before.
    I have setup Intercompany relationships in Great Plains 2010.
    I am receing the error “Destination account is required for distribution account:
    1000000-2001-000″ (account numer).

    The destination account is setup in the company that I am trying to enter an IC trx to, but I am still receing the message.

    Like

    • Hi Jeff,

      This is difficult to troubleshoot without looking at it. I would double check your Intercompany setup window to make sure everything is set up correctly for both the companies.

      -Victoria

      Like

  12. hi again victoria,

    hopefully this one is an easy one..though if it was i would have expected google to answer me! =).

    I can not seem to figure out how to make the settings we want for our customer email setup to be the default when creating a new customer record. I would like the quote and order documents to default to the correct message ID and .PDF format.

    I was able to go change our existing customers in bulk, but new customers dont have those options set, so the sales guys have to click on the email button on the customer card and set up the documents for each new customer. would really like to fix it so this happens by default. thanks!

    -jon

    Like

    • Hi Jon,

      I am not aware of any setting in GP that would automatically select the appropriate email documents when a new customer is created. I think the best you can hope for is for the Message ID and Format to be defaulted in when you enable each document. (Which is done on the Sales E-mail Setup window – GP | Tools | Setup | Sales | E-mail Settings.)

      -Victoria

      Like

  13. Hi Victoria,
    Many thanks for that, however it doesn’t quite do everything we rquire.
    The SQL returns values per user, but only for smatlists saved against that user id. What I am looking for is a list of all the smatlists that users have access to, by user.
    I realise this is a big ask, but any further help will be hugely appreciated.
    Jerry

    Like

    • Jerry,

      Unless I am misunderstanding what you’re asking, the results that you are looking for are a part of the results from my view. If you first create the view using the code on my blog, then you can simply run the following query:

      select * from view_Security_and_SL_Details
      where Resource_Type = 'SmartList Objects' 
      order by User_ID, Resource_Display_Name
      

      If you have multiple companies, you might also want to add Company_Name to the ‘order by’…or you can restrict to just one company by adding that to the where clause.

      -Victoria

      Like

  14. Hi,
    Is there a way of finding out which users on our system have access to which smartlists? I know it must in one or more of the tables, but it is like trying to find a needle in a haystack.
    Many thanks in advance.

    Like

  15. Hi Vitoria
    Many congrats for the MVP
    I want to modify the “Trial Balance Detail” report in report writer and bring the “Record Note” from the originating subsidiary module. Please help me how can I link the “Year-to-Date Transaction Open” on the report to “Record Notes Master”.

    Thank You

    Like

    • Because of the fact that there could be many different subsidiary modules and you would have to link to all of them, this is by no means an easy task. I do not believe you will be able to accomplish this in Report Writer without writing custom code. You might want to post your question on the GP Community Forum, as I do not work with Report Writer for anything this complicated and you might get some additional ideas there.

      -Victoria

      Like

  16. Hi Victoria,

    Thank you very much for an Amazing Blog, I use it on a daily basis to resolve table issues as you have set it out so easily

    I now however have a issue, more of annoyance really.

    One of my clients is unable to sort by other segments and I have researched this propperly

    Some obvious questions answered,

    1. When the system was installed there was provision made for sorting by account segments in the Account Framework
    2. I have run check links on the account Master file

    I also found the following,

    When sorting the account by Main segment it works perfectly in the screen (Account Summary) .

    1. When I sort by other segments it doesn’t work, BUT after doing a custom sort by on the different segments the other work perfectly

    So I am starting to suspect that when doing a custom sort by it pulls the data but when you just select the sort by it doesn’t?

    Do you have a suggestion why this would be happening or how I can start looking to fix it?

    Like

  17. Thanks for your reply Victoria. I know how to setup the intercompany transactions in GP. However there is a requirement that different account numbers be specified for different transactions. I am aware that when running through the setup for the intercompany transactions only one account number can be specified. Hope it makes more sense.

    Like

    • Nish,

      GP only allows for 2 different accounts – a ‘due to’ and a ‘due from’. If you need something beyond this, it will require either a customization or a 3rd party product. Or, alternately, if there are not too many of these, they can be entered manually without using the Intercompany functionality.

      -Victoria

      Like

  18. Hi Victoria,

    I need to setup intecompany transactions between the GL and the accounts payable module in Dynamics GP 2010. The process seems failry simple however the accounts will vary in the one company from one transaction to the other. Please advise on the how we should go about setting that up.

    Your help will be awesome
    Nish

    Like

    • Hi Nish,

      I am not sure what you’re asking. If you’re asking how to set up the Intercompany module, then I would recommend reading through the steps in the manual. If you have a specific question about one of the steps, please let me know. If you have complicated intercompany requirements, you may want to talk to your GP Partner about the setup so that you can explain what you need to them in detail and they can give you a recommendation for your specific situation.

      -Victoria

      Like

  19. Hi Victoria,

    I would like to get some of the posting information added to the cross-reference report in GP. Mainly the Approved by and Approval Date fields that are on the posting edit list. Is there an easy way to do this?

    Like

  20. Thank you for your effort in creating this list!

    Like

  21. Is there a way or can you point me to the tables that will provide a list of modified reports. I saw you have SY10800 but can’t link that back to a useable name for the report as this table only references an ID.

    Like

    • Hi Allen,

      I am not aware of a table that will simply store a list of modified reports. You may be able to cross reference SY10800 with table SY09400 to get the report names.

      -Victoria

      Like

  22. Hi Victoria,

    I have experienced a situation where a general ledger journal is captured manually with both Debits and Credits, but on printing the Transaction Edit List the debits and credits do not appear. I have run a reconcile and checklinks but to no avail. What could be the cause and the solution to this? We are on GP2010 running on SQL 2008 R2.

    Regards,
    Tumi

    Like

    • Tumi,

      Have you confirmed that this is not simply an issue with the report? If you’re using a modified report, try switching back to the default one. Also, try printing the report on a different computer and with a different GP user to make sure it’s not an issue specific to a particular computer or user.

      -Victoria

      Like

  23. Hi Victoria,

    I need an expert advice. We have 3 dental companies which was deployed to our local server. And now, we need those 3 companies to be deployed to the LIVE server.. We already have restored the 3 companies to LIVE server, and everything is doing good except for the currency we have seen.

    The currency ID that we are using is Philippine Peso (P) but in the transaction amount, the australian currency had appeared. We cannot change it into P. Upon checking the currency setup, Philippine Peso symbol is P. Please help us.. Thank you.. :)

    Like

    • Scha,

      All system-wide settings including users, security and multicurrency setup are stored in the DYNAMICS database. If you only copied over the company databases and not the DYNAMICS database, then none of the system-wide information came over to your live server. At this point, if you can redo the live deployment, you could restore the 3 company databases again, together with the DYNAMICS database. If it is too late because you have started entering transactions, then I would recommend working with your GP Partner or GP Support to straighten this out, as the fix could potentially be very complicated.

      -Victoria

      Like

  24. We have started using GP to send invoices as PDF to customers. In the process, some customer files have been updated to include the email address in the “To…” line of the E-mail address portion of the Internet Information page of the customer card.

    I am trying to find the table and field in GP where that “To…” field is located so I can query and show which are completed and which need to be updated. We have instances where the E-Mail field in Internet Information is filled, but the To… field is not, so we can not rely on that.

    Thank you

    Like

  25. So I am not sure if this posted or not. I am trying to track down where an operation is tied to a task. “Inventory Transfer Entry” window is the operation access that is linked but I have not been able to determine which task linked to the roll is giving access. We are in the middle of an audit and I need to remove this access from our Controller’s roll. Any assistance is greatly appreciated.

    Like

  26. Victoria,

    On GP 10 and have via audit found that we have an errant assignment of the window “Inventory Transfer Entry”. I need to track down where it is coming from. I have looked through many of the associated Role and Task ID’s but it only shows attachment to the users when printing the operation access report. I have looked into the SY009XXX tables and do not find the specific operation names. SY10700 doesn’t give names of the operations. Any pointers to what I am missing would be great.

    Like

  27. Hi victoria,

    I just want to ask what is the physical tablename of Payables Manual Payment Entry in Purchasing module? it is really frustrating to search for physical table names.

    Thanks so much.

    – Rhon

    Like

  28. I have a user who made a payment to one creditor,the Creditor Information was updated correctly, But the amounts are not showing in the GL the trial balance report shows only transations up to the 6th February as with the new info. there is nothing.Any help in this?

    Like

  29. Oops! There they are! Thanks

    In GP 2010, Microsoft Dynamics GP | Tools | Setup | Company | E-mail Settings is not showing on the menu and the email options are greyed out on Debtors and Creditors cards. Do you have any idea what might cause this?

    Thanks again,

    Richard

    Like

  30. Hi Victoria,

    Do you know of any reason why the email option tables might be missing from my DYNAMICS database? Tables SY04900 to SY04915 are not present when I check the DYNAMICS database so I don’t have email options in GP. It is version GP 2010 with sp2.

    Like

  31. Victoria,
    Wonderful site, thanks for all the info!
    I am upgrading from Version 8.0 to 10 (and then 2010). I get errors on Report Options (Purchasing, Inventory, RM) I have no problem recreating these if I can get them to upgrade. Also Distribution Account Master? Any thoughts greatly appreciated.
    Debbie

    Like

    • Debbie,

      Report Options might be ok to recreate, but I would be concerned about the Distribution Account Master. Did you perform database maintenance (specifically check links) prior to starting your upgrade? I find that usually helps avoid all sorts of issues like this.

      -Victoria

      Like

  32. Thank you for all your great answers. I have a batch in Financials that was created when I ran CheckLinks. The batch does not have any transactions or Batch ID. I try to select it to delete it but it tells me I have to make a selection. I have tried Reconcile but that comes up with no errors. I removed it with a SQL Query, then ran CheckLinks again and the batch came back. What can I do to make this batch go away and stay gone?

    Like

    • Hi Sue,

      It could be that there are detail lines in the GL10000 and GL10001 tables that were never posted and until they are cleared out this will keep coming back. Without looking at these in detail and checking them against posted transactions in the GL20000 and GL30000 tables it’s hard to say whether these should just be cleared out or if something else needs to be done to fix them. If this is not easy to find, it may be a situation where getting GP Support involved might help.

      -Victoria

      Like

  33. Victoria –

    Curious to know if there is a SQL that would return the total of a tax schedule ID (i.e. Tax Schedule ID = STATE SALES TAX + COUNTY TAX) on one line. I used one of your other SQLs that got me a listing of the tax schedules, but I have one line for my state tax and another for county. Would prefer they be one total.

    Thanks in advance!!!

    Like

    • Mark,

      I am not aware of any code that would do that for you. Part of the problem is that GP stored taxes at the tax detail ID level and you could have a tax detail ID that is part of more than one tax schedule ID, so this is not something that can be written generically. You would need to create some code that is specific to your data to show the tax this way.

      -Victoria

      Like

  34. Hi Victoria,

    I use your blog a lot so thanks for all the information! I’m receiving lots of errors when in our test company related to WennSoft (3rd party). I noticed in the SY02100 table (DYNAMICS) that I have extra dictid’s in one company that is working and they are not there for the one that isn’t. How does the SY02100 get populated and how would I add a dictionary id to this table? The companies are reading the same set file (and same client folder) and are in the same SQL instance, so I’m not sure how one has a dictionary id that another company doesn’t have. Wennsoft is installed in both companies and all the setups are the same. I also dropped all wennsoft tables and stored procs in test company and re-created and this remains an issue.

    11 836 10 0 TRI/dbo/ TRI dbo local 166
    11 836 10 22006 TRI/dbo/ TRI dbo local 167

    These are the 2 lines in the SY02100 table that are in the working company that aren’t in the test company.

    Not sure if you can answer, but any help would be greatly appreciated!

    Like

    • Hi Alishia,

      It sounds like maybe the test company did not get copied or created properly, but I have not worked with WennSoft, so I cannot offer more specific help on this. If you are getting WennSoft errors, first place I would check is with their support – they might have a quick and easy answer for you having seen this before.

      -Victoria

      Like

  35. Hi Victoria

    I set time aside this weekend to update some pricing in my system. When i went to run integration manager i found out that my partner lied to me and said I had the full version of integration manager instead of the 240 day version. Now I am stuck. Are the price levels just in IV00108? If they are can i just write an update script myself to uptade that table??

    Thanks
    Vic

    Like

    • Hi Vic,

      I have not updated prices directly in the tables myself, so I cannot tell you 100% it will definitely work. However, if all you are doing is updating existing records with new prices and you are using Standard Pricing in GP (as opposed to Extended Pricing), I don’t see why it would not work with a SQL script to update IV00108. I am sure you were already planning this, but I would make sure to have a good backup and try it on one or two records before updating everything. Good luck.

      -Victoria

      Like

      • Hi Victoria

        Thanks!! Through some major google searches and help from others like yourself in the great GP community, I pieced together a couple scripts that did the job. In fact I will be turning them into a repeatable stored procedure in the near future. One thing of note is that you also have to insert a record into IV00107 if it is a new record in addition to adding it to IV00108. Hopefully that info will help someone else out in the future. Also I used your excellent view view_Inventory_Price_Levels to help me accomplish this task!!!

        Thanks again for all your work!

        Vic

        Like

  36. Hi Victoria,

    I love your blog. It’s very informative :). I was wondering if it’s possible to void a posted cheque? We have an interco set up. The cheque on the AP side has been voided but we need to void the other company but that transaction has been posted and the period is closed. Is there anyway to void that portion? Thank you.

    Like

    • Hi Kevin,

      A posted/historical check can be voided under Transactions | Purchasing | Void Historical Transactions. The check itself cannot be an Intercompany transaction unless you have a customization or 3rd party product doing that, in which case, all bets are off. :-)

      When you void the check, that will cause the invoice(s) it was applied to to become open again. If the invoice was Intercompany and you void that (under Transactions | Purchasing | Void Open Transactions), then I believe the Intercompany portion of that invoice will need to be adjusted manually in the General Ledger of the other company. This may depend on your GP version and various other setup, so you would need to see what happens with the void and proceed accordingly.

      Hope that helps,
      -Victoria

      Like

  37. Hi Victoria….

    Not sure if this would be the right place for this ?…..

    GP v 9 – The GL Period Palance in Summary does not = the Period balance in Detail…the summary balance is correct. How do I get the detail to agree to the summary. I’ve determined the entry that is causing the difference…………Any suggestions

    Like

    • Colin,

      Have you tried running Reconcile and Check Links on the Financial series?

      -Victoria

      Like

      • Hi Victoria

        Thanks for your kind response……

        No I haven’t thought that there would be another option for the reason that I would need to take a backupup before I use this method…my backup process takes in the region of 4 hours. but am goint to try this now as I’ve restored by DEV server.

        Thanks
        Colin

        Like

        • Colin,

          That doesn’t sound right…how big is your company database? How are you doing the backup?

          -Victoria

          Like

          • Wow..thats fast…its 11.51 here in sunny SA.

            I’ve restored from SQL back up. If I take a backup on the fly thats how long it take
            my db size is app 260G

            Like

            • It is 6am in not yet sunny New Jersey. :-)

              Can’t say I have ever taken a backup of a db that big, but still 4 hrs sounds excessive. You’re doing a backup directly in SQL? What SQL version?

              In any case, if your summary and detail are out of sync, reconcile and check links are the first 2 steps to try to fix that. I would start with reconcile first.

              -Victoria

              Like

  38. Victoria,

    First, GP version 10.00.1061 (10 sp2 +)

    The question:
    Our users have recurring batches set up in payables module. Is there a way to have a recurring batch in the Sales module?

    Todd

    Like

    • Hi Todd,

      The Receivables module does have recurring batches, similar to Payables. However, I am guessing you mean SOP by ‘Sales’? If so, then no, there are no recurring batches there. You may be able to make use of the ability to create recurring quotes or orders, but that may be too limiting for what you need.

      -Victoria

      Like

  39. Hi!

    I would like to place a new registration keys on GP 9. When I do this the Access Rights I assigned to the users where removed. How do I make sure it would not disappear? Is there a way to backup the access rights of the GP users?

    Like

    • Mick,

      I have never seen new registration keys change GP completely remove user permissions. It sounds like something may have gone wrong in your case. I don’t know if there is a way that you can selectively back up permissions only, and easily restore them – it would depend on exactly what permissions got reset.

      -Victoria

      Like

      • It happened more than once. When I loaded the new access rights. I needed to mark and unmarked access rights again to the windows of GP.

        Like

        • Mick,

          This has not been my experience. However, since I do not know your environment or the specifics of your GP implementation, it’s really difficult to comment further. I would recommend talking to your GP Partner to see if they can help you determine why this is happening.

          -Victoria

          Like

  40. Is SY00500 the only table that contains batch data? I realize a transaction may have a batch number from SY00500 in the BACHNUMBR field, but that’s not what I’m referring to.

    I’m moving invoices around programmatically using eConnect, and this causes some of my batches to become empty, which is intended and expected. eConnect doesn’t appear to provide a method to delete batches, and I want to simply use a SQL query to accomplish this.

    Thanks in advance.

    Like

  41. Hello Victoria,

    When i am trying to close FINANCIAL YEAR IN MS Dynamics Great Plains GP 10. Ver with SP2.

    Analytical Accounting Module is installed….

    The Error:

    Microsoft ODBC SQL SERVER Driver SQL Server: ERROR CONVERTING DATA TYPE INT TO SMALLINT.

    How can i get rid of this Error.

    Like

  42. Hi Victoria,

    I recently upgraded GP 9 to service pack 2. I was able to upgrade my DYNAMICS database as well as 2 other company databases. I have a problem upgrading the third company database. When I ran update in GP Utilities, it stops in the “Load Additional Required Data” stage on table SY40100, then it gives me this error:

    “The stored Procedure SynchronizeTableData() of form duSqlAccountSynch: 27 Pass through sql returned the following results: DBMS: 102, Microsoft Dynamics: 0″

    I also noticed that there are SY40100_T and SY40100(V1) tables added in the database. Is it normal?

    The DEXSQL log says “exec duAN_SynchronizeTable ‘SY40100′,’SY40100_T’,’D’

    then it stops with this log:

    SQLSTATE:(01000) Native Err:(15472) stmt(16657232):*/
    [Microsoft][ODBC SQL Server Driver][SQL Server]The object ‘(null)’ does not have any indexes, or you do not have permissions.*/

    Any help will be highly appreciated.

    Thanks much ^_^

    PJ

    Like

  43. Hi Victoria!

    I would like to ask something about Sales Utilities.

    We used the Remove Trx History from Sales Utilities to remove old transactions in order to decrease the size of our company’s database. After using the said utility, we found out that the database size was not decreased even though we removed 4 years of transactions. Does the utility just transfered the data from the original tables to another table? If so, could we reverse the removal of the said transactions?

    Also, could you give us some advice on how to minimize the size of our database?

    Thanks in advance!

    Like

    • Hi Chris,

      The Remove Transaction History Utility does actually remove your data, it’s not moved elsewhere. There is no reversing this process, the only way to get it back is to restore from a backup.

      For the SQL Server database size – you may need some SQL processes to shrink your database, this is a better question for a SQL dba. I would recommend posting on a SQL forum or the GP customer forum for some advice on this, as it’s not my area of expertise.

      For the future, there is a product called Company Data Archive that can archive older GP data – this might be preferable to your method, depending on your specific needs and budget.

      -Victoria

      Like

  44. Hi Vitoria,
    I found this website as I am struggling in finding the resolution for my problem. There is no recent changes in GP, suddently we cannot post any recurring batches. It says Recurring Batch error – Edit Required.
    I check the SY00500 and found all the batches have MKDTOPST = 0 and BCHSTTUS = 0, there is no lock in SY00800 or SY00801 either.
    Would you please instruct me what I need to do?
    Thanks in advance.

    Like

    • Hi Phat,

      When a batch has an error, GP typically puts it into a ‘suspended’ status in Batch Recovery (Tools > Routines > Batch Recovery). You can select it there and click continue to get it out of the ‘suspended’ status. Next, go to where the batch is (for example, if it’s a GL batch, it would be under Transactions > Financial > Batches) and print the Batch Edit List. This will tell you what the problem with the batch is so you can correct it.

      -Victoria

      Like

      • Victoria,
        Thank you.

        The batches are clean now in the edit list. However, I don’t know if finance staff still have the same issue. If the issue persist, I will follow your advice.

        Like

  45. Hi Victoria,

    I would like to ask something about Financial Batches..

    One of our users reported that one of their IVTFR batch returned a “Table Updates Interrupted” status. But the user also stated that the affected batch posted fine when she tried to check the transactions.

    What caused this kind of behavior? Is it safe to delete the batch since it is posted already or are there some errors that we are unaware of that triggered the said status?

    Thank you very much!

    Like

    • Chris,

      This is not an official answer, as I do not have any kind of list that would tell me what specific conditions would return this error, but I have seen that message if there was a connectivity issue between the client and the server while posting or there was a problem detected in one of the tables that was being updated by the posting process. Without checking ALL the associated tables, it is really impossible to tell if there is still an issue at this time.

      If this is an isolated problem and the inventory and GL modules look like they got updated correctly, I would recommend running check links and reconcile (on both financial and inventory series). If you have a batch of transactions that are both in posted and unposted status and you’ve verified that the posted transactions are fine, you should be able to delete the duplicate unposted ones.

      If this is something that happens regularly, I would work with your GP partner and/or GP support to resolve it…otherwise you can be left with lingering data issues that may not be so obvious right away, but will cause problems later.

      -Victoria

      Like

  46. Hello victoria,
    i am developing an application for Dynamics GP and i created some additional tables under one of the companies. The problem that i am having is that im able to perform a select to any of the Dynamics GP tables but when i try to perform a select to one of my tables i get ” INVALID OBJECT NAME”
    i changed all the secutirty settings on my tables and its the same issue. im using the GPConnNET reference on connect to GP. i really hope that you can help me. thank you

    Like

  47. Hi Victoria,

    I know I can get company name from SY01500 table in the Dynamics DB….I want to link this to the bank account details for the company, sort Code, Acc number etc….which tables are these found in?

    Many Thanks in advance
    Nev Browitt

    Like

    • Hi Nev,

      The bank details will be in the SY04100 table (Bank Maintenance) in the GP company databases. If you’re looking for checkbook information, that is the CM00100 table (Checkbook Maintenance).

      -Victoria

      Like

  48. Hi all

    GP9

    I need to create a user account report from GP referencing all areas related to basic user setup for audit purposes.

    Where is the information shown under ‘Advanced SQL Server options’ stored?

    I don’t see any references in SY01400 other than possibly SECACCS but that does not appear to change when marking/unmarking the check boxes.

    Help always appreciated

    Regards

    Stu

    Like

  49. Hi Victoria,

    We are seeing that when we make a change to the GL account assignments at the item class level, it gives us the option to push these changes to all items in the class. However, we are seeing that it is not pushing changes to the Unrealized Purchase Price Variance down to the items in that class.

    Can you tell me what table I can query to view Inventory Item to GL Account assignments?

    Thanks,

    Jeff

    Like

  50. Hello Victoria,

    I am working on GP 7.0 I am trying to set new User Class for entry level only. Accordingly I have made changes thru Security Level but still its shows all pallets although i have keep unmarked. Please help me on this. Also please let me if there tables wherein we can make changes for User Class or access.

    Thanks

    Sanjay Parab.

    Like

    • Sanjay,

      Are you using Advanced Security? If so, can you try rolling the user class settings down to the user(s) to see if that will help? If not, I am not sure how much I can help, as the ‘old’ security is something I have not used in many years. One thing you can try after making all the changes to the user class is to create a new user and put them in that class – does the new user have the appropriate security?

      -Victoria

      Like

  51. Can you provide definitions for the SY02000 fields – DICTID, RESTYPE, RESID, ALIAS, ALTDICID?

    Example If you look at the SY00500 table (BCHSTTUS field – 0 is for Available, 8 is for Interrupted while Printing etc etc

    Many thanks

    Like

    • Colin,

      Maybe I have not had enough coffee yet, but I am not seeing the SY02000 table anywhere. Perhaps I do not have the module that installs that table on my test machine. However, by the name of the fields it sounds like you’re asking about a security table. If so, I am not aware of a listing available for the value of those fields, sorry.

      -Victoria

      Like

  52. Hi Victoria,

    Congratulations on your MVP status for another year.

    This error that we get is driving us mad.

    When we try to post a timesheet batch (timesheet batch entry window) – we get the following error “This Batch must be pre-processed first”

    Have printed edit list – no errors

    Batch is available.

    Any thoughts would be appreciated.

    Cheers

    Paul

    Like

    • Hi Paul,

      I have not come across this error before, nor can I find anything even remotely close to it in the GP KnowledgeBase. If you’re not able to get help on any of the GP newsgroups, I think you may need to talk to GP Support to resolve this.

      -Victoria

      Like

  53. Hi Victoria,

    In Transaction Dimension Code, is there a way to modify the accounting class link without affecting the unposted transactions?

    Thanks.

    Ernesto

    Like

    • Ernesto,

      Is this for Analytical Accounting? If so, I would recommend posting your question on one of the Dynamics GP newsgroups (links are on the right side of this page) with some more details of what you are trying to accomplish.

      -Victoria

      Like

  54. Dear Victoria,

    I was upgrading my GP from SP1 to SP4 and I have a total of 7 companies.
    After the upgrade.. 2 companies were successful and the others, when you login to GP utilities, have a small icon with a lock.
    When you try to select one of them it gives you “The company is being updated by another client”

    Is there a way to check the status of the companies from SQL because there seems that something went wrong during the upgrade and they became locked? or do you have any solution in mind ?

    thank you in advance,
    Barrak

    Like

  55. Hi all

    Is there a best practice for recovery of Smartlist Favourites should a user delete one other than user recreation of said favourite?

    If not, how would I best recover a deleted favourite if user recreation is not an option?

    Thanks as always for any assistance.

    Stu

    Like

    • Stu,

      The only other option would be to restore from a backup. That might be a lot more work than having the user recreate their SmartLists. You could also restore the backup to somewhere else and see if you can find the needed data and copy it directly into your tables. Unfortunately, I do not have all the tables needed for this and you may interfere with new favorites created in the meantime, so you may need to do some additional research and/or talk to Dynamics GP support.

      -Victoria

      Like

  56. Dear Victoria,

    I know this question might sound lame but I have a company for testing that is not showing in GP, although it has been in GP before now its not showing.
    I checked SY01500 and its not listed.
    My question is how can we put it back to show in GP and is it a manual procedure?

    Like

    • Barrak,

      If the company was showing up before but is no longer there, especially if it is not in the DYNAMICS..SY01500 table someone would have had to remove/delete it. Without knowing the details of what exactly was done, the safest suggestion I can think to get your test company back is to create a new company (I would use a different database ID, but you can use the same company name if you want). Then once the new company is created, if you want to get the data in your old test company into the new one, just restore from the old to the new test database in SQL.

      -Victoria

      Like

      • well thank you so much for the reply victoria.

        I deleted the testdb from sql and then recreated it in GP and then restored it just like you said.

        thank you again

        Like

  57. Hi Victoria,

    I am stuck with a stuck check batch in recovery , i checked the sy00500 table the batchstatus is set to 15, there is no entry in the dynamics..Sy00800 for that batch , we are using ver 6 ,
    the checks printed successfully
    vendor invoice shows open
    vendor pmt shows work
    gl did not update
    checkbook did not update , however in checkbook maintenance it shows the correct next available check #

    Any idea or input in this regard will be highly appreciated.

    Thanks,
    Vinay

    Like

    • Vinay,

      Check posting problems are typically very difficult to pinpoint remotely like this, however, I would recommend changing the batch status to 0 (and also error state to 0 if it is not) in the SY00500 table, then trying to open the batch in GP to see if you can tell what the problem is.

      -Victoria

      Like

      • Thank you victoria for your suggestion, I updated the batch status to 0 , the error state was already set to 0, had the user print the batch to check for any errors, there were no errors found, the user reprinted the checks with the same check numbers (over writing the ones that were used), and re processed it , the batch got processed fine without any issues.

        Thanks again for you help

        Vinay

        Like

  58. Hi Victoria,

    Could you please help me with this concern? I’d like to disable the pop-up for ‘credit limit override password’ on Sales Order document within SOP window. Do you know where I can go to disable it?

    Many Thanks & Warm Regards,
    Nikki

    Like

    • Nikki,

      I do not know of a way to completely disable that pop-up without a customization. The only work around I can think of is setting the credit limit to unlimited for every customer – that would ensure that you are never prompted to override the credit limit.

      -Victoria

      Like

  59. Hello Victoria,

    In which table I would get Period Start Date and Period End Date?

    Like

  60. Thanks Victoria Its really helpful for my report.

    Regards,

    Sanjay.

    Like

  61. Can you pls let me know what is Posting date, Posted date and Document date in Payable Module?

    Like

  62. Victoria,

    Sometime ago we lost our Fabrikam, Inc. and would like to reinstall it. Any idea as to how we would do this?

    Thanks!

    Like

    • Bill,

      It would really depend on what you mean by ‘lost’, as there might need to be some cleanup done, but I would start with going into GP Utilities and choosing the option to reinstall the sample company.

      -Victoria

      Like

      • The Fabrikam company was completely removed through the transistion to a new server as they figured it was not needed. I ran GP Utilities and installed a sample company and it created a “TWO” company but I could not access it from GP.

        Like

  63. Victoria,

    First of all thank you for putting all these useful info on here, It has helped in the past in number of ways in locating the data,

    Now to the question, what would be the best way to remove a batch,

    Like

  64. hai
    Yudin
    it is execellant composition of all tables at one place
    i am searching from the last 4 months
    Iam aFinance Controller of EPSILON at hyd,India,& we r implementing Nav 09
    thank u for u guidance
    get back to u if rerequired

    Like

  65. Victoria,
    We have a transaction that is stuck in the marked/print (5)status. Generally our stuck transactions have not yet been posted (mkdtopst 1 and bchsttus 0) and thus we are able to simply reset the transaction and the user can then repost. In this case the transaction shows both the sy00500 table and the dynamics sy00800 table, however the user is not currently logged into GP. I’m tempted to simply reset the transaction to batch status 0 and marked to post 0 but I’m concerned about the fact that it’s already posted. Any suggestions? Thanks!

    Like

    • Dave,

      The batch being in SY00800 simply means that there is a stuck process, not that it’s already posted. First thing I would try is logging in as that user to see if GP will recognize there is a process running and finish it. If that does not work, then you would need to delete the process out of the SY00800 table and reset the status in SY00500. To verify that the batch is not already posted (while that is unusual, I have seen it happen), I would typically check the work and posted tables for your transaction type. Hope that helps.

      -Victoria

      Like

      • Victoria,
        Thanks so much for the response. Having the user log back into that company and then run batch recovery worked! Who knew it would’ve been so simple :) Thanks again.

        Like

  66. Hi,Victoria,

    Thanks for your prompt response. I try to add the INTERID = ‘INSG3′ before “Select” but system saying that “Incorrect syntax near “=”. Would you please take a look the following statement and give me some input?

    Thanks a bunch!

    SELECT GL00105.ACTNUMST, GL00100.ACTDESCR, GL20000.JRNENTRY, GL20000.TRXDATE, GL20000.DEBITAMT, GL20000.CRDTAMNT, GL20000.DSCRIPTN, GL20000.SOURCDOC, GL20000.ORMSTRNM, GL20000.REFRENCE, SY01500.CMPNYNAM
    FROM INSG3.dbo.GL00100 GL00100, INSG3.dbo.GL00105 GL00105, INSG3.dbo.GL20000 GL20000, DYNAMICS.dbo.SY01500 SY01500
    WHERE GL00105.ACTINDX = GL20000.ACTINDX AND GL00105.ACTINDX = GL00100.ACTINDX AND ((GL00100.ACTNUMBR_1 Like ‘1100%’) AND (GL20000.TRXDATE<{ts '2009-05-01 00:00:00'}))

    Like

  67. Hi, Victoria,

    I am trying to show “Company name” on my query with GL 00100, GL00105, GL 20000 and SY01500 selected. However, it does not show up correctly. What kind of relationship should I set up beteween SY01500 and other tables?

    Thank you very much!

    Like

    • Lily,

      Since the SY01500 table is in the DYNAMICS database and the other tables are in your company database(s) you would need to do a link on something like:
      INTERID = 'COMP'
      replacing COMP with your company database ID.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. Releasing Stuck Batches and Transactions without exiting all Users - Developing for Dynamics GP - December 3, 2008

    [...] KB Article 852420 (see link below) provides more information on Batch Status values as well as Victoria Yudin's post on Company/System Tables. [...]

    Like

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,561 other followers

%d bloggers like this: