Archive for October, 2011

Undoing a bank reconciliation after it’s already been posted for Dynamics GP

headshot of DougWhen I was a freshman in a U.S. college I hung out with a group of friends from my little hometown in Canada. We were appalled by the lack of knowledge of our American roommates (and most of our fellow student body) about our beloved homeland. On October 13th that year we decided to celebrate Canadian Halloween. We dressed up in costumes and went around trick or treating (mostly around girls housing complexes.)

There is no special Canadian Halloween (unlike Canadian Thanksgiving, Independence Day, Remembrance day, Family Day etc.) but my recollection is that most of the people we trick or treated to were quite open to our Canadian culture when we announced it was Canadian Halloween. If they didn’t have candy we said we’d take canned soup or any type of food. I don’t think we had to go shopping that week and we met a ton of girls. Although I’m sure most of the girls we met decided that they would never date a Canadian after meeting our motley crew.

What does this have to do with opening back up a bank reconciliation after it has already been posted? I’m going to discuss a method below that some of our consultants use to open back up a bank reconciliation. You can believe me without question or you can test this out first on a test company.

******************************

SQL queries:

1) Determine the Recon# of the reconciliation to be un-done (replace XXXXX with the appropriate Checkbook ID):

select * from CM20500 where CHEKBKID = ‘XXXXX’

order by RECONUM

2) Change the transactions in that recon to not reconciled (replace the ##.##### with the exact RECONUM determined in #1 above):

update CM20200 set Recond = 0 where RECONUM = ‘##.#####’

update CM20200 set ClrdAmt = 0 where RECONUM = ‘##.#####’

update CM20200 set clearedate = 0000-00-00 where RECONUM = ‘##.#####’ update CM20200 set RECONUM = 0 where RECONUM = ‘##.#####’

3) Remove the Recon from history (replace the ##.##### with the exact RECONUM determined in #1 above):

delete CM20500 where RECONUM = ‘##.#####’

4) Update the Checkbook Master with the revised Last Recon Date and Last Recon Amount (Replace MM with the desired Month and DD with desired day. Repace #######.## with the appropriate amount – no commas. Replace XXXXX with the appropriate Checkbook ID):

update CM00100 set Last_Reconciled_Date = ’2010-MM-DD 00:00:00.000′ where CHEKBKID = ‘XXXXX’

update CM00100 set Last_Reconciled_BALANCE = ‘#######.##’ where CHEKBKID = ‘XXXXX’

**********************************

Although I’ve told anyone that asks for the past several years that you can’t unpost a bank reconciliation that’s not entirely true. The above method works great. It just takes a little SQL knowledge and you can be re-reconciling in no time.

But don’t just take my word for it. I once swindled a whole apartment complex out of all their precious chocolates and candy.

Happy Canadian Halloween.

Pivot Tables with SQL? The Sum of Payments Posted Per Vendor Per Month

There was a time when a customer approached to me and said “Francisco I need to create a pivot table from SQL, I am tired of sending raw data to excel and formatting, I don’t want to create macros, I just want an export from SQL that provides me the sum of paymentsFrank New RBS posted per vendor per month”.

As you can imagine at first my mind gave me a BSOD, fortunately I knew that it was possible to do, so I started playing with Fabrikam and SQL and came with the following script:

This is Fabrikam’s data for ACETRAVE0001 with payments on February and April

Data

This is the result from the query:

Matrix1

 

Disclaimer: Before running any script provided make sure you have a backup of your databases, as well TEST, TEST, TEST in a development environment never do this on a production environment without confirming the script will fix your issue. The script is provided as is for educational purposes.

– begin script

select VendorID, [1] as ‘January’, [2] as ‘February’, [3] as ‘March’,   [4] as ‘April’,

                        [5] as ‘May’,     [6] as ‘June’,  [7] as ‘July’,      [8] as ‘August’,

                        [9] as ‘September’, [10] as ‘October’,  [11] as ‘November’, [12] as ‘December’

from (Select Docamnt, MONTH(Docdate) as ‘Month’, vendorid from PM30200 where (DOCDATE between ’01/01/2017′ and ’12/31/2017′) and DOCTYPE = 6) P

Pivot(Sum(DocAmnt) For [Month] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as pvt

order by pvt.VENDORID        

 

– end script

As you can see this query is not limited to Payments, if you change the DOCTYPE to 1 you will get the value of all invoices.

Feel free to adjust it and if you have a better script let us know we can all share.

Thanks

Francisco G. Hillyer

FRx does not print my imported budget!!!

Yesterday I had a call from one of our customers, they recently upgraded all computers to Windows 7 64 bit, office 2010, since they upgraded every time they import a budget to GP, they can’t see it in FRx, the book name is populated but no data is printed.Frank New RBS

They can export the data using excel and everything is there, but not in FRx, we had them run the GL Rebuild process with no good results.

It was another day in action, I “Bing-ed” keywords no results, search the online communities nothing at all, I don’t know if someone has experienced this but here is the solution (Temporary) I had to run on their SQL server.

I would like to mention that I did SQL maintenance (Drop/Created Autoprocedures, used Database Maintenance to rebuild Triggers, Views, procedures) it is a single install no third party software.

 

In my research I found that in the budget lines the YEAR1 column is set to 0, while in the budget header table the YEAR1 is set to the year the budget is setup like 2011, 2010 etc.

I wrote the following script to update the tables with the proper YEAR information.

I know its not an elegant solution but at least right now my customer is able to run financials, specially with an audit in place. This will give me time to do more research on the matter when they are less pressured with the audit.

 

Disclaimer: Before running any script provided make sure you have a backup of your databases, as well TEST, TEST, TEST in a development environment never do this on a production environment without confirming the script will fix your issue. The script is provided as is for educational purposes.

 

update p set p.year1 = a.year1 from GL00201 p join GL00200 a on a.budgetID = p.budgetid where p.year1 = 0

 

Thanks and have a great day!!!

Francisco…Where is my stuff?

Ok, the title it’s a bit demanding but when someone asks you this question regarding inventory here is a hint that will help you on your quest of finding all the “stuff”.

Dynamics GP has several reports built in as well inquiries concerning inventory items, the most important in my career so far are the following:

Back-Ordered Items Received Report

I have seen in the past (not so distant) where customers are more into “Integrating” orders from various external systems or Web stores, when they integrate data into GP via Web services, a customization using eConnect, integration manager or any other type of magic we have to deal with items that we do not have in stock. We need to quickly Frank New RBSidentify which Items in order to make sure that our shipping process is on time to the customer.

This particular report contains information for sales documents that were linked to a purchase order, with cost information up to date and those quantities allocated and fulfilled on sales orders.

  • Go to Reports > Purchasing > Analysis
  • Once the Purchasing Analysis Reports window opens, from the drop down choose Back Ordered Items Received.
  • Create New or modify if existing reports are available
  • If you created a new report, make sure you enter a report name
  • If you need, you can filter the Item Numbers for this report, if you want to report on all items do not enter an Item number range
  • Select your destination (Printer, Screen, File)
  • Print the report.

Item Inquiry Options

The item inquiry window allows us to view inventory related transactions, it provides a nice summary window for all sites of inventory or a particular site id.

The item transaction inquiry window allows us to view any posted transaction for a particular item from any GP module.

If you are using FIFO (its not limited but it’s the best fit) the Purchase Receipts Inquiry window should be your best friend because it brings all the purchase receipts that exist for a particular item.

I do personally work very well with Item Allocation and Item Stock inquiry windows.

  • Go to Inquiry > Inventory
  • Choose the desired inquiry window

A cool feature that you might find great is that some inquiry windows allows you to go into a deeper level using the Go To windows, or they might have a printer icon, which allows you to print your current view.

Stock Status Report

Last on today’s post is the Stock Status report, which provides us with up to date information on each update like:

  1. Inventory Value
  2. Quantities on Hand, Allocated, On Order, Back Ordered, Requisitioned
  3. Current cost

How can we get this report?

  • Reports > Inventory > Activity
  • From the reports drop down choose Stock Status
  • Create new or modify existing
  • Choose sites or all
  • You can select to include serial numbers/ negative quantities / Zero quantities
  • You can filter by item, description, class, manufacturer, bin and much more
  • choose your report destination and print it.

So here you have a few options that will help you on your quest finding “stuff” if you are on GP 10 SP5 or greater you have an additional tool at your fingertips and its called "HITB: The Historical Inventory Trial Balance” it depends if your organization has this tool enabled and setup it will provide you with additional information.

Thanks

Francisco G. Hillyer

Value of 0 is not valid for Selected Index (Analysis Cubes Install)

A customer called in trying to install analysis cubes the first error was “Microsoft Dynamics GP client must be installed in order to complete the Microsoft Dynamics GP Analysis Cubes installation”.Frank New RBS

 

I would like to mention that this client had installed the spanish version of GP, after the customer followed up the instructions on KB949152 still was getting the error.

After rebooting the virtual machine he tried once again but now he got an unhandled exception.

unhandled exception has occurred in your application.
InvalidArgument=Value of ’0′ is not valid for ‘SelectedIndex’
Parameter name: SelectedIndex
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.ArgumentOutOfRangeException: InvalidArgument=Value of ’0′ is not valid for ‘SelectedIndex’.
Parameter name: SelectedIndex
at System.Windows.Forms.ComboBox.set_SelectedIndex(Int32 value)
at Microsoft.Dynamics.GP.AnalysisCubes.fMain.LoadReportingCurrencies()
at Microsoft.Dynamics.GP.AnalysisCubes.fMain.ProcessSelectedCompanies()
at Microsoft.Dynamics.GP.AnalysisCubes.fMain.bNext_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

 

How do we fix this you may be asking, here is the solution for this dilemma:

The company that is getting the error we need to…

  1. At least have one transaction posted
  2. At least have configured a functional currency Tools > Setup > Financial > Multicurrency, field Functional Currency
  3. Configure multicurrency access for company Tools > Setup > System > Multicurrency Access and verify each company access.

At least for this customer that was the solution.

 

Thanks

Francisco G. Hillyer

FRx Error 5 An Invalid character was found in text content

Great day in San Diego the morning traffic was smooth as a chocolate no issues at all, arrive to the office and I see on my email “Support Request FRx Error 5”.Frank New RBS

I called in the customer, we did a remote session if the customer generates a report for September everything is great, but if it generates for october then we have an issue.

Microsoft says that an incompatible version of MSXML.DLL was registered, but it works for a month and not for another? that doesn’t seem to be right.

I followed up the instructions to re- register the XML file but still getting the issue.

 

There is a KB from Microsoft called “The Special Characters that are NOT supported in Microsoft FRx”

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;941691

 

Microsoft states that FRx can query several different databases each DB has its own specific requirements.

The complete list of characters not supported is as follows:

  • /
  • [
  • ]
  • ;
  • =
  • \
  • :
  • ,
  • .
  • !
  • .
  • -
  • (
  • )
  • *
  • &
  • ^
  • #
  • @
  • $
  • %
  • ?
  • +
  • {
  • }
  • |
  • <
  • >
  • .
  • ~

The Microsoft FRx engine must reserve the following special characters to use in an account description:

  • (
  • )
  • ^
  • *
  • /
  • %
  • \
  • +
  • -
  • >
  • =
  • <
  • &
  • |

When these special characters exist in other fields, the special characters may interfere with report generation.

Back to the customer we searched October transactions in the DB and we found 4 journal entries that had a combination of the special characters on the Journal Entry Reference field as well on the Distribution Reference. Once corrected FRx worked as a charm.

Thanks

Francisco G. Hillyer

Rebate Tracking with Binary Stream

Binary Stream offers the Vendor Contract Pricing & Rebates module that allows you to track rebates from your vendors. It allows you to track them as you sell items to your customers, and then periodically report on them to your vendors so you can collect the rebates. It also easily creates an accounts payable credit memo to make the request part of Dynamics GP accounting records to make sure you collect the rebates.

This is an example of the Contract Maintenance screen where vendor contracts are maintained:

You’ll notice that in this example I buy the item for $40, but sell it for $38. This is a money losing proposition unless I collect the $10 rebate from the vendor. This module helps me control this situation so I can easily account for the rebate and process a request to the vendor.

I also assign the contract to my clients to whom I sell this product. This allows GP to automatically detect that a vendor contract is associated with the sale, and will start tracking the rebate for this item and sale. Here is what a sales order looks like for this item:

Notice that the margin is recalculated and shown on this screen, assuming that the rebate is received from the vendor.

Periodically you will want to claim the rebates from your vendors for the  items sold. There is a screen that will display all rebates that should be requested and a direct load to Excel to provide reporting. When you process the rebates, a credit memo will be created in Payables Management for the amount of the request.

Done.

I created this short video showing this in action: http://youtu.be/wwWOFxKVqHA

FRx Error 988672

Yesterday it was a cloudy day here in sunny San Diego, everyone here at the office busy as always all of the sudden an email came to support. The customer could not generate their report book from Report Manager in FRx.Frank New RBS

 

The error in question was found after FRx Service pack 10 was released and it was fixed on FRx Service pack 11. But our customer is on 10, and it worked the previous month so something changed between last month and current month that triggered this error.

 

The error says: “FRx Error 988672 Access denied because another caller has the file open and locked”, my first reaction was to ask the customer if in fact they had the file open, but it was not, my sherlock holmes identity came out and I went to their server, pulled out Task Manager, verified all open instances of excel (found none), then I went to my toolbox and pulled Process Explorer from sysinternals, I tried running the report book again, same error.

This time I searched for all open handles for that particular file, being found once while FRx was showing up the error, the handle was released after I clicked OK button to make the error go away. I reached out to my toolbox again and pulled Process monitor, I placed some filters around the user instance on the server I tried again the report with error results.

Process Monitor did not gave me the solution either, I went to my default search engine (BING) and found a case on the SL community, Microsoft states that this error is resolved by installing FRx SP11, but it was working before… and my client was not on the mood for a software update at this time.

To aid in my investigation I did the following tests:

  • Open Frx Designer and run the report –> Result: Report Generates
  • Open Report Manager: On the book definition, right click and Generate –> Result: Report Generates
  • Open Report Manager: On the book definition, Generate the reports automatically –> Result: Report Fails

 

After almost being done with all the report ID’s that made the Report book, I found 1 report that was not valid, I searched for the catalog id in report designer but I couldn’t find it. Once I changed it to the correct report I was still getting the error.

 

So I had the user do the following:

  • Log out from GP
  • Log out from FRx
  • Close Report Manager
  • Log out from Citrix
  • I was still in citrix as administrator and deleted the users profile temp folder
  • had the user log back in and everything worked.

 

Please give it a try, let me know if this temp solution avoids the install of SP 11 when you can’t do it, but eventually install it.

 

Thanks

Francisco G. Hillyer

You don’t have security privileges to open this window (SOP)

Recently on the Dynamics communities someone posted this error message:

Microsoft Dynamics GP: You don’t have security privileges to open this window. Contact your system administrator for assistance.Frank New RBS

The owner of the post stated that this message was related to the print of Invoices in the Sales Module, it doesn’t matter if they are closed invoices or open invoices the message pops out.

While several of my friends replied back asking if they were modified reports etc., the post owner stated that after clicking ok, they could print, only that it was annoying to see that message there.

 

Ian Stewart an MVP and guru on the EMEA region asked about the replication steps, the post owner replied with the following:

 

They get the error message immediately after they the report destination window pops up.

From Sales Transaction Entry window:

1.Click on the printer icon or File>Print to open the Sales Document Print Options window.

2.Check the box next to Invoices Blank Paper under Include.

3.Click Print.

4.The Report Destination window appears.

5.Click OK.

6.The error message appears.

7.Click OK.

6.The invoice prints.

 

This doesn’t happen with the POWERUSER role or SA, so it is a security related issue, once I read that,  I jumped in and asked the owner if they had manufacturing installed.

They replied with a positive answer, manufacturing was present, so I had the answer to get rid of the painful message or at least that was my idea.

Create a new security task or modify an existing one, select from the Products: Manufacturing, on the type of resource choose: Windows, and on the series select: Third Party.

Scroll on the objects that appear on the selection window and search for SOP_Dummy_Report_Form and add it to your security task, save the task and make sure the user has access to it.

 

This should get rid of the error message. Let me know if this helps you.

 

Francisco G. Hillyer

Get the job done!!!

Hello, have you ever worked on a project, giving away your weekends trying your best to keep up with a tight deadline and stretching beyond your means?.Frank New RBS

Does this sound familiar? being so close to completion and all of the sudden you get a dozen “Just one more thing…”?

I feel lucky to ever go that route once and for all.

As you read this you may feel like I am complaining but believe me I am not, I feel lucky as this makes me a better consultant as I have kind of placed myself in the customer shoes.

Lessons learned:

  • Never assume project settings
  • Do communicate, don’t be afraid
  • Better now than later (changes)
  • Choose the right tool
  • The wheel was invented years ago, don’t spend time reinventing it

Never assume project settings

Ask, please ask about settings on current environment I started my project using .NET 4.0 and SQL server 2008 R2, when I tried to deliver….. yes you are right the company environment was not totally up to date and .NET 4.0 could break current integrations so it was a no Go on it, I moved my application to .NET 3.5 SP1 and about SQL they had SQL Server 2005 SP2, so I could not just backup and restore my database settings I had to re-deploy all SQL objects.

Do communicate, don’t be afraid

Even if you know it all and have all the specifications do ask questions, get back to the customer even if you know the answer, sometimes the customer might add a quiz or a puzzle in the documentation to see if you are looking or really paying attention. Do not wait until the last minute.

Better now than later (changes)

As Franny Fried explains on her blog post “The email I just sent asking you to estimate what it would take to tweak your project according to my specifications is added work”.

I understand that it gets annoying to be so close to complete your task and be “stuck” applying all this late changes. But it’s better now than later, imagine you compile/publish/package/deploy/install/test your application and the customer send this request to you all that time for nothing, better do it while still on the process and do it once.

Choose the right tool

You can say that you are expert in C#, or an expert in T-SQL for Dynamics GP there are different tools, eConnect, Dexterity, Modifier, VBA, .Net, Continuum etc.

Each one has its limitations and advantages choose wisely the best development tool that adapts to the project needs and stick with it.

The wheel was invented years ago, don’t spend time reinventing it

When the tool I was assigned to build, one day a coworker came to me and asked: “Francisco so are you using integration manager to push the data?” Ok let me explain Integration Manager is great but its not as fast as you can expect to import/process 65,000 records in a short period of time, or the use of direct table integration.

For that particular reason there is eConnect and you can use it to your advantage in a .NET development, or if you are a true master for GP and know its ins and outs, you can use plain T-SQL to accomplish this task easily and executing the needed processes to validate your data. Don’t spend time decrypting scary XML maps or other stuff don’t complicate yourself read and reach out, asking for help is not considered a weakness its intelligent (unless you are asking me why you see asteroids on the screen when entering your password).

 

To read Franny blog post click here: My foot on your backside…is that in scope?

 

Thank you for reading and let me know anything you have learned from a painful experience and how that made of you a better consultant/employee as well how this changed from undeliverable to GET THE JOB DONE!!!.

 

Francisco G. Hillyer

Subscription Options:
Subscribe via RSS
Articles Categories