I have over 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 implementations, customizations and support. 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 and in 2013 I published an updated version, Microsoft Dynamics GP 2013 Implementation.
hi
Victoria Yudin 
Hi Victoria
After leaving a job a few years ago I’ve been away from using GP, but I just took a new position that has little to no reporting or operational output from their current system which is…GP. So I jumped back in and set up the ODBC connections etc, then I looked at the list of tables and realized I’d forgotten a lot of what was in each table, etc. Then I remembered your site and here I am. It is such a wonderful resource. Thanks for all the work and willingness to share it with us simpleton accountants who just want to run some reports that are outside the smartlist world.
Brian
LikeLiked by 1 person
Hello Victoria,
Just want to take a minute and say thank you for all the information on your site, truly so helpful!
Problem:
Had an inventory user that during receipt of purchase order, encountered some sort of interruption or window closing not done before trying to post. Which as a result, has yielded this particular PO as incomplete/unavailable.
There were two transactions for the receipt, one for 1500 yds populated the Inventory transaction while the second transaction to line 6 of the purchase order was not populated in the inventory transaction, nor the purchase order line. I suspect that the second quantity to line 6 was an open window not closed during the process when the user hit post to send the transaction.
In any case, we have the inventory tables updated with a quantity that is not available to match a purchase order receipt transaction for invoicing.
Any thoughts? Cheers.
Thank you,
-Kory
LikeLike
Hi Kory,
Thanks for your kind words.
One thing you could try is POP reconcile on just that one PO (to minimize the time) and/or Check Links on the Purchasing series, but in my experience often you will need to fix the data directly in the database when this happens.
Unfortunately these kinds of things are incredibly difficult to diagnose or give specific advice on without looking at all the data directly. There are just too many tables to check for different things and too many possible combinations of issues. Do you have a GP partner that can help you look at these things, or any available support incidents with MS?
Victoria
LikeLike
Thank you Victoria, unfortunately we already tried both of those potential solutions. Yeah, completely understand with the lack of data pretty hard to diagnose. We do have a GP partner that we’ll have to reach out to.
Thank you again,
Kory
LikeLike
Good luck Kory. Let me know if I can help with anything else.
-Victoria
LikeLike
Hello, Victoria. I’m not exactly sure where I should put this question, but it looks like others have reached you by posting a little something here, so I’ll give it a go, too.
Our Financial Manager is asking if there is a way in GP to take a bunch of old accounts that are no longer used and kind of “merge” them into one account so he doesn’t lose their history, but also makes those accounts inaccessible to users.
Not sure if you ever came across this question before, but have you any idea how that would be done?
Thank you!
-Jeff Morris
LikeLike
Hi Jeff,
This can be done with the PSTL (Professional Services Tools Library) Account Combiner tool, however, it will then look like it’s been the same account all along so you will not see the different accounts in history. If you want to keep the history for the separate accounts, you can just make them all inactive instead. Both are valid approaches that are used frequently.
-Victoria
LikeLike
Hi Victoria,
Like many here I’ve used your blogs for years and it has been extremely helpful and remained useful even in 2021. Thank you very much for all your contributions.
We’re moving off GP and onto Dynamics 365 (D365). Do you know of any equivalent blogs that go into detail on D365 tables, joins, etc?
Again, thank you so much.
Brendan
LikeLike
Hi Brendan,
Thank you for your kind words. I am not aware of a D365 blog similar to this one, sorry. If I ever start working with D365 I will blog about it, as well, but that’s not going to be in the short term as we are still very busy with Dynamics GP.
Good luck with your move!
-Victoria
LikeLike
Hi Victoria,
I have been referencing your blogs for years and want to let you know how much I appreciate the well-organized and easy to find information. What you have done for the partner and customer communities is invaluable. Thank you for stepping up and taking the time to provide this information. YOU ROCK!!
Be well,
Lisa
LikeLiked by 1 person
Hi Victoria: I was looking for a place to leave a general comment of appreciation and respect for the work that has gone into this site — available to all! Thank you! David
LikeLike
Thanks David, I really appreciate that!
-Victoria
LikeLike
Dear Victoria,
I have been going through blog posts searching for a solution to a pain-staking situation I am having with modifying the Receivables Management Cash Receipts Edit list in Dynamics GP.
We are trying to modify this report in report writer so it can serve as a Cash Receipt to be issued to customers. We have made significant progress with the design so far, the only challenge now is to convert the receipt amount to words in a calculated field and having that displayed on our receipt.
I will be grateful if you can share with us anything that could be of help.
Thanks so very much for your usual corporation.
Warm regards.
Joe.
LikeLike
Hi Joe,
I cannot help at all if you’re looking to do this with Report Writer, I gave up on that as a reporting tool a long time ago.
However, if you want to be able to create reports using Crystal. SSRS, and/or SQL/Excel and use them seamlessly inside Dynamics GP, we have an amazing product called GP Reports Viewer that can help with that. More at: http://www.GPReportsViewer.com/GP-Reports-Viewer
-Victoria</em
LikeLike
Hi Victoria,
Do you have an example of getting actual sales and forecast sales in a sql script for a list of products ?
My searches of your site have not quite joined the dots for me.
Regards
Peter
LikeLike
Hi Peter,
Dynamics GP does not have a way to forecast product sales. While I have seen companies do this, it is usually in different ways – some with custom code, some with Extender, some with other products. So there is really no way to write code for this that is generic, it would have to be custom for each situation.
-Victoria
LikeLike
Hi ,
scenario is like this
vendor master is created with payment term : net 30
purchase order is created with payment term : net 7
when invoice match is done it is carrying vendor master payment term.
instead of vendor master payment term how we can carry PO payment term on to invoice.
Thanks
Sharat
LikeLike
Hi Sharat,
I see the same thing when I test this.
I think short of a customization or Microsoft changing this in a service pack or future release, there is no way to address this. For now, users will have to manually change the payment terms on the invoice.
-Victoria
LikeLike
No questions here, just thanks for all the info and the help you give out. Stumbled accross your blog during an upgrade and you had the version info I was hunting for. Much appreciate what your doing here.
LikeLike
Awesome content here. IO come here all the time to try and find answers. I have one unresolved issue I was hoping you could help with. Is there a way to include unposted (working) entries in a trial balance. It shows up in transaction detail, but only posted entries seem to change a trial balance.
LikeLike
Hi Daniel,
The short answer is yes – if the data is there, it can be reported on. The longer answer is that it might take some effort and/or cost to do this depending on what trial balance you’re talking about (GL, AR, AP, something else?) and what reporting tools/resources you have.
-Victoria
LikeLike
Victoria,
Thank you so much for the information that you share. It really makes a difference.
LikeLike
Hi Victoria, i have seen your page and it looks great, Unlike other Microsoft Dynamics blogs, yours is the only one that brings really interesting things like sql scripts. do you happen to contribute something about Dynamics SL? Thank you
LikeLike
Hi Manuel,
Thank you for your kind words. I only with with Dynamics GP, no plans to work with SL.
-Victoria
LikeLike
Have been asked to provide the following data:
2004 – present
End of Year Revenue
End of Year Margin Dollars
End of Year Cos of Goods
End of Year Overhead Dollars
End of Year Net Income
Having difficulty finding the appropriate tables to make this all happen.
LikeLike
Hi Eddie,
There is nothing generic that will accomplish this for you – it’s going to be specific to your company, chart of accounts, and how you are using GP. For example, if I give you SQL code to calculate yearly revenue for my GP data, it will likely not be correct for your data. Often companies will have software that will pull in GP data to accomplish this, so you might also check into that to see if you already have something setup. Typically the best approach would be to work with your GP partner to help you get this accomplished.
-Victoria
LikeLike
Victoria,
LikeLike
Hi Bernardo,
My first thought on reading this was that you might not have installed SmartList Builder (or another ISV product) on the new computer. However, by copying and pasting the entire GP folder you’ve likely changed things to the point where you might not be able to correctly clean this up. And any of the things I would have thought to check are now not possible to check. If you have a backup of the folder on the new PC, prior to overwriting it with the one from the old PC, I would put it back.
Are you working with a GP partner? It may be a good idea to work with them to help you clean this up. They will (hopefully) know your environment better and will also know what products may be missing that are causing what you’re seeing.
While it’s certainly not brain surgery, there is no general set of instructions for installation that will work for everyone because every GP environment is different — there are many things that can be done in different ways and often there are additional products involved requiring separate installation steps. It’s also crucially important not to install what you do not need – that can cause even more problems than missing a component you do need.
So we typically do it one of two ways: (1) if the client wants to have instructions and will be installing GP on their own, we work with them and create specific instructions based on their GP environment for future installs or (2) if this is not a common occurrence or they do not have anyone internally to do this, we perform new installs for them. There is no right or wrong here, just whatever works best.
-Victoria
LikeLike
Victoria,
LikeLike
Hi Bernardo,
While this seems to have worked for you it is not something that I would recommend. For example – some of the products that you might have in your GP environment may require something additional – a DLL, a registry key, something else. Copying files will not register DLLs or create appropriate registration keys. The proper way to install would be to run the installer for each product.
So, while it looks like it’s working, you don’t know what will break or when if things were not installed properly. So again, I strongly discourage this. I will leave it at that – it’s your environment.
-Victoria
LikeLike
Victoria,
LikeLike
Victoria,
Question about Paid Transaction Removal in Sales. If we have a document for example dated in March 2019 and run statements the invoice will show for the end of March and this is all good. The problem we have is when the invoice is then paid for example on April 1 2019 and then we run the paid transaction removal process for March invoices.The April payment will not show on the April statement since it was technically removed during the March month end even with a cutoff date of March 31 for transaction removal. We do not want to have to wait until March is completely closed before posting April payments as our balances are not correct for cash etc… Is there a way around this or is that just the way it works? Hopefully I have explained it well enough. Please let me know if you need more clarification.
thanks!
LikeLike
Hi Bryan,
It’s impossible to really answer this because there are about 500 options on the statement window in GP and there may also be different results depending on transaction dates, apply dates, etc.
My 2 cents are that the issue is with the statement report and not the paid transaction removal process. What we do is use a custom Crystal report with SQL code behind it that shows what we want and nothing more or less. We use GP Reports Viewer (disclaimer: our product) to mass email these to our customers. I am not trying to sell anything here, just explaining what we do. Part of the reason we built special statement functionality into GP Reports Viewer was because so many GP users do not like the default GP statement behavior.
-Victoria
LikeLike
Hi Victoria,
I have a question that I can’t seem to get an answer to. We currently send out the statement report in GP for our clients and want to automate this more and create a similar report in SSRS. To do this we go to Sales –> Routines –> Statements. We then pick a predefined set of options and generate this for each client. I have had our GP partner look at this and they did some work, but couldn’t reproduce this statement. Any help would be greatly appreciated.
LikeLike
Hi Paul,
In my experience the out of the box statement report in GP is pretty awful. Is your already customized? Or are you trying to reproduce the out of the box one? In either case, it should certainly be doable. I have created dozens of different statement reports for various clients. We actually have pretty awesome statement functionality as part of our GP Reports Viewer product. It would let you use a custom Crystal or SSRS report and mass email and/or print statements to your customers. Let me know if it might make sense to discuss this in more detail and I will email you directly.
-Victoria
LikeLike
Hi Victoria. Greetings from Australia again. Hope you are well. I was wondering what you thought was the best option for getting a new company’s opening General Ledger balances into GP2015. We will be upgrading to GP2018 in a few weeks as well so I am not sure if it will be easier in GP2018 and worth waiting.
Cheers,
Josh.
LikeLike
Can I simply add directly the database eg GL10111?
LikeLike
Hi Josh,
I would not recommend this. There are other related tables involved so it’s not that simple.
-Victoria
LikeLike
Hi Josh,
Typically this is done by entering or importing a General Ledger transaction. should not matter if you do it in GP 2015 or GP 2018.
-Victoria
LikeLike
Thanks Victoria. Appreciate your help. 🙂
LikeLike
Victoria,
I need help with the Historical Aged Trial Balance SSRS report. We have combined multiple Manufacturing companies ionto one company and these old companies are now loctations within the new. How can I add the locncode field as a parameter to this report since it is using a stored procedure?
LikeLike
Hi Philip,
In my travels I have found that making changes to the out of the box HATB reports in Dynamics GP is incredibly complicated, if not downright impossible. I have seen others put ‘wrappers’ around the existing code then add their own code to it, but that’s extremely inefficient (performance-wise) and still not so easy. So I cannot help with that. What I typically do is create my own code/report to replace it, then I have a lot more control over the data.
Separately, not sure if you’re looking for AR or AP (or both), but in both of those modules LOCNCODE is not going to be a field present on all transactions, so you will likely need to have logic that is more complicated than simply adding it and using it as a parameter.
-Victoria
LikeLike
Victoria…I need your sage wisdom/advise…
(using GP2013) our COA was orig setup as XXXX-XX where seg2 was SiteID
over the years various accountants have used (and created) accounts with the understanding that seg2 was some kind of sub account so now we have a mess…
As the IT guy (not an accountant) my solution is to add -X (SiteID) to the end of current COA making it XXXX-XX-X (acct-sub-site).
Q 1, does that solution make sense?
Q 2, do COGs accts need to be site specific? (I am planning to break out IV accts per SiteID)
LikeLike
Hi Brad,
For most of your questions the answer is “it depends”. This really needs to be talked through with someone in charge in Accounting, as well as possibly your GP Partner, so that you can take into consideration ALL the related issues/products, both on the accounting side and the technical side.
-Victoria
LikeLike
As with most “dirty data”, the owner is the user.
As a 30 yr IT Manager, I have made it practice to assist users, but never own their problems.
Building solutions to accept data failures will always lead to what I call data apathy.
Document the problem
Fix the problem
Assist with the development of Standard Operiating procedures so it doesn’t matter how many Accounting people your company turns over they have a process already in place to follow.
LikeLike
Hi Victoria,
greetings from Australia.
I am using the Setup Checklist for a configuration document for our users to setup a new company. Do you know if the checklist data is stored in a table? I am new to GP.
Cheers,
Josh.
LikeLike
Hi Josh,
Looks like the checklist data is in SY40600 and SY40601 holds the status of any tasks opened from the checklist. Both are in the Dynamics database. Hope that helps.
-Victoria
LikeLiked by 1 person
Thanks Victoria.
LikeLike
Hi Victoria,
we have an issue with the AP Historical aged trial balance report if you could give us a hand with, and below is the detailed case
• We have 5 invoices for vendor (3012299) was booked on 2016 and applied in 2017 with a payment booked also in 2016
• We have generated the AP historical aging report as of 31/12/2017 and the report shows their still an outstanding transactions but the vendor have a zero balance as we generated the aged trial balance and its zero balance
What could be the cause of this issue and if you have faced this before
LikeLike
Hi Dana,
Are you using the out-of-the-box historical report? If not, there may be an issue with the report. If yes, there is likely an issue with the data, but this is impossible to really troubleshoot without looking at your data. I would recommend working with your GP Partner or Microsoft support to help you fix this.
-Victoria
LikeLike
Good Day Victoria.
We had been using Microsoft Dynamics GP10 since 2008 until December 31, 2016. However, due to the unstable security situation in Libya, and the person maintaining the GP10 was not available anymore, the management decided to acquire a locally developed Accounting System and used it instead of the GP10 starting year 2017 until today. Now, the management is planning to go back to GP10 starting next year 2019. The problem is: the last Fiscal Year set up in GP10 is year 2016.
Now my Question is:
Is it possible to set up Fiscal Year 2019 (skipping Years 2017 & 2018) and enter the new beginning balances of each account? The beginning balances will be taken from the Trial Balance as of December 31, 2018 which will come from the locally developed Accounting System.
LikeLike
Hi Ernesto,
GP will not let you skip years. You will have to create 2017 and 2018 to be able to use 2019.
-Victoria
LikeLike
Hi Victoria!, we have an issue with Dynamics GP 2010, where the Journal number was 64300 and suddenly we are working on 1200-1500. how can this happen and what are the reasons the system go back to 1200 sequence number and is it duplicate or what. and how can we fix this,
please help
LikeLike
Hi Dana,
Sounds like someone has changed the Next Journal Entry number on the General Ledger Setup window (Microsoft Dynamics GP | Tools | Setup | Financial | General Ledger). GP will allow using the same JE number as was already used in a prior year, that will not cause a technical problem, but it’s definitely something I would recommend avoiding.
I would figure out what the highest number used was and change the Next Journal Entry number to that number plus one.
I would also consider limiting access to the General Ledger Setup window to only people that actually need to have access to it.
-Victoria
LikeLike
Thank you for your quick response, I have used this script to check what are the JV numbers but unfortunately I found theirs duplicate numbers how did the system accepted this we thought its unique number.,
I have checked no one have changed the next number from the setup, what’s happened then? and how to prevent that from happening again
LikeLike
Dana,
There is no problem with having the same JE number as long as it’s in a different year. There are also special situations, like recurring and reversing JE’s, that will have the same numbers and could be in the same year.
For more details and help, I would recommend working with your GP Partner, who will know more about any customizations and imports you might have that could impact this. They can also look at your data together with you and recommend further actions.
-Victoria
LikeLike
Victoria,
is their a way to disable the JV number field from the modified window, but its required field
LikeLike
Good morning Victoria, I have been looking at your content for about two years now, and it was because of you, I understand the tables in GP – thanks. I am not experienced at SQL beyond select queries and have respect for performing deletes, etc. I have done delete queries academically but not in the office place.
If you are not able to help me, no problem at all, and I still appreciate you making your content available to me – thanks.
I am not able to delete the Stock Count Schedules in GP. From what I have researched, it appears they may be hung up and need the temp stock count tables cleared. Table IV10301 has my stock count schedule that has items in it, and I would like to delete it. Also, I have another stock count schedule with no items in it, and I am not able to delete it either (in GP). If I delete IV10301, will it rebuild/recreate itself when I create a new stock count schedule, and will this resolve my issue? Or, do I need to delete the records out of IV10301, and leave the table? If I need to delete the records only, is it a simple delete query, and will I mess anything else up that is potentially connected to this table?
There is also data in IV50200. Do I delete this one as well?
Sincerely,
Brigitte
LikeLike
Hi Brigitte,
I am very hesitant to recommend doing anything manually in the database, especially deleting data, without actually looking at it. I would recommend that you get some help from your GP Partner to look at this together with you and help you with the best solution.
-Victoria
LikeLike
Victoria,
I’m a huge fan and when I was new to GP you answered my silly questions and taught me a lot and for that, thank you so much!
I have another question for you today. I’m working with a company that uses Binary Streams Property Management add in. Does your company’s GP Reports Viewer work with this module? What we would like to do is mass emails or mass print offs of invoices that have outstanding balances. Example would be if a tenant paid half of their rent, we’d like a report to print off that shows their rent, the month (or date) of that charge, what they paid on it, and the remaining balance but for every tenant we have. Worth noting, our entire AR system is a complete nightmare and I’m completely unfamiliar with the AR module in GP due to module restrictions in my last role.
Thank you!
LikeLike
Hi Chris,
Thanks for your kind words!
I think we can definitely help with this. Please reach out to me at victoria@flex-solutions.com and we can set up a time to discuss.
-Victoria
LikeLike
Hi Victoria, We recently switched from QuickBooks to Dynamics and your posts have been a complete lifesaver!! They are so easy to read!
I was hoping to get your opinion on best practice. We are a services company and we do not currently use purchase orders. But we need to tie certain expenses to our customers. In QBs there is a field where you could put a customer name next to practically any transaction. Now I am lost as to how to tie purchases (or even journal entries in GL) back to a customer in the system. This specific example is an automatic ach each month, one lump sump, and we have the breakout between client. So we could transaction in purchasing or GL, but neither seem to work. We don’t need to tie it back to a certain sales invoice, just at the client level.
Thanks so much! Stephanie
LikeLike
Hi Stephanie,
Thanks for your kind words. There is nothing like what Quickbooks had in GP, however, there are different places to put additional data, depending on the specifics. Then, you just need a good report to tie it all together. There is really no “best practice” on this, as it is going to be completely dependent on the specific needs you have as well as your GP implementation. A few possibilities:
Multidimensional Analysis (MDA) – this is a quick and non-invasive way to add additional data at the GL level, however, only a few transaction types support it. Mainly Payables and GL. You mentioned both of those as possible entries, so this might actually be a good fit.
Extender – this can give you a way to add additional data to just about any window in GP.
With either of these, there are some built-in reports available, but it is possible you would need a custom report to show you the data you’re looking for.
I would recommend having a discussion with your GP partner about the details of this and see what they recommend.
-Victoria
LikeLike
Victoria,
You have a great website full of good information and I wonder if you can help me.
We use a custom order entry program that creates open orders in Dynamics with eConnect stored procedures taSopHdrIvcInsert and taSopLineIvcInsert and it works very well. Occasionally an order will have a mix of standard inventory and non-inventory order items such as special handling charges. If we want to delete a standard inventory item, we use taSopLineDelete with no problems, but if we attempt to delete a non-inventory item it does not get deleted and we get the following error code / procedure / message:
02263 / taSopLineDelete / Unable to update the existing SOP Header record
Can you suggest what might be going wrong? I experimented with deleting the line item from SOP10200 using SQL followed by a call to taSopHdrRecalc and it seems to work fine in our test system, but am afraid to implement this without knowing if other tables might be adversely affected.
Thank you!
LikeLike
Hi Paul,
I don’t work with eConnect myself, and I have not heard of this issue, so I cannot offer help on that specifically.
I will say that I would not recommend simply deleting records from SOP10200. There are many other related tables, including the totals in the SOP10100 table that will not get updated when deleting in SQL directly. If you are using taxes, commissions, etc., all of those tables will need to be recalculated, as well. If you find that is the only way to accomplish this, then you would need to go through all the other SOP tables and account for possible changes in each.
Another option to address this might be to not have non-inventory items. For example, if it’s always special handling charges, why not create that as an inventory item of type service or misc. charge? That way SOP will consider it an inventory item, even though it’s not tracking quantities.
-Victoria
LikeLike
Hi Vicrotria,
Your posts and replies are really helpful. I had a quick query. I wanted to understand if there is a way to restrict negative values to be entered in the sales transactions / invoice creation process.
We want to ensure that the system does not allow the users from entering any credit notes or negative invoices in any situation.
Hoping to get a solution on this.
Regards
Kandy
LikeLike
Hi Kandy,
To my knowledge this is not possible without a customization. You can restrict what ‘documents’ someone can create in Sales Order Processing, so using Dynamics GP security you can restrict users from entering a SOP Return, but that’s about it. Entering a negative number on an invoice or entering a Receivables credit memo or return cannot be restricted in the out-of-the-box Dynamics GP.
That said, some would say that if your users should not be doing this and they are, that is not just a system problem. Why should you have to create a system where it is absolutely impossible for users to do something you don’t want them to do? Why is it not enough to train them properly and expect them to follow training and instructions? 🙂
-Victoria
LikeLike
Victoria,
I just wanted to stop by and say Thank You for this wonderful site. It is bookmarked, and I usually visit daily. I appreciate all you do!
LikeLike
Hi Victoria
This blog has been very helpful for some SQL Views I have a question in the inventory view is there any view that can see the profit margin for each item of inventory?
Regards
LikeLike
Hi Ramon,
The short answer is it depends. Every company uses Dynamics GP differently. Data is entered differently, workflow is different. To be able to answer that question someone would have to work with you and look at your data to determine how profit can be reliably calculated with your specific GP setup and use.
-Victoria
LikeLike
hi Victoria,
Very Informative site! Quick question, when I make a change to a Smartlist In GP 2015 the changes are reflected in the Live and Test co, is there a way to separate that? I would like to be able to us my Z test company, to create and test some smartlists without reflecting these changes in production. thank you!
LikeLike
Hi Natalie,
The general answer is that SmartLists are global to all companies. So typically no way to separate changes.
If you let me know specifically what you are doing and what kind of changes you are making I might be able to recommend some workarounds. No promises.
-Victoria
LikeLike
ok, good to know.
thank you for responding so quickly!
LikeLike
Victoria, are you currently consulting with a firm? if so, would it be possible to send over your contact information?
LikeLike
Hi Natalie,
I own a Dynamics GP consulting company called Flexible Solutions. Here is the link for the website: http://www.flex-solutions.com.
-Victoria
LikeLike
Great! Thank you.
I’ll contact you through the site.
Thank you!
LikeLike
HI Victoria,
I am looking for Ledger balance report. I saw you have posted view_Open_Yr_GL_Trial_Balance.sql that is trial balance each month.
what I am looking for full ending balance. do you have the related post?
Thanks,
Thomas
LikeLike
Hi Thomas,
I have a similar view with the month end balances here. Hope that helps.
-Victoria
LikeLike
Hi Victoria,
I really appreciate your blogs – they are well laid out and always have useful information. Thanks for writing!
I have an issue I’m hoping you might have some insight on. I am trying to setup email for our sales documents, particularly invoices, but keep receiving the message “A Word template must be assigned before sending this document”. I’ve read many posts, tried many things and I’m still getting this error. We had no issues setting up email for our purchasing documents.
I have assigned the Word templates under Template Maintenance and Template Configuration (checked “Enable all templates for all companies”). I have also ensured that the format is set to “Blank Paper”. Customer email detail is setup as well. In the transaction window, or through batches, the option to email is there but it always fails with the Word Template error. Any ideas on what else I’m missing?
LikeLike
Hi Roxanne,
Thanks for your kind words. I am of the opinion that Word Templates were a really terrible idea and are incredibly difficult to work with, so I do not use them. Sorry I cannot help you with this. Maybe if you post your question on the Dynamics GP Community Forum someone there can help you.
-Victoria
LikeLike
I agree, the word templates are horrible…a bad idea. for a purchase order report, I just created one in ssrs, and purchasing loves it.
LikeLike
Hi Victoria. I was wondering if it is possible to calculate + 2 business days on a user defined field in SOP entry with another field also in SOP entry UDF? I have essentially a start ship and cancel ship date requirement and I am using two date fields in the UDF area of SOP entry.
LikeLike
Hi Ralph,
This would require a customization.
-Victoria
LikeLike
Hello Victoria,
I’ll join the many people here thanking you for your amazing contribution to the GP community. I’ve relied on your solution more that care to remember and I’m back again for more. I could not find this on your blog, so I will ask the question here.
We are running GP 2015. The finance team would like to archive (move to history) all the past sales transactions via Sales Transaction Removal, or PSTL tool, however, we need to retain the ability to print a full customer statements which shows all historical as well as open transactions. Talking to MS support they confirmed that the built-in statement report does not pull records from history tables.
Do you have a query that would do that, or another suggestion on how to go about it?
Thank you in advance!
LikeLike
Hi Mario,
Thanks for your kind words.
Just to confirm – do you mean you want to use the Paid Transaction Removal routine to move paid transactions from the open tables to the history tables?
In that case, since the GP out of the box reports won’t work, my suggestion would be to consider using our Dynamics GP add on, GP Reports Viewer. With it you can create custom reports to show whatever data you want. This is true for statements, as well as other reports.
-Victoria
LikeLike
Hello Victoria, first of all, wish you a very happy 2017, full of family and work successes.
I am writing to thank you and declare me an admirer of your valuable contribution to the consultants and professionals who make a living with GP.
From Chile many thanks for all your help and great willingness, people like you make life better.
LikeLike
Hi Victoria. I always find great information from your blogs. Thank you so much. May I ask a question? I am looking for a SQL script that will give me the YTD amounts paid for Employer Paid Health Benefits and the EE Health Deductions by EE. I’m thinking that someone “out there” must have this to run against the ACA fields needed on the W2. Maybe there is an easier way?
LikeLike
Hi John,
Sorry, I don’t have anything for this. Maybe posting on the GP Community forum will help, there are some sharp folks on there. Here is the link: https://community.dynamics.com/gp/f/32.aspx.
-Victoria
LikeLike
Victoria, as the year ends just wanted to say thanks for your page and sharing your knowledge with all of us…Just today had a request from a customer for a SQL script that I was scratching my head on and looked here and low and behold you had 99% of it done…add one field take 10 min and bill an hour. Thanks!!
LikeLike
Dear Victoria,
I am a great fan. Your GP implementation book was very helpful when I was learning how to set up new companies.
We are now trying to perform integration with custom made web applications that we have made, and would like to know your recommendations regarding the most suitable tools to implement it.
I was trying to find out whether there’s a way to directly call the stored procedures in the DB to trigger events, which will be a lot easier than using eConnect, but I couldn’t find any reference for using this, do you think that is possible? and how?
Thank you!
LikeLike
Hi Hassan,
I have been involved in numerous integration projects and I usually recommend eConnect. Only if you cannot do it with an existing tool would I recommend custom code. While it may seem easier at first to call stored procedures directly, I don’t believe it is the best approach in the long run. This is because you would be bypassing a lot of the business logic build into GP/eConnect and to duplicate the functionality will actually require a lot more troubleshooting and testing.
-Victoria
LikeLike
Thanks Victoria for your response. We will use SQL then, and it really is no big deal to use it, I was just wondering if it was absolutely a requirement. Thank you again for your response, and have a great weekend
Alex
LikeLike
Hi Victoria, I have read your blog posts in the past and have found much useful insight into GP over the last few years. This website is a great resources.
A quick question: I understand that Microsoft Dynamics GP 9.0 can be installed on a computer without needing SQL to be installed? Once GP 9.0 is installed, how does one log in or set it up so they can log in if they are not using a SQL server setup?
Many thanks for your assistance in advance.
Alex
LikeLike
Hi Alex,
All versions of Dynamics GP in the last 12 years or so require SQL Server. SQL does not have to be installed on the machine where you are installing Dynamics GP, but it has to be on the network and available for computer where you are installing the Dynamics GP application to connect to.
-Victoria
LikeLike
Good morning, I am hoping you can help me. I am not sure if this is the right place to ask this but I am having an issue with Management Reporter and Unit accounts. Frx did not count unit accounts as financial values but MR does and I cannot figure out a way to exclude them from my results. I hope you can help as I cannot find a resource anywhere that is helping me resolve this issue and not have the unit account inflate my numbers.
Thank you so much in advance,
Bryan
LikeLike
Hi Bryan,
I am not aware of anything about the treatment of unit accounts that is different between FRx and MR. I suspect that maybe your report is created differently in MR and is thus including Unit accounts based on the account numbers/segments. Or maybe you have new unit accounts that are inadvertently being captured by your report. I typically recommend that unit accounts are created with completely different natural accounts than regular/posting accounts so that they can easily be separated.
For more help, I would recommend posting your question on the Dynamics GP community forum, where there will be a lot more people with specific experience on this.
-Victoria
LikeLike
hi I really enjoy your page! I have found it very useful in finding information to utilize gp 2013 for our business. Do you have any blogs on AA Tables and how to better utilize AA in a smart list or any other tips. I use AA to track our projects and all though its not the best for our business i’m trying to use what we have the best I can. I have been trying to run queries in gp and externally and I am very frustrated. Thanks 🙂
LikeLike
Hi Jennifer,
Thank you for your kind words. AA is not something I work with or recommend, partly because of the issues you’re describing. So I do not have any blog posts about it, nor are there any planned. Sorry. 😦
-Victoria
LikeLike
Hi Victoria,
First of all…thanks for such a great place for GP knowledge! =). Secondly, we have a need to change some of our inventory parts from MFG BOMs to GP Kits. we understand that we cant just convert them, but we also need to use the original part number for the GP kits we are going to create. we have decided to change the original part numbers to something different and then re use the original part number when we create the new GP kit for the item. do you have any thoughts on the best way to to this? can we just change it in the GP interface somewhere or will there need to be SQL scripting involved? I am also asking our partner for assistance but I was wondering what you thought. thanks!
-jon
LikeLike
Hi Jon,
You should be able to use the free Item Modifier Tool (part of the Professional Services Tools Library) to change your existing item numbers to allow re-using the original part numbers.
-Victoria
LikeLike
awesome. i will look into that. thank you!
LikeLike
Hi Victoria,
Firstly your site has been a god send for me. I have inherited a GP installation and I am tasked with integrating all RM transaction into my system being Salesforce.
The question I have is that I need to add a column into a table but I am assuming this is possible but not advisable due to updates/upgrades of GP’s core. So can I clone/copy a table then add my column?
What I need to achieve is a last modified datetime column in the RM20101 table because the fields I have already do not give me any accuracy in record modification which in turn will make my ETL processes hugely inefficient for processing records, coupled with the fact that the business itself is not processing with GP process logic paid invoices will remain in the open table for a very long time and will not be commited to the history table.
Now I have looked at the last edit field however that doesn’t seem to react as I thought it would do and I have many records that are showing the GP binding date 01/01/1900 as the last edit date and I have got finance to make multiple edits on a particular record.
Any suggestions would be grateful.
Regards
Stefan Taylor
LikeLike
Hi Stefan,
Adding a column to GP tables is not supported and strongly discouraged, as you suspect. It will most likely cause the next upgrade or even service pack to fail. If it doesn’t it will likely remove your changes anyway. An alternate approach may be to simply create a custom table that hold the one new piece of data you need. So for RM transactions you need the DOCNUMBR and RMDTYPAL columns, then your new column for the date. Then you can use this for any reporting/other coding you need.
-Victoria
LikeLike
Hi Victoria,
I created my new table with the fields you suggested DOCNUMBR and RMDTYPAL columns and i called the table RM20101_Test but it doesn’t pull any data into it – i am sure i have missed something very simple.
What have i missed, do i have additional settings i must config in GP directly?
Regards,
Stefan.
LikeLike
Stefan,
What do you mean “it doesn’t pull any data into it”? Where would it pull data from and how? The idea is that you would populate that table yourself with the data you want to store in it. You could either do this as part of the import that you are performing or with a SQL trigger on the RM20101 table.
-Victoria
LikeLike
HI Victoria,
I totally understand now and thanks for the quick response.
Regards,
Stefan.
LikeLike
Hello Victoria, quick question. I have a customer who inherited some GP databases from an acquisition. I already migrated the Vendors, and they are just curious if there is some way to migrate the AP history from one company to another cleanly. I know we could re-post everything, but with applying payments, etc I would imagine that is quite difficult and time consuming.
If there is no way to migrate data, then I was considering somehow exposing that data by doing a SmartList from another database or something similar.
Please let me know if you have ever heard of someone migrating that kind of data
Thanks
LikeLike
Hi Devin,
I have done this more than once in the past by (a) creating custom reports/SmartLists and/or (b) creating custom tables in the live GP company to hold whatever data is needed. Depending on how you are doing the reporting, you might need (b) to avoid cross SQL instance reporting issues.
-Victoria
LikeLike
Victoria,
First of all I need to say again I appreciate your site and all the help you have provided.
I am running into an issue with a migrated database where they did not have Fixed Assets setup and now it is in an environment where FA is there, and I am getting errors on FA tables when attempting to input an invoice and if I try to setup FA, even as default, I am getting errors.
I attempted to run SQL maintenance on the company in question, but I initially messed up and performed SQL Maintenance on FixedAssets for Dynamics… I did not take a backup of Dynamics, I backed up the company I thought I was re-building
When I ran the rebuild of FA objects on the actual companies, the fix worked.
I know there are limited tables related to FA in Dynamics. The only one that has any data in it from a recent Dynamics backup is the ACRS table. They are not using ACRS in the other companies where they are running Fixed Assets
How concerned do I need to be about restoring a recent copy of Dynamics? Can I just copy the FA tables from a recent backup into production?
Everything about FA appears to be operating normally.
Thanks
LikeLike
Hi Devin,
This is not something I have gone through before, so I am not sure I can answer this, sorry. You might want to talk to GP Support or post your question on the GP Community Forum in case someone else has had this particular experience before.
-Victoria
LikeLike
I have a SOP30200 table that is not very well organized. We have different types of orders coming from multiple entities. There was a design made prior to me joining the company that decided that all the entities would share the DOCID of ORDER, but that the integrated order would start with a digit assigned to that entity. Problem is this was only sometimes followed and as a result has become very difficult to track and report on. Its also very limiting since we are growing and we are adding entities, and our current model stops at 9 options (1-9). My solution is to change our approach to start using the DOCID to separate entities (where the order originated from). But to really make this work going forward, I’d like to make a one time update to the DOCID field in SOP30200. Doing this would allow us to simplify reporting. So we can simply query based on DOCID = ‘EBAY’ vs. SOPNUMBE LIKE ‘9%’ and SOPNUMBE NOT LIKE ‘%MFG’ and etc… it gets uglier, trust me.
I have 15 years working in GP development so I’m not green by any means. I am pretty sure updating the DOCID on historical documents will have no effect on business rules anywhere but I was hoping for some additional reassurance. Any thoughts?
LikeLike
Hi Jason,
I have never changed DOCID’s on historical transactions before…so I cannot with 100% confidence say that won’t break anything. Because of the complexity of the SOP tables and logic, I really try not to touch those unless absolutely necessary.
If you’re only using this for reporting, which will likely need to be custom, another option might be to create a new custom table to hold: SOPTYPE, SOPNUMBE, DOCID. With the DOCID being what you want in this new table. Then your reporting for all transactions can be based on the following logic: if it’s in the custom table, use that DOCID, otherwise use what’s in SOP30200.
-Victoria
LikeLike
Hi Victoria,
I am an software architect at an e-commerce startup. We recently merged with a company that uses MS Dynamics GP 2010. Your blog/site is best source of information on GP and has helped me a lot to get familiar with the product. Thanks a lot. I had a quick question and wanted your advice on that. There is a lot of information we currently capture on spreadsheets which doesnt go into GP. The information thats captured could help us tie sales orders to purchase orders and give us end to end visibility to the entire process. Our plan was to load the captured information into custom database tables in the GP SQL Server schema. I wanted to know if this a standard practice for companies to store information that cannot be captured using GP forms? Thanks for your help.
Vijay
LikeLike
Hi Vijay,
Yes, if all you need is to store the data for reporting and the users do not need to interact with it, storing it in custom SQL tables is ideal. I have done this many times.
On the other hand, if you want the users to be able to enter/change the data directly in GP, you might consider Extender – it’s an add-on for GP that will allow you to create additional windows in GP to store this kind of data. I have also used this many times. It’s more user friendly, however, reporting on Extender tables is a little more cumbersome. And if you plan to upgrade to a newer version of Dynamics GP, then the tables will change once you upgrade past GP 2010, so that needs to be taken into consideration.
-Victoria
LikeLike
Hi VIctoria,
Just got done reading your book. I really enjoyed it. I was wondering if you had some additional books that covered Manufacturing and MRP? I did not see any mention of them or BIll of Material in your book.
Thanks,
Ralph
LikeLike
Hi Ralph,
Thanks for reading my book! I don’t have any content covering Manufacturing, MRP or Bill of Materials. And I am not aware of too much content out there for this. This is about the only thing I can think of: http://www.accoladepublications.com/gp-category1/12.html
-Victoria
LikeLike
Hi Victoria,
I am looking to become a Dynamics GP partner. I have yet to find a clear answer as to how to test for it, or pay dues for the ability to do so. What do I need to do?
LikeLike
Sorry, I don’t have an answer for you, since this changes all the time. I would recommend calling Microsoft at (800) 456-0025 to see if they can help.
-Victoria
LikeLike
Hi Victoria,
I am trying to run a report in GP 2010 to pull all orders with a specific shipment payment type..i.e. third party, prepaid etc (in the fulfillment options). Any suggestions?
Thank you,
Amber
LikeLike
Amber,
Where is the “shipment payment type” entered? I am not familiar with that terminology, maybe it’s called something else in GP? Or is this something custom that you’re tracking?
-Victoria
LikeLike
Victoria, Do you know of any place where I can get an good sample of an eConnect XML file OUT for creating a Vendor? I need the actual example file. This is not included in the samples that are installed with eConnect.. Do you know of anywhere I can get this?
LikeLike
Hi Devin,
Sorry, I am not aware of anywhere you can get this. I would recommend posting your question on the Dynamics GP Community Forum to see if anyone there can help you.
-Victoria
LikeLike
I set up a user with only rights to view inquiries and see some reports. When I log in as that user, I see access to Transactions>Purchasing>Manual Payments. I verified I can actually enter a manual payment. I’ve verified the security permissions of this user multiple times. Printed User Security report, which matches what I entered. Any idea why this user can enter manual payments?
LikeLike
Hi Guy,
Have you tried running one of my scripts to see all the security details? Like this one? That might help narrow down what is going on.
-Victoria
LikeLike
Hi Victoria,
We are using Microsoft Excel to pull data directly from GP 2015 tables. I’m trying to create a check information report from the PayablesTransactions table. I was able to pull in all the data I need except the G/L distribution on the Invoice. I looked through the table and I don’t see any fields that have the G/L distribution listed. CAn you please help: My query in excel looks like this: Does the Payable transactions table contain this information? Or do I need to do a join with another table?
/*PayablesTransactions Default**/
select
[Voucher Number],
[Vendor ID],
[Vendor Name],
[Document Type],
[Document Date],
[Document Number],
[Document Amount]
from PayablesTransactions
Where
[Document Type] = ‘Payment’
and [Document Date] > ‘3/31/2015’
order by [Document Date]
LikeLike
Hi Justin,
You need to add a lot more data…you have to first add in the invoices that the checks were applied to, then get the GL distributions for those invoices. I have a view that does this already: Payables apply detail and GL distributions
To use it in Excel you would probably want to pretty up the column names and maybe take out any columns you do not need.
-Victoria
LikeLike
Hi Victoria,
Is there an easy way to see in Dynamics GP the Bank deposit where an AR cash receipt is on? via smartlist? inquiry? via a view?
thx
Raymond
LikeLike
Hi Raymond,
You should be able to use the Bank Transactions SmartList for this. A cash receipt will show up with a Source Document of RMCSH. You can add a column for Deposit Number to see what deposit each cash receipt is on.
-Victoria
LikeLike
Hi Victoria,
I’m trying to build a detailed Aging Smartlist with Aging Buckets in GP 2013. I was using the information from your post in the community back in Sep 2010:
Victoria Yudin responded on 27 Sep 2010 12:33 PM
SmartList out-of-the-box will not give you a detailed current aging, only summary. The good news is that this is pretty easy to get from the tables. The data for all unpaid AR transactions is in RM20101 – filter on CURTRXAM 0. I also typically link to the RM00101 table on CUSTNMBR to get some additional customer details. The other critical piece is the type of transaction (RMDTYPAL) in RM20101. From https://victoriayudin.com/gp-tables/rm-tables/:
Add these:
1 – Sale / Invoice
2 – Reserved for scheduled payments
3 – Debit Memo
4 – Finance Charge
5 – Service Repair
6 – Warranty
And subtract these:
7 – Credit Memo
8 – Return
9 – Payment
Victoria Yudin – Dynamics GP MVP (2005-2015)
However when I go to the CURTRXAM field and select negative values RMDTYPAL is not an option to select to create a negative value. If you could help me understand what step I am missing that would be very helpful.
LikeLike
Hi Rachel,
I am not quite sure what you mean by selecting negative values of RMDTYPAL, those will never be negative. However, why not use something like my Current Receivables Aging Detail view as a start? You might be able to just use it as is, or slightly change the aging buckets to match what you need.
-Victoria
LikeLike
Good afternoon, Victoria. I have a question regarding GP that is quite vexing, and I wanted to try to gain some understanding of the 1099 process.
In a nutshell, the amounts that are generated by the 1099 Edit List and Print 1099’s are always off. The client recently upgraded to GP2013 but has had problems with 1099’s all along and has relied on a SmartList query and manual adjustments to get the amounts right. Client never understood why.
I did some detailed analysis of the data, and it APPEARS that when you run 1099 Edit List or Print 1099’s, the “Year” it is using is going from 12/23 – 12/23. So for example, a payment made to a 1099 vendor on or after 12/23/14 would show on the detailed query of 1099 payments but would be excluded from the amount showing on the Edit List. Conversely, a payment made on or after 12/23/13 shows as 2014 Fiscal year on 1099 Edit List.
This is very strange behavior in my estimation and it leads me to believe that somewhere in database there is some other date or calendar or setting that the 1099 Edit List process is using that is wrong.
We straightened things out for 2014 so this is not critical for now, but the client would like to be able to rely on the numbers that the canned 1099 process is generating.
Two questions: Have you ever heard of this? AND where can I find the Stored Procedure or Code that produces the 1099 Edit list, just so I can take a look at it?
Thank you in advance for any advice or help you can give me.
Sent from Windows Mail
LikeLike
Hi Devin,
That does sound very strange and I have not seen anything like what you’re describing before. The only thing I can think of is to check the GL dates on payments. I believe the 1099 logic uses GL dates, not Document dates. Typically the GL and Document dates are the same on transactions involving cash, but might be something to double check before you start further investigation.
I don’t know what code is called to calculate the 1099 Edit List, sorry, you could possibly get that by running SQL Profiler on it. But I am not sure if the issue is actually with the 1099 Edit List code – I think that’s simply pulling from the 1099 summary table (PM00204). Maybe there is a problem in how that table is populated? Are there any customizations or modifications in place that might be changing how the data gets populated when transactions are posted?
Does your client have a test company/environment where you can duplicate this behavior? If so, I would recommend talking to Microsoft Dynamics GP Support about this so they can help you get to the bottom of this. If you can duplicate the behavior of a payment on 12/31/2014 going into the 2015 1099 total instead of the 2014 1099 total, they should be able to help you track it down.
-Victoria
LikeLike
Victoria, No, there are no customizations that would cause this to happen.
I have done a little digging in the PM30300 table, and there is a field called Date1 and GLPOSTDT
In every case where there is a discrepancy in the amounts of 1099, the Date1 field is going into the next year. Date1 almost never matches GLPOSTDT, but I don’t think it is a coincidence that all the payments with this difference are the ones where there is a discrepancy. Is the 1099 year being calculated using Date1??
For example
GLPOSTDT 12/23/14 DATE1 1/06/2015
I don’t know how this date is populated, but I would like to get more information about it before I take this to GP Support.
Thanks
Sent from Windows Mail
LikeLike
Devin,
WOW – great find! DATE1 is the Apply Doc Date. I just did some quick testing and you are absolutely right – that’s the date that drives the 1099 year. Not sure if that’s correct according to the IRS, that would be an interesting question for someone…not exactly sure who.
That said, in my experience most AP payments will be applied on the same date they are made. Is your client routinely making payments, entering them as unapplied in GP, then applying them later than the date of the check? (Or entering an apply date later than the check date.) That’s pretty unusual. If that’s what’s happening, is there a valid business reason behind this?
-Victoria
LikeLike
Charles Allen has an article about the Apply Date that explains the effect on 1099’s. http://www.dynamicsgpinsights.com/2012/02/06/this-date-applies-to-you/
LikeLike
Thanks Bill – that’s really helpful!
-Victoria
LikeLike
That is excellent thank you
Sent from my Windows Phone ________________________________
LikeLike
The client has been modifying the Apply Date since the beginning of time when building a check batch, because they are always trying to capture things that are due within the next 8 – 10 business days, and they just did not understand what they were doing when modifying the Apply Date. To make matters worse, at year end they would always be in a mode where they were looking for invoices due for the next 14 days or so, so they would tidy up year end AP balances, so this would ALWAYS throw off the 1099 balance every year, and they were always left scratching their heads about why it was off.
Now they understand, and are going to leave the Apply Date alone.
Is it possible to manually modify the Apply Date on existing transactions so that the transactions from the end of 2014 will not show on the 2015 1099’s when it comes time to generate them?
Thanks
________________________________________
LikeLike
Hi Devin,
Yikes. Good to know I can still be surprised by stuff like this. 🙂
I’ve not done this manually before, so I am not 100% comfortable recommending manually modifying the apply dates without a lot of testing. If you decide to do it, I would think you’d need to update at least the following: DATE1 and GLPOSTDT in PM30300 (and PM10200 if any of these are not fully applied) and DINVPDOF in PM30200 (and PM20000). One problem I can think of is that the summary 1099 table will still be off, as changing these dates in SQL does not update any summaries. You can try reconciling the 1099 amounts, not sure if they will get updated properly, but it would be a good test.
An alternative might be to simply fix the January 2015 1099 totals to what they should be now. Of course, if they ever run a reconcile, they numbers will revert back to the wrong data. This might still be better than messing with the transactional data in SQL – I try to avoid that if I can.
-Victoria
LikeLike
Victoria,
I am working on a problem now and researching diligently but If you have come across this, I would welcome your input.
GP 2013:
Open Voucher, and the process of applying a Manual Payment to that Voucher got interrupted by an error. “An open operation table ‘coPeriodCache’ has filled the disk.” so even though the Payment was showing as unapplied, the voucher went into a state where it was showing OPEN but nothing was available to Apply. Unfortunately, one of the troubleshooting steps that was taken was to run Check Links (without doing a backup) which marked the Voucher as HIST, but there is nothing applied and the Payment was still showing as OPEN. Customer deleted the unposted Payment, because as of this moment there is no way to record that payment and have it applied to the Voucher. Attempted to remove the Historical Transaction through Utilities and received an error “unable to remove this transaction and open apply record exists”
I am pretty sure I am in a territory where there is some sort of backend database edits that I need to do, but I am not the least bit confident in what that might be.
Is it possible that I can just move the Voucher out of PM30200 and back into PM20000 with a SQL query and then re-enter and apply the payment?
I understand you are not a support mechanism, and I don’t want to take advantage of your good nature, so please answer only if you are not terribly tied up on something else.
Thanks
Sent from Windows Mail
LikeLike
Devin,
I am not sure if I would attempt to “move” the transaction from PM30200 to PM20000, as there are a lot of related tables that would also need to be updated and it might actually cause even more problems if not done exactly right.
I am thinking at this point you might want to get Microsoft support involved to help you with this.
Sorry not to be of more help,
-Victoria
LikeLike
Thank you, I will speak to the client.
The other thing i discovered while trying to find out what was going on, was that the 1099 reconcile process in GP2013 PSTL is putting the entire document amount into the 1099 Amount, even though i found tons of documentation that indicated it was not including Miscellaneous and Freight. According to IRS Rules, only the Purchases Amount should be included in a 1099. I had to run this process because we had an eConnect invoice interface that was not properly dealing with 1099s. I find this odd that it is incorrectly setting the 1099 amount, but hopefully i will not have to use it again once i straighten out all my issues.
Thanks so much for your quick responses…
________________________________________
LikeLike
Hi Victoria,
I am trying to retire my old SQL 2008 server and replace it with a new SQL 2008 server (exactly the same versions). Anyway, we are using GP 2010 and i need to move the SQL logins over to the new database.
I ran the scripts from microsoft as per the below, which generated all the users on the new SQL server without an issue, however when i try to login to GP on this new server, the username/password is wrong. If i try to reset this in SQL, i am still unable to login? Any advice/suggestion as i have a lot of users i need to move as part of the database move.
http://support.microsoft.com/kb/918992
Eg for my login i ran the below:
— Login: matts
CREATE LOGIN [matts] WITH PASSWORD = 0x010028E5E88C3D2A46D9334E4F7EA305934C6A21CA642D3CA0FD HASHED, SID = 0xE20BB1D19C24264C86E985EB9F107370, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
Thanks
Matt
LikeLike
Hi Matt,
The old passwords will not work anymore, as they are encrypted with the old server name. You will need to log into the GP application as ‘sa’ and reset all the passwords.
-Victoria
LikeLike
Wow thanks, so simple and that worked! I didnt think of simply trying to reset them in GP.
Thanks for the ultra fast reply 🙂
Cheers
Matt
LikeLike
VY,
Thanks for all the useful information published in your site. I am a regular visitor.
I do have an issue that have not been able to resolve:
How can i fix this? Which would be the ideal “Check Link” process and “Reconcile” to adjust the SOP and Inventory recorded errors?
LikeLike
LJR,
Without knowing exactly what has been done and looking at your data, I would not feel comfortable giving you any instructions for this, as I would only be guessing and a wrong guess could make matters even worse. I would recommend working with either the 3rd party app developer, your GP partner, Microsoft support, or a combination of all 3 to correct the issues.
-Victoria
LikeLike
Hello Victoria,
just stubled across an article of yours with the magic-hat-man picture. I love that one and would like to license it but cannot find it on fotolia. Would you mind supplying your source?
Thanks
Micha from Germany
LikeLike
Hi Micha,
I believe that one was from istockphoto.com.
-Victoria
LikeLike
Victoria:
I’ve recently started working at a company that uses GP Dynamics, and I’ve come across your website. Thanks for the useful info.
Quick question – I’ve balanced AR and AP to their control accounts, and then unchecked “allow account entry”, to lock them down to the subledgers.
However, we use Integration Manager to bring in AP items, and we’re now getting “account 2000-000-000 does not allow account entry” errors in Integration Manager.
Is there a way to keep the control account locked down, but use Integration Manager to bring in entries that would default to the control account if they were entered manually?
Thanks.
LikeLike
Ed,
I believe there is a way that you can tell Integration Manager to use the default GP accounts for anything you’ve not specified. It’s been a while since I set this up, so I am not 100% on the steps, but I am pretty certain that’s possible. I would recommend looking into that, I think it may solve this issue. If not, then unfortunately have to allow account entry to be able to import.
-Victoria
LikeLike
Thanks for the tip. I’ve looked into it, and it’s possible within Integration Manager to specify that all credit amounts go to the default account. This isn’t good enough for us, however, because some of our AP items debit expense accounts, credit revenue accounts, and then record the net as payable. I’d lose those revenue credits. It’s a pretty rough-and-ready solution. The best answer would be for GP itself to recognize that Integration Manager entries are as acceptable to locked-down control accounts as manual subledger entries, and I don’t think the software is capable of this. It’s not the end of the world; we’ll just continue with unlocked AP control accounts.
LikeLike
Hi Victoria, I went into Posted transactions and voided some things and now they are not showing up as voided. We ran a trial balance and it looks like I didn’t void anything. When I go into transaction history they don’t appear voided. So I went back to posted transactions to void again. Now an error message appears saying that “this document doesn’t exist for this customer.” so I have 10 voided transactions that show up as open and I can’t figure out how to clear this up. Please Help!
LikeLike
Marybeth,
Unfortunately, it’s very difficult to help with something like this without being able to see your GP (and possibly the data on the server). I would recommend you contact your GP Partner or Microsoft Support so they can help you figure out what happened and find your transactions.
-Victoria
LikeLike
Hi Victoria
I would like to thank you for this Blog. I’m building BI reporting for our Dynamics installation. This Blog has answered so many of my questions and more practically has provided me data sources for BI reporting that I would have otherwise had to wait months for from our development team. This Blog is a brilliant piece of work and I wanted to encourage you with my appreciation.
All my best,
Kevin Geddert
LikeLike
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?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Checked with the user and our version doesn’t have sites, each address is a whole new vendor ID.
LikeLike
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
LikeLike
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!
LikeLike
Victoria:
Here is some more information that I found out:
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Devin,
Have you given your GP users permissions to the View SmartLists with SQL Tables operation? I talk about that in this blog post, towards the bottom: https://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/.
-Victoria
LikeLike
That is the problem I refer to, I don’t have SmartList Builder as an option, because we only have the standard GP SmartList, which allows for SmartLists based on SQL Queries, but it is not the advanced Add-In that used to be included with GP,since they changed the licensing model and sent a bunch of Add-ons back to the original vendors In this case SmartList Builder went back to eOne and you have to buy it, but we didn’t because the basic functionality of SmartList in GP 2013 allowed the SQL queries, and that is all we needed.
Does that make sense?
Sent from Surface Pro
LikeLike
Devin,
Ah! I didn’t understand from your original message that you’re using SmartList Designer. Please take a look at this forum discussion for help with the security: https://community.dynamics.com/gp/f/32/p/117053/257068.aspx#257068. There is a script towards the end that will hopefully help.
-Victoria
LikeLike
My apologies for mixing up the names. I forgot it was called Designer. Thank you.
LikeLike
Devin,
It’s all good. Please let me know if that post helps you resolve this issue.
-Victoria
LikeLike
Yes it did – thank you very much
Sent from Surface Pro
LikeLike
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/
LikeLike
Hi Randy,
I don’t have anything like that, sorry. I would recommend posting a question on the Dynamics GP Community Forum to see if maybe someone already has this written.
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Hi Lou,
Great! You can email me at jed@erpss.com.
Thanks.
Jim
LikeLike
Jim
I am very interested in discussing this further – let me know how I can contact you. And thank you Victoria for facilitating!
Lou
LikeLike
Lou, That’s great. You can reach me at jed@erpss.com.
Thanks.
Jim
LikeLike
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.
LikeLike
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
LikeLike
Hi Victoria,
Thanks for the answer, however i found a solution for GP 2010 myself, which is the “POP Receipt Tolerance” product which is free.
Appreciate you trying to help regardless.
Cheers
Matt
LikeLike
Matt,
Great, thanks for letting me know.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
Hi Brian,
I don’t have anything like this already written. Please let me know if you are interested in having this created for you as a consulting project.
-Victoria
LikeLike
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
LikeLike
Sorry Bryan,
I can’t help with this. I would recommend posting your question on the Dynamics GP Community Forum to see if maybe someone there can help answer this. If not, you might need to talk to GP Support.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
Hi David,
Sorry, I have not seen this before. What is in the 2nd batch that goes to Batch Recovery?
-Victoria
LikeLike
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.
LikeLike
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?
LikeLike
Hi Alan,
If I understood what you’re looking for correctly, I already have this written: https://victoriayudin.com/2013/05/01/sql-view-for-current-receivables-aging-detail-in-dynamics-gp/.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
That’s what I found on searching the smartlists. Looks like it will have to be an sql query.
Thanks for the help.
Martin.
LikeLike
Hi Victoria,
Is the date created stored anywhere for AP Batches?
Thank you!
Karen
LikeLike
Hi Karen,
Once a batch is posted, nothing except the batch ID is stored. For all unposted batches in GP, you can see the created date in the SY00500 table.
-Victoria
LikeLike
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
LikeLike
Henry,
Correct – you would need either SmartList Builder or SmartList Designed (available in GP 2013 SP 2) for this.
-Victoria
LikeLike
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).
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thanks so much
LikeLike
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?
LikeLike
Hi William,
I think what you’re looking for is in my SQL view for Payables apply detail and GL distributions.
-Victoria
LikeLike
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
LikeLike
Ken,
Is “FOB Cost” a field you see somewhere in GP? If so, can you tell me what window it is on?
-Victoria
LikeLike
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 🙂
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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 :(((
LikeLike
Susanne,
Sorry, since Cashbook is not used in the US, I am not familiar with it. I would recommend either posting your question on the Dynamics GP Community Forum or talking to your GP Partner/GP Support about this.
-Victoria
LikeLike
thanks anyway Victoria 🙂 have a lovely day 🙂
LikeLike
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
LikeLike
Hi Brian,
RM are all receivables tables, not payables, so I was not sure which you are looking for?
-Victoria
LikeLike
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
LikeLike
Hi Ryan,
The only solution that I know of is to use the Company Data Archive product from Professional Advantage: http://www.profad.com/products/company-data-archive/
-Victoria
LikeLike
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
LikeLike
Robyn,
This sounds like the EFT functionality in GP – have you looked into that?
-Victoria
LikeLike
Yes, but not really the same thing. In this case someone else is physically writing the checks, but I guess we could get creative with it…. Maybe an idea.
Robyn
LikeLike
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.
LikeLike
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
LikeLike
hi Victoria,
I don’t believe you can import historical data through integration manager.
-Vince
LikeLike
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
LikeLike
Thank you all for your replies.
LikeLike
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
LikeLike
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:
Hope that helps,
-Victoria
LikeLike
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
LikeLike
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?
LikeLike
Hi Danny,
This may present a problem for the upgrade. I am not sure, as I have not run into this specific situation before. To be certain, I would recommend asking Dynamics GP support. It may be worth a support incident to save yourself a lot of grief during the upgrade.
-Victoria
LikeLike
thank you, very gratefull
LikeLike
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?
LikeLike
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
LikeLike
thank-you for your reply, I’ll try those places for further help and try to dig through them.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Raymond,
FRx is not compatible with GP 2013, you will need to move to Management Reporter.
-Victoria
LikeLike
hi Victoria
I meant the fiscal year 2013 they are on gp10
raymond
LikeLike
Raymond,
I have seen this happen where you need to select each GP company as the default in FRx to have it see a new year.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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 =)
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
Thanks Victoria!
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Antonio,
Here is a link I was able to find online: https://mbs.microsoft.com/customersource/support/downloads/servicepacks/frx67fullinstall4gp10cs.htm
You will need a CustomerSource login to access this.
-Victoria
LikeLike
Hi Victoria,
Can you recommend any good training courses for GP addin development in .Net?
Thank you.
LikeLike
Hi gpuser,
This is the only course I am aware of that would come close: https://www.microsoftelearning.com/eLearning/collection.aspx?guid=5CEDC906-44B4-11DC-8314-0800200C9A66
-Victoria
LikeLike
Thank you. I also found the course but I wasn’t sure if it was an actual course or just some technical resource.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Jan,
I am not really sure where to find something like this – maybe look through the case studies? Here is a link: http://www.microsoft.com/casestudies/Case_Study_Advanced_Search.aspx. If that does not help, my next suggestion would be to contact Microsoft, although I am not sure if they give out that kind of information.
-Victoria
LikeLike
Hi Victoria,
I was wondering if there is a way to get the batch edit list errors using a sql query?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Hi,
The following statement will give you the warning messages stored in the dynamics database:
select * from Dynamics..SY01700
-Vince
LikeLike
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
LikeLike
Hi Joe,
I don’t believe anything has changed for MC since version 9.0. Are you looking for something specific?
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Jack,
Yes, please post back with what this turns out to be.
Thanks,
-Victoria
LikeLike
Victoria or Jack – did you ever resolve this issue? I have 5 companies in GP – 2 of them show retained earnings, but 3 do not. I have no idea what to do to correct this problem. FRx shows the retained earnings correctly.
LikeLike
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
LikeLike
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 .
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
Hi Ron,
Thanks for your kind words. I have not worked with HR integration, however, you might want to talk to eOne – perhaps their SmartConnect product can help with your needs.
Happy Holidays!
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
Hi Shamila,
1. The answer to this would depend on exactly what you are trying to add. If you’re talking about the 3rd address line, there is a how-to article available describing how to do this here: https://mbs.microsoft.com/customersource/documentation/howtodocuments/msd_gpaddress3fieldsalesdocuments.htm?printpage=false&sid=cqxvwyvzfcw4gt52td0f4ft1&stext=How to add the Address 3 field to documents and how to suppress blank address fields in Sales Order Processing in Microsoft Dynamics GP.
2. GP does not have a ‘historical packing slip’, so you would need a custom report to accomplish this. Our GP Reports Viewer product http://www.GPReportsViewer.com/gpreports.html can help with something like this.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
Victoria good afternoon, is there any way (from GP) to remove Sales transactions (unposted) on a massive scale?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
Dede,
I can think of several things that could cause that:
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
LikeLike
Hi Victoria!
Is Possible create an additional type of document entry transaction sales, in addition to quote, order, invoice. ? example: “order of delivery.”
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Thank you Victoria. We want to do comparative Income Statements. Can I bring in income/expense period activity or just balance sheet period changes?
LikeLike
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:
-Victoria
LikeLike
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.
LikeLike
Yoselin,
I don’t believe this would be possible, but I would recommend posting your question on one of the GP forums to confirm.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
Talal,
I cannot think of a way to do this out-of-the-box in GP.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
I tried that but nothing happens, the document is not moved to history.
LikeLike
Victoria,
In that case, most likely GP does not consider those orders closed. You will need to check every line item to see if there is something still left open.
-Victoria
LikeLike
Hi Victoria,
My review of your book is up at http://www.aaronberquist.com/2011/01/book-review-dynamics-gp-2010-implementation-by-victoria-yudin/
All in all, job well done!
Thanks,
Aaron
LikeLike
Hi Aaron,
Thank you very much!
-Victoria
LikeLike
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
LikeLike
Hi Gordon,
Thank you, I am very happy to have been able to help. 🙂
Happy New Year!
-Victoria
LikeLike
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.
LikeLike
Hi Hope,
Thanks for the kind words. Sounds like you can use my Payment Apply Detail view for this, but you may need to add a few columns to get everything you need, like 1099 amount and invoice description.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Jan,
I am sure there is a large number of public companies using GP. If I recall correctly, Starbucks is one of them. Here is something I found by quickly searching this on the internet: http://www.microsoft.com/Casestudies/Case_Study_Detail.aspx?casestudyid=4000006651 that you may find interesting.
This is the kind of question you (or your GP Partner) should be able to go directly to Microsoft with and get a good answer as well as a reference.
-Victoria
LikeLike
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
LikeLike
Jason,
If you want to keep the history, you should not delete the part numbers. 😦
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Terry,
I sent you an e-mail about this.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Hi Jim,
Thanks for following up and detailing your solution! Glad you were able to find something that works. 🙂
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Lam,
This is not a security or user specific setting. There is a company-wide setting in General Ledger Setup that controls this. Take a look at the General Ledger section of this blog post: https://victoriayudin.com/2009/04/11/where-did-my-transaction-go/.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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:
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
LikeLike
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
LikeLike
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
LikeLike
Hi Victoria,
It worked! You did it again. I will certainly remember how to do this for future reports.
Thanks and regards,
Gordon
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Yana,
I believe those are hardcoded and I am not aware of a way to change this, sorry. 😦
-Victoria
LikeLike
Thank you for your help, Victoria. We will be looking to buy both Extender and Modifier.
Yana
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Yana,
Sorry, I am not sure what you mean, can you please clarify?
-Victoria
LikeLike
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
LikeLike
Yana,
These can be changed on the Receivables Setup Options window. Tools > Setup > Sales > Receivables > Options button.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
Hi Darren,
This is not really my specialty, but you may find this discussion on the MS Dynamics GP newsgroup helpful. I would recommend posting your question on that newsgroup as well, so that you can get folks with more SQL expertise responding.
-Victoria
LikeLike
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)
LikeLike
Jean,
Sorry, I have not seen that before, the only thing I am familiar with seeing there is a * indicating a voided transaction. Is this window customized? If so, are you able to disable the customization and see if that is still there? Does it show on all computers and server or just on a particular computer? Are all transactions showing a # or just some? If just some, what is different about those transactions?
-Victoria
LikeLike
We were wondering the same thing, and one of my team members found this:
http://msdynamicsgp.blogspot.com/2010/05/weekly-dynamic-identifying-intercompany.html
Looks like the (#) symbol indicates an intercompany transaction.
LikeLike
Jessica,
Thanks for replying to this. You have seen this on the Payables Transaction Inquiry window? Because mine has a column called IC with a check next to Intercompany transactions.
-Victoria
LikeLike
Yes – it was in the Payables Transaction inquiry window. I checked the screen again, and there is no IC column…maybe it’s a version difference? The client we were working with is on GP 10.
LikeLike
Hmmm…with a US installation? I have always seen that IC column, definitely as far back as GP 9.0.
-Victoria
LikeLike
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
LikeLike
Nate,
Nate,
Here is a company we have worked with before that does great Forecaster work:
http://www.accountnet.com
Contact: John Peace 212-244-9009
Good luck with your project!
-Victoria
LikeLike
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
LikeLike
Bill,
That is a pretty complicated script. I do not know of anyone that has it available for free.
-Victoria
LikeLike
Do you know where I can purchase it?
Bill
LikeLike
Bill,
Have you checked with your GP partner? It’s possible they have something available or can do this for you as a consulting project. If not, my company (Flexible Solutions) offers report creation services.
-Victoria
LikeLike
I found 2 sites that have the code:
http://www.box.net/shared/1bcc214vjr
and
http://www.albaspectrum.com/PressReleases/062207_7.htm
However, each uses a different where clause. One uses DOCDATE and ApplyToGLPostDate for the cutoff dates and the other uses DOCDATE and GLPOSTDT to filter the Apply tables. Any idea which is the correct one to use??
Bill
LikeLike
Bill,
That all depends on what parameters you choose when you are running the report. DOCDATE is equivalent to the Document Date for the ‘Select Transactions for Report Using’ option and GLPOSTDT is GL Posting Date.
For what it’s worth, I just tested both of these against the actual Payables Historical Aged Trial Balance on our live live data and they are both significantly off the results on the GP report. Are you finding that either of these actually matches your results in GP?
-Victoria
LikeLike
Victoria,
My results are off as well. I will use these and a SQL trace as starting points and go from there.
Thanks,
Bill
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Sharat,
I don’t believe there is an easy (meaning out-of-the-box) way to restrict someone from changing a note once they open it. I would recommend posting this question in the Microsoft GP newsgroup for additional ideas from others.
-Victoria
LikeLike
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
LikeLike
Hi Akbar,
I believe you got an answer for this from Dencio on the the Microsoft Dynamics Community Forum.
-Victoria
LikeLike
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
LikeLike
Hi Ron,
No problem at all – thanks for checking with me!
-Victoria
LikeLike
Thanks a ton!
LikeLike
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
LikeLike
Prakash,
If you’re able to get this to work with giving additional rights the user in SQL, then that’s probably going to be the answer. If that’s not something you want to do, then you might want to look into using a product like our GP Reports Viewer to print your Crystal Reports from GP.
-Victoria
LikeLike
Thank you very much Victoria.
-Prakash
LikeLike
Hi Victoria,
Congrats on being named an a Dynamics GP Zone MVP on Experts Exchange!
Steve Endow
LikeLike
Steve,
Thank you very much!
-Victoria
LikeLike
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
LikeLike
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.ITEMNMBRFROM 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
LikeLike
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
LikeLike
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.ITEMNMBRFROM 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
LikeLike
Victoria,
SUCCESS!!!
Now for the UNION ALL… wish me luck.
Thanks,
Gordon
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Ahhh, I bet you are right. I am in the central time zone which is GMT – 6.
LikeLike
Scott,
Here is what I found out: DEX_ROW_TS stores the time in Coordinated Universal Time (UTC), provided by getutcdate() in SQL.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Raul,
So if you, right now, go and enter an invoice for this vendor, what payment term does it bring in by default?
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Raul,
I am sorry, I have not worked with the Project Cost product at all.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Steve,
Thanks for letting me know the new URL. It’s been updated on my Blogroll.
-Victoria
LikeLike
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.
LikeLike
Sharat,
That script does not sound like a typical GP one. Do you have a customization or 3rd party product installed that could be calling that?
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Raul,
Build 10.00.0872 is somewhere between no service pack and SP 1. I am not able to confirm whether this is an issue for that particular version of GP, but I do not think so, since you had the paper clip when your users were pointing locally to their C drives for the notes. I would suspect that the issue is either network permissions to the share where you are re-pointing the notes, or that the proper folders to not exist there. Here is a newsgroup posting about the missing paperclip that might help: http://groups.google.com/group/microsoft.public.greatplains/browse_thread/thread/ea8f2e38aecfd3ae/dd5b6756ffff3781?lnk=gst&q=Cannot+Attach+Documents+To+Notes#dd5b6756ffff3781
-Victoria
LikeLike
Hi Victoria,
This is regaring Missing Paper Clip.
We are using Great Plains 7.5
In the Dex.ini file if i set the olepath to c:\tmp(any folder) it is showing the paper clip.
if i set the olepath to \\10.1.1.12\volume2\it dept\it dept\accounting\
it is not showing the paper clip.
i given the olepath in other way also what i mean is i deleted the olepath from dxe.ini file then i restarted great plains. opened the general entry window then i clicked the notes then it poped up the small window to eneter or select the olepath. i select the unc path still it is not showing the paper clip. when i selected local c drive and some folder then it is showing the paper clip.
what the mistake iam doing .
Thanks
Sharat Mittakola.
what is the mistake iam doing.
LikeLike
Sharat,
Try creating the necessary folders first by following the method I outline in the newsgroup post here: http://groups.google.com/group/microsoft.public.greatplains/browse_thread/thread/ea8f2e38aecfd3ae/dd5b6756ffff3781?lnk=gst&q=Cannot+Attach+Documents+To+Notes#dd5b6756ffff3781.
-Victoria
LikeLike
Hi Victoria,
Thanks A Lot. this is the second solution i got from you.thanks again.
It is working.
Sharat Mittakola
LikeLike
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
LikeLike
Raul,
No worries, please let me know how it goes.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Raul,
I am not clear exactly what the issue is – can you please elaborate?
-Victoria
LikeLike
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 )
LikeLike
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
LikeLike
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
LikeLike
Ron,
Another product that I have looked at is from Azox: http://azox.com/ECommerce/eSourceB2C/tabid/156/Default.aspx. Not sure how the pricing compares to Nodus. Looking at MSDynamicsWorld’s solution directory, I see there are a couple of other choices: e-commerce solutions. (You’ll need to create a free login to access this.) I have not worked with the other companies listed, so I cannot tell you much about them. And it sounds like you’ve already investigated you other option – getting the data and importing it in yourself using IM.
-Victoria
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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
LikeLike
Microsoft has confirmed this is a problem.
“For now, we are looking at trying to update our documentation to indicate that this issue exists, and that the workaround would be to apply tax updates to GP 9.0 on a non-Vista computer OR to apply the TX.SQL file manually via SQL. “
LikeLike
Darren,
Thanks for the update!
-Victoria
LikeLike
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
LikeLike
Hector,
Have you tried using an Advanced GoTo?
-Victoria
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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…
LikeLike
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
LikeLike
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”?
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
Victoria,
We have created a customer specific pricing module for GP that nicely solves this problem that might be of interest.
We created it for SBF but we have GP resellers asking for it and ported it.
http://landiscomputer.com/modules
Matt Landis
LikeLike
Matt,
Thanks for letting us know about this! Here is a plug for Matt at Landis Computer – he knows his stuff, definitely worth taking a look if you have a need for this functionality!
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Thanks Victoria for you help.
Shauna
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
is there a way to find out in sql what period in the gl invoice details were posted to?
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike