About Me
I have over 18 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 Gold Certified Dynamics GP Partner offering the GP Reports line of products and GP customizations and support. I currently hold certifications from Microsoft for Dynamics GP (formerly Great Plains), FRx Financial Reporting and Small Business Financials. I have an undergraduate degree from the Wharton School of Business at the University of Pennsylvania and have the honor of being named a Microsoft Dynamics GP MVP each consecutive year starting with 2005.
hi
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.
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: http://victoriayudin.com/2009/04/11/where-did-my-transaction-go/.
-Victoria
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
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
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
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
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.
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
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.
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
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
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
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
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
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
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
Hi Victoria,
It worked! You did it again. I will certainly remember how to do this for future reports.
Thanks and regards,
Gordon
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
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
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
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
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
Hi Yana,
I believe those are hardcoded and I am not aware of a way to change this, sorry.
-Victoria
Thank you for your help, Victoria. We will be looking to buy both Extender and Modifier.
Yana
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
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
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
Yana,
Sorry, I am not sure what you mean, can you please clarify?
-Victoria
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
Yana,
These can be changed on the Receivables Setup Options window. Tools > Setup > Sales > Receivables > Options button.
-Victoria
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.
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
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.
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
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
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
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
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
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
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
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.
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
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)
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
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
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
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
Bill,
That is a pretty complicated script. I do not know of anyone that has it available for free.
-Victoria
Do you know where I can purchase it?
Bill
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
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
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
Victoria,
My results are off as well. I will use these and a SQL trace as starting points and go from there.
Thanks,
Bill
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
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
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
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
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
Hi Akbar,
I believe you got an answer for this from Dencio on the the Microsoft Dynamics Community Forum.
-Victoria
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
Hi Ron,
No problem at all – thanks for checking with me!
-Victoria
Thanks a ton!
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
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
Thank you very much Victoria.
-Prakash
Hi Victoria,
Congrats on being named an a Dynamics GP Zone MVP on Experts Exchange!
Steve Endow
Steve,
Thank you very much!
-Victoria
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
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
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
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
Victoria,
SUCCESS!!!
Now for the UNION ALL… wish me luck.
Thanks,
Gordon
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
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
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.
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
Ahhh, I bet you are right. I am in the central time zone which is GMT – 6.
Scott,
Here is what I found out: DEX_ROW_TS stores the time in Coordinated Universal Time (UTC), provided by getutcdate() in SQL.
-Victoria
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
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
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
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
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
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
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
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
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
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
Raul,
So if you, right now, go and enter an invoice for this vendor, what payment term does it bring in by default?
-Victoria
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
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
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
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
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
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
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
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
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
Raul,
I am sorry, I have not worked with the Project Cost product at all.
-Victoria
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.
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
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.
Steve,
Thanks for letting me know the new URL. It’s been updated on my Blogroll.
-Victoria
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.
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
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
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
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
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
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
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
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.
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
Hi Victoria,
Thanks A Lot. this is the second solution i got from you.thanks again.
It is working.
Sharat Mittakola
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
Raul,
No worries, please let me know how it goes.
-Victoria
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
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
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
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
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
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
Raul,
I am not clear exactly what the issue is – can you please elaborate?
-Victoria
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 )
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
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
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
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
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.
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
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
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
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!
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
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. “
Darren,
Thanks for the update!
-Victoria
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
Hector,
Have you tried using an Advanced GoTo?
-Victoria
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
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.
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
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…
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
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”?
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
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?
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
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
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
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
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
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
Thanks Victoria for you help.
Shauna
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.
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
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.
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
is there a way to find out in sql what period in the gl invoice details were posted to?
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
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?
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
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