Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Wednesday, September 16, 2015

Rule of Thumb | Megring Line items when Migrating a Requisition into a Purchase Order

There is one important missing piece related to the requisition management in Dynamics GP that is left unexplained or not quite common for users working with the new requisition cycle. It is specifically related to migrating the existing fully approved requisition into a purchase order. 

Requisition with two line items (almost identical)
The scenario that is proposed implies that the line items have the following identical parameters or characteristics which are:
  • The two line items have the same item number
  • The two line items have the same vendor
  • The two line items have the same unit cost
In case the conditions above are true, the system will automatically merge these two line items under one line item and purchase order when purchasing the requisition or migrating this requisition into a purchase order. Let's see how line items will be grouped by and redesigned on the purchase order preview window:

Purchase Order Preview Window

As shown above, the original requisition has the following line item:
  1. Item Number (Paper) , Item Description (Paper A4), Unit Cost (1) and Vendor is (Acetrave0001) , quantity is 10
  2. Item Number (Paper) , Item Description (Paper A3), Unit Cost (1) and Vendor is (Acetrave0001), quantity is 20
On the other hand, the generated purchase order is redesigned to include one line item which is:
  1.  Item Number (Paper) , Item Description (Paper A3), Unit Cost (1) and Vendor is (Acetrave0001), quantity is 30 (which is the sum of both line items above)

Although, the important note to keep into your consideration that this is the default behavior but still it is not obligatory as you can still manipulate the line items which will be created in the desired purchase order. This can simply done through the "Organize" button on the left navigation pane, through which you can separate the line item in another purchase order and; as a result, keep the separate two line items in separate purchase orders. 

Organize Purchase Order Preview

Follow the steps below:
  • First of all, click on the button prior to the organize, which is add vendor. To add a new Purchase Order, for which you should first fill in the vendor ID field.
  • Then, click on the Organize Button highlighted above, and a warning message will pop up stating the following "please select a source node followed by a destination node"
  • Click on the required node from the two above, and then click on the new node created which is the destination. 
  • This will create two purchase orders including separately your original two line items, without having them merged under one purchase order and one line item. 
Line items are separated into two purchase orders

Best Regards,
Mahmoud M. AlSaadi

SQL Server - Determining the language of a name: Arabic English or Multilingual.

As part of Data Cleansing project, I struggled with one specific case while developing custom algorithms to cleanse, detect and smartly handle duplicates in master files. The case was primarily related to fields with mixed language. 

The original legacy system has fields for the name in which; sometimes, both Arabic and Language characters are recorded. Theoretically speaking, there must be a very simple way out of this in SQL Server, which is not supposed to be time-consuming neither a reinvention of the wheel. 

There was someone in the Stack Overflow community talking about something similar but not precisely the exact thing, he was proposing a way to determine the language of the field by using the Patter Index function in SQL server, his way is quite simple and to the point, which looks for any of the alphabetical characters within the passed string, and return the position of this character. If the return value is larger than 0, it means that any of the alphabetical characters was found (either English or Arabic). Although, that would never be comprehensive in my case as some fields may contain both characters. The function is illustrated below:

CREATE   FUNCTION [dbo].[Fn_CheckName] ( @string NVARCHAR(MAX) )
    DECLARE @Value NVARCHAR(100)
    IF ( PATINDEX(N'%[أ-ي]%', RTRIM(@string)) > 0 )
            SET @Value = 'A'
        IF ( PATINDEX(N'%[A-Za-z]%', RTRIM(@string)) > 0 )
                SET @Value = 'E'

    RETURN @Value


Now, the alternative of checking the pattern within the name which could be misinterpreted and processed, is to consider the first and last characters of the string, and compare the result with the original result retrieved by the function above. As follows:

SELECT  ( SELECT    dbo.Fn_CheckName(NAME)
        ) AS Language ,
        LEN(NAME) Length ,
        ( SELECT    dbo.Fn_CheckName(SUBSTRING(NAME, 1, 1))
        ) AS F_Character_Language ,
              ( SELECT    dbo.Fn_CheckName(SUBSTRING(NAME, 2, 1))
        ) Second_Character_Checkname,
        ( SELECT    dbo.Fn_CheckName(SUBSTRING(NAME, LEN(name), 1)))   
AS L_Character_Language ,     
FROM    [dbo].[namelist]

Here is the overall criteria when breaking down the name into characters and check the language of the first and last character to determine which is an Arabic, English or Multilingual field.

Process Visualization

Best Regards,
Mahmoud M. AlSaadi