Chronoform v6 read data where conditions syntax

Hi guys,
congratulations for the excellent work you are doing.
I'm having some difficulties using chronoforms v6. I explain:
I have created 1 new joomla user additional field (province) in the joomla login form with related groups and access levels. There are one sql dropdowns that take the value from the tb_province tables in the database. The value of the new joomla user additional field is stored by joomla on xxx_fields_values database table. I managed to load the value into text field field_text_province of chronoforms using one read data (read_data_province). With a second read data (read_data_id_province) recovery the id value of the province value in tb_province of the additional field.

Now I need to retrieve data and populate a dropdown where the id_province in tb_city table (linked to the tb_province) equal to id of tb_province to the recovered one with the read_data_id_province. What syntax should I use in Where Contitions?

Thank you guys!

Hi Max.
How can I get results where condition is null?
if I give this condition:User.deleted:NULL it gives "where User.deleted = 'NULL'"
​
'NULL' is parsed like string than I get wrong results.
​
Thanks in advance for your reply

Hi peppelaria,
​
Please use this format for null:

field_name/is:null

Best regards

Hi Max. Your solution doesn't work.
this is the answer I get: "1064 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 ''null'' at line 1".
​
Any suggestion?

Try field_name/default_value

EDIT: This was a useless suggestion, my bad

thanks for your reply.
now the error is: "1064 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 'DEFAULT_VALUE ''' at line 1"

"where User.deleted = NULL" ? get rid of the quotes around it?

the problem are the quotes around NULL.
it is passed to the query as a string using the syntax User.deleted:NULL.
In the debugger i have => where User.deleted = 'NULL'

User.deleted/is:null
or
User.deleted:is:null
or
User.deleted:/is:null ?
​
Just guess at this point🙂

Hi healyhatman.
Thanks for yous suggestions.
Unfortunatelly in new chronoforms shortcode syntax everithing after colon is put in the query as a string.
​
for example User.deleted:is:null returns WHERE User.deleted = 'is:null'

oh oh!!! how about custom PHP code block, called "getanull", and all it has is "return null;"
​
Then User.deleted:{var:getanull}
​
Just as a hack til Max comes back

thank you.
I will try as soon as possible and let you know if it works

Hi healyhatman.
​
Same result.
Everything after colon is treated as a string, than User.deleted:{var:getnull} returns WHERE User.deleted = ''

what I need to have my form work correctly is something that returns WHERE User.deleted IS NULL.
​
Is there a way to give conditions using php code like in cf5?
​
example: <?php return array('User.deleted' => 'IS NULL'); ?>

value:/is:null

Just tested it and it's working for me - searching a table it gave me all the rows where "value" was empty.

I just tested it but it returns

WHERE `User`.`deleted` = ''

Can you post a screenshot of your read data action?

And just to make sure, that table has a column called "deleted" ? And there are rows in there where the deleted value is empty?

yes sir. It has a column called deleted having values equals to NULL

Don't know what to tell you sorry, it's working perfectly for me.

it works for you because te value in your table is empty. In my table the value is a datetime and it is set to null

Right well I can't help you then we've reached the end of my knowledge. I suppose you could use a php block with
​
$myArray = $this->get("read_data12.Customer", "");
return array_filter($myArray, function($k) {
return is_null($k["deleted"]); } );
​
Replacing read_data12 with whatever your black label is

Hi healyhatman.
I appreciate your help.
I will try to find a way to figure it out a most afficient solution

Did you figure it out? If not I'm pretty sure Max sent me an email when I asked him about it

Hi healyhatman. The solution is: Model.field/IS:{var:Null}
​
Best regards

you have a ":" before "/"
​
????
​
":" should go only before "null"

Hi gix.
Using the above code
​
Model.field/IS:{var:Null}
​
it returns exactly wath I want: all fields where field is NULL.
Using different syntax it returns "null" as a string than the query gives wrong result.

hi peppelaria
yes, sorry, i was referring to a couple of posts before
​
healyhatman
(http://www.chronoengine.com/forums/posts/t104925/p376756#p376756 )
have ":" before "/" and i can't understand why.

you're welcome

Hi Max. Is there a Short code to ti give to a read data a "in" condition?
​
Something like: "id/in:{data:someId}"
​
Thank you for your help

you need it in an array. So use a PHP action to get all the IDs on their own in an array and /in will work with the var.
​
For null use {value:null}

hy healyhatman, for null i managed simply using "field/is".
​
it gives "field IS NULL" in the query that is simply what i needed.
​
Thank you very mutch for your help, I will try to transform the string in array

Hi healyhatman, it works like a charm.
​
Thankyou very mutch

peppelaria instead of having to use php to get an array try the following in the where conditions of your second read data action
​

model.field/in:{var:read_data#.[n].model.field}