When I returned to the office on Wednesday after being away at reIMAGINE for a few days and then being on holiday in Kentucky, I was asked for assistance by Jason on our service desk.
A client had logged an issue with a PO; lets say the PO Number was PO34791 for the sake of argument (I don’t actually recall what it was).
When the PO Documents Enquiry (Inquiry) window was used, the PO looked fine; the creditor (vendor) was the correct one and all other values were okay and the PO showed as received.
However, the PO was also showing in PO Entry with a different creditor id.
Jason showed me two SQL queries;
- The first, was a select on POP10100 WHERE PONUMBER = ‘PO34791’ and showed the correct PO.
- The second, was a select on POP10100 WHERE VENDORID = ‘1ST001’ AND DOCAMNT = ‘791.50’ and this showed the other PO which the PO Entry window was loading. This query could be run for any information unique to the PO and it would return this PO instead of the correct one; ibncluding the PONUMBER field in the select would cause it to return nothing.
All very unusual.
I ran another query against POP10100 WHERE PONUMBER LIKE ‘PO34791%’ and this returned two lines of information both showing a PONUMBER field of PO34791.
in SQL Server Management Studio I dragged the values from the recordset into the query window to compare them; one was shorter than the other. The cause was that one of them (the one for the Vendor ID 1st001) contained a control character which is why it appeared shorter.
We’re not sure what the user had done, but the second, incorrect, PO was entered with a PO Number containing a control character; they’d been able to successfully enter the PO, but GP was then becoming confused about which one should be loaded when the user typed, or did a lookup, for PO34791.
We copied the live company into test and deleted the second PO (for vendor 1st001) from all tables (it had lines and tax and so on) and then verified the data with a Check Links and Reconcile and asked the user to double check.
This process would then be repeated on live with the PO being re-keyed (it was only a single line).
The final session of the day I attended, was on Custom Workflow Creation. I was interested in this session, as we need to create some new Workflow Types. One which is needed, is a Workflow Type of EFT Bank.
The Vendor Change Workflow Approval is good, but does not do anything when the EFT Bank Details are changed and this is a major requirement for many of our clients in the UK.
The session showed how the new Workflow Type is created, tables updated, buttons added to windows and I took the opportunity to discuss wider requirements such as the need to update code elsewhere to respect the new Workflow Types created.
The changes for new Workflow 2.0 Types must be done in Dexterity.
There is a lot of scope for creating new Workflow Types in addition to EFT Bank Details such as SOP Transactions, Bill of Materials, and there my mind goes blank. Been in this airport too long.
The session on custom workflow creation was one of my favourites due to my interest in Workflow 2.0 (did I mention I am doing a second edition of my Workflow 2.0 book?).
I did a SQL Script back in October 2014 which allowed Vendor EFT Details to be inserted, but, if the client is going to run it themselves, requires the assistance of the IT Department as end users don’t usually have access to SQL Server Management Studio.
For most clients, this hasn’t usually been a problem, but a number of the clients I have worked with recently have had multiple projects on the go at the same time. This has meant that while an IT representative can be available, it isn;t usually very timely and can delay UAT.
As such, I wrote some VBA script for Integration Manager which inserts both the Vendor Email and EFT Details. Continue reading → VBA for Integration Manager to Insert Vendor EFT Bank And Email Addresses
Microsoft Dynamics GP 2013 R2 saw the introduction of the Workflow 2.0 (which was the subject of myfourth book. Workflow 2.0 was enhanced further in both Microsoft Dynamics 2015 R1 and R2, and is again being improved in Dynamics GP 2016 R1.
This time, the enhancement is a twofold one. Firstly, the Batch Edit lists have a default Word Template and, secondly, this can be emailed to the workflow approver:
The main features of are:
- Workflow Batch Approvals now have a default Word Template for the following edit lists:
- Gneeral Ledger Batches
- Payables Batches
- Receivables Batches
- Can be emailed to approver for complete information before approving
I think this feature will prove to be popular with those clietn using Workflow approvals as an approver will always want s much information before approving as possible. This will likely be of great interest when Workflow approvals are being used.
MDGP 2016 R1 Feature of the Day: Prepayments on Purchase Orders
Again this feature of the day is building on one introduced in a recent version. Prepayments were introduced in Microsoft Dynamics GP 2013 R2, but all the clients I spoke to about it said that it wasn’t useful as the prepayment could only cover the subtotal.
This feature of the day allows the prepayment amount to cover taxes, freight and miscellaneous:
The main features of are:
- Prepayment amount on purchase orders can now include Taxes, Freight and Miscellaneous
- Entire PO amount can be included in the prepayment
This update to prepayments finally makes them usable. I’ll need to remember the clients I have previously discussed it with and let them know of the new functionality.
This feature of the day adds a third transaction type to the payment process to allow a payment run to be made by credit card instead of cheque or EFT:
The main features of are:
- Option for a computer check batch to be paid by credit card
- The credit card payment will create an invoice for the credit card vendor
- A remittance form will print to show the invoices paid
I’ve just been doing an upgrade of a client to Microsoft Dynamics GP and had a discussion around the payment of invoices by credit card. When they upgrade again this feature will, I’m fairly sure, be quite popular with the payments team (unfortunately their next upgrade is probably a year or so away.
I already have my first upgrade to Microsoft Dynamics GP 2016 R1 booked in, so I will have to have a chat with them and see if they process many payments on credit card.
Microsoft Dynamics GP 2013 R2 saw the introduction of the purchase requisitions and they are being enhanced further with integration with Project Accounting:
The main features of are:
- Project Number and Cost Category ID fields available in Purchase Requisition Entry window
- Requisitions with project and cost category can still go through workflow process
- Project and cost category will flow to Purchase Order
Microsoft Dynamics GP 2015 R2 saw the introduction of the Purchasing All-In-One Document View, and Dynamics GP 2016 R1 sees the introduction of an Inventory equivalent:
The main features of Inventory All-In-One Document View are:
- View quantity in and quantity out transactions for one item in one window
- Accessible from Item Maintenance, many item inquiry windows, navigation lists and the home page
As with the Sales All-In-One Document View I am prepared to be underwhelmed by this feature of the day based on the Purchasing one introduced in Dynamics GP 2015 R2.
This has come up from a couple of different clients in the last few weeks. It would be good if approval workflows could be added to Receivings Transaction Entry and also the Enter/Match Invoices windows.
While there is workflow on the PO, a PO might not be fully received/invoiced so it would be good to have approval of the receivings document or the invoice.
The MS Connect suggestion can be found here.
Please take a look and cast your vote.
When I was doing the training I was explaining what a Temporary Creditor (or Vendor for the American readers) was and why I hated them so much. As I was talking I was showing that even through the temporary creditor itself had been deleted, the history for it remained.
It was when I tried to open the Payables Transactions navigation list that I got the below error:
Microsoft Dynamics GP[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into 'VENDNAME', table 'tempdb.dbo.##181163', column does not allow nulls. INSERT fails.[Microsoft][SQL Server Native Client 11][SQL Server]Cannot insert the value