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.
- 1 JIRA tickets
- 2 Suggested topics
- 3 First day
- 3.1 Oracle to Postgres migration (and other database migration issues)
- 3.2 Oracle vs PostgreSQL
- 3.3 PostgreSQL optimization
- 3.4 PostgreSQL Replication
- 3.5 Virtual UI columns (Murilo)
- 3.6 Ask for Input improvements (Murilo)
- 3.7 Special Forms (Murilo)
- 3.8 Multi selection field
- 3.9 Community communication / messenger
- 3.10 multiple choice representation in database
- 3.11 Guidelines
- 4 Second day
- 4.1 Product dependency on price list
- 4.2 More free configurable dimensions in fact_acct
- 4.3 Currency rate by document or transaction
- 4.4 User preferences to see inactive records (and even more)
- 4.5 Open window with a predefined search
- 4.6 Dynamic validation in search
- 4.7 Search in the toolbar
- 4.8 Improve Toolbar
- 4.9 Transportation management system
- 4.10 Workflows
- 4.11 REST
- 4.12 Replication from Norbert
- 4.13 share tab customizations
- 4.14 Simple Production
- 4.15 Processes working on multiple records
- 4.16 bulk changes
- 4.17 Aggregating accounting in fact_acct
- 4.18 Assets
- 4.19 Combobox auto filter
- 5 third day
- 5.1 DB Transaction management - for long trx
- 5.2 TMS Transportation Management System
- 5.3 User Experience
- 5.4 Attract developers and implementors
- 5.5 User Experience Focus Group
- 5.6 Other user Experience topics
- 5.7 Accessibility
- 5.8 Alternatives for zk
- 5.9 Distribution / shipment process
- 5.10 DMS
- 5.11 How to display kiosk screens
- 5.12 Column reference type for json/jsonb
- 5.13 Machine learning
- 5.14 Open item management on fact_acct
- 5.15 Customer and vendor accounting
- 5.16 Charge on requisition
- 5.17 Documentation - who is doing what
- 5.18 Foreign context
- 5.19 Tab context for processes
You can find all JIRA tickets we created or touched here:
Some participants proposing what items we can discuss.
- Workflows (Frank)
- DMS Solution by Deepak
- Norbert is interested in REST
- (Sergey, Norbert, Deepak, Carlos)
- -> responsive UI, integration with other frameworks
- 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)
- identify functionalities that are core vs plugin
- Plugin Marketplace (Thomas)
- Best practices
- Validation - certification
- How to collect business features (Norbert)
- costing recalculation
- 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
- Open item management at fact_acct (Anton)
-> 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)
- 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
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:
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.
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.
iDempiere has some functions about Replication:
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.
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.
Client side callouts
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.
Thomas wants to have guidelines e.g. for plugins. That allows to know about and judge guidelines. It will also improve documentation.
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.
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.
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:
- 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.
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.
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)
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.
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".
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.
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.
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.
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.
- 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).
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.
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
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
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 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.
TMS Transportation Management System
A meeting will be coordinated in future. Transportation Management System
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.
Other user Experience topics
- Guidelines about window layouts
- Multi-Dashboard - the layout of home windows is outdated
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
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.
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.
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):
Chuck showed us what his Open Source ERP Academy offers and gave us an overview.
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.
- 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. ;-)