Number to Words in Report Writer

By David Musgrave

One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report Writer Functions) was the RW_ConvertToWordsAndNumbers() function to convert a currency amount into words. After the code was added, it was realised that Report Writer calculated fields of return type string are limited to 80 characters. This means that if your amount in words is longer than 80 characters, only the first 80 characters will be returned and the rest will be truncated and lost.

This example using the “Check with Stub on Top” report was created to provide a method of using Visual Basic for Applications (VBA) to overcome this 80 character limitation. The code uses the Continuum Integration Library to execute Dexterity sanScript code and also uses the Dynamic User Object Store (DUOS) to allow the Dexterity code to return its results to VBA. The DUOS is used because the DUOS table (SY90000) is easily visible to Dexterity as the SY_User_Object_Store table and to VBA as the DUOS objects.

The VBA code creates a Collection and creates 3 blank DUOS records to store 3 string properties. Then the code uses pass through Dexterity to call the RW_ConvertToWordsAndNumbers() function and then uses the RW_ParseString() function to split the returned result into 3 lines of 50 characters which are then stored into the SY_User_Object_Store table. When the code returns to VBA, it reads the DUOS records and displays the results to the report and finally removes the records in the DUOS.

NOTE: This customization uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.

Example code for v8.0, v9.0 & v10.0 is found under the following link:
http://blogs.msdn.com/developingfordynamicsgp/attachment/8968837.ashx

Regards,

Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Advertisements

Microsoft Dynamics GP Service Pack 3!

Link below will redirect you to the download page for GP SP3:

https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP10_ServicePack3.htm

Regards,

Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Filter Dynamics GP Lookups Using VBA

Guys,

A got allot of mails requesting to have Item Security functionality to distribute sites items and show only certain items for the warehouse keeper.

For those who are looking for such functionality, you need to follow the steps below:

1. Create a new table in the database that contains “User ID”, “Item Number” fields. This table will hold the items that will be mapped to each user.
2. Add “Items Lookup” form to VBA and include Item Number field.
3. Get the current connected user from Globals.
4. Loop the following code on all the items returned for the logged user:

Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
If ItemNumber.Value “Does not exist in our table” Then
RejectLine = True
End If
End Sub

Please let me know if you may need any help, this code is applicable for all lookup forms and details windows.

Regards,

Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Filling Missing Transactions Numbers in GP

If the end user created any kind of transactions in GP that holds let’s say the ID 9 and another one that holds 10, then deleted 9; the next number will be 11 and 9 will be ignored.

For the clients that do not accept these gaps, and since almost all of GP transaction tables have setups for loading next ID number, I have created the following solution:

Create a trigger on your transactions table For INSERT and DELETE that perform the following actions:

UPDATE “Setup-Table” SET “Next-ID” =

(SELECT TOP 1 Covert(BIGINT, A.Number) + 1 As NextID From Table As A LEFT OUTER JOIN Table As B on Covert(BIGINT, A.Number) + 1 = Covert(BIGINT, B.Number) WHERE B.Number IS NULL)

WHERE SETUPKEY = XXX

Replace the “Number” with column name and Table with your table name.
Now each time a transaction added or deleted, your trigger will get the next available number and update the defined next number.

Regards,

Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

Dynamics GP, AX, SL, NAV into one ERP called Project Green?

Project Green is Dead!

Project Green started making headlines in late summer 2003, a few months before Microsoft made the formal announcement of what was supposed to become a horizontally integrated ERP suite. Project Green would do for ERP applications what bundling did for Office: put together disparate but related applications in a tidy, integrated package and eventually bring them to a single code base.

But Project Green seemed doomed right from its conception, with the horizontal-bundling task running into logistical and management problems. What looked good in concept proved harder to execute, and the channel may have been one of the major reasons.

http://www.eweek.com/c/a/Enterprise-Applications/Project-Green-is-Dead151for-the-Foreseeable-Future-at-Least/
http://fscavo.blogspot.com/2007/03/microsofts-project-green-is-dead.html
http://www.microsoft-watch.com/content/business_applications/rip_project_green_2003_2007.html

Regards,

Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

DEX.INI Commands Explanation

Interested to learn more about DEX.ini file and what does it effect? I found a post under (http://msdynamicsgp.blogspot.com/2007/09/weekly-dynamic-dexini-settings.html) and found it interesting, below the parameters definition for DEX.ini File:

SuppressChangeDateDialog=TRUE – Turns off the Date Change dialogue that opens at midnight
Synchronize = TRUE – Forces a synchronization of the chart of accounts format
OLEPath=\\server\folder\ole – Sets the path for linked and embedded files. This is often set up inconsistently in the initial setup. You’ll need to include your path in place of file://server/folder/ole
NoPrintDialogs=TRUE – Suppress the print dialogue box
ShowResids=TRUE – Make resource ID’s visible in Dexterity
SQLLogSQLStmt=TRUESQLLogODBCMessages=TRUESQLLogAllODBCMessages=TRUE – Turn on Dex SQL Logging (all 3 lines)
ShowAdvancedMacroMenu=TRUE – Turn on the Advanced Macro Menu
C:\DPS1\DEX.INI DPSInstance=1C:\DPS2\DEX.INI DPSInstance=2C:\DPS3\DEX.INI DPSInstance=3 – Run multiple process servers on a single machine. Specifics on CustomerSource here. (One line for each process server)
AutoInstallChunks=TRUE – Add CNK files without the “Add New Code?” dialogue
ExportOneLineBody=TRUE – eliminate line wrapping when exporting reports to comma or tab delimited files.
IMPath=C:\Program Files\Microsoft\Great Plains\Integration Manager\IM.EXEIMExecPath=C:\Program Files\Microsoft\Great Plains\Integration Manager\IMRun.EXE – Set the path to Integration Manager so it will launch properly from the menu. (2 lines)
WindowMax=TRUE – Open the GP main window full screen
SampleDateMsg=FALSE – prevents the sample company dialog box from being displayed
SampleDateMMDDYYYY=00000000 – Prevents the sample company dialogue box from displaying and sets the date to the current date.
SampleDateMMDDYYYY=MMDDYYYY – Prevents the sample company dialogue box from displaying and sets the sample company to the date defined. (5/1/08 would be 050102008).

Regards,

Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/

List of inventory items that have not sold between two dates

View below list the inventory items that have not sold between two dates:

SELECT IV00101.* FROM
dbo.IV00101 INNER JOIN
dbo.IV00102 ON dbo.IV00101.ITEMNMBR = dbo.IV00102.ITEMNMBR
WHERE
(dbo.IV00101.ITEMNMBR NOT IN
(SELECT
dbo.SOP30300.ITEMNMBR
FROM dbo.SOP30300
INNER JOIN dbo.SOP30200
ON dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
AND dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE
WHERE (dbo.SOP30200.DOCDATE BETWEEN
CONVERT(DATETIME, ‘2006-07-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2008-10-30 00:00:00’, 102))
))
AND (dbo.IV00102.QTYONHND > 0)

Regards,

Mohammad R. Daoud
MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 – 79 – 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com/