IDempiere workshop 2019

The workshop at the 2019 conference started without a fixed agenda. This page is used for a transcript of what we did there.

JIRA tickets

You can find all JIRA tickets we created or touched here:

JIRA label ChateauChapeau2019

suggestions for topics

Some people question for things that we can discuss.

• Workflows (Frank)

• DMS Solution by Deepak

• Norbert is interested in REST
  • (Sergey, Norbert, Deepak, Carlos)
  • -> responsive UI, integration with other frameworks
  • authentication
  • Client side development
  • Heng Sin implementation of REST

• Client side callouts
• User Experience (zk)
• How to display kiosk screens/data visualisation (Anton)
• Ask for input callouts
• Multi selection fields
• Process callouts
• Styles on info window
• Editable info window
• Combobox auto-filter
• Alternatives for zk

• Modularization - decoupling (Carlos)
  • Distros
  • identify functionalities that are core vs plugin
• Plugin Marketplace (Thomas)
  • Best practices
  • Validation - certification

• How to collect business features (Norbert)
  • CMS
  • costing recalculation

• Documentation
  • who is doing what

• DB Transaction management - for long trx (Chuck)
• Distribution (Chuck)
  • Shipment vs Move+Shipment
  • Stock monitor
  • move confirmation
• Charges and Products (Chuck)
  • Charge on requisition
  • Using product as charge
• product dependency on price list (Chuck)
• machine learning (Chuck)
• Selective data Replication (Chuck)
• User export of data in large quantities
• Oracle to postgres migration

• Asset - core has some fundamental things wrong (Anton)
  • Dependency between asset and product
  • asset maintenance
  • asset must be an dimension on fact_acct
  • fixed assets vs asset management
  • Asset Tracking (Deepak)
• more free configurable dimensions in fact_acct (Anton)
• BP Accounting
  • customer vs vendor accounting
• currency rate by document or transaction

• BI

-> primefaces app for BI

• Tab context (Anton)
• Tab ID and SubTab ID in context
• "go to line" in detail tabs
• foreign context
• Multi-Row Process button (that is already done, but quite new)
  • pagination problem
• User Preference to see inactive records
  • other user preferences
• Open window with a predefined search
• share saved searches - share tab customization... other user preferences
  • saved search accessible from toolbar button
  • relative search
  • multiple tab customization - accessible from toolbar button, shareable
• User preference formulas (Murilo)

• Bulk changes

• JSON and JSON-Blobl field type (Norbert)
  • nosql

• Oracle vs PostgreSQL - trend of postgres only features (CHuck)

• Form development - best practices (Chuck)

• Development environment management (Murilo)
  • projects, developers, tools (sonar), plugins

• Simple production (Anton implemented this and has some improvements)

• Aggregated fact_acct ??

• Average costing

first day

oracle to postgres migration (and other database migration issues)

Carlos used this tool for a long time http://www.adempiere.com/Migration/DDLUtils and it worked very well. For very big databases it does not work. It seems to load everything into memory. He then tried Ora2Pg but there seems to be no documentation about using this with iDempiere.

Also an interesting tool to copy databases is:

• https://wiki.idempiere.org/en/NF6.2_Copy_Client

You can use this tool to read from Oracle and write to PostgreSQL.

This process help to migrate tenants. It does not copy system data. That means you have to be prepared before and have 2packs for system customizations.

If you use that process everything is validated well even if you copy only parts of the database. It can preserve IDs or create new ones. Look at the wikipage and the screenshots there to get an overview.

https://wiki.idempiere.org/en/NF6.2_Migrate_ID can help if you get problems with IDs that are already in the database.

Both processes do not (yet) work with multiple selection fields.

Oracle vs PostgreSQL

Carlos would always choose Postgres for new installations. Sometimes user companies want Oracle for several reasons - mostly historical reasons.

Chuck asked if we like to change that the internal sql language of iDempiere is Oracle. Carlos does not like to change that. Oracle is still an important market.

Carlos says that PostgreSQL has become very effients in optimizing complicated queries. He advises to use the analyze command before these kind of queries.

PostgreSQL optimization

Chuck advises pgbadger to analyze database queries. It analyses the logs of postgresql and gives hints about wrong or slow or a lot used queries. Norbert uses it and has good experiences using it.

Norbert uses https://explain.depesz.com/ to better understand the "explain analyse" command of postgresql. We had a look at this page and it does a nice job and it has also good documentation about how optimizing postgresql works.

PostgreSQL Replication

iDempiere has some functions about Replication:

• https://wiki.idempiere.org/en/Manual#Replication_Data

Chuck also told us that you can use "foreign data wrappers" (fdw) of PostgreSQL to connect databases. You can use a VPN to connect far away databases and e.g. create a materialized view from an foreign table and so you have an effient way to work with the foreign data and extract records that you need.

Virtual UI columns (Murilo)

Murilo showed the new functionality of virtual ui columns.

Thomas advised to add the possiblility of "virtual search columns" to have virtual columns to search with an sql formula. Perhaps we can even use that for reports. Carlos liked the idea.

• IDEMPIERE-4087

Ask for Input improvements (Murilo)

Murilo showed us how to use the Input windows. We have improved functionality of https://wiki.idempiere.org/en/NF2.1_Ask_Input_Within_Process

In IDEMPIERE-3969 you can find that. Murilo did some example Process and Callout code for that and showed us.

Special Forms (Murilo)

Murilo talked about "special forms" that are included in the core. The special form can use user selected data (choosen from an infowindow) to do something useful.

We talked about how to make the creation of forms more easy. It was thought to do a form modelled in the application dictionary but that is not really possible. A besser solution is to create ready made components that we can use in our java code to fasten the creation of forms.

Carlos had the idea to create a form definition in the application dictionary that consists of ad_field entries. That makes the fields configurable. He does not likes to use ad_tab for that because it needs to create ad_table and ad_column for every form. But it even may be possible to create a view for every form. Then we got the idea to use the InfoWindow structure for that. It gives us a directory of fields connected to system elements.

It is worthful to avoid forms because they are special work. And even if we think about using another ui library than zk in the future then the forms will be a huge lot of work to reimplement one by one.

A lot of form tasks may also be solved in an infowindow.

The goal should be to have a more generic way to create forms using a common standard way by using common components.

embedded snippets

Norbert said we can improve the rest interface and create the form in javascript on the client. But that is a very different approach. It may be we talk more about that when we discuss the rest interface. Carlos summarized this as: "Allowing non-zk forms by using rest access." Norbert said "Embedded Snipped".

client side callouts

Deepak talked about the possibility that Java code can inject JavaScript code into the ui elements to create client side callouts. It may be interesting to use that in very special circumstances. The idea is: Having Java Script callouts in the dictionary that can be attached to fields on browser side.

Multi selection field

Murilo showed us a good extension that they use about multi selection fields. But after seeing the new functionality in core that Heng Sin did he likes that more. We had a deeper look at it. You can create fields with "multi selection table" and "multi selection search".

That field allows us to use multiple records in search parameters.

community communication / messenger

We asked ourselves how we can improve the communication of the community. We asked if IRC is still the best tool for a fast communication. One other way can be to use a Telegram group. Norbert likes slack but we are not sure if it is free. One advantage to e.g. Telegram is that it supports threads. Diego proposed gitter (Gitter at Wikipedia). It is similar than slack.

Hideaki sometimes does an summarize of jira tickets and wiki changes. Carlos likes the idea but someone has to do that manually or moderated.

We also think about guidelines to create jira tickets to not create too much noise at jira.

multiple choice represantation in database

Deepak suggested to use the array data type and not comma separated string values for the multiple choice fields. He suspects that this can give us referential integrity. Paul says that it does not. We should review that.

guidelines

Thomas wants to have guidelines e.g. for plugins. That allows to know about and judge guidelines. It will also improve documentation.

Plugin Guidelines

second day

product dependency on price list

We want no dependency on pricelists. (Chuck) We can have a flag at the product to make a pricelist that have prices mandatory or not. Norbert proposed his own solution where in the document type there is a flag to make the product price mandatory. Thomas said that he uses a plugin for conditions & calculations that creates prices without using a price list. This plugin should not be broken when we change things.

• IDEMPIERE-4086 product dependency on price list

more free configurable dimensions in fact_acct

Anton asked for that. There are reasons to have dimensions for warehouse, charge, asset, locator

(Chuck opened a ticket before about this) TODO: add chucks ticket

Carlos thinks that it can be worth to add more columns as User1 and User2. Two of them might be not enough.

currency rate by document or transaction

There is a patch from Trek Global that uses a spot currency rate type (e.g. "manual") that allows to enter a currency rate for every single document.

• IDEMPIERE-4083

user preferences to see inactive records (and even more)

Thomas and Nicolas proposed to have it possible to not show inactive records in the search. There are different ways to do that.

• We can do a default search value.
• Another way is to create a special SQL where filter in the tab definition.
• As Carlos mentioned the SQL filter can use environment variables. That allows to use user preferences to switch this kind of filter on and off (because they also are in the environment).

Thomas liked also to have preferences to switch fields on or off or make it read-only or so. As the user preferences are in the environment you are able to configure different levels of information or read/write using a system preference.

Carlos advised us to revisit the proposal from Nicolas to see if we can include a useful way in the search window to implement that. IDEMPIERE-733 Show only active records by default when opening a window

Open window with a predefined search

Chuck advised to be able to save searches and use them in the toolbar buttons.

Thomas said that we want to share searches to all users or to a specific role. Chuck said we can have a default search (perhaps configured in system) for a window (up to now it is configured by table).

An additional idea: We want to have relative formulas in the searches (to do things like "last month").

Instead (or additional to) the virtual search columns we can have sql code in a relative formula. For that we can use a flag for superuser to use sql in the search definition. Then the field "validation code" can contain "@SQL=".

Norbert proposed that already in the last workshop and Diego added tickets at this time:

• IDEMPIERE-2837 use in a better way saved searches
• IDEMPIERE-2836 sql searches for advanced users
• IDEMPIERE-2835 Allow relative searchs

Someone should review these tickets and optionally open new tickets or add description based on what we spoke about today. (TODO)

dynamic validation in search

Paul complained that opening a search window with dynamic vaildation fields that can be very slow because all the validations load when the search opens.

Murilo said that this is nearly solved but some small issues have to be solved.

• IDEMPIERE-3981

search in the toolbar

We like to have preconfigured searches in the toolbar. We can add these as a submenu to the search button or as a combobox. The combobox also shows the actual search's name in the ui. (Norbert showed us his implementation)

• IDEMPIERE-4085

improve toolbar

Norbert showed us his toolbar. It looked good. He has icons and text buttons and you have only some buttons shown at the left and a "more" drop down menu at the very right.

The audience agreed that Norberts toolbar is nice and we should open a ticket to add his changes.

• IDEMPIERE-4084

Norberts transportation system

Norbert showed an example of its transportation management.

They use shipping region tabel and a shipping slot. We liked to talk about that later in a smaller circle of the "Transportation Management Group".

workflows

Frank was interested in subworkflows. They are not implemented yet in iDempiere. We talked about several features of workflows.

We have no real workflows in iDempiere. There are just basic capabilities beyond the simple document workflow.

REST

Sergey showed us what state the REST implementations have. There is an implementation from Heng Sin and one from Sergey.

Heng Sin created authentification services, services to get window definitions and data object of PO objects. His code is very generic.

It seems that you can replace the ui using that. HEng Sin is not here to explain. Carlos noted that it will not call callouts or have dynamic validation. It may also be the plan to configure a tab and fields for special needs (for a client side application) in the application dictionary and use it here in a generic way. This will not be a real window but a definition of the interface of fields to exchange with a client application that may add its own logic.

Carlos advised to have a look at http://odata.org

Someone said that we can use the JSON parser code to also include document data into pdfs (for example use this german standard https://www.ferd-net.de/zugferd/definition/was-ist-zugferd.html).

Norbert and Paul talked about GraphQL. That can be a more modern alternative to using REST.

Replication from Norbert

Norbert has an system to replicate data between iDempiere systems. You define your own Export formats, then configure one of very different export processors (export to another database but also to other things like a file or an nosql store). He uses that also to replicate to mobile clients and the like. He uses different configurable replication strategies for different use cases. For example replicating only several tables. In his usecase he uses ActiveMQ and sends JSON objects for the exchange of replicated data.

share tab customizations

A superuser should be able to create a tab layout, give it a name and make it as a system or client standard or share it with a role or another user.

If a user copies it he creates an own copy and does not change the global customization.

(not Chuck) Thomas had the idea to add an customization to the export window. That allows to export e.g. csv files with different columns.

We like to have several different grid customizations. Norbert says there is a ticket Hiep is working at. IDEMPIERE-2708

Carlos wants to add a tab customization field to the user search query table. That allows to open a specific customization if the user ues a specific search. Thomas idea is to add an ordering for that. We can save it in the customization record.

Chuck advised that he likes to have a way to see if something is customized or not.

• IDEMPIERE-4088 share tab customizations

bug in csv export

No - Chuck did not say that, it was Thomas idea. ;-) But Chuck said that virtual columns do not work in csv files.

• IDEMPIERE-4089

simple production

• Document type (Deepak has already code for that)
• Manufacturing setup (Anton)
• Adaxa improvements
• Multi-BOM (TrekGlobal, Chuck suggested a hack using views replacing M_ProductBOM)
• Negative BOM Line quantity (verify)
• WIP - financial and inventory move
• reservations when preparing production documents
• Production (single) vs Production (multi) - worth to consider enabling it back?