Minimum Order Quantity.

[Sunny]

Hi,
Customer has a Custom Field- CtnQty for certain Inventory items and Sales order is to be raised with a minimum of this Quantity or multiples thereof.
I have created a plugin (attached) which creates a popup message box warning user of the minimum quantity, if this scenario occurs.
However customer requires that we actually enter this minimum order quantity in the "Quantity" column of the lines grid and the totals grid at the bottom right to update.
Could you tell me the event handler and the business logic code to handle this?

Thanks!
Sunny

[Mike.Sheen]

Hi,
Customer has a Custom Field- CtnQty for certain Inventory items and Sales order is to be raised with a minimum of this Quantity or multiples thereof.
I have created a plugin (attached) which creates a popup message box warning user of the minimum quantity, if this scenario occurs.
However customer requires that we actually enter this minimum order quantity in the "Quantity" column of the lines grid and the totals grid at the bottom right to update.
Could you tell me the event handler and the business logic code to handle this?

Thanks!
Sunny

Your handler SalesOrderLine_Added just needs to set the item.QuantityOrdered, i.e.:

Code: Select all

Public Sub SalesOrderLine_Added(ByVal item As JiwaSales.SalesOrder.SalesOrderLine)
      'msgbox(item.PartNo)         
      '---------------------
      Dim Sql As String
        Dim SQLReader As SqlDataReader = Nothing
        Dim SQLParam As SqlParameter = Nothing
      Dim sNote As String = ""
        Try                 
            With JiwaApplication.Manager.Instance.Database               
                Sql = "SELECT dbo.IN_Main.PartNo, dbo.IN_CustomSettingValues.Contents FROM dbo.IN_CustomSettingValues INNER JOIN dbo.IN_CustomSetting ON dbo.IN_CustomSettingValues.SettingID = dbo.IN_CustomSetting.SettingID INNER JOIN dbo.IN_Main ON dbo.IN_CustomSettingValues.InventoryID = dbo.IN_Main.InventoryID WHERE (dbo.IN_CustomSetting.SettingName = 'CtnQty') AND (dbo.IN_CustomSettingValues.Contents <> '') AND (dbo.IN_Main.PartNo = '" & item.PartNo & "')"
            'msgbox(Sql)
            Using SQLCmd As SqlCommand = New SqlCommand(Sql, .SQLConnection, .SQLTransaction)                         
                    SQLReader = SQLCmd.ExecuteReader()               
               If SQLReader.HasRows Then                  
                  Do While SQLReader.Read = True   
                                                        item.QuantityOrdered = .Sanitise(SQLReader, "Contents")         
                        End If
                     Loop                  
               End If   
                    SQLReader.Close()
                End Using
            End With
        Finally
            If Not SQLReader Is Nothing Then                  
                SQLReader.Close()
            End If
        End Try   
      '-----------------------      
   End Sub


I've just taken your existing code and added in the change - but I note you're being very naughty and building a sql query string instead of using SqlParameters. SQL Injection is mitigated when using SqlParameters, so I advise you use them!

Here's an example to get you on the right track:

Code: Select all

sql = "SELECT dbo.IN_Main.PartNo, dbo.IN_CustomSettingValues.Contents FROM dbo.IN_CustomSettingValues INNER JOIN dbo.IN_CustomSetting ON dbo.IN_CustomSettingValues.SettingID = dbo.IN_CustomSetting.SettingID INNER JOIN dbo.IN_Main ON dbo.IN_CustomSettingValues.InventoryID = dbo.IN_Main.InventoryID WHERE (dbo.IN_CustomSetting.SettingName = @CustomFieldName) AND (dbo.IN_CustomSettingValues.Contents <> '') AND (dbo.IN_Main.PartNo = @PartNo)"

Using SQLCmd As SqlCommand = New SqlCommand(Sql, .SQLConnection, .SQLTransaction)
   SQLParam = New SqlParameter("@PartNo", System.Data.SqlDbType.VarChar)
   SQLParam.Value = item.PartNo
   SQLCmd.Parameters.Add(SQLParam)

   SQLParam = New SqlParameter("@CustomFieldName", System.Data.SqlDbType.VarChar)
   SQLParam.Value = "CtnQty"
   SQLCmd.Parameters.Add(SQLParam)

   SQLReader = SQLCmd.ExecuteReader()
   ...
End Using


Note that just handling the Added event of the sales order lines won't help if they key in the quantity. You will want to handle the Changed event of the sales order line as well:

Code: Select all

Public Sub SalesOrderLine_Changed(sender As Object, ByVal item As JiwaSales.SalesOrder.SalesOrderLine, ByVal e As System.ComponentModel.PropertyChangedEventArgs)
    If e.PropertyName = "QuantityOrdered" Then
        Dim SalesOrder As JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder = sender
        RemoveHandler SalesOrder.SalesOrderLines.Changed, AddressOf SalesOrderLine_Changed ' Remove the handler to prevent recursion
        Try
            ' Replicate the code to get the inventory custom field value here - You might want to refactor that to be a function, so both Added and Changed call the same code
            item.QuantityOrdered = .Sanitise(SQLReader, "Contents")
            ' item.QuantityOrdered = GetProductCartonQuantity(item.InventoryID) ' <--- this is better - make a method named GetProductCartonQuantity which returns the carton quantity
         Finally
             AddHandler SalesOrder.SalesOrderLines.Changed, AddressOf SalesOrderLine_Changed ' Add back the handler
         End Try
    End If
End Sub


And don't forget to add the handler in the plugin Setup for the line changed:

Code: Select all

    AddHandler salesOrderForm.SalesOrder.SalesOrderLines.Changed, AddressOf SalesOrderLine_Changed


[Sunny]

Hi Mike,
Thanks for the help, and I will mend my ways re using SqlParameters!
There are 2 issues:
1. First I tried to execute the Plugin by just setting the item.QuantityOrdered There are no errors and the plugin compiles just fine, but the Quantity column gets populated by the default value-1
2. Once I added the SalesOrderLine_Changed handler I am getting error "does not have a signature compatible with delegate 'Delegate Sub ChangedEventHandler..

I have modified the Plugin and the revised plugin is attached.

The reason I have not used function is because more code is required to factor in logic that user can enter multiples of Carton Quantity in the line change event.

Thanks for yor help.
Sunny

[Mike.Sheen]

Hi Mike,
Thanks for the help, and I will mend my ways re using SqlParameters!
There are 2 issues:
1. First I tried to execute the Plugin by just setting the item.QuantityOrdered There are no errors and the plugin compiles just fine, but the Quantity column gets populated by the default value-1
2. Once I added the SalesOrderLine_Changed handler I am getting error "does not have a signature compatible with delegate 'Delegate Sub ChangedEventHandler..

I have modified the Plugin and the revised plugin is attached.

The reason I have not used function is because more code is required to factor in logic that user can enter multiples of Carton Quantity in the line change event.

Thanks for yor help.
Sunny

1. This is most likely because the setting of the quantity happens after the added event you are handling. Try the AddInventoryItemEnd event instead - this should be fired after the line is added, and after the dafault quantity is set.
2. That was my fault - There are two ways to hook into the line added event - and the one I supplied was incorrect for the AddHandler.

Change

Code: Select all

Public Sub SalesOrderLine_Changed(sender As Object, ByVal item As JiwaSales.SalesOrder.SalesOrderLine, ByVal e As System.ComponentModel.PropertyChangedEventArgs)

To this:

Code: Select all

[code]Public Sub SalesOrderLine_Changed(ByVal item As JiwaSales.SalesOrder.SalesOrderLine, ByVal e As System.ComponentModel.PropertyChangedEventArgs)[/code]

[Sunny]

Hi Mike, I'm almost there. The code below is now giving an error as there is now no declaration of sender. What should it be?

Code: Select all

Dim SalesOrder As JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder = sender

Thanks.
Sunny

[Mike.Sheen]

Hi Mike, I'm almost there. The code below is now giving an error as there is now no declaration of sender. What should it be?

Code: Select all

Dim SalesOrder As JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder = sender

Thanks.
Sunny

Try:

Code: Select all

Dim SalesOrder As JiwaFinancials.Jiwa.JiwaSales.SalesOrder.SalesOrder = item.SalesOrderLines.SalesOrder

You don't really need the SalesOrder variable, you could just change the references from SalesOrder to item.SalesOrderLines.SalesOrder in that method.

Mike

[Sunny]

Thanks Mike, that worked!
Appreciate your help.
Sunny