Arithmetic operations on numeric fields

pdurrant · 12-23-2014, 07:09 AM

I have a custom Floating point number field in my database.

Is there any way I can apply I one-off arithmetic manipulation to the field? (I want to divide by 1.5 and then round to the nearest 0.01 for all books)

chaley · 12-23-2014, 09:00 AM

You can do it with bulk metadata edit/search & replace, using a template as the source.

Here is a capture of the s/r screen.

Click image for larger version

Name: Capture.PNG
Views: 159
Size: 44.3 KB
ID: 132834

The template is hard to see in the capture, so here it is

Code:

program: format_number(divide(raw_field('#myfloat'), 1.5), '{0:.2f}')

Change '#myfloat' to the lookup name of your column. The format_number function rounds the result to 2 places (.2f).

pdurrant · 12-23-2014, 09:04 AM

Wow. I'm impressed. I feared I'd have to do some nasty kind of export/munge/import. Many thanks!

HarryT · 12-23-2014, 09:06 AM

As a matter of interest, what are you doing? Converting prices from dollar to pounds?

pdurrant · 12-23-2014, 09:21 AM

Exactly so. For many years, I could only buy ebooks in dollars (Baen, Fictionwise), so I tracked my spending in dollars. As I bought more ebooks in sterling, I applied an arbitrary 1.5 $:£ exchange rate to be able to keep my records consistent.

But now I buy nearly all my ebooks in sterling. So rather than add a sterling price column, I'd rather like to convert my historical prices to sterling as well. The 1.5 conversion rate will do fine. It will convert my pseudo dollar prices back to their actual sterling prices (+/- 1p), and it will be good enough for the dollar prices - the actual exchange rate has fluctuated over time, of course, but I'm not going to obsess that much over accurate figures!

pdurrant · 12-31-2014, 07:37 AM

I've just applied this. Thanks - it worked perfectly. Although understanding that the sample books shows the column after the function had been applied took a few minutes

Hooray - all my calibre prices are now in Sterling.

chaley · 12-31-2014, 07:51 AM

Quote:

Originally Posted by pdurrant

I've just applied this. Thanks - it worked perfectly. Although understanding that the sample books shows the column after the function had been applied took a few minutes

I understand. I don't really like that either, but it is an unavoidable consequence of using a template as the source. The test text column shows the source text, which is the template result including the computation. The test result column shows the same thing because it is a copy of the source.

Quote:

Hooray - all my calibre prices are now in Sterling.

12-23-2014, 07:09 AM	#1
pdurrant The Grand Mouse 高貴的老鼠 Posts: 71,640 Karma: 306652114 Join Date: Jul 2007 Location: Norfolk, England Device: Kindle Voyage	Arithmetic operations on numeric fields I have a custom Floating point number field in my database. Is there any way I can apply I one-off arithmetic manipulation to the field? (I want to divide by 1.5 and then round to the nearest 0.01 for all books)

12-23-2014, 09:00 AM	#2
chaley Grand Sorcerer Posts: 11,789 Karma: 7029971 Join Date: Jan 2010 Location: Notts, England Device: Kobo Libra 2	You can do it with bulk metadata edit/search & replace, using a template as the source. Here is a capture of the s/r screen. The template is hard to see in the capture, so here it is Code: program: format_number(divide(raw_field('#myfloat'), 1.5), '{0:.2f}') Change '#myfloat' to the lookup name of your column. The format_number function rounds the result to 2 places (.2f).

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Aura HD georgia font numeric quirk	cybmole	Kobo Reader	6	04-29-2014 02:54 PM
Non-numeric series indices (for Kobo)	GeoffR	Devices	4	03-05-2013 09:06 PM
Reset book's numeric field to blank (undefined)?	Rob557	Library Management	4	07-31-2012 03:26 PM
Arithmetic number puzzle	pdurrant	Lounge	7	06-13-2010 07:18 AM
Cybook Numeric Entry Dialog	delphidb96	OpenInkpot	8	08-07-2008 09:54 AM

12-23-2014, 09:04 AM	#3
pdurrant The Grand Mouse 高貴的老鼠 Posts: 71,640 Karma: 306652114 Join Date: Jul 2007 Location: Norfolk, England Device: Kindle Voyage	Wow. I'm impressed. I feared I'd have to do some nasty kind of export/munge/import. Many thanks!

12-23-2014, 09:06 AM	#4
HarryT eBook Enthusiast Posts: 85,544 Karma: 93383043 Join Date: Nov 2006 Location: UK Device: Kindle Oasis 2, iPad Pro 10.5", iPhone 6	As a matter of interest, what are you doing? Converting prices from dollar to pounds?

12-23-2014, 09:21 AM	#5
pdurrant The Grand Mouse 高貴的老鼠 Posts: 71,640 Karma: 306652114 Join Date: Jul 2007 Location: Norfolk, England Device: Kindle Voyage	Exactly so. For many years, I could only buy ebooks in dollars (Baen, Fictionwise), so I tracked my spending in dollars. As I bought more ebooks in sterling, I applied an arbitrary 1.5 $:£ exchange rate to be able to keep my records consistent. But now I buy nearly all my ebooks in sterling. So rather than add a sterling price column, I'd rather like to convert my historical prices to sterling as well. The 1.5 conversion rate will do fine. It will convert my pseudo dollar prices back to their actual sterling prices (+/- 1p), and it will be good enough for the dollar prices - the actual exchange rate has fluctuated over time, of course, but I'm not going to obsess that much over accurate figures!

12-31-2014, 07:37 AM	#6
pdurrant The Grand Mouse 高貴的老鼠 Posts: 71,640 Karma: 306652114 Join Date: Jul 2007 Location: Norfolk, England Device: Kindle Voyage	I've just applied this. Thanks - it worked perfectly. Although understanding that the sample books shows the column after the function had been applied took a few minutes Hooray - all my calibre prices are now in Sterling.

Advert

Advert