A custom column built from other columns: simple math operations

[Joanna]

In my library, I have a custom column #pages (the number of pages as estimated by the Count Pages plugin) and a custom column #progress (which is a number, entered manually based on the information from my Kindle: if the Kindle shows 54%, I enter 54 etc.).

I would like to have:
a) a column that shows the number of pages read,
b) a column that shows the number of pages left.

The rough formulas would be:
a) #progress/100 x #pages
b) (100-#progress)/100 x #pages

which, translated into the Calibre template language, look as follows:

a) {#calc:'multiply(divide(field('#progress'), 100), field('#pages'))'}
b) {#calc:'multiply(divide(subtract(100, field('#progress')), 100), field('#pages'))'}

This seems to render correct results. Still, I have some doubts and questions:

1) I don't quite get the very beginning: do I have to put the name of field (#calc: )? It doesn't seem to matter which field I put there (still not quite getting the syntax ).

2) What function should I use so that the calculations are made only if #progress is not empty, and otherwise the target column is left blank?

3) Is it possible to sort such a calculated column in an ascending/descending order like a regular numerical column, and if so, how to achieve that? At the moment, sorting is kind of alphabetical, based on the first digit, then the second digit etc. while ignoring the order of magnitude, i.e. 40 and 400 would be between 4 and 5.

[chaley]

Originally Posted by Joanna

In my library, I have a custom column #pages (the number of pages as estimated by the Count Pages plugin) and a custom column #progress (which is a number, entered manually based on the information from my Kindle: if the Kindle shows 54%, I enter 54 etc.).

I would like to have:
a) a column that shows the number of pages read,
b) a column that shows the number of pages left.

The rough formulas would be:
a) #progress/100 x #pages
b) (100-#progress)/100 x #pages

which, translated into the Calibre template language, look as follows:

a) {#calc:'multiply(divide(field('#progress'), 100), field('#pages'))'}
b) {#calc:'multiply(divide(subtract(100, field('#progress')), 100), field('#pages'))'}

This seems to render correct results. Still, I have some doubts and questions:

1) I don't quite get the very beginning: do I have to put the name of field (#calc: )? It doesn't seem to matter which field I put there (still not quite getting the syntax ).

There are three distinct template language syntaxes: Single Function Mode (SFM), Template Program Mode (TPM), and General Program Mode (GPM). The three share some features and syntax.

You are using TPM, which is designed to look like SFM while adding some of the capability of GPM. In particular, both SFM and TPM let you specify a column that can be used later in the template. In SFM the value of the column becomes the first argument to the (one) function. In TPM the "$" variable is replaced with the column's value. As such you could write your "pages read" (I think) template (in TPM) as

{#progress:'multiply(divide($, 100), field('#pages'))'}

and your "pages left" template as

{#progress:'multiply(divide(subtract(100, $), 100), field('#pages'))'}

However, given the complexity of the templates I would use GPM instead of TPM in order to make the templates easier to read and easier to maintain. One possible GPM template for "pages read" would be:

program:
	progress = field('#progress');
	percent = divide(progress, 100);
	multiply(field('#pages'), percent);

This template returns the value of the last function -- the "multiply".

Originally Posted by Joanna

2) What function should I use so that the calculations are made only if #progress is not empty, and otherwise the target column is left blank?

We get one solution by extending the above GPM template:

program:
	progress = field('#progress');
	percent = divide(progress, 100);
	read = multiply(field('#pages'), percent);
	test(progress, read, '');

Note that this tests for #progress being empty, not being equal to zero.

Originally Posted by Joanna

3) Is it possible to sort such a calculated column in an ascending/descending order like a regular numerical column, and if so, how to achieve that? At the moment, sorting is kind of alphabetical, based on the first digit, then the second digit etc. while ignoring the order of magnitude, i.e. 40 and 400 would be between 4 and 5.

Change the "Sort/search column by" value for the column to "Number". See the attached screen capture.

[Joanna]

@chaley, thank you for so much your help! GPM does look much less confusing.

So now I am using your template for "Pages read" and a similar one for "Pages left":

program:
	progress = field('#progress');
	percent = divide(progress, 100);
	pagesread = multiply(field('#pages'), percent);
	test(progress, pagesread, '');

program:
	progress = field('#progress');
	left = subtract(100, progress);
	percent = divide(left, 100);
	pagesleft = multiply(percent, field('#pages'));
test(progress, pagesleft, '');

This works perfectly, with the exception of all books that have 1000 pages or more (regardless of whether #progress is empty or not). For those, I get a template error: invalid literal for float().

This has not worked right in my previous TPM template either. Not sure what the reason is, though.

There is also some problem with sorting: I've chosen sorting by numbers in the column dialog but now when I try to sort, nothing seems to happen.

[chaley]

Originally Posted by Joanna

[...]

This works perfectly, with the exception of all books that have 1000 pages or more (regardless of whether #progress is empty or not). For those, I get a template error: invalid literal for float().

This has not worked right in my previous TPM template either. Not sure what the reason is, though.

Ahhh, I should have thought of that. The problem is that the "field()" function gets the data with the default formatting for that column, which in the case of #pages will include a comma between the 100s and 1000s place (2,345 instead of 2345). You should use raw_field() instead, which gets the number without formatting it. EDIT: Alternatively, change the formatting for the pages column to remove the comma.

There is also some problem with sorting: I've chosen sorting by numbers in the column dialog but now when I try to sort, nothing seems to happen.

Now this is a mystery. It works fine for me. See the attached screen capture. The column "#myint" is what I used for "#pages" and "#myint2" is "#progress".

[Joanna]

Thanks! I thought it must have had something to do with the comma! Glad it works now.

One more question: I realized that for a couple of books (audiobooks, in fact) I don't have the number of pages entered. How to get 'blank' results for those books? I.e. how to have something similar to your

test(progress, pagesread, '');

but checking the content of two columns: #progress and #pages (and if either of them - or both - is empty, render a blank composite column)?

I'm pretty sure the sorting doesn't work, though. Are you absolutely sure it works for you? Based on the screenshot, it looks like it certainly does but theoretically, you could have sorted on myint first, by coincidence, and then the order would not have had to change.

No idea what's wrong with my sorting. When I switch the sorting method to Text or Date, sorting works (obviously, producing bizarre results). When I switch to Numbers, nothing happens whatsoever.

// EDIT I've checked it with another library and another Calibre installation - same results .

[chaley]

Originally Posted by Joanna

One more question: I realized that for a couple of books (audiobooks, in fact) I don't have the number of pages entered. How to get 'blank' results for those books? I.e. how to have something similar to your

test(progress, pagesread, '');

but checking the content of two columns: #progress and #pages (and if either of them - or both - is empty, render a blank composite column)?

There are several ways to deal with this.

One complexity: raw_field distinguishes between empty fields and fields containing an empty value by returning the string "None" in the first case. This matters because the value for #pages is now fetched using raw_field.

The easiest solution is to check #pages in the first argument to "test", but this would need to be done using "field" instead of "raw_field". Something like:

program:
	progress = field('#myint2');
	left = subtract(100, progress);
	percent = divide(left, 100);
	pagesleft = multiply(percent, raw_field('#myint'));
	test(and(progress, field('#myint')), pagesleft, '');

where as before, #myint and #myint2 stand in for your columns. The "and" checks that both progress and #myint are empty. Note that I use "field" instead of "raw_field" so that undefined turns into the empty string.

I'm pretty sure the sorting doesn't work, though. Are you absolutely sure it works for you? Based on the screenshot, it looks like it certainly does but theoretically, you could have sorted on myint first, by coincidence, and then the order would not have had to change.

Nope, that isn't it. I sorted by author then the composite column (column built from other columns) and it works fine.

Be sure that you are running the latest calibre. There were some problems with sorting in the mid calibre 2 releases.

If you wish I am willing to look at your metadata.db to see if I can determine why it isn't sorting. Zip it up and send it to calibre2 at charles dot haleys dot org. Send only the metadata.db, not the library. Note that I will see the contents of your library. If that is a problem (and I can imagine that it might be) then don't send it.

[Joanna]

Thanks! Now everything (but sorting) works perfectly .

I'm running the almost-latest Calibre (2.57.1), so it's probably not that.

I'd be more than happy to supply you with my metadata.db, if you're willing to have a look at that . One e-mail coming!

[chaley]

Originally Posted by Joanna

Thanks! Now everything (but sorting) works perfectly .

I'm running the almost-latest Calibre (2.57.1), so it's probably not that.

I'd be more than happy to supply you with my metadata.db, if you're willing to have a look at that . One e-mail coming!

I have your metadata.db and ... it works fine. See the screen captures.

I don't have any plugins installed. Given your problems, it is possible that some plugin you have installed is interfering with sorting. You might try starting calibre from the command line using (something like)

calibre --ignore-plugins

to see if the problem goes away. You might also try running calibre in debug mode (Preferences / Restart in debug mode)to see if calibre has anything to say.

Finally, there are some "tweaks" (Preferences / Tweaks) that affect sorting. You should check if you have set any of them by searching for the word "sort" in the tweaks editor.

[Joanna]

@chaley, thanks for checking!

Now I'm really puzzled. Is there any way the problem could be somewhere outside of Calibre (my computer configuration, locale, anything)?

I'm asking this as I've just made a fresh installation of Calibre Portable, no plugins/tweaks whatsoever, default settings only. I've opened the library - and sorting still won't work, which is really strange.

[chaley]

Locale is a fine suspect. Try changing the tweak "locale_for_sorting" to "en" and see if anything changes.

What OS/version are you running? I am on Win10/64.

[chaley]

Also, do run calibre in debug mode. It could be that something is going wrong inside calibre that it can tell us about.

[Joanna]

Tried changing the tweak, to no avail. I am on Win7 Pro 64-bit, Polish version.

[Joanna]

I've run Calibre in debug mode (both my normal installation and the new one). It doesn't look like anything interesting has come up but here are the logs:

calibre Debug log
calibre 2.57.1 Portable embedded-python: True is64bit: False
Windows-7-6.1.7601-SP1 Windows ('32bit', 'WindowsPE')
32bit process running on 64bit windows
('Windows', '7', '6.1.7601')
Python 2.7.9
Windows: ('7', '6.1.7601', 'SP1', 'Multiprocessor Free')
Successfully initialized third party plugins: Extract ISBN && ADLIBRIS_SE && Unplugged && Modify ePub && User Category && ADLIBRIS_NO && Search The Internet && Resize Cover && Quick Preferences && Generate Cover && BOL_NL && Barnes & Noble && Job Spy && BOL_DE && LubimyCzytac && Favourites Menu && MultiColumnSearch && Reading List && Open With && View Manager && Count Pages && Author Book Count Hierarchy && Author Book Count && Walk Search History && Goodreads && catawiki && Quality Check && Manage Series && biblioNETka.pl && Find Duplicates
Starting up...
Calibre, and hence Job Spy, was gracefully shut down last time?  True
Last time daemon started:  never
Last time daemon failed:  never
Total daemon starts inception_to_date:  0
Total daemon failures inception-to-date:  0
Job Spy has finished initialization...
[0] un-optimized number of undesirable characters accumulated:  759
calibre_plugins.multi_column_search.ui:211: UnicodeWarning: Unicode equal comparison failed to convert both arguments to Unicode - interpreting them as being unequal
[1] optimized number of undesirable characters accumulated:  660
Started up in 9.14 seconds with 2421 books

calibre Debug log
calibre 2.58 Portable embedded-python: True is64bit: False
Windows-7-6.1.7601-SP1 Windows ('32bit', 'WindowsPE')
32bit process running on 64bit windows
('Windows', '7', '6.1.7601')
Python 2.7.9
Windows: ('7', '6.1.7601', 'SP1', 'Multiprocessor Free')
Starting up...
Started up in 5.37 seconds with 1 books

[Joanna]

I've tried sorting on my other computer: same results. The computer runs on Windows 8.1 64-bit (also the Polish language version).

[BetterRed]

@Joanna - maybe it's something in Windows->Regional Settings->Advanced Settings->Numbers. FWIW - mine are:



BR