Create a process that walks over all records shown in the tab

Problem

Sometimes a process has to go through all records in a tab. Going through a whole table is easy in SQL so what?!? The problem depends on how you define "in a tab". For example you have to care that you get only records from your own client. What about access rights? What if you used the search filter form to see only a part of the data?

Thoughts about the way to go

The simplest answer to this question is: Take the information what is shown from your window. The window should know what it's output shows. :-) But it is not that easy. The architecture of iDempiere separates running processes from the view layer. A Process runs in the data layer of iDempiere. In principle it is possible that the process runs autonomous in the server background and your window is already closed or it is in a swing client a thousand miles away. To deal with that problem you have to catch the information about the showed records in the very moment where you start the process and keep it until the process runs. The problem here is: You can't start your own code when the process is created in the view layer. :-( But there is a way to give informations from the user using the view layer to the process in the data layer: Process Parameters.

Now comes the magic trick: When creating Process Parameters you can use Context Variables. These contain some state information of the view layer. You can see them by clicking on the Extras/Settings Menu in the Swing Client (or clicking the iDempiere Logo at the top left in zk) and going to the "Context" tab. One Context variable per tab contains the SQL Query String that was used to create the tab's output.

Solution

You can create a Process Parameter and call it e.g. "TabInfoSQL" with the following settings:

• Name: TabInfoSQL
• DB Column Name: TabInfoSQL
• Reference: Text
• Length: 5000
• Default Logic: @0|_TabInfo_SQL@
• Display Logic: Y=N

(Remark: The magic default logic string begins with "0" what means you use the Query String of the first tab. Change it for other tabs.)

To save the SQL string as a parameter in a long string you have to extend the length of the Parameter table. Go into the window "Table & Column" into Table "AD_PInstance_Para", go to Column "P_String" and set the length to 5000. Do not forget to synchronize this change into the database.

Now you can use this Parameter value as an SQL string to cycle over all records shown in the tab.

Code Example

I created an abstract baseclass that uses this magic to iterate over all records. It uses a second Parameter "AllRecords". If you use it the user can choose if the process will be done for one or for all records.

See my code and feel free to use it.

The core of the code that uses is this:

 MTable table = new MTable(getCtx(), getTable_ID(), null);
 String sql = bean.TabInfoSQL;
 Statement stmt = DB.createStatement();
 ResultSet rs = stmt.executeQuery(sql);
 String result = "";
 while (rs.next()) {
   PO record = table.getPO(rs, get_TrxName());
   String lineResult = processRecord((T)record);
   if (!Util.isEmpty(lineResult))
     result += lineResult + "\n";
 return result;