Advise alternative Item in document row

Ronald Grentzius

Hello everyone,

I want to advise our employees if an alternative item is available. We don't use standard SAPB1 availablity check for several reasons. I modified a working rule, but I keep getting a failure message:



Error in coresuite customize Rule System.InvalidCastException: Kan COM-object van het type System.__ComObject niet converteren naar interfacetype SAPbouiCOM.EditText. Deze bewerking is mislukt doordat de QueryInterface-aanroep voor het COM-onderdeel vo.



So my intention, when an ItemCode is selected in a document row, the rule checks if an alternative item is registered in the tabel OALI and shows this in a popup. If there is no alternative item nothing happens.Thanks in advance for the help!



The code:



Try

Dim ItemCode As String

'get ItemCode code

ItemCode = customize.UI.Components.TextEdit.GetFromUID(pVal.Form, "38").Value



'query to get alternative items

Dim query As String

query = "SELECT T0.AltItem FROM OALI T0 WHERE T0.OrigItem = @ItemCode"

Dim cmd As System.Data.SqlClient.SqlCommand

cmd = New System.Data.SqlClient.SqlCommand(query)

cmd.Parameters.AddWithValue("@ItemCode", ItemCode) 'parameter



'execute query

Using sdr As System.Data.SqlClient.SqlDataReader = customize.B1Connector.GetB1Connector().ExecuteQuery(cmd)

If sdr.Read() Then

Dim outputString As String

Dim text1 As String

Dim text2 As String

Dim text3 As String

text1 = sdr(0).ToString() 'queryresultaat 1

text2 = sdr(1).ToString()'queryresultaat 2

text3 = sdr(2).ToString()'queryresultaat 3

If Not String.IsNullOrEmpty(text1) Then

If Not String.IsNullOrEmpty(text2) Then

outputString = "LET OP! Er is een alternatief artikelbeschikbaar"

Else

outputString = text1

End If

Else

If Not String.IsNullOrEmpty(text2) Then 'IsNullOrEmpty controleerd of de waarde leeg ('') of null (database leeg) is.

outputString = text2

Else

Return True

End If

End If

customize.UI.Dialogs.MessageBox.Show(outputString, "OK")

End If

End Using

Catch ex As Exception

customize.Messaging.StatusBar.WriteError("Error in coresuite customize Rule " & ex.ToString())

customize.Messaging.Debug.WriteMessage("Error in coresuite customize Rule " & ex.ToString(), customize.Messaging.Debug.DebugLevel.Exception)

End Try



Return True

SeKo

You are doing it wrong... item "38" is the matrix. So you should load it as a matrix:



Matrix mtx = Matrix.GetFromUID(pVal.Form,"38");



When do you launch this rule? If it's on the validate of the itemcode column of the matrix, you can use pVal.Row to give you the current row.



string itemcode = mtx.GetValue(pVal.Row,"1")

Ronald Grentzius

Hi SeKo



Thanks for your support!



I modified a working rule which was made by a consultant and he explained to me what to modify to use it for other functionalities, but I have not much programming skills. But I allways try first, the best way to learn :-).



I will try to use your explanation and hopefully I get it to work. Thanks!

Ronald Grentzius

Hi SeKo



I managed to load a new grid from the matrix with good results. When I searched the forum I found it had to be done completely different than I've setup.



The rule is triggered when the value of item 38 is changed in the form. Only now the rule always loads a grid, even when there are no alternative items available. Is it possible to load the grid only when the query has results?



Thanks!



Code:



try

{

SwissAddonFramework.UI.Components.Grid grid = SwissAddonFramework.UI.Components.Grid.CreateNew("COR_GR1");

grid.Width = 900; grid.Height = 200; grid.Top = 5; grid.Left = 5;



SwissAddonFramework.UI.Components.Form form = SwissAddonFramework.UI.Components.Form.CreateNewForm("COR_CUS_SHOW1", "COR_CUS_SHOW1" + SwissAddonFramework.Utils.UniqueStringGenerator.Next());

form.Height = 300; form.Width = 950; form.Top = pVal.Form.Top; form.Left = pVal.Form.Left;

form.Value = "Er zijn alternatieve artikelen gevonden!";



grid.Height = 220;



SwissAddonFramework.UI.Components.Button buttonOK = SwissAddonFramework.UI.Components.Button.CreateNew("2");

buttonOK.Value = "OK";

buttonOK.SetPosition(grid.Left, grid.Top + grid.Height + 10);

form.AddItem(buttonOK);



form.AddItem(grid);



form.Load();



string itemCode = Matrix.GetFromUID(pVal.Form, "38").GetValue("1", pVal.Row - 1);

string query = "SELECT dbo.OITM.CodeBars, dbo.OALI.AltItem, dbo.OITM.ItemName, dbo.OALI.Remarks, dbo.ITM1.Price FROM dbo.OALI INNER JOIN dbo.OITM ON dbo.OALI.AltItem = dbo.OITM.ItemCode INNER JOIN dbo.ITM1 ON dbo.OALI.AltItem = dbo.ITM1.ItemCode WHERE dbo.ITM1.Pricelist = '1' AND dbo.OALI.OrigItem = '" + itemCode + "' ";



grid.ExecuteQuery(query);



grid.Columns["CodeBars"].Editable = false;

grid.Columns["AltItem"].Editable = false;

grid.Columns["ItemName"].Editable = false;

grid.Columns["Remarks"].Editable = false;

grid.Columns["Price"].Editable = false;



}

catch(System.Exception ex)

{

SwissAddonFramework.UI.Dialogs.MessageBox.Show("Unexpected error happend while trying to display the values of the query!\n" + ex.Message, "OK");

}

return true;

SeKo

You should run the rule on item 38 on column 1 so that it only runs when the itemcode is changed. Then you should run a query that counts the alternative items. If it returns 0, just leave the function. If it's >0, you can load the form.