Forums - How to use data table functions in read data?

healyhatman , April 27 2018

Answered

Chronoforms v6

healyhatman

36 45

April 27 2018 #377318

I want to be able to use COALESCE to get one of two values.

I have timesheet.earnings_rate , and template.ordinary_earnings_rate_id

COALESCE(timesheet.earnings_rate, template.ordinary_earnings_rate_id):timesheet.earnings_rate

gives me

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'template.ordinary_earnings_rate_id) AS `timesheet.earnings_rate`, `employee`.`va' at line 1

I assume it's breaking on the comma between the two fields, but looking at the code read_data_output.php isn't helping me

Web developer at SkySpider.com.au
I don't work for ChronoEngine but I do accept donations paypal.me/healyhatman

DON'T COPY+PASTE FROM THE FORUMS! It adds hidden code-breaking format characters!

healyhatman

36 45

April 27 2018 #377331

Answer

https://www.chronoengine.com/forums/posts/t105174/mysql-functions-in-fields-to-retrieve-in-read-data?keywords=database%20functions

1. Commas in parameters need to be surrounded by spaces as in [param1 , param2]

2. Need to use model.field, can't use field on its own

In this way, more complicated queries can be used, like

if(entry.earnings_rate is null or entry.earnings_rate = '' , template.ordinary_earnings_rate_id , entry.earnings_rate):entry.LogicRate

Web developer at SkySpider.com.au
I don't work for ChronoEngine but I do accept donations paypal.me/healyhatman

DON'T COPY+PASTE FROM THE FORUMS! It adds hidden code-breaking format characters!

2CheckOut.com Inc. (Ohio, USA) is an authorized retailer for
goods and services provided by ChronoEngine.com