MII report for MII table maintenance

[Ben_jamin]

Hi Experts

My functional consultants want an easy way to update data in bespoke tables within the MII database.

They want to do mass updates. This is so they can run a demo of some interfaces, then reset the MII data to its previous state.

They cannot log on to SQL.

My plan is to enable this using an MII report running an update query/queries.

Has anyone done something similar? Can anyone give an example of an MII report to show table data and then allow the data to be updated in the underlying table (I suppose using an update query, allowing flexible updates)?

Or is there an easier way to enable a user-friendly table maintenance? Has to be something quick to build

Many thanks, Ben

[Former Member]

Not sure what you intend to do.

You want to create a custom table in MII's database (NW database) for you functional consultant so that he can do test on mass data storage in this table? He cannot log in to SQL ??? as he tried to log in to SQL in external DB? Yor MII DB an SQL?

I think you might want to elaborate what exactly your intention is.

[Ben_jamin]

Hi Seng,

We have created some tables in MII database, they contain transaction data

The team want to be able to maintain this data - update certain columns, change dates etc.

I do not want them to log on to the database - too risky.

So I want to create an MII report to show the table data, in an iGrid, and let the team use this update cells, rows or columns in the back end table, from the MII report

Hope I explained it better this time!

[Former Member]

Benjamin , yes you do explain what you need this time :).

- you need to create SQL template, IGrid in workbench to the db

- you need to create webpage for the user to view, delete, update, insert or whatever SQL.

if you have knowlegde in MII, I would say from 3-4 hours effort (or maybe less for the fast doer ) .

hope this help.

[Ben_jamin]

Hi Seng,

Thanks for the help.

Also I found this blog useful /people/rupesh.bajaj/blog/2007/12/14/update-igrid-using-javascript

One other useful thing for me would be if anyone has javascript to update multiple cells in the iGrid with a single value.

So we can, for example, change all values in a table column holding a date field, to a new date.

Thanks again

[Former Member]

I have similar code, it selects the data from multiple selection in iGrid. You can change them to update.

For the eg to work. I will post in 2 thread, it seems like I have problem posting in one thread

1. In Default project create folder MultipleSelection

- iGrid name GridMultiple

- TagQuery, Modes History Method Min, Max,AVG. Tag Query Details are AsetUtil1 to 4. Save as TagHistory

[Former Member]

hmmm it seems I am not able to post code, it goes to error all the time

[Former Member]

2. in Web, create irpt with this script

function Selection(){

	var myApplet = document.iGridBatchData;
	var myGridObject = myApplet.getGridObject();

	var rowCount = myGridObject.getSelectedRowCount();
	var txt = "";

	for (i=1; i <=rowCount; i++) {
		var RowNumber = myGridObject.getSelectedRowAt(i);
		 txt= txt + "," + myGridObject.getCellValueByName(RowNumber,"DateTime") ;		
	}
	var txtLength = txt.length;
	txt =txt.substring(1, txtLength);
	var SQL = "SELECT * FROM x where DATETIME not in " + "(" + txt + ")";
	document.getElementById('TT1').value=SQL;	

}

in body

<APPLET NAME="iGridBatchData" CODEBASE="/XMII/Classes" CODE="iGrid" ARCHIVE="illum8.zip" WIDTH="640" HEIGHT="400" TABINDEX=1 MAYSCRIPT>
	<PARAM NAME="QueryTemplate" VALUE="Default/MultipleSelection/TagHistory">
	<PARAM NAME="DisplayTemplate" VALUE="Default/MultipleSelection/GridMultiple">
	<PARAM NAME="SelectionEvent" VALUE="Selection">
</APPLET> </p>

   <input type="text" id="TT1"  class="txtField"  maxlength="400" size="200"> </p>

The answer is something like this

SELECT * FROM x where DATETIME not in (Aug 12, 2011 11:55:01 AM,Aug 12, 2011 11:56:01 AM)

Aug 12, 2011 11:55:01 AM,Aug 12, 2011 11:56:01 AM is from 2 rows selection and it will increate for multiple rows you selected. Now you just need to change the SQL to update statement.

regards

sengkiang

Edited by: Seng Kiang Hoe on Aug 12, 2011 1:09 PM

[Former Member]

I am not able to provide full code .... as the IE sends me error

[Ben_jamin]

Thank you, Seng, very helpful!

[Former Member]

your welcome

hope it solve your problem