Open Purchase Order Overview popup when double-click on a article on a sales document

Rene Jansen

Hi There,

I want a popup when I double click on the article number of a row on a sales document that shows me all OPEN purchase orders including open-quantity and delivery date.

Is this posible with the optimizer tool or must it be combine with designer?

I have tried the folowing SQL statement and rules set into cusomize optimizer. But when there are open Purchase orders there is happen nothing and when there is nothing open the give the warning. But I can't get no pop-up like a the query result popup's.

This would be very handy for this but also I want this kind of tooling for open sales orders etc.

Type: SQL
Handeling: VALIDATE
Form Type: 139
UID: 38
Col: 1
Type: DoubleClick
Modus: ALL

SELECT     dbo.OPOR.DocNum, dbo.OPOR.DocStatus, dbo.POR1.ShipDate, dbo.POR1.OpenQty
FROM         dbo.OPOR INNER JOIN
                      dbo.POR1 ON dbo.OPOR.DocEntry = dbo.POR1.DocEntry
WHERE     (POR1.ItemCode = [%A;38;1]) AND (dbo.OPOR.DocStatus = 'O')

Many thanks in advance.

Rene Jansen

Michael Egloff

Hi Rene,

you missunderstood the type 'SQL' of the optimizer.
The meaning of this is to check on the DB and then give a warning. It is not meant to display the results in a list.

If you want to show the results you'll need a opitmizer rule with c#. This is already discussed here:
http://www.coresuite.ch/index.php?id=48&L=&tx_mmforum_pi1[action]=list_post&tx_mmforum_pi1[tid]=2202/

What you have to do is to register your rule to the doubleclick and read the current value (article number).

Rene Jansen

Hi Michael,

Thank you very much.

When I activated the code he give's a warning, also when I will parse the code he give's this warning:

"COR_Utility.Helper.Run_FO_COR_CUS_00000052(COR_Utility.Logic.CustomizeEvent): Not all code return a value (CS0161)"

Can maybe make an example of the CSHARP code of that topic with my SQL code in it.

I Think this Customize rule is very helpfull for many customerservice people that want a quick check of the open article's including open qty and delivery date.

Many thanks.

Rene Jansen

Michael Egloff

Hello Rene,

just put a

return true;

at the end of your code. If you still get problems, please put the code here.

Regards,
Michael

Rene Jansen

Hi Michael,

Many thanks, the code is working now with with the dubbelclick action and the SQL query of your example from the other topic.

But with my own query it doesn't work, because I think the query is not 100% good now it is inside the C# code.

See below my code. Have you any sugestions?

Many thanks in advance.

try
{
// GRID, WILL CONTAIN THE VALUES TO DISPLAY
SwissAddonFramework.UI.Components.Grid grid = SwissAddonFramework.UI.Components.Grid.CreateNew("COR_GR1");
grid.Width = 380;grid.Height = 260; grid.Top = 5; grid.Left = 5;

// SAP FORM, WILL CONTAIN THE GRID
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 = 400; form.Top = pVal.Form.Top; form.Left = pVal.Form.Left;
form.Value = "Results";

// ADD GRID TO THE FORM
form.AddItem(grid);

// LOAD FORM
form.Load();

// YOUR QUERY
string query= "SELECT     dbo.OPOR.DocNum, dbo.OPOR.DocStatus, dbo.POR1.ShipDate, dbo.POR1.OpenQty
FROM         dbo.OPOR INNER JOIN
dbo.POR1 ON dbo.OPOR.DocEntry = dbo.POR1.DocEntry
WHERE     (POR1.ItemCode = [%A;38;1]) AND (dbo.OPOR.DocStatus = 'O')";

// ATTACH QUERY TO GRID
grid.ExecuteQuery(query);

// SET COLUMNS NON EDITABLE (DISPLAY ONLY)
grid.Columns["DocNum"].Editable = false;
grid.Columns["DocStatus"].Editable = false;
grid.Columns["ShipDate"].Editable = false;
grid.Columns["OpenQty"].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;

Michael Egloff

Hi Rene,

you can't use special syntax in a normal query.
You have to replace

(POR1.ItemCode = [%A;38;1])

with this:

Add as a new line

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

Replace old line

(POR1.ItemCode ='" + itemCode + "')

Olaf Borke

Hi Michi,

I have installed this cocu rule and modified one line.
Now I receive the popup form with the right entries AND an error message.

the rule is added.

Could you check it, where is the mistake?

Thanks in advance

Best
Olaf

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-file-uploads/winzent/Bestellmenge und Zeiten aus Auftragszeilen.cocu]Bestellmenge und Zeiten aus Auftragszeilen.cocu[/url]

Michael Egloff

Hi Olaf,

you have to be very cautious.
Change the line with the column description from this

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

to this

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

The defined query and the column description has to be exactly the same (case sensitiv!)
SELECT CardCode ...
fits to
grid.Columns["CardCode"]...

Olaf Borke

Dear Michi,

Thanks a lot ! :oops:

I knew it, it could be only a silly thing....

Best

Olaf