(SOLVED) Calc element displays fine in Fabrik but returns NULL in SQL query

Status
Not open for further replies.
M

mbeley

Member
I have 2 Calc elements in my candidates table.
One is the age of the candidate, calculated as such (thanks Hugh!)
$thendate = strtotime("{candidatos___dados_datanascimento}");
$nowdate = strtotime("today");
$datediff = ($nowdate - $thendate);
$diff = round($datediff / 31556926);
return $diff;

The other one assigns the candidate's age to one of two age brackets: less than 35 and more than 35 years old
$idade ="{candidatos___idade}";
if ($idade < 35) {
$idade2="<35";
}
else {
$idade2=">=35";
};
return $idade2;

The first one works fine in Fabrik and fine in an SQL query.
The second one displays fine in Fabrik but reutrn NULL in an SQL query.

Please help!
Thanks,
Mathieu
 
Is candidatos___idade the first calc elemen?
You can't use calc elements inside a calc element (you don't know which one is calculated first), you have to redo the complete calculation in the 2nd calc element.
 
It makes sense... But it does not change my problem!
Still it displays fine in Fabrik list view but return NULL in an SQL query.

Now my calc element is as follows:
$thendate = strtotime("{candidatos___dados_datanascimento}");
$nowdate = strtotime("today");
$datediff = ($nowdate - $thendate);
$diff = round($datediff / 31556926);
if ($diff < 35) {
$idade="<35";
}
else {
$idade=">=35";
};
return $idade;
 
Yup. A calc element is only saved into DB if you are saving the record.
So if you are adding a calc element to an existing list/table this column is initially empty, if you need the values in the DB you have to set them in phpMyAdmin (or resave every existing record).

But it doesn't make sense in your case as a calculation including e.g. today (or other values which may change) has to be calculated "on the fly" ("today" will change every day;)).
 
Thanks for your reply.
You are right I just added the calc element to an existing list.
I did not choose "Only Calc on Save" (but it sounds like the save to DB is independent from the settings).
When you say "it doesn't make sense in your case", do you mean that any info stored in the DB from a Calc element will be set once and for all (as opposed to dynamically calculated on the fly)?
 
What Troester said.

When you set up / change the calc, if you have existing rows of data, and you need the calculated values to be stored in the table, you have to handle "priming" that data yourself. We don't magically re-calculate and re-save all your element data in all your rows.

But that brings up the question of why we have the "Calc on save only" option.

We (Fabrik) have no a-priori knowledge of what your calc will do. Obviously the most efficient way of handling calcs is to only do them when the form they are on is submitted, save that calculated value, and simply display that saved value in List view, like any other element data.

Which is fine if a) the calc is "self contained", and only uses static values, or values from within the same row of data, like (say) concatenating last_name and first_name in to a calculated real_name, and b) nothing outside of Fabrik's form submission process for that list/form ever changes the row data.

If either of those conditions are not true, if you use non static values (like today's date), or any other data which can change outside of the form submission process, then you can't rely on the stored value in the table. The calc has to be performed every time the element is displayed.

Which is where that option comes in. We always store the calculated value, but that setting controls whether we use that stored value when re-displaying the value (in list or details view), or whether we re-calc it every time the data is displayed.

-- hugh
 
So, short version of the above ...

Because you use today's date in your calc, you can't use the stored value when displaying the value (because by "tomorrow", the value that was calculated "today" will be wrong). It will have to be re-calculated every time you display it. So you need to set "Only calc on save" to "No".

As mentioned above, we will still save the value in the table when the form is submitted, it just means we won't use that value when displaying it, rather we will re-calculate every time that element is displayed.

-- hugh
 
FYI, another consequence of this is that ordering / grouping by a calc element is problematic, and won't work as expected if "calc on save only" is off. Ordering and grouping is done in the MySQL query, using the data in the table, prior to any "on the fly" re-calculations of the element data during the rendering process.

-- hugh
 
Well, that depends on whether your views use fields from your tables which are calc elements, and if those calc elements need to be calculated on the fly or not, as per the discussion above.

So if your views need to show things like "age", which have to be calculated from first principles on every access of the table then yes ... you'll have to figure those in to your view query using MySQL date calculations.

Just FYI, in my "clipboard of many things" (aka Evernote), I have this query for calculating age from date of birth, which takes in to account things like leap years:

Code: 
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS

Obviously 'dob' needs to be replaced with your field name for date of birth.

-- hugh
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 167 (members: 2, guests: 165)
    Back
    Top