About Me


Microsoft Dynamics GP MVP

I have 20 years of experience in designing, implementing, integrating and customizing business management and accounting systems. In 2000 I started Flexible Solutions, Inc. to bring together my experience in accounting and business with my love of technology. Flexible Solutions is a Microsoft Dynamics GP Partner offering the GP Reports Viewer add-on for  Dynamics GP as well as GP customizations and support. I currently hold certifications from Microsoft for Dynamics GP (formerly Great Plains), FRx Financial Reporting and Small Business Financials. I have an undergraduate degree from the Wharton School of Business at the University of Pennsylvania and have the honor of being named a Microsoft Dynamics GP MVP each consecutive year starting with 2005. In 2010 I published a book called Microsoft Dynamics GP 2010 Implementation.

hi

376 Responses to “About Me”

  1. In the process of doing a reconciliation of the Accounts Receivable module to the General Ledger. This company has many transactions that are imported into Accounts Receivable. So trying to the GL to RM reconcile is not really an option.
    Seems that the Accounts Receivable module has been out of balance for some time.
    Would you know of a SQL script that could help identify these transactions that may not be in one or the other?

    Like

    • Hi luckyksg,

      Since transactions were imported, there is probably nothing ‘generic’ that would help. Someone would need to look at the actual data and write some scripts for you.

      -Victoria

      Like

  2. I have a user running GP 9.0 (yes I know it’s unsupported by MS but that’s what they use). Anyway, the user is creating batches and they select the option of one check per vendor but some of the vendors are being issued multiple checks (different invoices) in the batch. Why is it doing this for a couple vendors in the batch and not issuing just one check.

    Like

    • Allen,

      Is it possible that the invoices for the vendors with multiple checks were entered with different remit to addresses? I know in newer versions of GP, invoices with different remit to addresses will print on separate checks. I am almost positive this was also the case in GP 9.0, but I don’t have a 9.0 install I can easily check.

      -Victoria

      Like

      • Checked with the user and our version doesn’t have sites, each address is a whole new vendor ID.

        Like

        • Allen,

          The only other thing I can think of is that they added an additional payment for a vendor that was already in a check batch. No way to really prove it (or disprove it) without doing testing specifically on their system and trying to duplicate this behavior. I do not believe this is a ‘known issue’ and I cannot remember anyone even complaining about this before, even on older versions. Sorry not to be of more help.

          -Victoria

          Like

          • It’s with a certain type of vendor which we call a carrier vendor that has a different class id than a “regular” vendor. I sent an email to the user asking for some of the carrier vendor information for those printing multiple checks instead of one. I will be in touch with you tomorrow.

            Thank you and have a good evening!

            Like

          • Victoria:

            Here is some more information that I found out:

            1. They create 1 invoice batch
            2. They create 1 check batch
            3. They transfer the check batches to electronic checks only once.

            The same vendor who gets multiple checks instead of one will always get multiple checks in future batches. Is there a setting that we are missing to ensure only 1 check is issued to a vendor with multiple invoices within a batch? If you like, I can zip up the process document and send it to you and maybe that will help unless the further information I provided is enough.

            Thank you!

            Allen L. Wallach

            Like

            • Allen,

              I am not aware of any setting that will cause this. And there is no out-of-the-box setting I am aware of that will dictate whether a vendor gets one check or multiple. Is it possible they have a customization causing this? Have you actually watched them do this to see if there is anything else going on?

              -Victoria

              Like

              • Victoria:

                How can I tell at what time a batch was posted in dynamics GP 9.0? I have an emergency request and cannot seem to find it.

                Thank you!
                Allen L. Wallach

                Like

                • Allen,

                  Typically you cannot tell the time a batch was posted in GP 9.0. Maybe the date, but not the time. In newer versions of GP there is a time stamp stored when most transactions are created, but I have never actually tested to see if those can be accurately used to track the posting time.

                  -Victoria

                  Like

  3. Victoria – I have found your SQL views to be most helpful thank you so much for the work you do. I have a question about the POP Invoice view.

    I need to get down to the level where I can get the GL Account that was used to post the original POP receipt, not the GL Account that was used to post the invoice.

    The GL Account used on the POP Receipt contains the useful GL information I need. The GL Accounts involved with Invoice posting are only dealing with Accts Payable Accruals and I need the Expense / Inventory account

    How would I do that?

    Thanks

    Like

    • Hi Devin,

      If you have the POP receipt number, you can get the GL distributions for it on the POP30390 table. If you need to get the POP receipt from the POP invoice, you can use the POP10600 table. Hope that helps.

      -Victoria

      Like

      • Thank you Victoria, I figured it out. This is actually an interesting problem. I have a client who wants to re-class all the Discounts Taken from the default Discounts account back to the original GL Account that was charged on the AP Transaction or Purchase Invoice. No discounts Available are tracked, and all discounts are taken at Check time, so there is no way to easily intervene and re-class. It was quite an adventure and I actually have a working view that effectively calculates the re-class amount at the line level and returns all the accounts for which the re-class should go. After I clean it all up I would be happy to share it with you if you want.

        Thanks

        Like

        • Hi Devin,

          Glad you figured it out! If you want to post the view here, that would be great. Or you could start a blog and that will be your first post! :-)

          -Victoria

          Like

          • Victoria,

            I hope you don’t mind me emailing directly, but I am perplexed on something with SmartList on Great Plains 2013

            I know some things about the product changed, and the advanced SmartList Builder is not available any more, it has been returned to the vendor eOne

            I have a fresh install of GP 2013 with SmartList, and I am having trouble with SQL view based SmartLists. Regular users who have SmartList access can see the SmartList but when they click it, it returns 0 records with no error. If I grant that user higher privileges in the SQL database, data is returned correctly.

            I have granted Select to DYNGRP for the view, and I realize I am missing something, but everything I look up refers to SmartList Builder, which had menus that include Setup for SQL View Permissions, and I am lost as to what step I am missing to make this work.

            Sent from Surface Pro

            Like

  4. Victoria,
    I have implemented some of your scripts and love them. Thanks. Do you have a suggestion on how to track inventory ageing? I found a view here that is very close but it only compares to the current date not how long the item spent in our inventory: http://msdynamicstips.com/2009/05/02/291/

    Like

  5. Hi Victoria! Great site! I have a problem that seems pretty basic, but our Dynamics provider has no answer to. I created and posted a recurring General Ledger transaction. It posted in Feb and Mar, but I deleted the batch before any other months could be posted. Now I need to back out those posted entries and replace them with more accurate figures. I have the journal entry ID, but when I try to correct it, the ID does not exist in the lookup. I find it hard to believe you cannot back out recurring entries – there’s no error message saying “You cannot back out recurring entries”! Help, please!

    Like

    • hi Andrew,

      I just tried this in my GP 2013 SP 2 and it let me back out a recurring transaction. However, instead of looking it up, I typed in the entry number, then it prompted me with a message saying that it is a recurring transaction and I needed to select which one to back out from a lookup it gave me. However, I still have the recurring transaction in the system, I didn’t delete it, not sure if that would make a difference. Can you try typing in the entry number to see if that works instead of trying to look it up?

      -Victoria

      Like

      • Thanks for the quick reply, Victoria. I am on GP 2010 – sorry, I should have mentioned. I did try typing the batch ID first, then I get the same message you got, returning me back to the lookup, wherein it cannot be found. ARGH.

        Like

        • Andrew,

          The only thing I can think of is that because you deleted the transaction, it’s not able to find it properly anymore to be able to reverse it. So it looks like you will have to manually enter the reversal that you want to do. :-( Hopefully it’s not a huge amount of lines (or that you have a way to import it, if it is).

          -Victoria

          Like

  6. Hi Lou,

    Great! You can email me at jed@erpss.com.

    Thanks.

    Jim

    Like

  7. Jim

    I am very interested in discussing this further – let me know how I can contact you. And thank you Victoria for facilitating!

    Lou

    Like

  8. Hi Victoria,

    Just wondering, is there a setup option in GP 2010 that allows you to stop users from over receiving a Purchase Order? Eg, say you order 10 of item ABC, when you receive this, you can type in you received 20 and the system accepts this and allows you to post it etc. I want to stop users from doing this as people keep typing things in here far to often, then it just wastes our time having to create a return to vendor etc to resolve the issue.

    Like

    • Matt,

      There is a new feature in GP 2013 that will allow you to enter an overage tolerance for each item to limit what can be received beyond what was ordered. However, I do not believe this is possible in GP 2010 out-of-the-box.

      -Victoria

      Like

  9. Victoria,

    I came across your blog while in search for a solution to my Great Plains issue – perhaps you can help? I am currently running GP 7.0 g12 – I would like to upgrade to the latest version of Microsoft Dynamics, however, I have been told that there is no data migration path to bring the historical data from GP 7.0 to the most current version. Here is the caveat: support lapsed with Microsoft some years ago – so we only have the original installation software for version 7.0. Microsoft has given us a price for bringing our support to current which would entitle us to the reset version of Dynamics, however, there seems to be an issue with migrating the data over. The best they can do (according to them) is bring over balances for each account for each month. Do yo low if data migration is possible for our situation?

    Thanks

    -Lou

    Like

    • Lou,

      This may be possible, but will depend on a number of things. For example, is your current GP 7 on MS SQL Server or another database? If it’s on MS SQL Server, that would possibly make it easier. Otherwise, the data has to be converted and the conversion utility may be very difficult to find. Since neither you nor MS has the media for older versions available, you will most likely need to find a GP partner to work with on this that has all the software versions that you need. Also, how much data do you have? That may make a big difference, as you’re talking about a large number of steps for the upgrade and all the data would have to be upgraded at each step.

      -Victoria

      Like

    • Lou,

      I may be able to help you on this. With regards to the old media, I’m an MSDN subscriber. My subscription includes all the Dynamics apps. I may still have the media you need for GP 7.0. In addition, I work with Scribe, an integration tool that has adapters for GP. In short, Scribe knows the table layouts and required data elements for each table that is being hit. It’s possible we could set up a clean company database and use Scribe to push your historical data in.

      Let me know if you’re interested in discussing this further.

      Thanks.

      Jim

      PS Thanks to Victoria for letting me share this here!

      Like

  10. Hi Victoria.

    Do you have a sql statement that will output the price of each item that incorporates the pricing options of rounding… etc.

    I would like to create a view that we can use to ftp item selling prices to our e-commerce site.

    We are using standard pricing.

    Best Regards,

    Brian

    Like

  11. Victoria, I am hoping you can help me I am looking for the stored procedure that populates the UPR_Reprint_Check_Temp table when a payroll check is recreated in the inquiry screen.

    Thanks for your help,

    Bryan

    Like

  12. Hi Victoria,
    I am trying to find out if there is a way to put text on a check each time it prints for a certain vendor. The attorney general requires us to put info on the check memo line and currently we are handwriting the info.

    Like

    • Linda,

      There should be a way to do this, but you would need to modify the check layout using Report Writer. (If you are using GP out-of-the-box reports to print checks, that is.) If you’re not familiar with this, your GP Partner can help you. If the text is 30 characters or less, I would recommend using something like the Comment 1 or Comment 2 field on the Vendor Maintenance window, which can easily be added to the check. If the text is more than 80 characters, that can get a bit messy and you might want to look into alternatives for printing checks out of GP, like Mekorma. Talk to your GP Partner and see what they recommend.

      -Victoria

      Like

  13. Hi Victoria,

    Really good blog, has helped me a lot!!! However, I am stuck on one bit and was hoping you could help…When I run depreciation on GP the process produces the correct entries, however, it also produces a 2nd batch with no transactions in it and this goes to batch recovery!!! How can I stop the 2nd batch from being produced? is there something within the setup that I should be looking at?

    Thanks and sorry if this is not in the correct place!!!

    David

    Like

    • Hi David,

      Sorry, I have not seen this before. What is in the 2nd batch that goes to Batch Recovery?

      -Victoria

      Like

      • I’m guessing this issue has already been resolved but thought I’d post in case it could help someone in the future. We recently encountered this when we had a new person start posting receiving of goods and were getting the journals for the receipts being put into batch recovery. This was due to the fact that we have account level security setup and the new person was not assigned at any security level, once we gave him access to the accounts that got hit during receiving posting (inventory, a/r) this issue went away.

        Like

  14. Victoria,

    First let me say that I am new to GP. I need to write SQL query that will provide a listing of all outstanding invoices with the invoice date, and original invoice amount for each customer. I also need to have a column in the report that shows the Oustanding amount due for each invoice. I am not sure if GP tracks the outstanding amount by invoice or not. Could you point me in the right direction?

    Like

  15. Hello Victoria,

    Smartlist Receivables Transactions GP 2010 11.0

    Sorry if it is in the worng place but the lower boxes dod not work on the RM screen.

    I am trying to exclude or highlight invoices in query/dispute. Is there a simple way to do it.

    I was using a Receivables Transactions smartlist but this does not seem to flag disputed transactions.

    Thanks.

    Like

    • Martin,

      I don’t think there is anything out of the box called ‘disputed’ that you can flag on receivables transactions. So the answer to your question may depend on how specifically you are designating something as disputed.

      -Victoria

      Like

      • That’s what I found on searching the smartlists. Looks like it will have to be an sql query.

        Thanks for the help.

        Martin.

        Like

  16. Hi Victoria,

    Is the date created stored anywhere for AP Batches?

    Thank you!
    Karen

    Like

  17. Victoria,

    Is there a way to change a smartlist so that the credit amount column is reflected as negative numbers? Would I have to use SL Builder to accomplish this?

    Thank you,

    Henry

    Like

  18. Great Blog Victoria! It has been a huge help!

    It appears “safe” to modify Segment Descriptions and G/L Account Descriptions through SQL Code. Is it safe to Delete unused Segments and G/L Accounts the same way? We have 30,000 G/L Accounts setup, and 15,000 have never had activity (I used your ‘All GL Transactions’ SQL query to confirm).

    Like

    • Hi Stephen,

      Thanks!

      Modifying segment descriptions and account descriptions in SQL is something I have done many times with no issues. Deleting may be a little more difficult, so I would recommend doing some testing first. Hopefully you have a good test environment where you can test with a recent copy of your live data. Delete a few segments and/or accounts and run check links on the financial series to make sure nothing gets broken.

      -Victoria

      Like

  19. Victoria. We have 5 locations, with accounts payable paid at the ‘main’ location. Therefore, there is only one a/p balance on the G/L. I have been asked to put together an ‘open’ a/p listing by the location where the payable originated and the purchase/expense was charged. Help would be appreciated! Thanks

    Like

    • Hi Nick,

      This should be doable, but is something that would be custom for you…not something I can generalize and post for everyone to use. If you’re interested in having this created for you as a consulting project, let me know and I will email you with my contact info.

      -Victoria

      Like

  20. Victoria, I hope you can guide me. I am trying to find all the check numbers related to payables for a particular GL account, in SQL. What tables do I need to join and how?

    Like

  21. Hi Victoria,

    I stumbled upon your this blog from Google.

    First, I am very new to MS Dynamics GP 2010. Since I am given a task to write reports (mainly Crystal Reports & SSRS) outside of GP report smartlist builder, I found your previous posts on SOP & POP tables are very informative. I Thank you for the information.

    Secondly, I do have a simple, stupid question: Which table(s)/field am I able to find the FOB Cost for my Open PO report.

    I appreciated any help from you or other viewers.

    Thank You.

    Best Regards,

    Ken

    Like

  22. hi victoria :) i have a few cash receipts that have been posted to the gl. they are perfectly visible and trackable in the receivables management but when i watch them in the journal enquiry and i try to see the source document, it says “unable to find originating document”. i can see the transactions in the gl table and also in the rm-cb link (CB100011) everything is still fine. but they must be missing in some relevant cashbook tables. do you have an idea where? and most of all, how could that happen? i very much appreciate every hint :)

    Like

    • Hi Susanne,

      I have not worked with the cashbook tables, but I am not sure that’s where the issue is. If you’re drilling down from the GL Journal Entry to the Cash Receipt, I believe it needs to use the information in the GL tables to link back to the RM tables. Assuming these are in the open GL year, what is in the GL20000 table for these in the SERIES, ORTRXTYP and ORCTRNUM columns?

      -Victoria

      Like

      • hi Victoria, thanks for your quick answer. i checked in GL20000 and find those values:
        SERIES = 3
        ORTRXTYP = 9
        ORCTRNUM = ‘PYMNT000000159988′
        what do you think?

        Like

        • Susanne,

          Based on that, I am not seeing why you should be getting an error drilling down on this…is this happening for all Cash Receipts or just some?

          -Victoria

          Like

          • only on some unfortunately… we assume that somehwere along the line, something has been deleted manually out of the batch as it only posted to and not through but now we have to reconstruct it… it is in the gl table and in the rm table (and in its link as i mentioned before) but not in the cashbook… theoretically impossible :(((

            Like

  23. Good morning Victoria.

    I am new to GP and I’m trying to assemble a few queries. I am having trouble finding all the pieces to fit together. I need a list of all invoices(AP) which include vendor, invoice fields(such as entry date, amt, etc) as well as the expense and AP GL accounts information. I have been looking at RM30101 and RM20101 as well as RM00401, RM10101 but I still cannot find all this information. Any suggestions?

    Thanks so much,

    Brian

    Like

  24. Hi Victoria,

    Do you have any suggestions for the archiving and backing up of data? Are there general practices that are used. We have several GP databases with the largest being well over 200 GB’s and the data being over a few years old.

    Thanks
    Ryan

    Like

  25. Victoria,

    Do you know of any 3rd parties out there that will flag an AP Check Batch to instead of printing the checks to create a flat file and upload to FTP site for a check printing vendor to actually print the checks and mail them?

    Thanks for your input. Love your site.

    Robyn

    Like

  26. Hi Victoria,

    I came across your blog. I am new to GP and I would like to get an advise on how to import historical gl data from one company to another. Based on my observations, I should import the records to GL30000 and update GL10111 (or Run Financial Reconcile for this?). Do I need to update other tables? Is it possible to reopen closed years then import records to GL20000 and update GL10110 (or Run Financial Reconcile) and then close the years? Thank you in advance for your help.

    Like

    • Natalia,

      I would not recommend importing transactions directly into GP tables. Even with 20+ years of experience and extensive knowledge of GP tables and transaction flows, I would avoid this if I could. Use an import tool like Integration Manager for this.

      -Victoria

      Like

      • hi Victoria,

        I don’t believe you can import historical data through integration manager.

        -Vince

        Like

        • Vince,

          You are correct. The typical process is to import ‘open’ transactions and post them, then ‘close’ them, which would differ depending on the module we’re talking about.

          -Victoria

          Like

          • Thank you all for your replies.

            Like

          • Victoria,

            I have a similar question on the other module. We are trying to import open POs from one GP system to another. What is the normal process to import the partially received/invoiced ones? If we import the balanced amount, The POs in the new system will not match the documents sent to the vendors.

            – Vince

            Like

            • Hi Vince,

              That is a good question and there is really no ‘right’ answer. It all depends on what your goal is. My 2 cents is to only do what is absolutely necessary, because otherwise you are wasting time and money. I think the two options below are the 2 extremes and you may actually need something inbetween, but just to give you some ideas:

              1. If your old system is still available, you could leave all the open PO’s there. Enable receipts without PO’s in GP, enter the receipts only into GP. This is probably only plausible if you do not have PO’s open for very long and if you get invoiced per inventory receipt (as opposed to the entire PO – see # 2 below). In situations where you have a large number of open POs that will likely stay open for several months or more, this may not be a very good option.
              2. If you have a lot of partial PO’s open and you expect to receive invoices for what has already been received together with what is still open, then it may be best to import the entire PO, then enter or import the partial receipts that have already occurred but have not been invoices yet. This way, when it is time to match the invoice, you will have all the items received in GP. However, this may change what you’re planning for inventory imports, as these receipts for items that have already been received will need to be backed out of your inventory, otherwise you will have doubled up quantities.

              Hope that helps,
              -Victoria

              Like

              • Hi Victoria,

                Thanks for the information. We decided to bring in the balance quantity only for the open POs. Once thing we noticed during testing, taxes are not handled by Integration Manager/e-connect/web services. We have to a macro to update the tax after the import.

                – Vince

                Like

  27. Hi Victoria, i am preparing for upgrade from gp10 sp5 to gp2010 sp3, and i have a doubt with sql collation, because in my actual environment production (gp10) the database collation is Modern_Spanish_CI_AS and for gp2010 the Supported Microsoft SQL Server Collation is SQL_Latin1_General_CP1_CI_AS, thus my question is: how this diference between collation can affect the upgrade process? in case to affect me: know you a guide that i can use to deal with this problem?

    Like

  28. Hi Victoria. This blog seems like a great resource. Thanks for sharing so much information. I do have a question for you concerning GP 2010 and FRx 6.7.

    We have multiple company databases in GP 2010. We use FRx 6.7 as well. In the CV company in GP 2010 a new 2013 calendar year was created by the appropriate user and now the she wants this calendar opened up in FRx. None of us are GP or FRx pros at all so we don’t know how to do this. Can you help?

    Like

    • Hi Jon,

      Is the issue that FRx is not ‘recognizing’ that 2013 is a valid year in the CV company? If so, you should be able to fix that by switching to CV as the default company in FRx (under Company | Select Default). Hope that helps.

      -Victoria

      Like

  29. thank-you for your reply, I’ll try those places for further help and try to dig through them.

    Like

  30. Hi Victoria,

    for my FRx issue, I tried your suggestion of selecting each company as a default but I still can’t select the 2013 year when generating the reports. I even tried one partnersource article recreating the fiscal periods.

    any other suggestions?
    thank-you
    Raymond

    Like

    • Sorry Raymond, any time I have seen this issue switching to the company in FRx fixed it. You might want to try the GP Community forum or contacting Dynamics GP support for more help on this.

      -Victoria

      Like

  31. Hi Victoria,
    Working on FRx 6.7 here with a client and 2013 is setup in GP but in FRX I can’t select it when generating the report. I tried deleting the *.g32 files already without success,

    any ideas?
    Raymond

    Like

  32. hi victoria,

    im not sure if this is where i should ask, but i have a dynamics sql question. seems like this should be pretty simple but i cant get it to work

    I have data in a column of a table in our test company. i want to update that data into the same column in our production company. i tried to write a simple sql statement to do it but the one database always seems inaccessible from the other.

    all i need to do is update the emailtoaddress field in production from the emailtoaddress field in test…from table sy01200 but i cant seem to get it to work. can you help? please. =)

    thanks,

    -jon

    Like

    • Jon,

      Without a lot more detail, and possibly looking at it, it is difficult to help you diagnose the issue of connecting from the one db to the other. However, one quick workaround would be to export the data you need from the one db and import it into a new table in the other db. Then do your updates all in one db.

      -Victoria

      Like

      • thanks very much victoria! sorry for posting this in the “about me” section of your site…i had two tabs open on your site and posted in the wrong one ><. that seems like a simple enough solution =)

        Like

  33. Being completely new with GP and I mean a Total Newbie here! Your blog has been -along with Mark Polino’s tables & fields spreadsheets- the most useful tool I could ever imagine. Thank you for putting up such straight and to-the-point relevant information and queries! :) You have had tremendous impact on my life (well, professional that is!)
    Regards.

    Like

  34. Hi Victoria,
    I seem to find myself on your site every day for one reason or another. Thank you for the valuable resources you provide. I was doing some multi-currency research and saw a post where you mentioned the Interdyn GL Multicurrency Consolidations product. Do you have first hand knowledge of this product? Does it work as advertised? Any limitations or gotchas? Thanks in advance.

    Like

    • Hi Sherry,

      I have a customer using GL Muticurrency Consolidations for a few years now and it works as advertised. I am not aware of any limitations or gotchas, at least that my customer’s run into…hard to say if their needs are identical to yours. If you have a lot of prior period changes, it might get a bit tedious, as you need to void and rerun the process for each month. But it’s really not such a big deal to do, just something to keep in mind and might warrant a slight change in your accounting process to more closely control ‘closing’ each period. Hope that helps.

      -Victoria

      Like

  35. Hi Victoria

    I was wondering if you had any information or can suggest anything i could use to help me pass the GP Financials 2010 exam. The areas i lack knowledge in are Sales, Fixed Assets and Bank Rec. (our main client uses GP in the weirdest way so i haven’t had any exposure to these areas) I would be really grateful if you could point me in the right direction :)

    Laura

    Like

    • Hi Laura,

      I have always just taken that test without any studying or reference materials, so I am not sure what to recommend. I would probably suggest starting by taking the online training for the modules you feel you do not know well enough.

      -Victoria

      Like

  36. Hi Victoria,
    Thanks for all the great resources and tips on your site. I was wondering if you knew of any good training classes specifically for writing reports in SSRS. From what I have found so far it seems that most people offer courses that are a broad overview which touch on creating reports, but don’t get into the nitty gritty. Any ideas? Thanks

    Like

    • Hi Mark,

      That is a great question and not the first time I have been asked. Everything I know about SSRS has been picked out from our developers, found on the internet or self-taught by experimentation. :-) I have asked around since your question came in, but have not gotten back any good recommendations yet. I will keep looking and let you know if I come across anything.

      -Victoria

      Like

  37. Victoria:
    Do you know where to find additional information [other than the Fact Sheet] for the MS Dynamics GP Light User [for GP2010] . All I have been able to find is the fact sheet. We are trying to determine if and how a light user can be used to allow new customer or new address records for an existing customer and to allow maintenance of existing customers.
    One consulting group told us that it would not be possible but from what little info I’ve read it seems like it should be doable.
    What do you think?
    Thank you

    Like

    • Ellen,

      My understanding is that for GP 2010, light users are only users that are able to access the SQL data in the GP databases, outside of the GP application (for example with an Excel or SSRS report). If you need a user to be able to go into the GP application to add or change records, then you would need a full GP user license. If I am not understanding your question, please reply back with more detail of what you are looking to do.

      As a Microsoft customer you could also always call Microsoft and ask them this yourself – that way you are getting the answers directly from the source.

      -Victoria

      Like

  38. Hello. I’ve been trying to find a download link to an FRx 6.7 install file. Tried the Microsoft Dynamics site and other avenues to no avail. Would you happen to have that here…on this site. Any help would be greatly appreciated. Thanks, Antonio

    Like

  39. Hi Victoria,
    Can you recommend any good training courses for GP addin development in .Net?
    Thank you.

    Like

  40. Hi Victoria, please a personal advice, i’m going to be in an implementation of dynamics AX, i’ve been working in GP for about 4 years, could you recommend me a dynamics ax blog please?, it would be awesome to find one like yours, with such detailed resources and reports. Thanks a lot!
    FAC

    Like

    • Hi FAC,

      Thank you very much for the kind words. Unfortunately, I do not have any advice for you on this, as I only work with Dynamics GP. Hopefully you can visit some of the AX forums and get some advice or leads there. Good luck on your project!

      -Victoria

      Like

    • Hi Victoria,
      We are converting our Lawson accounting package to GP. I have quite a few reports, views, and data extracts that reference the Lawson data. Until we get more familiar with the reporting available in GP, I need to convert these reports to the GP data rather than the Lawson data. Needless to say, there are a lot of tables in GP and our partner hasn’t yet converted all the data to GP. Have you worked on such a conversion in your past? Any help is greatly appreciated!

      Thanks.

      Bill

      Like

      • Sorry Bill,

        I have not worked with Lawson or a conversion from it. Hopefully some of the guides on my blog will help with what you need to do. Write back if you have any specific questions.

        -Victoria

        Like

  41. Victoria,
    Love your web site, tons of useful stuff on here. I have a question and can’t really find this on the web. Do you know of any company listed on the NYSE that is using GP2010? Doing a research for my CFO and he was curious about this

    Like

  42. Hi Victoria,
    I was wondering if there is a way to get the batch edit list errors using a sql query?

    Like

    • GPUser,

      I do not believe there is any easy way to do this, as the errors are generated when the report runs – they are not sitting somewhere in a table that you can just display. By tracing what happens when you run the report in GP it may be possible to determine what stored procedure(s) to run to simulate the report, but it may be more complicated than that, I am just not sure.

      -Victoria

      Like

      • Hi Victoria,
        Thank you for your reply. When you say running the trace – do you mean SQL server profiler or the GP Dex log?
        Yeah I realized that the errors may not be sitting in one table but there must be a schema/method behind it.
        If I could get a hold of the error reporting method then thats it!
        But anyway, I’ll give it a try with the SQL profiler first. Hopefully that will capture the quries that are being run when the report is displayed.
        Once again, thank you.

        Like

        • Part of the problem is that there is no way to know what kind of trace is enough, it might not be just SQL code running to generate the batch edit list. It also may be different for different transaction types. If this is important, you may want to consider working with GP Support to get some definitive answers.

          -Victoria

          Like

          • Hi,

            The following statement will give you the warning messages stored in the dynamics database:

            select * from Dynamics..SY01700

            -Vince

            Like

  43. Hi Victoria,
    I’m also a Dynamics GP Consultant with 20 yrs experience implementing Dynamics GP and other ERP systems. I was wondering if you knew of any current or updated documentation for implmenting Multi-Currency for Dynamics GP2010. I haven’t found anything since version 9. Thanks in advance.

    Joe

    Like

  44. Hi Victoria,

    We recently changed our year end from July 31 to Dec 31. However, we already closed our 2011 year end in GP and created a new one called 2012, running from Aug 1/11 to Jul 31/12. I was able to change the historical period for 2011 to be 17 periods, from Aug 1/10 to Dec 31/11, after which I ran a “Reconcile” on the 2011 historical year. However, the transactions that occurred from Aug 1/11 to Dec 31/11 are not showing on the Historical Summary Inquiry….

    Any ideas on how to properly make the change?

    PS I did this all in a “test” company so none of these changes have affected our “live” file.

    Like

    • Hi Jason,

      Good call on doing all this in a test company!

      I have not gone through this particular scenario before, I always make sure to make changes like this prior to closing the year. I am thinking that you need to ‘reclose’ 2011, but I am not sure if it is too late at this point. If you go to the year end close window, is it looking to close 2012? Or will it let you close 2011? If you can close 2011, you should. If not, then you may need to get Microsoft to open the 2011 year for you. They can do this as a consulting project and the cost depends on the size of your database and a few other variables. It may be the only way to do this with a 17 month year.

      Another option may be to have a 5 month year and ‘rename’ all your prior years so for example 8/1/11 – 12/31/11 becomes ‘2011’ and 8/1/10 – 7/31/11 becomes ‘2010’ (and all the prior years move back 1). You can do this with the Fiscal Period Modifier that is part of the Professional Services Tools Library. This is also not free, but may be less expensive and easier than having MS open the year for you and you might already own the PSTL.

      -Victoria

      Like

  45. Hi,

    I would like to know if there is a report that shows all outstanding purchase orders? I have develped an SSRS report from scratch, but the details given does not correspond to that which is on Great Plains.

    Like

    • Hi Marius,

      I do not have code posted for a report like this, but you could take a look at the code posted on Ron Wilson’s blog to see if anything there might help. Alternately, you can try to troubleshoot your report – what is not matching the information in GP? What tables are you using on your report and where are you looking in GP to compare this?

      -Victoria

      Like

  46. Hi Victoria —

    Thanks for all the valuable tips and advice!

    I have a strange issue that you may have come across before. We’ve recently closed the GL for several of our companies in testing copies of the respective company databases. Afterwards, we’ve printed the Balance Sheet from Management Reporter for three of the companies. The Report / Row / Column definitions in MR for these are essentially copies of one another. The detailed trial balance for each company shows the the Retained Earnings account has had the appropriate amount posted to it as of 12/31/2011. However, when we print the balance sheets, the Retained Earnings amount appears on only one of the three company balance sheets. I’ve poured over the definitions and have even restored one company DB and went through the year-end close process again. Same result: no retained earnings account / amount appears on the balance sheet report in MR. I feel I must be overlooking something very simple – but I can’t find it. Would very much appreciate some direction.

    Thanks in advance —

    Jack

    Like

    • Hi Jack,

      I would first confirm the Retained Earnings (RE) account in GP. Print a summary trial balance and verify that the RE account shows up as expected. If it does, then the issue is not with your data, no reason to restore and re-close. In MR, confirm the report is printing against the correct company. I have not worked with MR much yet, but if it is similar to FRx, then there is a place where you specify what SQL database corresponds to each company in MR. If that is correct and you are printing the report against the right MR company, then the next thing I would do is create a brand new report to test this. Make it really simple – put the RE account and one other account on there. If the new report works, then something on the old report is a problem. Maybe it was converted or copied and something didn’t work right…. If the new report is not showing the correct data, then it might be time to have someone take a look at this together with you – maybe your GP Partner or GP Support.

      Hope this helps.
      -Victoria

      Like

      • Hi Victoria —

        I was hoping to come back with an answer to the puzzle – well, a more positive one. Not to be — just yet, anyway,

        Yes, MR is much like FRx – the specified database is correct as is the company. I did create several brand new reports with just a few accounts on them and the Retained Earnings account amount still doesn’t appear. I even ran DBCC checkdb on the MR database. No good luck.

        I’ve reached out to our partner. If you like, I will post back final results…

        Thanks again —

        Jack

        Like

    • Hi Victoria,

      Just wondering if you or anyone else knows the SQL scripts required to retire a fixed asset? I know there is a utility within GP to do this, however in my situation, i cannot use this to mass retire the assets i want to retire, as they all have different criteria, so it is not possible to group them.

      Thanks

      Matt

      Like

  47. Hi Victoria,
    Recently i shifted my Gp10.0 Database old server to New server , With new server all companies are working fine, But when i run GP utilities getting this error ” The sorted procedure verify version information ()of form duSQLInstall Pass through SQL returned following result: DBMS:446,Microsoft Dynamics GP:0.

    Here I check collation name of GP DB=SQL_Latin1_General_CP1256_CI_AS

    But my new server master db collation name is different, i didn’t find the same collation of GP DB.

    How can i run my GP utilities .

    Like

    • Shan,

      Sounds like your new server has a different collation from what your old one had. If that is the case, while GP seems to be working, this could be causing additional problems besides what you’re seeing in Utilities and it should most definitely be addressed.

      As to how to fix it… This is not something I typically work with, so I would recommend talking to Dynamics GP support to make sure that this is done properly.

      -Victoria

      Like

    • I ever have same issue for my local environment. But I’ve fixed it by re-install my SQL (un-install and install MS-SQL again, not overwrite the existing). And select the proper collation same as the one in the server. So, when it finish I restore my DB backup and run my GP Utillities and it worked.
      Hope it help.

      Like

  48. First of all Merry Christmas and Happy New Year to all
    Second you have an exceptional community resource and your contribution is munch appreciated. Thank you.
    My inquiry and reason for this post. I am looking for some input with regards to integration resourses for the developers in our firm specific to the Human Resources portion of GP 10. We had hoped to use the very resourceful “Scribe” product but it is void of Human Resource connection objects that we can tell. If this is otherwise, please reply. We can not jump right to GP 2010 to get a better / resourceful eConnection runtime. So does know of a simular product / integration (mostly a data push / pull tool) as is Scribe as it is with most other GP modules / features excluding HR? Please forward.

    Like

  49. Hi, I wonder if you could help me with an issue on printing Cheques and Remittances.
    Our Cheque layout is whereby we have the remittance at the top of the report and the Cheque Stub at the bottom. If the cheque is paying lots of transactions, we need a continued remittance to print to a separate printer tray, that holds blank paper.

    I’ve been using “Stub on Bottom”, which if there are more than 12 lines on the remittance, you will then be promted to print the remittance once you have posted the cheques. However, this prints all the transactions again (including the 12 on the original).

    Is there anyway to get Report Wrtier to print on blank paper as part of the “Print Cheques” process and also miss off the 12 transactions on the continued remittance?

    Like

    • Hi Stephen,

      I have not investigated this in depth, however, my gut feeling is that that you cannot get GP to print the remittance on blank paper at the same time as printing checks on check stock without some significant customization.

      A less involved alternative may be to modify the remittance report to not repeat what is already on the check stub, but I would recommend asking that question on the GP Community Forum, where you can find people much better acquainted with Report Writer that I am. Another alternative would be to see if any of the 3rd party check printing software can help accomplish what you want.

      -Victoria

      Like

  50. Hi Victoria,

    1) How to I add an additional address line under “SHIP TO” on sales invoices?
    2) Is there a way to print packing slips for historical Invoices? What alternatives I can use to print packing slips for historical invoices?

    Thanks,
    Shamila

    Like

  51. Hello victoria, always grateful for your help.
    This time I wanted to know if it is possible to configure GP so that the quote has the shortage alert the same way you do the order and invoice.

    Like

    • Hi Yosselinb,

      I am not aware of a way to do this in GP. If you have the view on the SalesTransaction Entry expanded, you can see the Quantity Available as you are entering each item…but that’s about as close as you can get.

      -Victoria

      Like

  52. Victoria, your knowledge of GP is fantastic – wish you were our VAR.

    Can you please tell me if you are aware of the problem that GP has when exporting a sales order to a tab-delimited file – the shipping and billing addresses switch places? I’ve been told that this issue has existed since the origin of Great Plains and there is no fix. True?

    Like

    • Hi Anna,

      Thank you for your kind words!

      I have not heard of this issue. Are you doing this right from the Sales Transaction Entry window by clicking Print and choosing tab delimited file as the output? If so, then my next question would be…why? If that’s not what you’re doing, can you give me a bit more detail as to how you are exporting?

      -Victoria

      Like

  53. Victoria good afternoon, is there any way (from GP) to remove Sales transactions (unposted) on a massive scale?

    Like

    • Yosselinb,

      If they are all in the same batch (or a few batches) you can delete the entire batch at once. Otherwise, you might be able to do this directly in the database, but this is not something that I would recommend without getting some advice from your GP Partner or GP Support.

      -Victoria

      Like

  54. Hi Victoria,

    I am trying to create a modified Report in GP2010 using the standard GP Report Writer for Inventory Transfers. I just want to add the bin the item is coming from and the bin the item going to (along with sites). However, from what i can tell, the GP report writer will only allow one “1 to many” table relationships, however to add in the bins, i need two “1 to many” table relationships. Do you know anyway around this? I was thinking maybe i could build a report for Inventory Transfers in SSRS, but i am not sure how to integrate this with GP? Do i need to use sharepoint? I am using SQL 2008R2. Any advice/suggestions as to how i can deal with this seemingly simply report.?

    Thanks

    Matt

    Like

    • Hi Matt,

      That is correct, Report Writer does not allow multiple 1 to many relationships. You can possibly write some code to get around this, but I am not the best person to answer questions about Report Writer. For that I would recommend looking at David Musgrave’s blog. If you decide to create an SSRS report…you could just have users run it off the SSRS site, you do not necessarily need Sharepoint for that….or you could use something like our GP Reports Viewer product and actually run the report from just about any GP window. You could even automatically pass parameters to the report from fields on the GP window.

      -Victoria

      Like

  55. Hi Victoria,
    Great site! I was wondering if you have ever seen a situation where when using the detail inquiry feature, the net difference between the credits and debits equals a different amount shown than the account balance. The difference between the debits and credits is the correct balance, but is not properly calculating to the account balance shown.

    Like

    • Dede,

      I can think of several things that could cause that:

      • The inquiry window was open while transactions were being posted.
      • One of the ranges at the top of the window is not set to ‘All’.
      • The currency shown is not the functional currency.

      There may be others, but those are the ones that easily come to mind. I typically use the Summary Inquiry window instead of the Detail Inquiry to look at this stuff…it is a lot easier to see the numbers (and possible issues) there. If you believe there is an issue with the numbers, you may need to run Check Links and/or Reconcile.

      -Victoria

      Like

  56. Hi Victoria!

    Is Possible create an additional type of document entry transaction sales, in addition to quote, order, invoice. ? example: “order of delivery.”

    Like

    • Hi Yoselin,

      This is not really possible without a pretty large scale customization, and I don’t know if I would recommend it. I would suggest discussing the reasons you need this with your GP Partner to see if they can offer you some other workarounds.

      -Victoria

      Like

  57. Can you provide any insight on importing GL history from SL into GP? I need to import balance sheet beginning balances for the first year and then period changes for the remaining years up through the current period. I am using SmartConnect to import the JEs.

    Like

    • I have not worked with SL or SmartConnect, so I cannot speak to them. However, I will say that you need to start with ending balances, as there is no way to enter a true ‘beginning balance’ JE in GP. So if you are bringing in 2007 through 2010, start with the ending balance for 2006, then bring in the net change for each period through the end. It may seem like a minor issue, but if you need to do net change reporting or comparison this will make a big difference.

      -Victoria

      Like

      • Thank you Victoria. We want to do comparative Income Statements. Can I bring in income/expense period activity or just balance sheet period changes?

        Like

        • You should do both. Here is an abbreviated excerpt from my book on a sample process for doing this for someone that wants 2008 through 2010 data imported:

          • Create the 2007, 2008, 2009, and 2010 fiscal years in Dynamics GP.
          • Export the ending 2007 GL account balances from the old system.
          • Export the monthly GL account net changes for 2008, 2009, and 2010 from the old system.
          • Import the ending balances as of December 31, 2007 and the net changes for each month of 2008, 2009, and 2010 into Dynamics GP.
          • Post all imported transactions and close the 2007, 2008, and 2009 years to update Retained Earnings and all the Balance Sheet accounts.
          • Once everything is imported and posted in Dynamics GP, make sure that the resulting financial statements match previously generated financial statements.

          -Victoria

          Like

  58. Hi Victoria, first of all .. Thank you for your valuable support and clarify these small questions to the community helping us.
    I wonder if it is possible to apply security of level field to a window that was created from the extender module, I tried but when I have to select the window I can not find it anywhere.

    Like

  59. Hi Victoria,

    I was wondering if there’s a way to change the functional currency of a company with posted and unposted transactions. There is also an intercompany relationship established with this company. My understanding is that we will need to create a new company and rebook the transactions. This is fairly a new company. Thanks in advance. By the way i love your blog it’s so informative.

    Like

    • Hi Rev1976,

      To my knowledge there is no way to change the functional currency of a GP company once you have posted transactions. You could possibly try deleting all transactional data, (this would save the setup), then change the currency, but I have never done this and am not sure if it would work. It may just be easier to start with a new company and copy the setup needed, especially if it’s a fairly new company.

      -Victoria

      Like

  60. Hello Victoria, My name is Carlos, and I´m from of Venezuela. I work with Dynamics GP as developer. This blog is awesome! I´ve seen some usefull things or tools that I could use in my work. Thanks Best Regards

    Carlos Yáñez

    Like

  61. This time I wanted to know if it’s possible configuration options can lock a transaction for a customer sales suspended, ie not allowing the
    user to generate any sales transaction (quote, order, invoice) when the state is suspended or inactive. You can configure this in GP?

    Like

    • The only way to ensure this would be to change the customer to Inactive. However if the customer has transactions that are not in history, you will not be able to do this. Putting a customer on Hold will pop up a warning, but allow you to enter some transactions for them.

      -Victoria

      Like

  62. Hi, I have the below question hope to get an answer:
    can I assign particular inventory items to be sold only for particular customers? or viceversa?

    Thanks

    Like

  63. Hi Victoria,

    I’m having problem on the displayed currency symbol upon creating transaction on GP 10. For example, upon creating Transaction entry on Payables Management I select YEN as currency,the symbol display was correct at this point but when I check my distribution the currency symbol was Dollar. This also happen on other modules, ex. GL, AR. I check my Multicurrency Setup and Functional currency, which is YEN, and don’t see any problem. Is there any reason why this happen and what should I do to fix this? Your help is highly appreciated. Thank you!

    Tin Bolo

    Like

    • Tin,

      On most window in GP there is a menu at top right that will let you choose what currency you are seeing. Perhaps you do not have the right currency selected there? This is a per user/per window setting.

      -Victoria

      Like

  64. Hi Victoria,

    I have posted some invoices but the sales orders still open. How do I move those sales orders to history? and How does that happens?

    Thank you,

    Victoria P.

    Like

    • Hi Victoria,

      Try the Remove Completed Documents utility under GP | Tools | Utilities | Sales | Reconcile – Remove Sales Documents. Select your Document(s) and Remove Completed Documents and click Process. This will move fully completed sales orders to history for you. If you want to see what it will do first, check the Print Report Only checkbox.

      -Victoria

      Like

  65. Hi Victoria,

    Just wanted to wish you a Happy New Year and thank you for your assistance throughout 2010. Your help has been invaluable.

    I will try not to bother you as much in 2011, but I won’t make it a resolution. :)

    All the best,

    Gordon

    Like

  66. Hi, thanks for the helpful information here! Our tax guy wants a payables report listing all payments for the calendar year by vendor with check number, date of the check, amount of the check, invoice #s paid, invoice description, invoice amount, and the 1099 amount. Do any of the reports you have already created do this? I see you have a 1099 total report, but does it list each payment? Our Tax guy wants to see all payments so he can ensure we didn’t miss any that should be 1099’d. You have great info here, thanks so much.

    Like

  67. Hi
    I have the following issue on the apply sales transaction. If you have solved this before please let us know or is there any way to achieve this.
    1. Create a Sales Transaction with Originating currency as Z-UK and post them, your functional currency is Z-USD
    2. Create a Cash receipts with originating currency as Z-NZ and post them, your functional currency is Z-USD again.
    3. Go to apply sales transaction
    a. Try to apply the cash receipt posted to the z-uk Invoice.
    b. GP is not bringing those invoices other than functional currency or the same originating currency transaction of Invoice and cash receipt.
    I feel this is the functionality gap in the product, some of our customer receive money in different currency to the respective checkbooks.
    Please revert with your experience to solve this issue.

    Like

    • Hi Jai,

      We have not has this come up as a request in the past, so it’s not something I have worked with, but have you looked into euro relationships and triangulation to see if that can address this need?

      -Victoria

      Like

      • thanks for the reply

        i did looked in to EURO relationship, but that will not work out, it will convert every single transaction to EURO, yet will not solve the issue.

        i was thinking, there is apply from currency field in the screen this defaults originating currency, supposing by doing some customization to enable this field and select the functional currency may solve this issue

        share your thoughts on this.

        Like

        • Jai,

          I don’t think simply customizing this field would help, as this is likely much more complicated in terms of what has to be done inside GP for MC revaluation. I would recommend talking to Microsoft’s support or professional services team to see if this is something they can help create a workaround or customization for.

          -Victoria

          Like

          • Victoria

            did that even, MS support could not sove, PS Team charges 200 hrs which equal to 10 user lic for single functitionaly is not justified.
            if you come across any solution do revert thx

            Like

  68. HI Victoria
    Great site and a lot of useful information. Do you know of any companies listed on the Dow or any other major stock markets that use GP as accounting software?
    Our company (not in the US) is thinking of a IPO but my CFO wants to know if we need to change to a different system before we do so.

    Any suggestion would be welcome

    Like

  69. Victoria,
    Your site has been very helpful. I’m looking for a way to delete some inventory items out of Dynamics GP. I would like to keep the history of these items, but not have them show up when you hit the magnify glass. When i say keep the history; we use GP Manufacturing. I would like to keep the history of the material report. So i may have some items that have been posted to a MO, i want to keep that history, but delete that inventory part number. Any help would be appreciated. Thank you

    Jason

    Like

  70. Hi Victoria,

    Your site has been a big help.

    We are having a problem with sales data not importing into GP. We use econnect to import our data from our sales system. We have an item that has been causing us some issues recently. It keeps giving an error that the unit cost does not match the extended price. Currently the unit cost is going in with 2 decimal places and this appears to be where the problem is, because if there is a discount on one of the items the unit price turns out to have more then 2 decimal places so the unit price gets rounded and then doesn’t match up with the extended price anymore. We tried increasing the number of decimal places, but then we start getting errors that the number of decimal places passed in doesn’t match the setup. Do you have any suggestions on how to fix this second problem? Thanks for your help.

    Mike

    Like

    • Hi Mike,

      Are you specifying both the unit price and the extended price in your import? If so, can you try just providing the unit price and letting GP calculate the extended price? Not sure if that will solve the problem, but may be worth a try.

      -Victoria

      Like

      • Yes we are specifying both. The unit price is calculated from the extended price by summing the debits and credits of the sale and then dividing by the number if items. Would it be ok to supply the extended price and not the unit price?

        Like

        • Mike,

          Yes, you should be able to specify one (either unit price or extended price) and GP will calculate the other for you. Usually specifying the unit cost is easier, especially if there are markdowns. Obviously some changes will be needed to your import and you will want to test this, but hopefully that can fix the problem.

          -Victoria

          Like

  71. Hi All,
    I have just installed GP2010 on two machines both running on windows xp sp2 . The completion finished well but when you try to run the utilities GP does not respond at all. Kindly help on this issue as it is very urgent. Its the first time am encountering this since others installed pretty well.

    regards,
    Charles

    Like

    • Charles,

      I have not run into this before and we’ve installed GP 2010 on numerous Windows XP machines. The only thing I can think of is that it may be the service pack level – the requirements for GP 2010 are Windows XP SP 3. I have not installed GP 2010 on XP SP 2, so I cannot say that I have seen it work. If updating to SP 3 is not possible or if it still does not work, I would recommend contacting GP Support.

      -Victoria

      Like

  72. Hi and thank you for the response to my earlier question.

    Do you know if there is stored procedure equivalent to “taRMApply” that works on Payables? The procedure needs to mimic the functionality available in the “Apply Payables Documents” window.

    I am guessing the procedure will need the following parameters to accomplish the required functionality:

    INPUT
    Apply to document number
    Apply from document number
    Amount to apply
    Document type of apply FROM document. (Payment, Return, Credit Memo)
    Document type of apply TO document. (Shipment, Shipment/Invoice, In-Transit Inventory)
    Apply date
    Apply posting date

    OUTPUT
    ErrorState, int
    Error String, string, 255

    I cannot find an existing stored procedure, web services or econnect routine to perform this action. Please let me know if you or a referral has an interest in engaging in a consulting arrangement to write this procedure. Thx, Terry

    Like

  73. Hi Victoria,
    I’ve been trying to find help on the safest and quickest way to delete approximately 76,000 inventory items from our GP Inventory tables. These items do not have any transaction history and none of our sites in the IV00102 table have any of these items in stock.

    I know one method of clearing these items out would be to mark them as discontinued and let the year end process delete them, but we really don’t want to wait that long to get rid of these records since it amounts to around 15 million records that we don’t need in the IV00102 table.

    I found one possible solution in a forum that said to delete each item from tables: IV00101 – IV00116. But the author didn’t recommend this approach and mentioned discontinuing the items if possible.

    My concern with discontinuing this many items is that the year end process might literally take a week to finish. Last year it took almost 48 hours to complete!

    Any ideas would be greatly appreciated. By the way, your site has been very helpful, thanks for sharing your knowledge.

    Like

    • Hi Jim,

      This is not a situation I have faced before. Typically with this many records involved I would look for a solution directly in SQL, because, just like you I would be concerned about the amount of time any kind of processing like this would take in the user interface.

      That said, to feel comfortable about recommending something like that would require a lot of testing. Tables IV00xxx are a good start, but you also need to consider any add-on products or customizations you have as well as other GP tables. To be thorough, I would probably start by getting a list of all the tables that have the ITEMNMBR field and check each one, if it has these items, determine whether they should be cleared out based on what the table is. It’s a bid tedious, but may be worth it in the end.

      Good luck on this project and let us know how it goes!

      -Victoria

      Like

      • Hi Victoria,
        Because we are using Extended Pricing, the Extender module and have many customizations, we decided deleting items through the GP software is our safest bet. But it isn’t realistic to have someone delete this many items by hand.

        So I tried out a couple Windows automation programs and settled on AutoHotkey, which is a great open source solution. I wrote a script that automates the entire process of logging into GP and entering items to delete based on a list of item numbers in a text file. It uses image recognition to identify when the Item Maintenance window is ready to look up an item, when it has finished the look up and can respond appropriately to the various dialog boxes that may appear when deleting the item.

        Due to the many SQL jobs and nightly backups, we can only run it for 4 hours per night. The script is averaging about 275 deletes per hour and it’s already gotten rid of 12,000 items.

        Obviously my solution is not the quickest but we just don’t have enough information about the relationships among all the tables in GP to confidently delete these items directly from the database.

        Thanks again for your response and all the valuable information on your site.

        Like

  74. Hi, great site.

    Do you know if there is a way to automate the “Sales Document Commitments” in Purchase Order Processing? I am creating a PO document using GP 10.0 SP4/Web Services and setting the DoesAllowSalesOrderCommitments to true. How do you automate the association between the “Purchase Order” and the “Sales Order”? The manual process is to open the “Sales Commitments for Purchase Orders” window and pick the Sales Order Document. I have not had any luck finding a method in eConnect, Web Services or a SQL stored procedure to make this association.

    Like

    • Hi Terry,

      Thanks for the kind words. I do not know of an automated method for this, although you might want to double check this with GP Support or post this on a forum with a broader audience, like the GP Customer Forum. If there is no automated method, the answer may be to populate the SOP60100 table which holds these SOP-POP links.

      -Victoria

      Like

  75. Hi Victoria

    I have just learned of your site through my business partner and it looks very helpful to a lot of situations.

    I have a challenge, one of our clients is using GP Version 10 and have a situation will all the users and the system administrator, when trying to delete an unposted batch in Financial, the Delete area is grey out thus cannot delete those batches. I am aware this may be a set-up problem but we seem not to be able to grant anyone permission to do that task despite attempts to refresh their privilege.

    Kindly assist on this .

    Regards

    Lam.

    Like

  76. Dear Victoria

    Thanks for your posts and I am a regular reader and of your posts and also I use your website as my reference. I used to suggest this site to smy colleagues and friends.

    Before also I requested some help from you and thanks for your answers.

    Now I am facing another problem related to a check book. The check book is in local currency. When cash reciept or manual payment, foreign currencies are accepted. But when trying to make a bank receipt or withdrawl only the functional currencies are accepted.

    How we can solve this?

    Thanks in advance

    Abdul Rahman

    Like

    • Hi Abdul,

      Thank you for frequenting my blog! I don’t get too many opportunities to work with Multicurrency in GP, but it sounds like there may be a setup issue. Do you get an error when you try to change the currency or is the Currency ID field not editable? Have you tried the same in the sample to company to see if it works? If so, compare the setup windows with your live company to see what may be different or missing.

      -Victoria

      Like

    • Hello Victoria,

      We are stuck in the dark ages on GP 8.0, but we want to be able to update GP with invoiced sales orders. We currently use eConnect to send sales orders to GP. Is there a way to use eConnect to mark those sales orders as shipped/fulfilled? If not, can it be done without concern using SQL statements? Our in house GP expert didn’t think that it could. I appreciate any insight that you may have. Finally, you have a very lovely name. We named our daughter Virginia. :)

      Thank you,
      Todd

      Like

      • Todd,

        Thanks for the kind words!

        I am not an expert on eConnect, so I am not 100% sure if order fulfillment is possible with it. If this is important, I would talk to either an eConnect expert or GP support to find this out for sure.

        I would not recommend doing this directly in SQL. This is one of those situations where too many other modules and tables are touched by an operation.

        -Victoria

        Like

  77. I was searching for a solution to my problem and I’ve found your website, I don’t know if you could help, I want to display 2 reports (made in crystal reports) one behind the other, they’re 2 rpt files, they’re similar except for the kind of report, one is an import report and the other is an export report, anyway thanks a lot and I’m sorry for my english.

    Like

    • Hi Fernando,

      I am not sure what you’re asking – do you mean you want to somehow link the reports together? so if you launch one the other one runs? Or something else? Also, are you just launching them from Crystal or somewhere else? (Like Dynamics GP?)

      -Victoria

      Like

  78. Hello,

    I’m having a problem with chaining reports in FRx. When I change the report date on the first report it doesn’t update the chained reports. They still print with the default report date. Seems odd that I would have to manually update each report. Are any values passed onto the chained reports such as printer or page settings?
    If you have to change any of these settings frequently then report chaining isn’t really that useful is it?

    Also, is there a way to set the Default Base Period to a prior year? All of the options in the drop down list reference the current year.

    Thanks for your help.

    Like

    • Darren,

      In my experience chaining reports has never worked very well and I am not surprised it’s not working as you expected. If this is a big issue, I would recommend posting this on the FRx newsgroup or talking to support, but otherwise, I am not sure this is something that will be addressed, as FRx is being replaced with another product.

      I do not know of an option to consistently default the base to a prior year. Last closed period may work in some cases, but that’s only if your last year is closed. Some companies choose to hard code the years in the columns instead and simply have different reports for each year (that way you can also leave the budget IDs alone).

      -Victoria

      Like

  79. Hello Vicotria,

    My company is looking into on-line ordering solution for VARs. The key requirements are a self-service portal that allows VAR partners to log in securely, enter orders with either credit card or PO information, and check status.
    We are on GP10. Microsoft is advetising business portal Order Management solution, and this is one of the options we are considering.
    If you are familiar with the business portal OM functionality, could you suggest which modules we’ll need to purchase for it to work? Would it make sense to upgrade GP to gain more functionality in that area?

    Thanks,
    Yana

    Like

    • Yana,

      I am not familiar enough with BP or OM to advise on this. Based on what I have seen previously, I would suspect that BP will not be what you’re looking for, but there may be new functionality that I have not seen. I would advise some very serious due diligence and testing on this type of project to make sure you are going to get something that meets all your requirements.

      -Victoria

      Like

  80. Hello Victoria,

    We have a problem tying Customer Yearly Summary Inquiry to sales transactions. The Sales figure in the Yearly Summary Inquiry screen is lower for the 1st quarter than the one we get from the sales transactions.

    When I compare the data in the tables, they look incorrect to me. I cnanot figure out how the data gets into the Summary table RM00104. Please see below the queries I’ve ran and the results.

    select sopnumbe, docamnt, glpostdt
    from sop30200
    where soptype = 3 and voidstts = 0 and custnmbr = ‘com249′ and glpostdt >’12/31/2009′

    Results:

    SOPNUMBE DOCAMNT GLPOSTDT
    0011245 37968.00000 2010-01-14 00:00:00.000
    0011409 18984.00000 2010-02-07 00:00:00.000
    0011492 36172.00000 2010-02-20 00:00:00.000
    0011611 18984.00000 2010-03-05 00:00:00.000
    0011612 28776.00000 2010-03-05 00:00:00.000
    0011693 55494.00000 2010-03-16 00:00:00.000
    0011694 28776.00000 2010-03-16 00:00:00.000
    0011816 28776.00000 2010-03-30 00:00:00.000

    select custnmbr,periodid, year1,numofinv,smrysals
    from RM00104
    where custnmbr = ‘com249′ and year1 = 2010

    CUSTNMBR PERIODID YEAR1 NUMOFINV SMRYSALS
    COM249 1 2010 1 -8845.00000
    COM249 2 2010 2 53297.00000
    COM249 3 2010 5 157926.00000
    COM249 1 2010 1 -8845.00000
    COM249 2 2010 2 53297.00000
    COM249 3 2010 5 157926.00000

    The number of invoices matches in each period, but the amounts are in each period, especially in January.

    Is there anything else I should consider when trying to match sales to the yearly summary inquiry screen?

    Thank you,
    Yana

    Like

    • Hi Yana,

      There are a number of things that may make the RM summary tables not add up to what you’re looking at. It would be impossible to tell without looking at your data, but here are the 2 main things that come to mind:

      1. The RM summary includes ALL customer RM transactions, not just the SOP ones (SOP transactions become RM transactions once posted). To see all RM transactions look in the RM20101 and RM30101 tables or use my view here: http://victoriayudin.com/2009/04/24/sql-view-with-all-posted-receivables-transactions/.
      2. I do not think that the summaries are using the GL posting date – I believe they are using the DOCDATE (again, from the RM tables). If you have transactions where the GL dates are different from the Document Dates, that will cause a difference.

      This is of course not even going into anything that may have been imported or changed directly in the database. Both of those may also cause the summaries not to be correct.

      Hope this helps.
      -Victoria

      Like

  81. Hi Victoria,

    I have a question regarding Date Parameters in my Crystal Report. I cannot figure out how to set it up.

    Basically, I want the report to display data for a specific transaction date (not a range) only. I should point out that this is a cash report that displays all of the transactions from the Cash Management module.

    I can set up the parameter easily enough, but setting it up in the Select Expert is proving to be a challenge and the online Help hasn’t helped because I am doing something wrong.

    Any ideas?

    Thanks and regards,

    Gordon

    Like

    • Gordon,

      It’s a bit difficult to answer specifically without seeing your report, but in general, you should be able to go to Report > Selection Formulas > Record and enter a formula for this. I would expect the formula to look something like this:
      {?Report Date} = {CM20200.TRXDATE}
      This is slightly different than using the Select Expert, but I find it more straight forward to enter my selection criteria in Crystal this way. If this is not working, or if you are getting an error, please write back with more detail.

      -Victoria

      Like

      • Hi Victoria,

        It worked! You did it again. I will certainly remember how to do this for future reports.

        Thanks and regards,

        Gordon

        Like

  82. Dear Victoria

    We recently migrated from GPv8 to GP v10 but found that the version of FRX 6.711027 on v10 does not produce an analysis column of information that v6.7.141 used to.

    We download hundreds of cost centres with analysis of the expenditure including Supplier names. The new FRX report seems to ignore this column when producing the analysis reports.

    It has been suggested that we re-install the old version of FRX.

    Any views?

    Thanks

    Victor

    Like

    • Victor,

      I have not run into this particular problem before, but I also don’t typically use FRx for that level of reporting. What is the exact Type of column that is not giving you results anymore?

      -Victoria

      Like

  83. Hey Victoria,

    I’m wondering if maybe you could shed some light on a project that I’m working on.

    We’re wanting to publish online reports for our clients – similar to how a bank or investment firm would.

    Currently, the reports are created in FRx, but for the online access, I’m writing a site in PHP that grabs the data directly from SQL when the user logs in, so the data that they see is always the most up to date.

    The issue that I’m running into is that I can’t find where a lot of the data resides.

    IE – “Beginning Balance”, “Net Income”, and “Ending Balance” are just a few. I have located the tables for the current year gains/losses as well as the prior year, but I’m wondering if there’s some kind of trick to doing this.

    One thought.. does FRx have the ability to create a SQL view, which just the relevant data in the view?

    Anyway, any help you can give me would be appreciated.

    Thanks,
    Matt

    Like

    • Matt,

      You don’t specify what data you are actually reporting on, but since you’re able to use FRx for it, I am going to guess it’s General Ledger accounts? In that case, the reason that you’re not finding stored data with beginning and ending balances is that GP does not store this data. Net income (if this is just the net change for an account number) can be found in the following tables:
      GL10110 – Open Year Summary Master
      GL10111 – Historical Year Summary Master

      Or the following views:
      GL11110 – Open Year Summary Master
      GL11111 – Historical Year Summary Master

      You will need to calculate the beginning and ending balances on your own.

      Hope that helps,
      -Victoria

      Like

  84. Hi Victoria,

    Currently, we are emailing all statements at once to customers using GP statement emailing capability. I was wondering if there is a way to change the file name of the statement. Right now, it looks similar to this: rmStmt_CustomerID_00.pdf. We would like it to be more descripive.

    Thank you,
    Yana

    Like

  85. Thank you for your help, Victoria. We will be looking to buy both Extender and Modifier.

    Yana

    Like

  86. Victoria,

    The users want to enter re-defined values into a user defined field, and they want to have a look-up or a drop-down next to that field with those values pre-populated.

    Thank you,
    Yana

    Like

    • Yana,

      So you want to give users the ability to choose from a list of predefined values for one of the user defined fields on Customer Maintenance? If so, I do not believe this will be possible without a customization. If you have the Extender module, that would be a better option for doing something like this.

      -Victoria

      Like

  87. Victoria,

    Thank you so much for your reply! That’s one place I haven’t look at.
    Now, the customer asks me to add a lookup on the custom field. Could you suggest what tools I need to do that?

    Thanks again,
    Yana

    Like

  88. Hello Victoria,

    Is there a way to rename User-Defined 1 and User -Defined 2 fields on Customer Maintenance screen through Setup? We don’t have Modifier, and are not planning to get it just yet.

    Thank you in advance for your help.

    Yana

    Like

  89. Victoria,

    I have searched several sites to see if anyone has a solution for our problem. We would like to be able to enter recurring payables transaction type of credit memos. We are in an industry that uses this as a reduction, to employees who earn commission, for items such as insurance.

    Do you know of any solution to this that does not use some kind of Integration tool? Can modifications be made to Dynamics to allow the use of credit memos in recurring batches?

    I appreciate your time and thoughts on this.

    Like

    • Zara,

      Just about anything can be customized, but I suspect this would be a fairly complicated (and thus costly) customization. Why not use an integration tool instead? That would be my recommendation.

      -Victoria

      Like

      • Victoria,

        We currently use Integration Manager for some of our customers, but we are looking for this to be a more simplified process.

        Thank you for your prompt reply.

        Like

        • Zara,

          If you have multiple customers needing this, it certainly makes it more worthwhile to look at a customization. Perhaps you could automate the import using eConnect and build it into an add-on for your customers? They would not actually need to do any importing themselves, so it would be ‘simplified’ for the users.

          -Victoria

          Like

  90. Hi Victoria,

    Was hoping you could solve this problem that we have on Sales Order Entry. When we enter a sales order from a distributor that is being drop shipped to his/her customer and print the packing slip, the name of the drop ship customer is replaced with the bill to distributor name. So every time we have to go to the ship to field and manually type the customer’s name. Is this a GP problem that can be fixed. Would really appreciate your advice. Thank you.

    -Punita

    Like

    • Punita,

      This may be a silly question, but if this is being drop shipped, why are you printing the packing slip? Also, is your packing slip a custom report? I am fairly certain there is no packing slip that prints for a drop ship order or invoice in GP. If it’s a custom report, it may be that the report is not pulling information from the right place.

      -Victoria

      Like

  91. Hi Victoria

    I’m working with a client in Toronto migrating AR transaction data from legacy systems to GP10. It will help enormously if I can lay my hands on a copy of the GP10 data model such that we understand how the model fits together and how the integration should work.

    Do you know any experienced, professional GP functional and technical consultants in the Ontario area?

    Thanks in advance

    Regards
    Mark

    Like

    • Hi Mark,

      You might find the information in the Dynamics GP SDK helpful – it can be installed from the Tools folder on the Dynamics GP media.

      I don’t know of someone to recommend in Ontario, but if you don’t mind getting help remotely, perhaps my company (Flexible Solutions) can help. If you’re interested, please e-mail me.

      If you do need someone local, I would post a message on one of the GP newsgroups asking for recommendations. And be specific about looking for someone local, otherwise you may get inundated with replies.

      -Victoria

      Like

  92. Hello Victoria,

    I was wondering of you could help me to solve a SmartList builder issue. I have two views that I’ve added to SL (thanks for the great article on that!). Both views return results when ran in the SQL Query window, but only one of the views returns the results in SL. I’m the only one creating views and running them in GP. I am a poweruser in GP.
    I’ve granted permissions to my GP user using GRANT statement. I get no errors, just a blank screen.

    I’ve already spend many hours restarting GP and recreating my views without any positive outcome.

    What am I missing???!!!!

    Thank you in advance,

    Yana

    Like

    • Hi Yana,

      Typically, you do not want to grant specific users permissions to the view in SQL, you want to grant DYNGRP permissions. Then, within GP, user permissions to your SmartList are controlled in security. As a test, can you try running the ‘problem’ SmartList when logged into GP as ‘sa’ – do you still get a blank screen?

      -Victoria

      Like

  93. Hi Victoria,

    I have a customer who wants a high availability solution for their SQL server which is primarily used for Dynamics GP. SQL clustering was a bit too costly so I’m testing database mirroring in SQL2008. Wondering if anyone else has tried this with GP?
    I’m able to failover to the mirror database without interrupting user sessions so it seems to work great. Working on a way to keep SQL logins in sync between the primary and mirror servers at this point.

    Like

  94. Hi Victoria

    We ran into this issue when one of our developers for our workflow/document management product (that integrates wtih GP) found this in an inquiry screen. Have you seen this before?

    What does it mean when there’s a pound sign (#) to the right of the Origin in the Payables Transaction Inquiry window. For instance, in the Origin column, we see HIST#.

    Is this from an add-on or 3rd-party product? (It is not from ours product)

    Like

  95. Victoria,

    I am not sure if you can help me on this or not, but I figured it was worth a try. From everything I have seen you only work with Dynamics GP. My company is trying to locate a good Microsoft Forecaster Consultant. I did not know if you might be able to point me in the right direction to finding a good one. If not I completely understand.

    Thanks,
    Nate

    Like

  96. I am looking for a SQL script that replicates the Payables Historical Aged Trial Balance report using GL Posting Date. Can you post one or point me in the right direction?

    Thanks,
    Bill

    Like

  97. Hi Victoria, a client of mine is looking at a 3rd party consolidation and reporting package (like Hyperion) but perhaps smaller for their reporting needs for budgeting, forecasting, consolidation and forex. They have about 53 companies and four foreign currencies but report in US Dollars to the SEC.

    They use FRX but their GP Consultant has told them that FRX is no good and their spreadsheets that they now use are no good either. These spreadsheets are somewhat cumbersome.

    Do you have any experience with mid-size reporting packages and do you think they are realy needed. IOW does GP 10 have limittaions here.
    Thanks
    Eckhardt

    Like

    • Eckhardt,

      Sorry, I do not know of any other packages that I would recommend. Everything that my customers have needed for financial reporting has been accomplished for the most part with FRx, and once in a while we’ve had to supplement with a Crystal or SQL report.

      -Victoria

      Like

  98. Hi victoria,

    The problem senario is like this

    1) My Hr Department people add the notes against the employees in the Employee Maintenance window.
    2) Payroll Deparment can view the notes entered in the employee maintenance window but not modify or delete those notes entered by hr department.

    how i can setup the security so that payroll department people can just view the notes but not modify or delete the notes enterd by hr department.

    thanks in advance

    sharat mittakola

    Like

  99. Hi Victoria,
    There’s 1 Shipment/Invoice in foreign currency and a payment is in functional currency. Payment is part payment.
    When i apply the part payment to this foreign currency Shipment/Invoice, a round off JV is passed, debiting payables account and crediting Foreign exchange Gain(loss) account. There by the Shipment/Invoice is becomming fully paid. Can you please gv me a suggestion to avoid automatically passing this JV.

    Thanks in Advance
    Akbar

    Like

  100. Victoria,

    I have just found your blog and it appears to be a wealth of knowledge! Thanks for sharing. Would you have a problem if I published your table information internally to our support staff? Currently we are using the database diagrams but this would be a great quick reference when we do not need to get that deep. I would definately give you the credit and publish your website along with it.

    Thanks,

    Ron

    Like

  101. Hi Victoria,

    I have designed a crystal report using a stored procedure written in the SQL Server 2005.
    I am executing the stored procedure and open the report using the VBA code.
    For the connection string, i am using the UserInfoGet in the VBA.
    But i am getting the following error message “Database Connector Error” in the report viewer while calling the crystal reports from the VBA code.
    If i had given the sysadmin role for that GP User in the SQL Server, then the report will be coming correctly.

    What could be the cause for this error? Please give any solution for this error.

    Thanks,
    Prakash

    Like

  102. Hi Victoria,

    Congrats on being named an a Dynamics GP Zone MVP on Experts Exchange!

    Steve Endow

    Like

  103. Victoria,

    First, let me offer my compliments on a great site and the great information you provide.

    I am very new to SQL Views, but am pretty good with Crystal Reports and Great Plains. I was able to successfully follow your example for a multi-company AP Open Invoice report and it will be a huge time saver. Thanks so much for that.

    Now that I am armed with a little bit of knowledge on SQL Views, I want to do more and I am at the beginning and am already stuck! I am trying to create a new View that extracts data from 2 tables (POP10100 and POP10110). I enter the SELECT
    FROM Company1.dbo.POP10100 and the table will pop up in the diagram pane, but I cannot for the life of me get the second table, POP10110 to pop up so it can be linked and I can start adding fields.

    What is the proper syntax when trying to get data from more than one table?

    Thanks,

    Gordon

    Like

    • Hi Gordon,

      If I am understanding where you are correctly, you can right click in the diagram pane and tell it to Add Table.

      Another option is to copy the following into the SQL pane:
      SELECT POP10100.PONUMBER, POP10110.ITEMNMBR
      FROM POP10100
      INNER JOIN POP10110
      ON POP10100.PONUMBER = POP10110.PONUMBER

      When you execute this it will update the diagram pane for you with the tables linked and one field from each table selected, then you can add fields as needed.

      -Victoria

      Like

      • Hi Victoria,

        Thanks so much for the prompt reply.

        I did as you suggested and have gotten the following results:

        In the SQL Pane, after I entered the commands you suggested, the program inserted ‘dbo.’ in front of the table names (dbo.POP10110).

        When I executed the command, I got the following message:
        [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘dbo.POP10100.

        In the Diagram Pane I got the two tables with the link between them, however, it doesn’t list each field in the tables, just a check box followed by *(All Columns).

        In the Grid Pane, I got the two fields (PONUMBER and ITEMNMBR), but next to ITEMNMBR in the Alias column is Expr1.

        So, I’m not sure why it’s doing this. I should point out that I am using SQL Enterprise Manager V. 8.0. Not sure if that means anything or not.

        Does where I create the View matter? I have 10 companies in GPS and of course each is it’s own separate database. I didn’t create the View in any of them as I am trying to pull data from all of them, just like the AP Open Invoice Report. The View is created in a different database that I created.

        As you can tell, I am a complete novice at this. Any additional help would be appreciated. Thanks.

        Gordon

        Like

        • Gordon,

          If SQL is creating code it sometimes adds the ‘dbo’ to the table names, this is not needed most of the time, so I don’t add it when I code. Should be no harm done either way.

          If you are trying to do this from a database that does not have these tables, you need to preface the table by the database name, so if DB1 is the database:
          SELECT PH.PONUMBER, PD.ITEMNMBR
          FROM DB1..POP10100 PH
          INNER JOIN DB1..POP10110 PD
          ON PH.PONUMBER = PD.PONUMBER

          Its easier to give ‘aliases’ to the table names rather than having to type out the whole thing every time.

          I would recommend getting the code perfected for one database first, then following the example in my multicompany AP view to UNION the other databases.

          -Victoria

          Like

  104. Hi Victoria

    Thank you for the reply. I have found a way to search for deltas on the database itself for inporting into an EDW. On some tables there are date fields that are used for updates. By comparing the date of last import with the current date in the field, it is possible to only import records that are new or have been changed. Unfortunately not all tables have columns that can be used for deltas. On transactional tables I can use sequencial id’s when they are present for the same purpose.

    I have found your site extremely helpful in my plight to decipher MGP.

    Johannes Henn

    Like

    • Johannes,

      Yes, the DEX_ROW_TS field was added in GP 10.0 (prior versions do not have it) to help with this kind of inquiry. However, as you’ve noted, not all tables have it. Also, it is limited because it will only store the date/time a record was last updated, not what was changed. So depending on what you are doing with this information, simply knowing a record was updated may not be enough. And deletions are not tracked.

      -Victoria

      Like

      • I am looking in the rm30101 table with SSMS and see that for some receivables that moved into this table this morning from the rm20101 table, the dex_row_ts has a date of today and a military time of later this afternoon.

        It is about 6 hours ahead of when I think it actually happened. I checked the time on the server and it is good. Any thoughts on why that might happen? Thanks.

        Like

        • Scott,

          Excellent question! On my test machine the DEX_ROW_TS is 4 hours later than my actual time. My best guess is that it’s using GMT to ‘standardize’, however I cannot find any documentation on this. I have submitted an inquiry on the GP partner forum to see if I can confirm this. I will post an update when I have one.

          -Victoria

          Like

  105. Hi Victoria

    I would like to find out from you how MGP handles Delta columns. If I were to draw the data from the MGP database into an EDW to a staging phase, how will I be able to determine when records were updated and added in the database since the last upload. The date fields in the database I am querying, is set at a default. I don’t see datetime stamps for this purpose. Am I missing something?

    Regards
    Johannes Henn

    Like

    • Hi Johannes,

      There is nothing built into GP to track delta changes. One option for capturing changes is to use the Audit Trails module, which can be set up to track any changes made to tables you specify. While this is a GP module, I don’t consider this an ‘out of the box’ solution as it is typically a separate purchase and has to be set up specifically to track what you want. Another option is to create SQL triggers and populate your own table(s) with the changes you want to track.

      -Victoria

      Like

  106. Victoria,

    I am trying to get rid of an inventory record for a part that does not exist. I’ve deleted the record from table IV00112 then did tried a reconcile while no users are on system but I’m getting message “You can’t complete this process while transactions are being edited”. There is nobody on system but me and I’m not doing anything but trying to reconcile this one item. Any ideas how I can resolve this?

    Thanks,
    Raul

    Like

    • Raul,

      Get everyone out of GP and run the following in SQL:

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

      Then try the reconcile again.

      -Victoria

      Like

  107. Victoria:
    I’m an accountant and very new with Dynamics, we have v.9.0 and about to be upgraded to v.10
    I would like to ask basic question, when I made a new Smartlist folder (object-?), let say “AP Apply”, it does not want to show up in my smart list screen; I have to ask “SA” to make it shown. Is there any way could be shown without the help from “SA”?
    thanks
    Karim

    Like

    • Karim,

      In GP 9.0 you need permissions to add new SmartLists as well as permissions to access Advanced Security to be able to make new SmartLists visible. If this is not something that your company wants to give to users, then you must rely on the system administrator(s) to do that for you.

      -Victoria

      Like

  108. Hello Victoria,

    Your web site is great and I thank you in advaced for having it open to all of us: dynamics gp consultants!

    My question is about Extender. I know is a very powerful tool and I wonder if it is possible to create a custom lookup field in a extender window. I mean, I would like to create a lookup field on a master table I created.

    Thank you again for sharing your knowledge and I look forward to hearing from you.

    Ignacio
    Dynamics GP Consultant
    Toronto – Canada

    Like

    • Ignacio,

      Thank you for the kind words! I believe the only way you can create a custom lookup on your own table in Extender is if your table is referenced within a ‘product’. So if your table is created within a Dexterity customization, then it should be available in the list to create custom lookups against automatically. I am not sure if there is another way to reference your table within a product. If you need more help on this, I would recommend posting this question on the Dynamics GP Developer newsgroup.

      -Victoria

      Like

  109. Victoria,

    Finally figured out what the problem was, the term field has been hard coded and was not pulling data from table(s). So it do not matter which vendor we tried it with, they all showed the same due upon receipt regardsless of what the actual terms were for the vendor. Thanks for your assistance once again.

    Regards,
    Raul

    Like

  110. Manually, customer is renting some of our equipment so we send out invoice on a monthly basis. Prior invoices all show net 30 and nothing has been changed.

    Regards,
    Raul

    Like

  111. Victoria,

    That is exactly what is happening, a new invoice was created yesterday and once it printed is when we noticed that the terms were incorrect.

    Regards,
    Raul

    Like

    • Raul,

      How are the invoices created? Manually or by an import or custom process? Can you manually enter an invoice for the vendor and verify what payment term it is picking up? Also, what exactly do you mean by ‘once it printed’? Printing payables invoices is not a typical process for most accounting departments, how are you printing them? Or are you printing out a report listing the invoices?

      -Victoria

      Like

  112. Victoria,

    I have a vendor setup with payment terms of NET 30 but when an invoice is printed is shows the payment terms DUE UPON RECEIPT, where is the disconnect taking place or what needs to happen in order to get this back in sync?

    Regards,
    Raul

    Like

    • Raul,

      Vendor setup is only used as a default when a new transaction is created for that vendor. You can change each invoice’s payment terms during invoice entry. Also, regardless of the payment terms, the due date on each invoice can be changed independently. And changing the vendor setup does not update any existing transactions, whether they are posted or not. So the first thing I would check is what the payment terms and due date on the specific invoice are. The only time I would consider there to be an issue is if you are creating a brand new invoice and the payment terms are not being picked up correctly from that vendor’s setup.

      -Victoria

      Like

  113. Hi Victoria,

    Question, we have migrated all of users to use RDC to get into GP and no longer have any client installs. I’m looking for a solution to migrating any attachments that maybe stored or pointing to the users local machine as the DEX.INI may have had the OLE path to be C:\Program Files\Great Plains…..

    Are these lost and have to be reattached or are there other options for not losing this function?

    Regards,
    Raul

    Like

    • Raul,

      You could try moving the files, but I think you may have to reattach them for the links to work properly. You could try talking to GP support or posting on the GP newsgroup to see if anyone has any other ideas, I have never actually had to perform a ‘move’ like this.

      -Victoria

      Like

  114. Hi Victoria,

    May in know how to add the expiry date in SOP BLANK PICKING TICKET ORDER ENTERED using GP Report writer.

    Pls guide me.
    Thanks & regards,
    R Prashantha

    Like

    • R,

      I really do not do much work with Report Writer and have been using Crystal Reports to make modifications. I would recommend posting your question on one of the GP Newsgroups (see the Newsgroups section over on the left of this page) and hopefully someone there can give you directions on this.

      -Victoria

      Like

  115. Any insight with the Project Cost module by Olympic Systems within GP? I’m trying to reconcile the differences from the PC Project Model screen to the underlying tables PC10000, PC30000 and PC00752 and have a gap that is unaccounted for. Any assistance would be greatly appreciated.

    Thanks,
    Raul

    Like

  116. Hi Victoria,

    Question for you, I would like to do this attached to the Sales Order Processing screen I want to develop one more screen using Microsoft SDK toolkit (GP Addin) since I have more fields to save, please let me know what would be the best way to do this. Your early reply is highly appreciated.

    Thanks,

    Farooque.

    Like

    • Farooque,

      I am not sure that I completely understand what you’re looking to do from the brief description, but typically the easiest way to add an additional screen with fields is by using Extender.

      -Victoria

      Like

  117. Victoria,

    Thank you for including a link to our GP blog on your website. We have recently moved it. Could you please change the link to this URL: http://www.rosebizinc.com/gpblog/ when you get the opportunity.

    Like

  118. victoria,

    when i try to login as sa on the server first iam getting following error.

    error 1:

    Unhandled form exception:
    Cannot open form. Script terminated.

    EXCEPTION_CLASS_FORM_MISSING

    then i cliked ok button.

    then again i clicked ok button on login screen then iam getting following error.

    error 2:

    Unhandled script exception:
    Illegal address for field ‘Day of Week’ in script ‘Switch Company OK Button_CHG’. Script terminated.

    please advice me.

    thanks

    sharat mittakola.

    Like

  119. Victoria / Sharat,

    The way I finally was able to get it working was by deleting the OLE path from dex.ini file, log in to GP, then select an item in order to prompt me to select path for ole documents. Now I’m going to each workstation and repeating process. Thanks to both for all your assistance.

    Raul

    Like

  120. Victoria,

    I’m assuming GP and the database reside on the same server in these cases. We have GP on one server and the database on another, so does the path in the dex.ini file need to point to GP or database server?

    Raul

    Like

    • raul,

      with the solution what victoria given is working fine for me.yesterday i tried and it is working fine on every end user.

      my great plains and data base are on the same server but olepath is on different server.

      what is did is i created the share folder in the this folder i created notes folder in this notes folder i created another folder with name as data base company idn this folder i created OLENOTES FOLDER.

      I UPDATED THE OLEPATH OF DXE.INI FILE THEN IT IS WORKING GREAT.

      I THINK THIS MAY HELP YOU.

      THANKS
      SHARAT MITTAKOLA

      Like

    • Raul,

      Where GP client is or the database is should be irrelevant to the OLE path. The notes are usually on a file share somewhere on your network that all GP users have proper permissions to.

      -Victoria

      Like

  121. Victoria,

    I tried both options, using drive letter (e:\…notes) and UNC path (\\servername\…\notes) but the paperclip does NOT show up as an option when attaching note. We are running Gp 10.00.0872 which may be the cause of this issue but unable to confirm.

    Raul

    Like

  122. Victoria,

    Sorry, I misread your previous post and thought the default location was the default path. I will make the change to a couple of the users and test out. Will post results once I test after changes. Again, sorry for misinterpretation on my part. Thanks for your help,

    Raul

    Like

  123. Victoria,

    I checked the first group of users and all had the same path as listed below in their Dex.ini files:

    OLEPath=C:\Program Files\Microsoft Dynamics\GP\Data\Notes\

    Anything else I can check?

    Raul

    Like

    • Raul,

      Sorry, I may not have made my initial answer very clear. How can your users share the attachments when each is pointing to their own local C drive? :-) They should all be pointing to a shared location on your network, so the OLEPath should be either a mapped drive or UNC path. If you are going to use a UNC path, then make sure the correct directory structure already exists (click here to see a discussion thread with more detail on this). One other issue that may come up is that I am not 100% sure if simply moving the files from users to a share will keep those links working. If not, you may need to re-add the OLE links that already exist.

      -Victoria

      Like

  124. Victoria,

    Thank you, I will go around and view their path within the Dex.ini file and correct as needed. Will advise once changes have been made.

    Raul

    Like

  125. Documents are being attached to the customers but only the user that attaches the document (pdf, xls, doc, img…) can actually view the attachment when the customer is pulled up for viewing. We are trying to get all users to view the attachements whenever they pull the customer.

    Raul

    Like

    • Raul,

      It sounds like your users are not pointing to the same OLE notes directory. You can find where each installation of GP is pointing to for shared notes by finding the OLEPATH= line in the Dex.ini file. The default location of the Dex.ini file for GP 10.0 is C:\Program Files\Microsoft Dynamics\GP\Data\. The only way uses can share OLE attachments is if every computer has the same entry for OLEPATH= in the Dex.ini file.

      <em-Victoria

      Like

  126. Victoria, I have a situation that I’m trying to resolve with sharing notes/attachments within Sales / Customer Maintenance. Have you come across this or seen similar issues? Please advise of resolution for this problem.

    Regards,
    Raul

    Like

  127. Hi Victoria, I have a question (at the bottom) but first some background about me: I am a programmer of 28 years now with PMP and about 8 years of J D Edwards experience. I got my MSDN licence to learn Great Plains and will write the certification for GL, AR, AP etc fairly soon.

    I have developed a utility in MS SQL server that allows you to take a snapshot of all record counts for all GP tables, then you can go in and do something in GP (like enter a gl transaction), then take another snapshot, then you can list all of the tables that changed in the number of records.

    I am using this for learning, and thought it may be useful to others for learning, or debugging etc.

    The resulting list has the file descriptions, technical names, series etc merged in, eg CM40101 CM Transaction Type Setup CM_Transaction_SETP, which I have stored for about 2,200 tables.

    My question is: should I be able to publish this info for others on my website? It is to some degree Microsoft proprietary information describing the names and tables, but I can’t see that it would be a problem.

    Thanks – Dave – (relative newbie – not sure if this is the best way to contact you )

    Like

    • Hi Dave,

      Sounds like you have a very intesting tool there, I would certainly be interested in seeing it. However I am not sure I am qualified to answer your question about publishing it. I think a lot might depend on specifically what your utility is doing and how it is doing it. This is a great question to post on the GP public newsgroup to see if anyone there has more direct experience with something like this, or could possibly direct you to a Microsoft resource for answering this question.

      -Victoria

      Like

  128. Victoria
    Please note my email had a typo in previous post and I should mention we are on GP v10

    Sorry for the double post. Thanks

    Ron

    Like

  129. Victoria

    After a recent merger, I just intherited a webstore that dumps a file down to quickbooks for processing sales orders.
    We use Dynamics GP so of course I would rather have the file dumped to SOP. I know I can buy integration manager and import customr records, sales order invoices and payments from the delimted file that is generated by the web store. Is there a better product that does this without the hefty price tag of something like Nodus?

    Any guidance is appreciated!

    Ron

    Like

  130. Hi Victoria,

    we are using great plains 7.5

    problem :

    To issue out the material to the employees we are using invoice entry window.

    But when we enter the issue the items (item type : sales invetory ; valuation method :FIFO)to the emplyees it is not following the FIFO method(valuation method) it is just taking unit cost against the item.so for what amount is debited to the inventory account that much amount is not credited at the time of issued to the employees.

    Please Give me some solution.

    Thanks

    Sharat Mittakola.

    Like

    • Sharat,

      Of course GP 7.5 has not been supported for over 2 years, but I don’t recall anything being different about inventory costing in that version. The only thing that I can think of is to make sure you are using FIFO perpetual (as opposed to periodic). If you’re using FIFO periodic valuation, your items will be valued at their standard cost, not their actual cost.

      -Victoria

      Like

      • Victoria,

        Thanks for your reply.

        Yes we are using FIFO perpetual.

        The problem senario is like this.

        for example.

        item number :JUS-165-17145
        item description :CHAIN TROUGH, CAST IRON, A48-25B, DWG#158390MK-G
        item type : sales inventory.
        valuation method : FIFO perpetual.

        we made a PO of Quantity 2 at unit cost of 50.

        we received Quantity 2 at unit cost of 50.

        mean while my store room attendent or purchasing people changed the unit cost of the item to 45.

        when we issue out this material to employess through Invoice entry window and trx is posted.

        the item is issued out at the cost of 45 insted of 50.

        when i reconcile this item i am geting 10$ as amount in hand and zero as quantity on hand.

        please help me to resolve this problem.

        thanks
        sharat mittakola

        Like

        • Sharat,

          I just noticed in re-reading your first message that you’re using the Invoice Entry window. If so, all bets are off. Use either Sales Order Processing Entry or Inventory Transaction and it should pick up the correct cost.

          -Victoria

          Like

  131. Hello Victoria,

    I’m running into a problem with the Payroll Tax Update utility in GP9.00.0369 (round 4 tax update) on Vista machines.

    As soon as you select Help>U.S. Payroll Tax Updates>Check for Tax Updates,
    Error #1: “Cannot establish connection. Please verify that your server, login and password is correct.”
    After selecting the Automatic or Manual option additional errors pop up.
    Error #2: “An Error Occurs:3709, The connection cannot be used to perform this operation. It is either closed or invalid in this contect.”
    Error #3: “Requested operation requires an OLE DB Session object, which is not supported by the current provider.”

    I’ve verified .Net Framework 1.1 SP1 is installed. The ODBC connection is using the SQL Native Client driver to connect to the SQL 2005 instance.

    I’ve found a reference here that says the Payroll Tax Update Engine is not compatible with Vista: http://msdynamicsgp.blogspot.com/2007/01/weekly-dynamic-installing-gp-90-on.html

    Have you run across this before?
    Any official confirmation that the Payroll Tax Update utility is compatible with Vista?

    Thanks for any help!

    Like

    • Darren,

      Unfortunately, I do not have any first hand knowledge of this, sorry. Besides that post on Mark Polino’s blog that you are referencing (which is over 2 years old!), I have not been able to find any documentation that says the Payroll Tax Update Engine is not compatible with Vista, so I am not sure what to tell you. I would recommend posting this question to the Microsoft GP Newsgroup to see if you can find someone with experience on this.

      -Victoria

      Like

  132. Hi Victoria,

    I was wondering whether you could help me a little bit with an issue that I have with SmartLists and SmartList Builder.

    Background

    We are currently using the out-of-the-box Sales Transactions SmartLists (SL) with Extender fields. However, those are too slow and I want to replace them with my own SL which use SQL Views. The original Sales Transactions SL have the ability that when you double-click on a list item, it either opens the Sales Transaction Entry window or the Sales Transaction Inquiry Zoom window, depending on whether the document is open or posted.

    Problem

    I created a SL with SmartListBuilder (SLB) and it displays records quite fast as I intended. The problem is that when I’m programming the GoTo, I don’t know how to make that the right window, Sales Transaction Entry or Sales Transaction Inquiry Zoom, opens when I double-click depending on the status of the document. In the GoTo settings, I only see the option to open one fixed form, but no options for adding conditions.

    I was also wondering whether by calling a GP procedure, but I don’t see anything in the SDK that could do the job; unless I missed it.

    Do you have any ideas?

    Thank you,

    Héctor Herrera

    Like

  133. Hi Victoria,

    Yes. This table, SM00230, not exist in GP10.
    Stored procedure is a 3rd party routine which is done by previous programmer.
    And I have found the similar table for it.

    I can use table IVC10100. It has same structure and fields name.

    Hope this info will help others who have same issue as me.

    Many thanks for your support.

    Erik

    Like

  134. Victoria,
    It is nice site you have. And I got more information about GP. Because currently I’m using GP 10.

    I need to ask about a table that exist in Dynamics 7.5 but not in GP 10. It is table SM00230.
    What is the purpose of this table?
    Can I use another table in GP10 that have same meaning/function?

    Because I need to copy a stored procedure from Dynamics 7.5 to GP 10. But I got an error that table SM00230 is not exist in GP 10.

    Like

    • Erik,

      Glad you like my blog!

      I am not familiar with any out-of-the-box tables in any version of GP starting with SM. Unfortunately, I do not have any GP 7.5 installations that I can check easily, but it would be VERY unusual for GP to get rid of a table, typically they just add tables or columns to existing tables. Are you sure this table is not created by a customization or 3rd party add-on that you have installed for GP 7.5? What is the stored procedure that you’re trying to copy? Maybe that will give you a hint as to where the table came from.

      -Victoria

      Like

  135. Victoria, thanks for the prompt reply. The problem is that the service items in question are just that, services, for which we do not stock inventory or need to track quantities on-hand. If you make the item a Sales Inventory item type, the accounts are posted correctly, but you also get negative on-hand values. What is the work around or what is the best way to handle these items?

    Specific example: We sell e-mail spam filtering to our clients. We sell it for $1.99 per mailbox and it costs us $0.99 from our 3rd party provider. We have item “80001” setup for this service and assigned it a cost of $0.99. When we post a sale for this item on an invoice, we want the revenue to go to our “Revenue – Internet Services” account and the cost to go to the “COGS – Internet Services” account. If the item type is Sales Inventory, the transaction posts the right accounts, but it deducts from Quantity On-Hand creating negative On-Hand values. If the item type is Services, quantities are not tracked but only the revenue account is posted.

    So, is there a way to not track quantities on Sales Inventory item types? What is the best way to post the costs for the transaction described above? We don’t want to use a non-inventoried miscellaneous item number that does not exist in the item master file, because we have some reporting requirements that key off one of the user-defined category fields on the item record.

    Any other thoughts or solutions would be greatly appreciated…

    Like

    • Bobby,

      The short answer is that out of the box GP is not set up to do exactly what you want…which is a Service item that posts to COGS. If you set up these items as Sales Inventory, then all the accounting is done correctly automatically, which is a big plus. The drawback, as you mention, is the negative inventory. Since you have to pay someone for these services, why not enter them into inventory as purchased items before posting the sales invoices? That would eliminate the issue with the negative inventory. If this is not possible, for whatever reasons, then your other option is a customization that creates the additional GL distributions for these items as needed. The drawback to this, besides cost, is that you’ll have a customization you will need to document and pay to upgrade to future versions. The big benefit is that you can get the exact functionality that you want. Hope that helps.

      -Victoria

      Like

  136. Victoria,

    Question for you. We have a service that we resell from a 3rd party. So, the item is setup in inventory as a “Services” item type so it will not track quantities and we can assign the current cost from our vendor. When we post a sale for that service, the Revenue & AR accounts are posted, but the COGS account is not posted. How do we post the costs of service items (i.e. spam filtering, offsite backup, remote monitoring, etc.) that have an item type as “Services”?

    Like

    • Bobby,

      The only way to have GP automatically capture and post the COGS is to make this item a Sales Inventory item type. Services will not work because while it doesn’t track the quantities, it also does not track the COGS. Also, if you have a lot of these items being sold and purchased, then you might want to consider setting them up to track either serial numbers or lots. Otherwise, you’re going to end up with potentially crossed costs on different orders, even if you have the Sales Orders linked to Purchase Orders.

      -Victoria

      Like

  137. Victoria,

    I saw your blogpost “SQL view to show items with SOP POP Link”.

    Is this something that could be translated over to Business Portal via the Sales Reports section?

    Would we have to use MS ACCESS or Crystal Reports to carry it out?

    Like

  138. Hi,

    I am Do.
    I just found your website today.

    ex: price level by customer

    Customer A has price level 1 for product AA
    Customer has price level 2 for product AA

    However, for product BB, customer A has price level 2 – not rpice level 1, and customer B still has same price level 2.

    Is it possible to display different price by different customer ?
    Customer’s price levels are not fixed for each item. It depends on products as above example.

    I know fixed price level is possible, but not sure about this. Please let me have your opinion.

    dH

    Like

    • Do,

      It sounds, basically, like you have a different price list for each customer. If I am understanding you correctly, then you probably need to set this up by create a price level for each customer. So Customer ABC001 would have price level ABC001 and customer XYZ003 would have price level XYZ003. Then you need to create an entry for each price level and item combination. This could get very time consuming if you have many items and customers, but you could simplify this by importing the price list or copying it from another when only a few changes are needed.

      -Victoria

      Like

  139. Hi Vic,

    We have just implemented gp 10 in our company, we have to shipped multiple shipment from one fulfilment order is there any solution available to control the delivered qty.

    Thanx Khalil

    Like

    • Khalil,

      I am not sure you can do this from a Fulfillment Order, but I do think it is possible from an Order. The work flow of Sales Order Processing can be very complicated and for anything but the simplest of situations I would recommend that you work closely with your GP Partner to help you come up with the optimal work flow and set up based on your requirements.

      -Victoria

      Like

  140. Thanks Victoria for you help.

    Shauna

    Like

  141. Hi Victoria,

    I am a fairly new GP Consultant with a bank rec question and I saw some of your posts on the Partnersource community. I hope you have time to answer this. In the bank reconciliation manual it says
    “We recommend that you set up separate checkbooks for each Cash account so
    you can more accurately track payments and receipts. For example, you might want to set up separate checkbooks for payroll checks, credit card payments from
    customers, and payments to vendors.”

    I have a client that has a large number of trans per month, some about 25% cash/check and the rest credit card. I would like to suggest when the implement bank rec that they have a separate checkbook for credit card transactions and one for cash/checks. Is this practical? They would be tied to separate G/L accounts. Would this help or hurt reconciliation? Their problem is trying to get a handle on their credit card transactions.

    Like

    • Hi Shauna,

      Typically I would recommend keeping everything that comes on the same bank statement in one GP checkbook and one GL account. If the credit cards specifically are an issue and if you think reconciling them separately would help, I would recommend opening a new bank account and moving the credit card activity there.

      -Victoria

      Like

  142. hi victoria! my name is herson and working as a customer support consultant of dynamics gp. I receive a query from a client regarding messages they encounter during their year-end closing. I’ve tried to search this topic in the knowledgebase but there was none that talks about this issue. Here it is:
    1) An open operation on table ‘IV_Reconcile_PO_TEMP’ has an incorrect record length.
    2) An open operation on table ‘IV_Reconcile_PO_TEMP’ because the path does not exist.
    Their year-end closing processes continues after these messages but they, and me also, wants to know what could be the reason for this? Could there be any effect with their future transactions on inventory? Thank you.

    Like

    • Hi Herson,

      I am guessing this was during the inventory module year end processing? I have not come across this particular issue before, but based on the name of the table, it’s possible that the issue is caused by a time out or disconnection from the SQL server during the closing process. Click here to see a recent blog post by David Musgrave going into more details about this. The symptoms David describes are a little different than what your customer is seeing, however, so I cannot be sure this is the same issue. I would also suggest posting this on the Microsoft GP Newsgroup to see if anyone else has other suggestions for you.

      -Victoria

      Like

  143. is there a way to find out in sql what period in the gl invoice details were posted to?

    Like

    • Anna,

      Yes. There are a few ways to do this, so the answer will depend on whether you are posting to the GL in detail and what type of invoice. If you are posting to the GL in detail, you can get this information from the GL20000 and GL30000 tables. If you’re not posting to the GL in detail, you’ll need to go to the tables for the module the invoices are in.

      -Victoria

      Like

  144. Do you respond to questions? I have a smartlist builder smartlist set up as multicompany. Then I have a calculated field for Company ID which says db_name (). It returns the company ID for the company I’m in, rather than the company ID for the one matching the data. Is there a way to fix other than creating a lengthy SQL script?

    Like

    • Aimee,

      Besides bringing in the Company ID in SQL, the only thing I can think of is doing this in Dexterity, which I imagine would be much more difficult.

      –Victoria

      Like

    • Hi, Aimee:
      I had a similar issue with one of my customers. We designed a SQL view in each company database using the db_name() reference, then built a UNION view in their main database to join them all together. After that, it’s a simple step to make a smartlist from the union view. I hope this helps.
      Constance

      Like

Trackbacks/Pingbacks

  1. How to Implement Dynamics GP Properly...And So Much More - Dynamics GP Land - GP Technical Blogs - Microsoft Dynamics Community - February 24, 2011

    [...] 0 Victoria Yudin, a Microsoft Dynamics GP MVP and owner of Flexible Solutions, has written an amazing book, [...]

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

%d bloggers like this: