The Application Dictionary allows you to configure iDempiere without programming. One of the items you can configure are Tables & Columns. In my iDempiere work, I’ve created new tables, windows and added new columns in one table which reference other tables.
Recently, a request was made to add a one new field in the Request table. The new field will allow the user to choose additional persons (aside from the Recipient) for the specific request. I had to add one drop down field containing certain users of the system in the field. It was a simple request that would have taken a maximum of 4 hours to do including testing, unit testing and user acceptance testing. To create a new foreign reference key, a new ID field in the r_request table pointing to the ad_user table was needed. iDempiere needs a format where you add more than 3 characters at the start to identify your field. It must also end with an “_ID”.
Unfortunately, we suddenly found ourselves facing a situation where it was generating a varchar in PostgreSQL everytime I synced iDempiere with it. We were scratching our heads as we’ve done this many times before and it never failed me. You cannot reference a foreign key with varchar. It took awhile for us to figure out our mistake. People with programming experience know that sometimes, it’s the simple mistakes that are hard to figure out i.e. a missing comma or semi-colon, spelling mistakes etc. In my case, I realized that the foreign key reference failed because i was using “_id” instead of “_ID”. This was my mistake. A simple change to “_ID” fixed my issue.
For details on how to create new Tables and New Windows, see http://www.adempiere.com/NewWindow.