I’ve recently been creating an HSBC Standard 18 BACs file format for a client in Microsoft Dynamics GP 2010 R2.
Unfortunately, once I released it to their system, they reported an error with the import where the Creditor’s Cheque Name wasn’t being output on the resulting file.
I did some testing and it turns out to be a bug in the export function of the EFT File Format (
) window in Microsoft Dynamics GP 2010 R2; I have subsequently tested on GP 2010 SP3 and GP 2013 and the import/export works fine. I realised something odd was going on when I looked at the template in the EFT File Format window ( ) and saw that while the Series was defined as Purchasing in the header, the scrolling window for the Data Field only had Receivables Management tables and fields available to it and not the Payables Management tables and fields I would expect;
In the last post I showed how the FRx menu can be renamed to show Management Reporter instead.
However, it is also a per user setting so would need to be done for each user created in Microsoft Dynamics GP who had access to Management Reporter.
To make the job a lot less onerous, I thought it might be useful to follow up with a SQL script to copy the FRx command customisations from one user to all others Continue reading
Earlier this week I posted about a bug in Integration Manager where it added extra zeros into the phone and fax number fields when importing creditors. Well, the same bug also affects debtors but a similar script to update debtors via a CSV is also possible.
I was doing some work for a client recently where we were loading lists of new creditors into Microsoft Dynamics GP from CSV files via Integration Manager. There is, however, a bug in IM 10 where it adds extra zeros to the phone and fax number fields of both creditors and debtors. These extra characters are, rather bizarrely, not always at the end but can be interspersed in the phone number.
The easiest work around was to put together a SQL script for loading the phone and fax numbers from CSV file which I did while on site which updated only a single address on the PM Creditor Master (PM00200) and PM Address Master (PM00300) tables.
After a slight mid-week diversion, here is another script I produced a while ago which updates the Item’s Default Purchasing Unit of Measure. It was produced when a client wanted to bulk update a large number of records which would have taken a long time manually but by script only minutes to write and run.
This is a script I’ve had a few months now and, like the one I posted yesterday, thought it might be useful to others.
This script was created for a client who changed some of their account codes on some Item Classes and had rolled them down to the Items themselves but there were many Sales Transactions already on the system which had the old code on and needed to be updated to the new one.
This script was produced for a client who wanted to bulk update the accounts defined against selected Inventory Items in Microsoft Dynamics GP 2010 R2. This particular client did not have Integration Manager so I needed an alternative approach to doing the update.
I could have used a GP Macro to do the job (doing one while recording the macro to create a template to be populated from a CSV using Mail Merge) but it was easier to create a SQL script to do the job directly from the CSV (this is the same view I took for updating the Account Segment Master).
I periodically have problems when trying to restore a GP company database over the Test database as SQL reports that the database is currently in use.
One way of resolving is to restart the SQL Server but this is only possible on a stand alone test system which is not being used by other people but this approach is overkill.
The better solution is to change the database to single user, restore the database and change the database back to multiple user. This can be done manually through SQL Server Management Studio but is far easier done via a SQL script.
On the Microsoft Dynamics GP Community site a question from Lisa Sorenson in October last year asking if it was possible to use the Table Import ( ) feature in Microsoft Dynamics GP to update some Segment descriptions has risen to the top with Steve Cummings linking to a post where the suggestion is to use a CSV file and Word template to generate a mailmerge.
This solution will work, but can be accomplished in much less time and effort by using the SQL command BULK INSERT to load the CSV (formatted as Segment ID, Segment Number and Description) Continue reading
I’ve recently been doing some work for a client where we’ve upgraded them from Microsoft Dynamics GP 10 to 2010 R2. Alongside this, we also migrated them from FRx to Management Reporter.
All looked fine after the upgrade until I opened one of the rows for editing and got the following warning message;