BusinessObjects Board

Automatic XI 3 Inbox Purge Program

I am in the process of rewriting the Automatic XI 3 Inbox Purge Program Object utility in Java, but realized it only considers report age as the delete criteria, where we need to consider report count.
Is there a combined query that could provide a listing of objects in all Inboxes, excluding the most recent “X” we are going to allow each user?

If anyone is interested in the direct replacement for the Automatic XI 3 Inbox Purge Program Object utility in Java let me know, it seems to be working fine for me, but the normal disclaimers would apply…

cpare :us: (BOB member since 2008-10-06)

Excluding the most recent “X” requires a sub query or a SQL window function, both of which are unsupported. I think you have to return all objects per mailbox and iterate to determine the most recent ones you want to keep.

BoB LoblaW :us: (BOB member since 2007-10-23)

I ended up using a 2nd Query as suggested above.
For those interested, here is the most recent version of the code, I am sure it can still be cleaned up, but it seems to be working as-is.

One thing to note, the lines to delete each object and commit the recordset back to the infostore are commented out to protect the innocent.

Is this a worthy BOB Apps Candidate?

 * This utility is designed to purge the user Inboxes to keep them to a reasonable size

package development;

import java.util.Date;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;

public class PurgeBOBJInboxes {
	public static void main(String[] args) {
		String strBOBJUser = "JasonGardieff";
		String strBOBJPass = "JuansMom";
		String strBOBJCMS = "mahserver:6400";
		String strBOBJAuth = "secEnterprise";
		String queryString = null;
		int intInboxLimit = 60;
		String strSQL = null;
		try {
			IEnterpriseSession eSession = CrystalEnterprise.getSessionMgr().logon(strBOBJUser, strBOBJPass, strBOBJCMS, strBOBJAuth);
			IInfoStore iStore = (IInfoStore) eSession.getService("InfoStore");
			System.out.println("Logged in");
			//Fetch all the users with more than intInboxLimit items in the inbox
			queryString = ("Select SI_ID, SI_NAME, SI_CHILDREN from CI_InfoObjects where SI_KIND = 'InBox' and SI_Children > " + intInboxLimit);
			System.out.println("queryString: " + queryString);
			IInfoObjects rsInboxes = iStore.query(queryString);
			if (rsInboxes.size()>0){
				for ( int i =0; i < rsInboxes.size(); i++){
					IInfoObject thisinbox = null;
					thisinbox = (IInfoObject) rsInboxes.get(i);
					IProperties inbox =;
					System.out.println("Purging Inbox for User: " +
							inbox.getProperty("SI_NAME").getValue() + "(" 	+		//Report Name
				    		inbox.getProperty("SI_ID").getValue() + ")"			//Instance SI_ID
					try {
						 * The query below has restrictions on both SI_INSTANCE and SI_KIND to reduce
						 * the risk of us inadvertently deleting something critical (users, groups, servers, plug-ins) 
						System.out.println("  Querying for the inbox objects for  " + inbox.getProperty("SI_NAME").getValue() + "(" + inbox.getProperty("SI_ID").getValue() + ")");
						strSQL = ("Select SI_ID, SI_KIND from CI_InfoObjects where SI_INSTANCE=0 and SI_KIND in ('Shortcut','Webi','Excel','FullClient','Pdf') and SI_PARENTID = " + inbox.getProperty("SI_ID").getValue() + " ORDER BY SI_CREATION_TIME DESC");
						System.out.println("  queryString: " + strSQL);
						IInfoObjects objRS2 = iStore.query(strSQL);
						System.out.println("  Inbox Object Count: " + objRS2.size());
						if (objRS2.size()>intInboxLimit){
							for ( int x = intInboxLimit; x < objRS2.size(); x++){
								IInfoObject inboxObject = null;
								inboxObject = (IInfoObject) objRS2.get(x);
								try {
									System.out.println("  This is where we delete object " + inboxObject);
									//Delete the object from the InfoObjects collection
									System.out.println("  Record Deleted");
								}catch (Exception error){
									System.err.println(new Date() + " Error while deleting records and commiting the recordset back to the InfoStore");
									System.err.println("/t" + inbox.getProperty("SI_NAME").getValue() + "(" + inbox.getProperty("SI_ID").getValue() + ")");
						//Commit the changes to the CMS using the commit method. This deletes the object.
						System.out.println("  Updating InfoStore");
					}catch (Exception error) {
						System.err.println(new Date() + " Error while querying for inbox items ");
						System.err.println("/t" + inbox.getProperty("SI_NAME").getValue() + "(" + inbox.getProperty("SI_ID").getValue() + ")");
		}catch (Exception error) {
			System.err.println(new Date() + " \n  Business Objects Authentication/Query Failed");

cpare :us: (BOB member since 2008-10-06)