How to append rows in an iGrid

[Former Member]

I have a form which has 2 iGrid, 3 textboxes and 2 Buttons.

The 3 textboxes where the user enters his selection read as

ModelNo:

SerialNoFrom:

SerianNoTo:

I populate the user selection into the selection grid when the user clicks on the Add To List button.

The selection list will be passed to a SQL query and the results on the query will be displayed in the results grid when the user clicks on the refresh button.

My QUESTION is:

How do I append the results of the query one after the other for different model numbers and the serial number range that the user has selected at one go.

Is there a way to store the results of the query at a temporary palce.

[Former Member · Accepted solution]

Hi Mike,

You are correct,

I am taking a group of rows

But I am passing it to a select query. I am not updating against some d/b table.

My query looks like this:

SELECT modelnumber,serialnumber,datecreated from Table1 WHERE modelnumber LIKE '%[Param.1]%' AND serialnumber BETWEEN '[Param.2]' AND '[Param.3]'

Lets say this query gives me 10 rows for the set of above 3 parameters.

I loop through the query till selection igrid RowCount > 0.

The next set of 3 parameters gives me 20 rows back.

Now I want to show all 30 rows in the results igrid when the user hits the REFRESH button.

Hi Lighthammer_Software,

You are correct. I can Use setCellValue in a new row. But since I don't know how many rows will the query return for a set of 3 parameters, I guess I can't use that.

[agentry_src · Accepted solution]

MKG_MKG,

I will go with a simple solution (and the first one that came to mind). Create a hidden field in html. Upon the first query save the sql script to that field. Upon the second query append the second script along with UNION, so the new script has both query scripts and so on as you add to it. When you hit refresh, use that hidden field to overwrite the query associated with the assigned query template. Now that I think about it, you should be able to do the UNION appending to the current script (SQL_Query property named Query) and simply update the query in the template using the same technique. Give it a try and if you run into technical difficulties, repost. I know there are other methods possible, so others, please chime in.

Note: This query is not saved as part of the template. This is only for dynamic adjustments in the webpage.

Good luck,

Mike

Edited by: Michael Appleby on Oct 7, 2008 5:51 PM

[Former Member · Accepted solution]

Thank you Mike. That looks like a possible solution and I am going to try it.

Can you please suggest how to overwrite the query template with the hidden field. I do not understand what do you mean by

"Now that I think about it, you should be able to do the UNION appending to the current script (SQL_Query property named Query) and simply update the query in the template using the same technique."

Thank you very much.

[Former Member · Accepted solution]

Hi Mike,

Can you please suggest how to overwrite the query template with the hidden field. I do not understand what do you mean by

"Now that I think about it, you should be able to do the UNION appending to the current script (SQL_Query property named Query) and simply update the query in the template using the same technique."

[Former Member · Accepted solution]

MKG,

I guess what Michael meant is the property of a query where the SQL statement is stored. You can do something like this in your JS:

var mySqlString = "SELECT ...";
document.YourQuery.getQueryObject().setQuery(mySqlString);

Then with every new entering of WHERE clauses from the users, you can append the new SELECT statement to the Query, concatenating them with a UNION.

mySqlString += " UNION " + <hidden value with new SQL>;
document.YourQuery.getQueryObject().setQuery(mySqlString);

When you are ready, execute the Query object:

document.YourQuery.refresh();

See also the Query details in the MII help.

Scripting Example:

[http://help.sap.com/saphelp_xmii115/helpdata/en/Applet_Reference_Details/WebScriptingGuide.htm]

Query details:

[http://help.sap.com/saphelp_xmii120/helpdata/en/45/cca31a93696f74e10000000a1553f6/content.htm]

Michael

[Former Member · Accepted solution]

Thanks Michael.

I am able to create the query string (mySqlString) in JS.

The name of results grid is ModelSerialResultGrid and the underlying query is ModelSerialGridQuery.

The APPLET is like:

<APPLET NAME="ModelSerialResultGrid" WIDTH="451" HEIGHT="260" CODE="iGrid" CODEBASE="/Illuminator/Classes" ARCHIVE="illum8.zip" MAYSCRIPT>

<PARAM NAME="QueryTemplate" VALUE="MESTemplates/Menu/Madhura/ModelSerialGridQuery">

<PARAM NAME="DisplayTemplate" VALUE="MESTemplates/Menu/Madhura/ModelSerialGrid">

I would like to set ModelSerialGridQuery = mySqlString.

How will my statement is JS look like ?

"document.YourQuery.getQueryObject().setQuery(mySqlString);"

[Former Member · Accepted solution]

MKG,

in your case it should work with

"document.ModelSerialResultGrid.getQueryObject().setQuery(mySqlString);"

I guess I usually use the ID parameter in the applet statement. If you experience problems with the solution above, maybe something like this helps:

...APPLET NAME="ModelSerialResultGrid" ID="ModelSerialResultGrid"...

Michael

[Former Member · Accepted solution]

The UNION can be obtained using aggregate queries and the corresponding connector. The result set is the same, but the method is more powerful (I guess) being able to use parameters and pass them accordingly to each individual query as required. Then the only thing left to do is to setParam() in html or javascript to the query objects. In the end (because the parameters are set) you can refresh the iGrid to show the latest set of data.

[agentry_src · Accepted solution]

Michael,

Thanks for picking up the thread. I was at a customer site and had limited access to the outside internet.

Mike

[Former Member]

Thank you all for the great suggestion.

I got it working.

[agentry_src]

MKG MKG,

I am not sure I understand your question. When the user hits the Add To List button, is a record actually added to a table in a database somewhere? Presumably using an iCommand applet? It sounds like that is what you are doing.

And is your question, how do you allow the user to build up a group of updates and then process them all at one time instead of one at a time? From your inputs, you would have a single Model Number and some range of Serial Numbers (incrementing by 1?). So if your model number is XXYYZZ123 and your Serial Number range is from 101 to 110, you would want to process a total of ten records. And you would want to allow them to do a number of times by changing the serial range, model number, or both and simply queue them up until they hit another button that say process?

Do I understand the issue correctly?

Regards,

Mike

Edited by: Michael Appleby on Oct 6, 2008 9:37 PM

[Former Member]

Hi Mike,

Here are some clarifications on my question.

The user inputs 3 model numbers and a range of 3 serial numbers corresponding to each model and each time he hits the AddToList button , the text box values get populated in the selection grid. These selections are not added to any underlying datasource (sql table). This is only so that the user can see what he had entered. I could have achieved this using a simple HTML table etc. But I did a igrid.

Next after entering the selection when the user hits the Refresh button, the 3 model nos and serial no. ranges get passed to a query (sql query).

I have written a loop in JS which calls the query till the rowcount of the selection grid is not zero and then call updategrid(true) in the loop to display the results of the query.

My problem is, results for only one set of parameters (Model1 SerialFrom1 Serialto1) are displayed in the query when Refresh is hit. How do I combine the results for all 3 parameter sets and display in the grid.

Kindly let me know for more clarifications.

I am very new to xMII.

[i053166]

HI MKG,

The igrid can display only the current query result. There is no feature as far as I know to append the data to igrid row by row other than modifying the underlying query. One way is to persist the query result of each loop iteration either in database or xml file then use another query to fetch the result to display on igrid. But this would complicate the scenario with more components.

An alternative would be create the sql query string dynamically based on user selection through javascript.The only dynamic part would be the where clause AND/OR grouping and then store it in a variable.

for eg: var var1="select Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.City, Customers.Country from Customers where '"country1"' LIKE Country AND 'Berlin' LIKE City OR '"country2"' LIKE Country AND 'London' LIKE City";

where country1 and country2 are variables.

After that user the following two commands in JS:

document.applet.getQueryObject().setQuery(var1);

document.applet.refresh();

where,

applet is the name of the applet having the display igrid. You can give any query template name for this applet in the body as its overridded in your javascript.

This method is suitable if you don't have too many conditions. Performance also should be better because here you are reducing your database hit to one as against the loop iteration.

Please try and let me know whether it helped.

[Former Member]

Yes, you can insert rows and column values into the grid, but those values will be lost if the grid is updated or the underlying query is re-executed. Use setCellValue in a new row (e.g. if there are 8 rows, set the cell value in row 9) to add a row.

[agentry_src]

As stated by Lighthammer_Software, you can add rows to the iGrid dynamically from your web page using javascript. Since the data in the iGrid exists only in the webpage, you cannot refresh the iGrid without losing the data. You need to determine the dataset in the iGrid use that information in your update.

I get the impression that you want to have the user build a group of rows in the iGrid. Then take that group of rows and do an update query against some database table. Is that correct?

If so, then you can either pass the entire contents of the iGrid as xml to a Xacute transaction to update your table or you can use an iCommand applet and a javascript loop using the contents (RowCount) of the iGrid to determine how long the loop continues and it will update the table with one row at a time.

Does this make sense to you? And are these possible solutions to your problem?

Regards,

Mike