iDempiere and ADempiere – Creating a Complex Key For PostgreSQL View

Hi Everyone,

The purpose of this post is to teach you how to take multiple columns and create a single compound key. Both iDempiere and ADempiere, open source ERP, love it when records have a key. If you do not have a key and you navigate off of a record, the record seems to disappear. By using the methods below, you can create a single key for complex views.

Let’s start by describing a scenario. We will assume that you are tasked with creating a window or queue that shows Purchase Order details that meet specific criteria. The view needs to include Material Receipt details if available. Since there exists a none-to-many relationship between an order line and a receipt line, creating a single unique key is tough!

Let’s assume the below PostgreSQL statement is your desired queue. Notice there is a left outer join on M_InOutLine. Because of this join, there might be no receipts for the order, or there might be many receipts for the order. In the prior case, M_InOutLine_ID will be null.

select o.documentno, ol.c_orderline_id, 
o.c_order_id, io.documentno as IODocNO, o.ad_client_id, o.ad_org_id, o.created, o.createdby, o.updated, o.updatedby
from c_orderline ol
join c_order o on (ol.c_order_id = o.c_order_id)
left outer join m_inoutline iol on (ol.c_orderline_id = iol.c_orderline_id)
left outer join m_inout io on (iol.m_inout_id = io.m_inout_id)

The unique combination of the C_OrderLine_ID and the M_InOutLine_ID creates the most unique key even when M_InOutLine_ID is null. Here is a string combination of the unique value.

ol.c_orderline_id||'_'||iol.m_inoutline_id

Since iDempiere and ADempiere require an ID to be number, we need to convert this string into a unique number. Here is a PostgreSQL function to convert or hash a string into a near-unique integer.

create function h_int(text) returns int as $
 select abs(('x'||substr(md5($1),1,16))::bit(32)::int);
$$ language sql;

Here are some other combinations that give you a better change at uniqueness; however, they do not play well with iDempiere and ADempiere’s ID reference type.

create function h_bigint(text) returns bigint as $$
 select ('x'||substr(md5($1),1,16))::bit(64)::bigint;
$$ language sql;

create function h_varchar(text) returns character varying as $$
 select md5($1);
$$ language sql;

By using the above query with the above function, you get:

create or replace view chuck_orderline1_v as
select h_varchar(coalesce(ol.c_orderline_id,0)||'_'||coalesce(iol.m_inoutline_id,0)) as randomKey, o.documentno, ol.c_orderline_id, 
o.c_order_id, io.documentno as IODocNO,
o.ad_client_id, o.ad_org_id, o.created, o.createdby, o.updated, o.updatedby,
h_int(coalesce(ol.c_orderline_id,0)||'_'||coalesce(iol.m_inoutline_id,0)) as chuck_orderline1_v_id
from c_orderline ol
join c_order o on (ol.c_order_id = o.c_order_id)
left outer join m_inoutline iol on (ol.c_orderline_id = iol.c_orderline_id)
left outer join m_inout io on (iol.m_inout_id = io.m_inout_id);

I used the h_int() function to create the view’s namesake ID. I used the h_varchar() function to create the most unique key possible. I used the coalesce() function to substitute a null result for a ‘0’. Doing so prevents the h_int() function from returning a null.

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.