Improve understandability of article upload/download spreadsheet format

[twothreenine]

1. I noticed that the column Ratios to supplier order unit on the far right, in the beer example, is filled with 20.0 Bottle, 10.0 litre because 1 crate contains 20 bottles corresponding to 10 litres. I think it would be easier to understand (and esp. to fill out) like in the article edit menu: 20.0 Bottle, 0.5 litre [per bottle].

2. Also I don't understand why you left the two empty columns (R + S) since the format is rearranged either way.

3. For better readability, I'd suppose to rearrange the table and rename the headers like this:

avail.	Order number	Name	Delivery unit	Custom unit	Ratios to delivery unit	Minimum order quantity	Billing unit	can be ordered per (amount)	can be ordered per (unit)	Price (net)	Price unit	VAT	Deposit	Note	Category	Origin	Manufacturer

Then we'd have the same order and terms as in the article edit modal (except for Order Number which is very important when using article upload)

[lentschi]

I noticed that the column Ratios to supplier order unit on the far right, in the beer example, is filled with 20.0 Bottle, 10.0 litre because 1 crate contains 20 bottles corresponding to 10 litres. I think it would be easier to understand (and esp. to fill out) like in the article edit menu: 20.0 Bottle, 0.5 litre [per bottle].

Hm... We've had the same discussion during our hackathon back then:
For a crate of beer it might be more readable, yes. But for example, if we're talking about a bottle of detergent, that contains 2l and weighs 2,1kg it might be easier to understand it this way. For the article edit form I agreed to implement it in the way you suggested (20 btl à 0,5l).

In the DB however, I save article_versions 1->n article_unit_ratios. That way, from each distinct article_unit_ratio I can calculate back to article_version's supplier_order_unit - so I think that makes most sense. Since CSV isn't only meant to be an export format optimized for human readability, but also to be machine readable, I thought it would be best to export it in a way, it's easy to make calculations with.

Also I don't understand why you left the two empty columns (R + S) since the format is rearranged either way. [...] For better readability, I'd suppose to rearrange the table and rename the headers like this: [...]

While, yes, due to the adaptation of units, we need to add new columns, I tried to at least keep the order the same. (So when manually adapting an old export to match the new format, one doesn't have to shuffle everything around.)

Also - should we decide to shuffle it all - where does (stock) 'quantity' go in your suggestion?

[twothreenine]

But for example, if we're talking about a bottle of detergent, that contains 2l and weighs 2,1kg it might be easier to understand it this way.

I think it's at least as likely to know the kg/l ratio of the detergent as knowing how many l and kg a package contains.
And in a potential olive oil use case I consider it far more likely to know the density of olive oil (you can look up an average value) than knowing how much a package contains.
So in my eyes, the use cases where the relative notation is more useful clearly prevail, in addition to the consistency (with the GUI) argument.

Since CSV isn't only meant to be an export format optimized for human readability, but also to be machine readable, I thought it would be best to export it in a way, it's easy to make calculations with.

You can still do calculations with the relative notation, though. It would still be machine readable?

While, yes, due to the adaptation of units, we need to add new columns, I tried to at least keep the order the same. (So when manually adapting an old export to match the new format, one doesn't have to shuffle everything around.)

I see. I'd argue, at least all the unit columns should be grouped together, including the price (unit) columns. Otherwise it's just too confusing.
We could leave the rest of the columns as they are, that would mean A-F remain the same and category would still be on the far right.

Also - should we decide to shuffle it all - where does (stock) 'quantity' go in your suggestion?

Do you mean the "unit quantity"? (why "stock"?)
Isn't this obsolete now? Even when I look at the non-migrated Unit migration test articles, butter seems to already have been semi-migrated to a custom unit 4x250g with a ratio of 4 x Piece per unit, group order unit Piece etc. The Quantity column is always 0.

[lentschi]

You can still do calculations with the relative notation, though. It would still be machine readable?

Well yes, but the involved algorithms would be slightly more complex and a bit slower (both in foodsoft when importing/exporting as well as for any third-party software using the export): You can have an arbitrary number of article_unit_ratios after all. With your suggested notation, if you wanted to convert a quantity from - let's say the 101st - ratio back to the supplier_order_unit, you'd have to convert it into the other 100 ratios up the chain first.
That is admittedly an edge case though (We won't ever have that many ratios for an article), and if you feel strongly about it, I can change it to your suggestion for import/export.

Do you mean the "unit quantity"? (why "stock"?)

No, unit quantity is gone for good, I actually meant quantity (the actual amount in stock), which is still stored in the articles table and thus got me confused. This has never been exported though, so I'll remove the column for import/export 👍

[twothreenine]

the involved algorithms would be slightly more complex and a bit slower ([...] for any third-party software using the export)

Not if those use cases prevail where you come from or need the relative ratio -- then it is the other way around, the relative notation is easier to process.

the involved algorithms would be slightly more complex and a bit slower in foodsoft

I think there'll be 0-2 ratios in most cases. I guess the performance difference in Foodsoft will be a matter of milliseconds?
To me, the advantages (most use cases, consistency) clearly outweigh it.

[twothreenine]

Related issue: Comma separation in article import/export inapplicable for units with commas

I hope it suits you that I don't open a separate issue for this.

I noticed that you used comma separation within Ratios to supplier order unit.
However, there are a lot of units (mostly piece units) with commas in their names, for example "Eurobox, CHEP box" or "ampoule, non-protected".
When I add an article with one of those units, export it, and try to import it again, I get
A problem has occured: undefined method '[]' for nil:NilClass

Thoughts on possible solutions:

• Using quotation marks (' or ")? There are 3 piece units with “ ” -- not " " -- (search the article table for Ò, as they were imported incorrectly) and one scalar unit with ' (Gunter's chain).
• Leaving it on the far right of the spreadsheet and using ; as separator? I guess this would be the easiest solution, but come with the price of worse readability. Also it would be a bit more complicated to process exported CSVs with a flexible number of cells per row.

[lentschi]

I think there'll be 0-2 ratios in most cases. I guess the performance difference in Foodsoft will be a matter of milliseconds?
To me, the advantages (most use cases, consistency) clearly outweigh it.

Like I said, yes - performance is probably insignificant here. But it's not just performance, also the slightly increased complexity of involved algorithms due to the non-normalized form. However, I doubt this matter justifies the long discussion, so I'll just change it without further ado. 😄

I noticed that you used comma separation within Ratios to supplier order unit. [...]

Yes, I had that in the back of my head (just wanted to see if there are any units actually causing an issue first), but thanks for bringing it up again!
I think, whichever custom separator we're going to use for this, the only solid solution will be using an escape rule. (Such as \, for commas and \\ if there's an actual backslash in the unit name). CSV just isn't cut out for this.

In response to your suggestions:

Using quotation marks [...]

We'd then have to use escape characters for quotation marks - this just moves the problem.

Leaving it on the far right of the spreadsheet

Would have been my first idea. (Parsing it wouldn't be too complicated, no.) But what if we'd want to add additional columns at a later point? Then all old exports would cause errors on import. (Like with this change, only this time it's inevitable.)

[twothreenine]

I think, whichever custom separator we're going to use for this, the only solid solution will be using an escape rule. (Such as , for commas and \ if there's an actual backslash in the unit name). CSV just isn't cut out for this.

Right. Then we don't need additional columns and can take the order I suggested, I guess?

[lentschi]

Then we don't need additional columns and can take the order I suggested, I guess?

Yes. 👍 But which one do you mean? Your original suggestion or the one where you conceded...?:

We could leave the rest of the columns as they are, that would mean A-F remain the same and category would still be on the far right.

[twothreenine]

I meant one of them (having ratios amid the other unit columns).
For readability, you could also make a case to take my original suggestion, but put Note on the far right since it can be very long. I think that would be my favorite way, but I'm happy with all three.

[twothreenine]

to do for myself: check if this is all solved

[lentschi]

Concerning #47 (comment) ... Any update @twothreenine?

[twothreenine]

I'm not happy with the order of the columns yet. Also the column heading Unit should be changed to Custom unit.
I'll do this.

Also I got another idea to improve the article CSV which would require another column, however I'd suggest to implement this upstream after this fork is merged, but before the next release.

[lentschi]

Okay, so I'll move this to Post-Merge then. 👍

[twothreenine]

Okay, so I'll move this to Post-Merge then. 👍

I meant that only for the other idea (upstream) which is not part of this issue.

I still wanted to rearrange the columns as #46 depends on it (we'd have to do more rearranging later) and did so in #84.