How to Configure House Keeping in iDempiere
The House Keeping window in iDempiere lets administrators schedule the deletion of temporary and process-generated records, such as inventory valuation runs, selection sets, and report buffers. In retail operations with high transaction volume across POS, inventory, and reporting processes, these temp tables grow quickly and slow down the database. House Keeping keeps the system lean by removing stale records on a defined SQL condition, directly improving report performance and query response time.
Process Flow
Business Rules
- Active House Keeping records are eligible for manual or scheduled execution.
- The SQL WHERE clause determines exactly which records are deleted from the target table.
- Save In Historic retains a copy of deleted rows before removal for audit purposes.
- Export XML Backup writes an XML copy of records prior to deletion.
- Last Run and Last Deleted fields log the outcome of the most recent execution.
- Inactive House Keeping records are skipped during batch or scheduled runs.
Prerequisites
- System Administrator role access to the System Admin menu.
- Target table already exists in the Table window (Application Dictionary).
- SQL WHERE clause tested and validated against the target table.
- Backup or archival process confirmed if records must be retained before deletion.
Navigation
Home → System Admin → General Rules → House Keeping
Configuration Steps
Step 1: Identify the Record and Search Key
Enter a unique Search Key and a descriptive Name explaining what the job deletes, for example “Delete T_InventoryValue records preserving last week”.
| Field | Sample Value | Description |
| Search Key | T_InventoryValue | Unique code identifying this House Keeping job |
| Name | Delete T_InventoryValue records preserving last week | Describes the cleanup purpose |
Step 2: Select the Target Table
Select the table from the dropdown, for example T_InventoryValue_T_InventoryValue, matching the process or report table generating the temp data.
| Field | Sample Value | Description |
| Table | T_InventoryValue_T_InventoryValue | Target table cleaned by this job |
Step 3: Define the SQL WHERE Clause
Enter a WHERE condition referencing a date or key field, for example filtering by AD_PInstance created date older than seven days.
| Field | Sample Value | Description |
| Sql WHERE | AD_PInstance_ID IN (SELECT AD_PInstance_ID FROM AD_PInstance WHERE Created <= SYSDATE – 7) | Deletes only records older than 7 days |
Note: Always test the WHERE clause as a SELECT statement first to confirm the expected row count before saving the House Keeping record.
Step 4: Set Retention Options
Enable Save In Historic to retain rows internally, or Export XML Backup to generate an external XML file before deletion. Leave both unchecked for routine temp-data cleanup.
| Field | Sample Value | Description |
|---|---|---|
| Save In Historic | Unchecked | Keeps a historic copy of deleted rows when enabled |
| Export XML Backup | Unchecked | Exports deleted rows to XML before removal when enabled |
Step 5: Run House Keeping and Review Results
Click the House Keeping button, then check Last Run and Last Deleted to confirm execution date, time, and the number of rows removed.
| Field | Sample Value | Description |
| Last Run | 07/27/2026 11:26:30 AM | Timestamp of the most recent execution |
| Last Deleted | 0 | Number of rows removed in the last run |
| Active | Checked | Determines if the record runs during scheduled execution |
Common Issues and Resolutions
| Issue | Cause | Resolution |
| Deletion removes more rows than expected | SQL WHERE clause too broad or missing a filter condition | Test the WHERE clause as a SELECT first and add a date or ID filter |
| Last Deleted always shows 0 | WHERE clause does not match any current records or wrong table selected | Verify the Table field and confirm the WHERE condition against live data |
| Important records deleted with no recovery option | Save In Historic and Export XML Backup left unchecked | Enable Save In Historic or Export XML Backup before running sensitive cleanups |
| House Keeping record does not run on schedule | Active checkbox unchecked on the record | Check the Active box and confirm the job is included in the scheduler |
| Job runs slowly on large tables | WHERE clause scans the full table without an indexed filter | Use indexed columns such as Created or AD_PInstance_ID in the WHERE clause |
| Export XML Backup file cannot be located | Export path not accessible to the application server | Confirm the server export directory and file permissions |
| Wrong table cleaned by mistake | Incorrect Table selected during configuration | Re-check the Table dropdown value before saving the record |
Next Steps
- Configure the Scheduler window to automate recurring House Keeping runs.
- Review the Archive Viewer for tables retained under Save In Historic.
- Check System Rules and server logs to confirm cleanup jobs completed successfully.