Column SQL - Virtual Column

The purpose of this discussion is to help you understand the concept of Column SQL and teach you best practices for how to write Column SQL one time then use it everywhere (code, views, print formats, etc...) without repeating yourself.

2014-09-02 Discussion about Column SQL Best Practices

• Use Column SQL to summarize Shipment information at the Shipment header
• 0:01:30 Simple example of creating a Column SQL or Virtual Column to dynamically derive a summary using the Shipment window.
  • Introduce the concept using a SQL editor
  • 0:05:15 Copy the above example into the column's Column SQL field. Add to Shipment Window. View Example.
• 0:08:45 A better way to creating Column SQL so that you never need to repeat yourself.
  • Create Column SQL view (m_inout_colsql_v) that contains all my Column SQL queries.
  • 0:10:15 Update Shipment's count Column SQL to use new view
• 0:16:15 Modify the Shipment's Print Format to include my new Column SQL RowCount field without repeating the SQL.
• 0:20:15How to use Column SQL from code (the Shipment's model).
  • When models are loaded by ID (not result set), Column SQL values are loaded with the model.
  • 0:21:15 Example of loading a model by ID.
  • 0:22:30 Adding a new field to an existing core model and using PO's getters and setters on your new column without needing to re-generate the model (in iDempiere - creating and iModelFactory)
  • 0:23:30 CORRECT - Set_ValueOfColumn() and Get_ValueOfColumn() (link)

Example of using Context in a ColumnSQL field to prevent certain users/roles from editing certain records in a window that is shared by all.

Another video demonstration.

Report Virtual Column

https://wiki.idempiere.org/en/How_To:_Using_Print_Format_Item_Scripts_In_Reports