Open Query with Selectioncriteria on Form (Item Master Data)

berbericz

Hello experts,



I would like to run a query on item master data which delivers me all prices (Sales/Purchases/Special...) which are defined in B1 for this item.



I have already created the query and it works for example with criteria ItemNumber. It is possible to have the query flexible by asking for itemnumber by using for example: [%0].



So my question is how can I realize that on Item Master Data? It can be a function button or a right click function which executes the query or...!?



I haven't found an idea for that in this forum.



Many thanks,

Gerrit

Lorenz Stierlin

Hi Gerrit



Here is an example for you. Create a function button on Item Master Data form (150) with following code:



[CODE]

try

{

//Read out ItemCode from form

string itemCode = TextEdit.GetFromUID(pVal.Form, "5").Value;



// GRID, WILL CONTAIN THE VALUES TO DISPLAY

Grid grid = Grid.CreateNew("COR_GR1");

grid.Width = 380; grid.Height = 260; grid.Top = 5; grid.Left = 5;



// SAP FORM, WILL CONTAIN THE GRID

Form form = Form.CreateNewForm("COR_CUS_SHOW1", "COR_CUS_SHOW1" + customize.Utils.UniqueStringGenerator.Next());

form.Height = 300; form.Width = 400;

form.Value = "Results";



// ADD GRID TO THE FORM

form.AddItem(grid);



// LOAD FORM

form.Load();



// YOUR QUERY

string query = "SELECT ItemCode, ItemName FROM OITM WHERE ItemCode = '" + itemCode + "'";



// ATTACH QUERY TO GRID

grid.ExecuteQuery(query);



// SET COLUMNS NON EDITABLE (DISPLAY ONLY)

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

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

}

catch(System.Exception ex)

{

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

}

[/CODE]



I hope this will help you.



Kind Regards,

Lorenz

berbericz

Thank you Lorenz for your example and help.



Your code works fine. In the beginning I got the error-message for my code "Matrix - [color="#ff0000"]invalid[/color] [color="#ff0000"]column[/color] [color="#ff0000"]number[/color]" but I could solve that by reading another article here in forum.



Unfortunately I still have one remaining issue for my Sql: It opens always two sql-grids with the same information even if I have the "grid.ExecuteQuery(query);" statement only once.



I have a very complicated sql-query with some transact sql and table declarations but the result in both grids is correct so I think that is not the issue.



How can I avoid that the grid opens twice?



Thanks,

Gerrit

berbericz

okay sorry! Restarting the coresuite addon seem to solve my problem. thx