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

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:

NF6.2_Copy_Client

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

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.

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 the idea to deprecate Oracle support. Oracle is still an important market.

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

PostgreSQL optimization

Chuck suggested 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 analyze" 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:

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 efficient 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 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 better 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 representation 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

Thomas did a proof of concept of a solution to this problem and it worked well. He used user preferences in a WHERE CLAUSE of a tab. He documented it in IDEMPIERE-733. We can think about adding that to the standard configuration in core.

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:

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

Dynamic validation in search

Paul complained that opening a search window with dynamic validation 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 still pending.

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 Norbert's toolbar is nice and we should open a ticket to add his changes.

IDEMPIERE-4084

Transportation management system

Norbert showed an example of his transportation management.

They use shipping region table and a shipping slot.

Evan and Ben also use a sophisticated transportation management in their "big dog erp". Thomas also has an implementation of that.

We liked to talk about that later in a smaller circle of an "Transportation Management System workgroup".

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 authentication 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 a 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 uses 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?

Chuck has experience with using http://frepple.com as a production planning system. One of his friends integrated this into iDempiere.

Processes working on multiple records

Now we have the possibility to check several records (using the checkmark at the very left of the grid view). Deepak did it. This is discussed and documented here: https://groups.google.com/forum/#!topic/idempiere/YDnr8nCFGyA

This functionality suffers of pagination. It can work only on what is shown on the current page (maximum of 25 records).

IDEMPIERE-3998 Execute Toolbar Process on Multiple Records

Thomas likes to have something that work on all records in the search query.

Thomas had the idea the create a kind of automatically loop that calls a process for every single record. That allows to use all older processes for that. But Carlos said that can cause problems because then it is not checked if the button is available or not (or even not visible) - that may be dependent on fields of the record.

bulk changes

Carlos thought about bulk changes. That is to have a button that shows all fields that can be changed in all shown records. There will be big issues because we do not know if everything can be changed or will be shown. After thinking about it it seems not a good idea.

The original proposal was about deleting or completing things. That can better be done with a simple process or with the csv importer.

Aggregating accounting in fact_acct

Norbert proposed to have a more similar accounting. For example if we complete an invoice with several product lines we get several lines in the accounting. He likes to have only one line for all products to make it simpler for him. Especially to make the tables smaller.

Carlos shows us a link to an quite old explanation from Jörg Janke about this: https://www.compieresource.com/2007/08/financial-data-warehouse.html

What Norbert proposed is doable, not easy, and not recommended

Assets

Anton has some issues with Assets. He says that you first have to create a product. Then you do a vendor invoice to buy the thing and the you can create an asset record.

Chuck explains that an asset can be created in two ways: From a product (if you buy it) or from a project (that you create yourself). The process to create it from a project does not work very well.

Some issues we found:

Dependency between asset and product
Asset maintenance
Asset must be dimension in fact_acct
Fixed assets vs asset management
Creating an asset group is not pre-filling default accounts
Chuck suggests to "pluginize" it -> knowledge group - > Anton, Chuck, Deepak, Steven/Paul
Improvement in core to support better Assets -> Product as a Charge that posts in material receipt and not just in Invoice (Chuck to collect and document this here: IDEMPIERE-4092

There are problems if you mix the concepts of "fixed assets" and "maintenance assets". Both use the same tables but they are very different.

Chuck already talked to Steven about the charge product he suggests and tries to figure out how to improve it.

Combobox auto filter

There is a new field reference type to create a combobox with an auto filter facility. You use a "table direct" and you set the "Autocomplete" flag. This is done in IDEMPIERE-3851 Filtering list box (AP2-671 TrekGlobal).

Thomas has problems using it because it is not very performant with big data sets.

Norbert uses https://fusejs.io to do that on client side.

Carlos had a look into our source code. It seems that it does not use the database for every keystroke but it sorts data on the server and has to resend it to the client. Something in this process is too slow.

Strategies to solve it:

make it configurable when it searches 'ab%' vs. '%abc%'
wait e.g. half a second before you run the query
run the query not in the foreground but in a background thread
see if we can cache data in the client and process it there

we added IDEMPIERE-4093

third day

DB Transaction management - for long trx

Chuck asked how to do that. These kind of processes happen when a new user fills tables when he starts working with iDempiere but there are also users that need to import long data day by day.

Carlos says there is a hardcoded limit to have no SQL transaction longer than 2 hours.

Improved process transaction management

Chucks question was if it is possible for a process source code to get the transaction handle of this transaction, commit it and create a new transaction.

Diego led us to Event Handlers for PRE_PROCESS & POSTPROCESS. You can find them in IEventTopics.java. They are done with IDEMPIERE-3924 Implement EventHandler for pre/after/post processes The OSGi Interface IEventManager can be used.

That can be used to extend the timeout of Trx, or to implement the postgresql no-logging.

Some of that are connected to ticket IDEMPIERE-3969

Call csv import in smaller parts

Thomas did a bash script that split a very long CSV file into parts calling a web-service to import every part in a single transaction.

CSV import button vs. CSV process button

CSV Import Button is slow. (That is the button that is in every window). It uses the UI thread. So it blocks the UI. It emulates to use the actual shown user interface.

The CSV process button (that has to be configured for specific tasks) uses an dedicated process and emulates just a smaller kind of a window (it also uses callouts). It runs much more performant.

Dedicated server for background processes

Norbert's idea is that it should be possible in an environment with multiple application servers to send a background process to a configurable dedicated service server.

An advanced idea is to make it possible to send a job to an external system working e.g. with AWS lambda.

We talked about improving the discovery strategy (schedulers). How to implement server groups and how to find out which servers are working or when to even start new server instances. For example EC2-tags, server variables, server name, etc.

IDEMPIERE-4090 start background processes on a dedicated service server

TMS Transportation Management System

A meeting will be coordinated in future. Transportation Management System

User Experience

We talked about:

a staging/experimental/prototype server
- Hiep already has an experimental server from an experimental branch. It helped to do complicated topics that needed several functions and tickets

Norbert showed us a list of User Experience changes he likes to have. We started a discussion about how to work on these items perhaps with a working group or so.

One idea was to open a kind of special branch for new user experience functionalities and use jenkins to automatically create a test server for that. A test server might be very helpful especially for User Experience issues because people that are interested in that are not developers and may like to see and use the new things.

Attract developers and implementors

We talked about doing more trainings. That is what Diego proposed in the conference. We do not all like the idea to have a certificate for that.

Carlos had the thought that teachers are a good point to see new students and see their abilities. In our community Chuck is the one to see new worthful developers. We may improve things to attract these.

There can be different views of the project and its parts. At the end we have the same thoughts that we need to find a better way to view the structure of the project (and also on code) that is not centric to one center (or even one center).

User Experience Focus Group

We like to build a user experience user group.

Accessibility

Alain expresses the importance of accessibility for people with disabilities in the UI, in countries such as France there are laws of ´inclusive-society´, therefore the software being used in companies need to be compliant with these laws easing the user experience for people with visual disabilities for example.

The focus of this work can also include mobile and high resolution displays.

Alternatives for zk

Evolving REST could lead to a client-side client
Evolving REST is strategic

Distribution / shipment process

Chuck explained that the workflow of shipment is quite simple. And he thinks to change or extend the way it works for complex cases. He want some intermediate place for inventory entries to go. Carlos says that is part of warehouse management but it does not work well in core.

shipment vs move + shipment
stock monitor
move confirmation

It was talked about a way to move items from a warehouse locator (that keeps big palettes) to a picking locator (that keeps single opened packages) to a packing locator (where items are prepared for shipping). Only the packing locator can be shipped. We thought how items can be moved from one locator to the other. That can be done based on replenishment or orders or we even have to create a custom version of them.

Chuck advised that it should be possible to change the generate shipments process to work with different locators. Some things have to be done with replenishment.

it must be a replenishment algorithm that takes into account the date needed
To support complex cases we need an allocation inventory document and a storage allocated view/table

DMS

Deepak explained how he implemented his DMS system.

We were interested how he implemented the storage provider. He still did not implement a CMIS interface.

If you like to access the files as a filesystem you can use the filesystem storage provider and access it as a direct filesystem.

How to display kiosk screens

REST will make it easy to implement outside applications to do that

Thomas likes to have a way to use one single http call to

login as a dedicated user
opening a dedicated window
in a borderless view
that refreshes after a set timeout
it can even be good to open more than one window and let the view cycle through them

ZK_SESSION_TIMEOUT_IN_SECONDS has to work not globally but on a single user (kiosk user without timeout)
we can use dashboards to collect data in one window
- Norbert explained that dashboard layout may be improved
integration with external kiosk tools - via sending HTML or screenshots periodically
/kiosk like idempiereMonitor -> dedicated to show the required info
could use the cookie information to login -> problem - how to relogin in a server crash
Carlos says there is an extension to refresh chrome every minute. As that creates a session every x minutes we should better have autorefresh windows

What already works:

if you close the borders (left, right and at the top) for a single user, this is persisted
you can automatic open a window when you login using menu favourites (you set a sequence there)
one can refresh a chrome tab in some ways

What does not work

automatic passwordless login
- Deepak already uses authentication login in web services
- using cookies does not work on a server crash
no menu for the kiosk user
automatic refresh e.g. for normal data windows, reports, etc.

IDEMPIERE-4094 kiosk mode screens

Column reference type for json/jsonb

Norbert thinks about adding JSON and/or JSONB field types. He says that allows to work as in a nosql database. There is a datatype for JSON objects in PostgreSQL.

Carlos proposed to change ASI to a JSON field.

We could maybe link JSON fields to attribute definiton.

Machine learning

Chuck talked about machine learning.

Open item management on fact_acct

Anton has problems to bring the balance of accounting together with the balancing of open items and reconcile everything. Especially using different currencys.

Customer and vendor accounting

There was a question from the audience how the difference in accounting for customers and vendors work.

There are accounting tabs for both of them in the business partner window. The preset for these can be set in the business partner group - that is copied when you create a new business partner. There is a button in the group to copy a changed group setting to all business partners that belong to this group.

Charge on requisition

Deepak has done that and will open this code soon.

Documentation - who is doing what

Chuck advice: much of the documentation is in the code
Manual from compiere is not available any more in the internet
we have ADempiere books
There is a compiere book
There is documentation from Red1
We have our wiki
There is a book from Michael McKay at http://adempiere.gitbook.io
Jan Thielemann did a lot of documentations in the wiki and in a video

An example for a implementor specific documentation using docbook is (in portuguese language):

http://docs.devcoffee.com.br

Chuck showed us what his Open Source ERP Academy offers and gave us an overview.

We talked about that someone wants to write a book. See e.g. http://erp-fundamentals.chuckboecking.com/basics/ that Chuck did using markdown and hugo.

Chuck likes to write the book. It will be open and on his website but it should also be able to be printed. Everyone will be free to translate it to other languages.

Foreign context

Document
Think how to standardize - take into account backward compatibility.

Tab context for processes

Anton opened this topic. When a process is launched by a button it can not access the state of the ui. To e.g. work on a range of all records in a window we need to know what is the actual window where the process has called from. It would help to include a tab identification (context variable id?) in the context.

The bug that Anton had should be solved now and it is possible to get the context of the right tab.

Chuck did a nice documentation about that and sent it to Carlos by mail. Someone has to copy that to our wiki. Please. ;-)

IDempiere workshop 2019