Facebooktwittergoogle_plusredditpinterestlinkedin

Skill Level: Advanced

Hi Everyone,

In an previous post, I discussed how to use the ADempiere and iDempiere code to answer the questions “How does XYZ work?” or “What happens if I click ABC?”. Using Eclipse to see business logic is a good starting point; however, there are times you need to know a little more about how a field is used.

The purpose of this post is to illustrate how to quickly view some of the settings that control what fields are visible, what fields are grayed out, what options show in a given drop down, etc..

ADempiere and iDempiere adopt a rails-like approach of defining a window through configuration. This means that attributes of a given field are defined and maintained in the database. The below query/view exposes these attributes so that you can search for dependencies on any given column name.

Copy and paste the below SQL statement into pgAdmin. This will create a view that combines up all field references into a single ADempiere/iDempiere window.

NOTE: the below is is for reference. Here is the most updated version of this query.

Create view X_ReferenceSearch_V as
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, 101 as AD_Table_ID, ad_column_id as record_id, lower(columnsql) as code, 'Column SQL' as messagetext from ad_column
union
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, 101 as AD_Table_ID, ad_column_id as record_id, lower(defaultvalue) as code, 'Column Default Logic' as messagetext from ad_column
union
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, 101 as AD_Table_ID, ad_column_id as record_id, lower(readonlylogic) as code, 'Column Read Only Logic' as messagetext from ad_column
union 
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, 101 as AD_Table_ID, ad_column_id as record_id, lower(Mandatorylogic) as code, 'Column Mandatory Logic' as messagetext from ad_column
union 
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, 107 as AD_Table_ID, ad_field_id as record_id, lower(defaultvalue) as code, 'Field Default Logic' as messagetext from ad_field
union 
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, 107 as AD_Table_ID, ad_field_id as record_id, lower(displaylogic) as code, 'Field Display Logic' as messagetext from ad_field
union
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, 102 as AD_Table_ID, AD_Reference_ID as record_id, lower(whereclause) as code, 'Reference Where Clause' as messagetext from AD_Ref_Table
union
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, 108 as AD_Table_ID, AD_Val_Rule_ID as record_id, lower(code) as code, 'Validation Rule' as messagetext from AD_Val_Rule
union
select 0 as ad_client_id, 0 as ad_org_id, date_trunc('day',now()) as created, 100 as createdby, date_trunc('day',now()) as updated, 100 as updatedby, null as AD_Table_ID, null as record_id, lower((pg_views.viewname::text || ':: '::text) || pg_views.definition) as code, 'View contents' as messagetext 
from pg_views
union
select 0 as ad_client_id, 0 as ad_org_id, date_trunc('day',now()) as created, 100 as createdby, date_trunc('day',now()) as updated, 100 as updatedby, null as AD_Table_ID, null as record_id, lower((pg_proc.proname::text || ':: '::text) || pg_proc.prosrc) as code, 'Function contents' as messagetext from pg_proc;

Bring the newly created view into ADempiere/iDempiere’s Table and Column window. Create a window for the newly created window. This tutorial shows you how to import a database view or table into ADempiere/iDempiere. Please note that this table will be viewed through the System Administrator role (not GardenAdmin role).

Here is what the ADempiere Table and Column should look like:

ReferenceSearch TandC

And, here is what the finished product looks like. From this window, you can search on any column name and see if it is referenced from any ColumnSQL, Validation Logic, References, Read Only Logic, Visible Logic, or Mandatory Logic.

ReferenceSearch Result

I hope this helps!! If you would like help gaining a better understanding of how ADempiere and iDempiere work, let me know.

Please note that when you import Record_ID into the Table and Column that you can make it a reference of type button. The system will automatically know the Record_ID is designed to zoom to the appropriate window.

ADempiere vs iDempiere vs Openbravo vs Compiere

Please note that ADempiere, iDempiere and Openbravo are forks or copies from Compiere. Therefore, they have similar abilities mentioned above. The biggest difference is that ADempiere and iDempiere are pure open source. There are no features held behind a commercial or paid license.

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 chuck@chuboe.com.

You can complete the form on this page.

Thank you for taking the time. I look forward to speaking with you.

Regards,
Chuck Boecking
http://www.linkedin.com/pub/chuck-boecking/10/970/17b

 

 

Facebooktwittergoogle_plusredditpinterestlinkedin

One thought on “ADempiere and iDempiere – Advanced System Element Find

Leave a Reply

Your email address will not be published. Required fields are marked *