The purpose of this post is to help you solve a common error condition that sometimes pains new ADempiere or iDempiere (AD/iD) ERP administrators. Customizing AD/iD by adding new columns is pretty easy; however, you should be aware of some tips and tricks to prevent the below errors.
- iDempiere shows the error as “Could not save changes – data was changed after query. – System will re-query”.
- ADempiere does not show an error; however, it states that “Changes Ignored”.
There are two common reasons for this error message.
- Yes/No boolean column with null value at the database.
- Table, Table Direct or Search column with a lower case “_id” at the end of the column name.
Yes/No Boolean Issue
In concept, boolean fields only have two states: Yes and No. If you create a Yes/No column and you do not give it a default value of either ‘Y’ or ‘N’, you will see the above errors when you try to manipulate a record whose Yes/No column value = null.
Yes/No Boolean Resolution
When you create your new Yes/No column, click the Mandatory field and give it a default value of either ‘Y’ or ‘N’. When you click on the Synchronize Column button (Window:Table and Column->Tab:Field), AD/iD will push the mandatory and default restrictions to the database, even if you previously created the column.
Ideally, both ADempiere and iDempiere would account for this issue automatically when you click Synchronize Columns. In the meantime, you will just need to remember.
“_ID” Column Name Issue
ADempiere and iDempiere are both case sensitive, and they expect all foreign fkey (Table, Table Direct and Search) references to end in an upper case “_ID”. Back in the days when ADempiere only supported Oracle, this was not an issue since Oracle keeps everything in uppercase. When you imported a table from the database, everything automatically was in uppercase. Now days, most people run AD/iD on PostgreSQL, and PostgreSQL keeps all column names in lowercase. When you import a table from PostgreSQL, AD/iD will import everything as lowercase. If you do not change the case, you will receive the above errors when you try to modify an existing record.
“_ID” Column Name Resolution
It is considered best practice to represent all AD/iD column names in camelcase. Here are some examples:
- chuck_user_id should be represented as Chuck_User_ID.
- chuck_bpartner_id should be represented as Chuck_BPartner_ID.
- chuck_accesslog_id should be represented as Chuck_AccessLog_ID.
I realize it takes time to update the names to camelcase when they are imported; however, doing so will prevent the above errors. Once you teach AD/iD how it should represent a column, it will remember your preferences in the future.
This naming convention is also important because it makes software development in AD/iD easier. When you create a column as Chuck_User_ID, you can reference the column in java as getChuck_User_ID(). Camelcase is the standard naming convention in java.
I hope this helps!!!
Why consider Open Source ERP
Open source ERP gives you every opportunity to prove or disprove its ability to support your company’s ERP needs on a timeline that satisfies your organizational needs. With open source ERP, you do not face the same financial constraints nor do you face the same conflicts of interest as with commercial ERP. Instead, you invest in the appropriate skills and knowledge for your people and processes. Best of all – if open source ERP cannot solve your company’s needs, you can safely justify spending the additional $2K to $5K per person per year for life of your commercial ERP to help drive your organization’s success.
ADempiere vs iDempiere vs Openbravo vs Compiere
The ADempiere, iDempiere, Openbravo and Compiere environments are amazingly similar. iDempiere came from ADempiere. ADempiere and Openbravo came from Compiere. Compiere came from Jorg Janke. Jorg came from Oracle. As a result, iDempiere and ADempiere have much in common with Oracle’s ERP in terms of the financial feature set.
This is both good and bad. Good because iDempiere and ADempiere are quite capable to help a company grow beyond $500M USD. Bad because they tend to be more complex in that they account for multiple languages, accounting schemas, currencies, calendars, costing types, costing methods, etc…. If you are a growing organization, and you need a system that will grow with you, and you have the right internal talent/resources, iDempiere or ADempiere will be a big asset for you.
The biggest difference between these products is that ADempiere and iDempiere are pure open source. ADempiere and iDempiere make all feature available for free. Compiere and Openbravo hold back features behind a commercial or paid license.
iDempiere and ADempiere vs OpenERP:
iDempiere/ADempiere (iD/AD) and OpenERP approach ERP from two very different directions. OpenERP comes out of the box with very simple options. If you are coming from QuickBooks, and you need a simple ERP system help you manage your business, OpenERP will look and feel comfortable.
iD/AD comes out of the box with every feature installed and configured to run a $200M+ USD business. If your business is growing rapidly, and you are willing to invest the time to learn an enterprise accounting system, then iD/AD will give you confidence.
Which one is best for you depends on your internal talent, growth and business complexity. Here is a post to help you learn more.
About Chuck Boecking: I am an ERP educator. I believe that open source ERP have achieved mainstream capabilities, and as a result, more companies can create greater efficiency across their organization. I started using the iDempiere code base in 2003. Back then, it was called Compiere. In 2006, I started my first multi-million dollar installation. Since then, ADempiere has helped me create great success with distribution and manufacturing companies all over the world. My vision of success is to find companies that can best use open source ERP to help them achieve a single, global instance that drives a discontinuous increase in profitability. I believe that organizations win when they own their technology.
If you have questions, comments or concerns, let me know. I definitely want your feedback.
You can contact me by phone using 512.850.6068.
My email is firstname.lastname@example.org.
You can complete the form on this page.
Thank you for taking the time. I look forward to speaking with you.