Mark Polino has a set of Historical Excel Reports available for sale. The reports available are as follows:
- Receivables Management Historical Aged Trial Balance
- Payables Management Historical Aged Trial Balance
- Historical Inventory Trial Balance
- Historic Stock Status Report
Now is the ideal time to buy them, as the price is increasing on 1st April 2017.
While implementing Microsoft Dynamics GP for a new client a while ago, we used Integration Manager to import over 100,000 Inventory items and then a SQL script to insert the 80,000 bill of materials (BOMs).
This worked well, in that it got all of the information loaded, but found that if we inserted the BOMs in this way, they could not be viewed in the BOM Inquiry window.
I used a SQL query to change the status from Active to Pending and then looked at the best way of changing them to Active through the Dynamics GP.
The only way to bulk change the data was by using a GP Macro; the usual way we do this is to do an extract of the data and then mailmerge this into a prerecorded macro in Microsoft Excel.
However, this can be avoided, by using the SQL select to not only get the data, but to output the macro at the same time:
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK). */ SELECT DISTINCT '# DEXVERSION=11.00.0364.000 2 2 CheckActiveWin dictionary ''default'' form bmBillMaintenance window bmBillMaintenance TypeTo field ''Item Number'' , ''' + RTRIM(BMH.ITEMNMBR) + ''' MoveTo field ''Bill Status'' item 0 ClickHit field ''Bill Status'' item 2 # ''Pending'' MoveTo field ''Expansion Button 1'' ClickHit field ''Expansion Button 1'' NewActiveWin dictionary ''default'' form bmBillMaintenance window ChangeStatus ClickHit field ''Bill Status'' item 1 # ''Active'' MoveTo field ''Process Button P'' ClickHit field ''Process Button P'' NewActiveWin dictionary ''default'' form bmBillMaintenance window bmBillMaintenance MoveTo field ''Save Button'' ClickHit field ''Save Button'' ' FROM BM00101 AS BMH INNER JOIN BM00111 AS BMC ON BMC.ITEMNMBR = BMH.ITEMNMBR WHERE BMH.Bill_Status = 2
I needed to make sure that SSMS was configured to return the data into text and that the data returned was more than the default 256 characters.
Once you have the returned macros, save the file, open Bill of Materials Maintenance and then run the macro.
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.
I have some clients who use the Bill of Materials and assemblies within the Inventory series rather than the Manufacturing series and full MRP; their processes are not so complex that they need this level of MRP functionality. To make it easy to link an assembly to a sales order, the assemblies are created with the same ID as the order (one of the clients has a high level of automation added via customisations to automatically create the assembly from the order).
I’ve created a script to return this information on more than one occasion, so finally decided to post it here so I can easily find it.
CREATE VIEW uv_AZRCRV_SalesOrdersToBeAssembled AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ SELECT CASE WHEN LEFT(RTRIM(SOP102.SOPNUMBE),3) = 'ORD' THEN SUBSTRING(RTRIM(SOP102.SOPNUMBE),4,LEN(RTRIM(SOP102.SOPNUMBE))-3) ELSE RTRIM(SOP102.SOPNUMBE) END + CASE WHEN (SELECT COUNT(SOPNUMBE) FROM SOP10200 SOP102I WHERE SOP102I.SOPNUMBE = SOP102.SOPNUMBE) > 1 THEN '_' + CAST(SOP102.LNITMSEQ/13684 AS VARCHAR(2)) ELSE '' END AS 'Assembly' ,FORMAT(GETDATE(), 'yyyyMMdd') AS 'Batch Number' ,RTRIM(SOP102.ITEMNMBR) AS 'Item Number' ,CASE WHEN SOP102.UOFM = 'EACH' THEN CAST(CAST(SOP102.QUANTITY AS DECIMAL(10,0)) AS VARCHAR(10)) ELSE CAST(CAST(SOP102.QUANTITY*10000 AS DECIMAL(10,0)) AS VARCHAR(10)) END AS 'Quantity' ,SOP102.UOFM AS 'UofM' ,SOP101.BACHNUMB AS 'Sales Batch' FROM SOP10200 SOP102 WITH (NOLOCK) INNER JOIN SOP10100 SOP101 WITH (NOLOCK) ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE INNER JOIN BM00101 BM101 WITH (NOLOCK) ON BM101.ITEMNMBR = SOP102.ITEMNMBR AND BM101.Bill_Status = 1 LEFT JOIN BM10200 BM102 WITH (NOLOCK) ON BM102.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3)) LEFT JOIN BM30200 BM302 WITH (NOLOCK) ON BM302.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3)) WHERE SOP102.SOPTYPE = 2 AND BM102.TRX_ID IS NULL AND BM302.TRX_ID IS NULL GO GRANT SELECT ON uv_AZRCRV_SalesOrdersToBeAssembled TO DYNGRP
I think this would be a good additin, so please take a look and cast your vote.
While implementing Microsoft Dynamics GP for a client earlier this year, we used macros to load Assembly Transactions into Dynamics GP as there is no integration available in Integration Manager. The macros were created on the clients development server, tested and then migrated to the live server.
However, despite the development and live servers having exactly the same Windows and Dynamics GP configurations, the following error was generated on the live server when trying to run the macro:
Microsoft Dynamics GP
Keyword or punctuation expected, but not found. (Line #2)
# DEXVERSION=11.00.0359.000 2 2 CheckActiveWin dictionary 'default' form bmTrxEntry window bmTrxEntry TypeTo field 'TRX ID' , '262307A' MoveTo field 'TRX Date'
The code displayed above is from the macro file; the problem was the key words (such as dictionary) were not capitalised when the macro was recorded and tested on the development server, but needed to be capitalised for the live server. To get the capitalisation correct, I recorded a macro on live and then used the source CSV file to create a new macro by using the newly recorded one as a template for the mail merge.
It did mean that this was not tested the way the previous one was, but we’d proved the method on test and this was deemed acceptable.
On this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.
As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):
CREATE VIEW uv_AZRCRV_CustomerItemLink AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ SELECT SOP102.SOPNUMBE ,SOP102.SOPTYPE ,SOP102.LNITMSEQ ,SOP101.CUSTNMBR ,SOP102.ITEMNMBR FROM SOP10200 AS SOP102 WITH (NOLOCK) INNER JOIN SOP10100 AS SOP101 WITH (NOLOCK) ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE INNER JOIN SOP60300 AS SOP603 WITH (NOLOCK) ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR GO GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP GO
This one came up from a query a client had about linking a sales order transaction line to the customer item in SmartList Builder. The problem is that to join two tables together, you need all of the key fields to be on the same table; unfortunately, with the SOP Transaction table, this isn’t the case when you want to link to the customer item.
SOP10100 (Sales Transaction Work) holds the CUSTNMBR (Customer Number), but SOP10200 (Sales Transaction Amounts Work) holds the ITEMNMBR (Item Number) which are both needed to link to SOP60300 (Sales Customer Item Cross Reference) which holds the customer item number and description.
While there may be a way to do this in SmartList Builder I’ve not been able to work it out (other than using two calculated fields), it is easier, quicker and more reusable, to create a simple SQL View which returns the relevant information.
In this case the view I created works only for transactions which are at a status of work:
CREATE VIEW uv_PI_SOPCustomerItemLink AS SELECT SOP101.CUSTNMBR ,SOP102.ITEMNMBR ,SOP603.CUSTITEMNMBR ,SOP603.CUSTITEMDESC FROM SOP10200 AS SOP102 INNER JOIN SOP10100 AS SOP101 ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPOwner = SOP102.SOPOwner INNER JOIN SOP60300 AS SOP603 ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR GO GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP GO
The SQL above includes the Grant statement used to add select permissions for the DYNGRP.
One client who was using Inventory was entering a lot of user categories, mistakenly entered the description into the Image field. In that case I did not know that they were populating the User Categories or I would have offered this script to them to save time.
To use the script you need a CSV file with four columns: User Category Value (the ID of the item you want to load), User Category Number (which of the user categories into which the row is to be loaded), Image URL and Description:
CREATE TABLE #UploadData (USCATVAL VARCHAR(100) ,USCATNUM VARCHAR(1) ,Image_URL VARCHAR(300) ,UserCatLongDescr VARCHAR(300)) GO BULK INSERT #UploadData FROM 'c:\temp\UserCategories.csv' WITH (FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n') GO INSERT INTO IV40600 (USCATVAL ,USCATNUM ,Image_URL ,UserCatLongDescr) (SELECT LEFT(UD.USCATVAL, 10) ,LEFT(UD.USCATNUM, 1) ,LEFT(UD.Image_URL, 254) ,LEFT(UD.UserCatLongDescr, 254) FROM #UploadData AS UD WHERE (SELECT COUNT(IV.USCATVAL) FROM IV40600 AS IV WHERE IV.USCATVAL = UD.USCATVAL AND IV.USCATNUM = UD.USCATNUM) = 0) GO DROP TABLE #UploadData GO
You will need to change the highlighted line to the location of your CSV file. As always before running a script on live, test it in a test company first and have a good backup of your database.
Unfortunately, when they configured the User Categories in Inventory Control Setup () the description for the category was entered in the Image field instead of the Description field.
Rather than making them go through and manually change all of the categories that had been defined, I put together a small script to do the job.
The script checks if the length of the entry in the Image field is greater than zero and that the Description has not been set before doing the update.
UPDATE IV40600 SET UserCatLongDescr = Image_URL ,Image_URL = '' WHERE LEN(RTRIM(Image_URL)) > 0 AND LEN(RTRIM(UserCatLongDescr)) = 0 GO
As I was writing this script we encountered the same setup issue on a client installation we took over from another partner so this looks like it may be more common that I thought it would be; caused I think be the fact the window has the Image field visible by default and not the Description which people expect.