enhance import format and allow to concatenate strings
Description
Environment
Attachments
Activity
Hi @Martin Schönbeck, finally I found the time to test this.
I did an additional commit
Can you please create a wiki page to document these new features:
imp format parameterized callout
imp format multiple callouts
concatenate columns and prefix
Could be a page like:
https://wiki.idempiere.org/en/NF9.1_Import_Format_Enhancements
Regards,
Carlos Ruiz
The packin didn’t run well - I think you increased the size of AD_ImpFormat_Row.Callout
Can you please also contribute a patch on https://idempiere.atlassian.net/browse/IDEMPIERE-1575 to increase the size of that column as required - in AD_Column the Callout is size 255.
Hi @Carlos Ruiz ,
does this (the files, lukas appended) help to understand the pull request or should we add additional information?
Regards,
Martin
Good evening,
I just added mock-ups of a csv table and fitting import loader format, as well as a build of our plugin that utilizes the trunk modification our customer requested.
First of all the concatenation takes is done for the field “name”, adding the first two columns to the entry and seperating them with a colon if concatenation is done.
Secondly the fourth column (list prices) uses two callouts with associated parameters; first adding 10% to the value and rounding to 2 digits after decimal, then adding another 16% VAT.
I hope this gives a better understanding of the issue at hand.
Sincerely,
Lukas Heidbreder
Hi @Carlos Ruiz
the change came due to an immediate demand by a customer. In the first time we thought, we could do it without change of core, but that didn’t work good. And using the CSV import you mention would AFAIK not fit our needs because a CSV file has to be prepared fitting to the import. With the old importer our customer can take the files he get from his vendor just saving it as CSV. But perhaps I overlooked a feature in the new importer.
To show what’s the change are for:
The file given by the vendor has four entries for the name an description of the product. Our customer wishes to concatenate these entries into the name of the product. And of course they should be separated by a blank. Example
'Stahlkleiderschrank CP Möbel' in the first field, ‘2-tlg. RAL7035/5012’ in the second. No more entries in the third and fourth entry. So the import definition for all four fields points to ‘Name’. All but the first have a blank as prefix. This blank than will only be used for the second entry, because the third and fourth are empty so that the generated name is Stahlkleiderschrank CP Möbel 2-tlg. RAL7035/5012’ without trailing blanks. If you want a separation without checking for an empty entry you simply can add this as constant between the entries to be separated.
By this way we also could remove a callout which provided a ‘2' as prefix for a category. Thats why I generally prefer to generalize such solutions (first idea was a check field 'separate with blank’.
The need to use more than one callout is not as hard. It came from our first try when completly doing it in a callout. There we tried to combine the prefixing and the concatenation with separate callouts. We thought it could be handy to work with little callouts (e.g. remove currency sign in a number and multiply than with a given value to remove the VAT).
The need to add parameters to the callout arose from changing UOM codes. The vendors deliver various names but of course our customer wants his standard names. So we prepared a callout for that which looks like
de.schoenbeck…..replace(Dtzd,DTZ,Stück,STK,Hundert,HDT) as an example.
Once this is in core, we will provide the callout as an example how to deal with this.
Regarding the previous discussion: I normally would do it. In this special case there was no time for it. And for example inhttps://idempiere.atlassian.net/browse/IDEMPIERE-4269 where I did it, there is no conclusion upto now. I first tried to discuss in the google group and then created the ticket. And I don’t believe, that anybody wants to create shipments with products which are already on delivery for another customer. So we have to change the core with this for more than a year now
Regards,
Martin
Addition: for anybody who doesn’t use this new features nothing changes. All previously defined imports will work as before.
when importing from files provided by third parties it often is necessary to concatenate certain fields to prepare fields in the import table. To circumvent this entries in the import format for the same field in the import table will be concatenated instead of ignoring and logging it. To be able to separate concatenated entry (typically with blank) an optional prefix for string entries is provided which is used only if the string itself isn’t empty.
By this way also prefixes and suffixes can be provided by using a constant entry
Also there should be more than one callout possible, separated by ; as is already described. The callout should be able to get parametrized.