New blog in town!

 

Samuel Mathew who’s Currently working as the Manager of Technology and Development division of Eclipse Computing in its US operations has started blogging about Dynamics GP, please join me in welcoming him to the blogosphere and take a look into his blog below:

http://www.smathew-gpblog.com/

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Dynamics GP Vs. Dynamics AX

 

I been reviewing one of the questions over the internet and noticed an article at ITToolBox portal that compares Dynamics GP to Dynamics AX by “Randy Smith - President - By Design Consulting”.

The question was about a customer who already running Dynamics GP and asking what value they will gain from upgrading to AX, Mr. Smith had the below answer:

I may be a bit bias but I will throw my 2 cents into the ring. I am a Dynamics GP reseller that specializes in Process/Formula manufacturing and I am also a part owner in a product that services process manufacturing for the Dynamics GP, SL and NAV markets - Vicinity Manufacturing (http://www.vicinitymanufacturing.com/)
GP tends to be more out of the box and relative to AX not as easy to customize by the user. It has a tremendous number of ISVs (development firms with specific niche) that support the product. In general GP offers good core functionality and relies on a best of breed approach to address the unique challenges - such as process manufacturing.
From my take on things AX brings some real power and flexibility to the table in the form of customization and security. With that power comes a price. It is my experience that companies wishing to take advantage of what AX offers really need to be able to commit internal development resources to the system. The system is really written and exposed to be customized to address very specific requirements a company may possess. The investment in labor is not insignificant. It is my opinion that AX would require at least one programmer on staff if not a small team. For some organizations that is not an issue and is just part of the make up of the company. For smaller companies this becomes and issue.

My ISV organization has chosen not to go into AX at this time because GP, SL and NAV address the $5-200 million market pretty well. Additionally the functionality in non-AX products is similar to the AX product (short of the customization capabilities previously mentioned) and there are more ISV products available in GP, SL and NAV. So we are sitting on the sidelines watching the AX product mature a bit. As we do that we are seeing more and more AX functionality make it into the GP, SL and NAV products as well. That is keeping the playing field pretty level.

There are situations that AX is a really good fit - don't get me wrong. I find however that there are a number of companies that pick AX when GP, SL or NAV would have done just fine and cost significantly less to implement. The real cost difference is not in software but rather in services. My experience shows that companies not getting what they want out of GP, SL or NAV are probably not working with a strong enough VAR or one that is only partially dedicated to the GP channel. That is not always the case but it is more times then not.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

How to add attachments to Purchase Order Workflow?

 

I got a request from one of my customers to add attachments to Purchase Order Approval and I thought this might be useful for others, follow steps below to add attachments:

1. Click on Documents in Workflow:

2. Click on any document Title:

3. Click on Edit Item:

4. Click on Attach File:

5. Select file to attach:

6. Notice the attachment icon:

Enjoy!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Empty “Table Name” when doing Import/Export for EFT File Format

 

Have you ever tried to import and export EFT File Format and found the table name “empty”? It is actually looks like a bug in the applications, it does not import the series of the EFT file upon import and leave it as “0”.

To resolve this, go to SQL Server and open table CM00103, set the series field to “4” for purchasing and enjoy!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

SOP Master Numbers not being assigned properly

 

A unique document number could not be found. please check setup.

I been with a situation where my customer were getting the above messages, noticed that the system is updating the master number in SOP40100 to a smaller number which sometimes being exist in SOP10100 or SOP30200.

I workaround this by creating a trigger on SOP10100 and SOP30200 to update the SOP40100 once saving or updating transactions and set the next master number to be current maximum master number plus 1!

Below the scripts I used:

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

Enjoy!

UPDATE: Folks at Accolade Publications, Inc has publish an article that contains a modified script to fix this issue, below is the modified script:

/****** Object: Stored Procedure dbo.sopGetMasterNumber ******/
if exists (select * from sysobjects where id = object_id('dbo.sopGetMasterNumber') and sysstat & 0xf = 4)
drop procedure dbo.sopGetMasterNumber
GO

create procedure dbo.sopGetMasterNumber
@O_iOUTMasterNumber int = NULL output,
@O_iErrorState int = NULL output
as

/*
**********************************************************************************************************
* (c) 1994 Great Plains Software, Inc.
**********************************************************************************************************
*
* PROCEDURE NAME: sopGetMasterNumber
*
* SANSCRIPT NAME: Get_Master_Number of form SOP_Entry
*
* PARAMETERS:
* @O_iOUTMasterNumber Retreived Master Number
* @O_iErrorState contains any errors that occur in this procedure
*
* DESCRIPTION:
* Returns the next number field from the given SOP_SETP record and increments
* the next number.
*
* Customization was made to look at SOP40500 to verify the NXTMSTNO is larger than existing values.
*
* TABLES:
* Table Name Access
* ========= =====
* SOP40100 Read/Write
*
* DATABASE:
* Company
*
*
* RETURN VALUE:
*
* 0 = Successful
* non-0 = Not successful
*
* REVISION HISTORY:
*
* Date Who Comments
* ------------- -------- -------------------------------------------------
* 24Jun98 msluke Initial Creation
*****************************************************************************
*/

declare @tTransaction tinyint,
@iError int,
@MaxMSTRNUMB int

/*
* Initialize variables and Output Parameters.
*/
select @O_iOUTMasterNumber = 0,
@O_iErrorState = 0

/*
* Start a transaction if the trancount is 0.
*/
if @@trancount = 0
begin
select @tTransaction = 1
begin transaction
end

/*
* Read record from SOP_SETP table within an update statement so a lock is held
* on the record until the number is updated. This will ensure that only a single
* user is reading this record at any given time.
*/
update
SOP40100 WITH (TABLOCKX,HOLDLOCK)
set
@O_iOUTMasterNumber = NXTMSTNO,
NXTMSTNO= NXTMSTNO + 1

if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
/*
* Do an additional read from SOP40500 to attempt to recover from the situation where the NXTMSTNO
* is less than or equal to the max value in SOP40500.
*/
select @MaxMSTRNUMB = max(MSTRNUMB) from SOP40500 (nolock)
if (@MaxMSTRNUMB >= @O_iOUTMasterNumber)
begin
update
SOP40100
set
@O_iOUTMasterNumber = @MaxMSTRNUMB + 1,
NXTMSTNO= @MaxMSTRNUMB + 2
if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
end

/*
* Reset next master number to 2, if master number has reached max value
* or it is zero.
*/
if (( @O_iOUTMasterNumber = 99999999) or ( @O_iOUTMasterNumber = 0)) and @O_iErrorState = 0
begin
select @O_iOUTMasterNumber = 1
update
SOP40100
set
NXTMSTNO = 2

if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
end

/*
* Determine if a rollback or commit should be executed.
*/
if @O_iErrorState <> 0
begin
select @O_iOUTMasterNumber = 0
/*
* Rollback the transaction if this procedure started it.
*/
if @tTransaction = 1
rollback transaction
end
else
begin
/*
* Commit the transaction if this procedure started it.
*/

if @tTransaction = 1
commit transaction
end

return

GO

GRANT EXECUTE ON dbo.sopGetMasterNumber TO DYNGRP
GO

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

 

Today, one of my customers reported the below error message whenever they try to post any kind of inventory transactions:

Microsoft SQL Native Client SQL Server Cannot insert the value NULL into column ‘SEQNUMBR’, table dbo.IV10400, column does not allow nulls. UPDATE fails.

Nothing modified at their database and the error start appearing suddenly, they are running the latest service pack for GP and running SQL Server 2005 with the latest service pack.

They followed all the known procedures trying to figure out what’s the reason behind this, but unfortunately non of the procedures helped. Worth to mention that the transaction posts properly and does not actually has any obvious issues.

I ran an SQL Server Profiler trace and tried to locate the actual reason behind the issue and noticed the problem in the Extended Pricing module.

The issue is that the customer has 50 concurrent users working on the application and they have big number of transactions daily, which lead to the fact that IV10400 table is no longer accepting new entries, the table contains a field called “SEQNUMBR” which holds the sequence number for transaction and increased by 16XXX each time a new record created.

The maximum number returned for SEQNUMBR returned was 22,000,000 which is too large to be handled by an integer data type, I had to modify the table structure for IV10400 to replace the integer data type with bigint and to update a stored procedure called “sopExtPriceBookSetup” to replace the declaration of integer variables into bigint’s, if you need help in replacing this, just let me know and I will send you the stored procedure I already modified.

Enjoy!

UPDATE: Before applying modifications on the database, I checked the linked table in dexterity and noticed that the Sequence Number is defined as long integer, that’s why I posted the article, but my customer informed me that this did not work as expected and they gets additional errors! I check the maximum number they achieved after 3 working years in the table and it was 2,700,000,000! It works as all sequence numbers in Dynamics GP by multiplying 16384 which allows only 131072 records in the table as the maximum allowed length for long integer is an integral number in the range [–2,147,483,648 to 2,147,483,647].

Therefore, I had to re-index the table to reduce the values by exporting table data to an excel sheet, renumber the SEQNUMBR field and return the data back to GP.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

The Most Influential People in Microsoft Dynamics for 2011

 

I don’t know how the folks of DynamicsWorld evaluates the votes nor how they rank, but I am ranked #38 in the list! Below what they used to rank:

We base our selections on a number of criteria including: Number of nominations received; Number of Microsoft Dynamics employees employed; Number of clients your ISV/VAR has; As an end user how many user licences taken; Social Media Followers (bloggers/twitter/ linkedin) (bonuses for group owners); Number of years experience of Microsoft Dynamics; MVP Recipients; Developers of Add-Ons and new verticals; Speakers and writers about Microsoft Dynamics; Forum contributors and Number of votes received. We attempt to limit what can actually be described as a sphere of influence. The advantage that we have had in quantifying influence in Microsoft Dynamics is that we are dealing with a group of people with similar interests, and so it is possible to be able to quantify one persons influence over the group against another person’s influence.

Many known names were listed, Erik Ernst was listed on rank 13, while Mark Polino was ranked 16, Mariano Gomez ranked 30, Leslie Vail was ranked 68, Steve Endow was ranked 79 and Victoria Yudin was ranked 85.

Below is the complete list:

http://www.dynamicsworld.co.uk/top-100/

On the other hand, it worth to mention that I couldn’t find many other names that really have huge influence on Dynamics.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Cash account is not being loaded in Cash Receipt and Manual Payment if the payment type is “Check”

 

I got an interesting question from one of my blog fellows, the guy been trying to create a manual payment and cash receipt of type check, while “Cash” type work perfectly, “Check” type does not load the cash account.

I have connected to the environment and validated the accounts are properly configured, but noticed that “Payment Document Management” is installed and activated for no reason.

When having payment document management activated, application will look into the accounts in payment document setup screen linked to the checkbook master.

Your available options are either to disable the module from the database as you cannot disable it from the screens by running the below script:

UPDATE     RVLPD001

SET           EnablePmntDocPM = 0, EnablePmntDocRM = 0

Or setup accounts for your checkbook in the required screen.

This will resolve your issue!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

eConnect Error: The argument type `SqlClient` cannot be converted into parameter type `Microsoft.Dynamics.GP.eConnect.EnumTypes+ConnectionStringType`.

 

After upgrading one of my customers from GP 10.0 to GP 2010, I noticed an error in eConnect adapter developed to integrate receiving, the error was:

The argument type `SqlClient` cannot be converted into parameter type `Microsoft.Dynamics.GP.eConnect.EnumTypes+ConnectionStringType`.

While investigating this out, I noticed that I have upgraded the eConnect on the client’s server but didn’t upgraded the .Net solution DLL to the new eConnect!

Upgrading the eConnect DLL on my solution resolved the problem!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Post to General Ledger has not been marked for this batch.

 

I got a call yesterday from one of my customers, they have a case that they are being notified when posting inventory transactions that the transaction will not post to GL:

“Post to General Ledger has not been marked for this batch. Analytical Accounting information that may be created for this batch will be deleted on posting. Do you want to continue?”

First things I used the customer to check is the posting setup, but they confirmed that nothing changed and posting setup is marked to post to GL.

Then I realized the they are posting a “transaction” not a “batch”, which lead to the fact that something is incorrect.

I asked them to go to SY00500 and check if there is any batches with empty batch number and that was the case! They had a batch with empty batch number exists in the table and not marked to post to GL and GP was looking into this table when posting without batches!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com