500 Error from WHERE condition in dbj and cdd elements

[Bauer]

In a details form view, a 500 error is produced from a Where condition using a placeholder in both a databasejoin and cascadingdropdown element.

It works fine in form view, but when in details view the raw data is not used in the query, despite being specified in the Where condition.

This is the error shown in the console...
POST https://mydomain.com/customize-reports/details/225/10 500 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Care & Services AND IF(Home Care & Services=5, mng_locked=1, locked=1)' at line 4 SQL=SELECT `fb_breakouts_repeat_filtered_members`.id AS id,`fb_breakouts_repeat_filtered_members`.parent_id, `fb_breakouts_repeat_filtered_members`.`filtered_members` AS value, `fb_member_facilities`.`member_facility_selector` AS text FROM `fb_breakouts_repeat_filtered_members` LEFT JOIN fb_member_facilities ON `fb_member_facilities`.`id` = `fb_breakouts_repeat_filtered_members`.`filtered_members` WHERE facility_type_id=Home Care & Services AND IF(Home Care & Services=5, mng_locked=1, locked=1))

And this is the WHERE condition being used...
WHERE facility_type_id={fb_breakouts___facility_type_id_raw||0} AND IF({fb_breakouts___facility_type_id_raw||0}=5, mng_locked=1, locked=1) ORDER BY facility_name

 

[troester]

Where are you using this WHERE condition?
Which exact Fabrik version?

 

[cheesegrits]

Well, guess what, I can't duplicate. All my raw data is intact in detail view, for joins and CDDs,.

What element type is faciity_type? I'm assuming a join?

What other elements / "stuff" do you have on the form?

-- hugh

 

[Bauer]

Well, guess what, I can't duplicate. All my raw data is intact in detail view, for joins and CDDs,.

What element type is faciity_type? I'm assuming a join?

What other elements / "stuff" do you have on the form?

-- hugh

Why am I not surprised that Phil's Law strikes again?

I'm doing this right - I must have 100 dbj where clauses in this entire project by now.

I narrowed it down to the CDD element - if that is enabled it blows up in detail view. But why it picks up the raw values specified in the WHERE clause in the form view, yet not in the details view, is beyond me.

In the dbj, it is in the 1st option in the 'Data - where' tab
Joins where and/or order by statement (SQL)

In the cascading dropdown, it's in the Advanced tab - Where query
(see pics attached)

Like I said, this had been working fine - until I noticed this today when I tried try to open a detail view.

The message shows in the Chrome java debug Console. That error then prevents the javascript from running (That inserts a header and removes the underscores in my pseudo table list that I created using the dbj and ccd elements.)

The cdd is the 'Included Members' multiselect element on top (which includes only member facilities of that type) and is read-only - and the watch element is the 'report type' (which can't be changed after the initial add).

It's even more complicated in the form view because there I run a User Ajax routine (which selects certain filtered facilities (options) in the cdd element). But that isn't even being done in the details view.

Again, here's the Where clause (and you'll see in the screen snips, I don't include the 'where' in the cdd, per the instructions.)

WHERE facility_type_id={fb_breakouts___facility_type_id_raw||0} AND IF({fb_breakouts___facility_type_id_raw||0}=5, mng_locked=1, locked=1) ORDER BY facility_name

I also included screen snips form the forms - both the form view - and the details view (that shows the error).

 

[troester]

I can't replicate, too.
It's taking the label and throwing errors without "_raw", taking the key with "_raw" in dbjoin and CDD in form and details.

What are your element settings? Is "Data" Value(dbjoin)/Id(CDD) set to id[recommended]?

What do you mean with "CDD... is readonly"?
What is stored in the database?

 

[cheesegrits]

And how is "can't be changed after the initial add" implemented? Element ACL's?

Sent from my Nexus 7 using Tapatalk

 

[Bauer]

I can't replicate, too.
It's taking the label and throwing errors without "_raw", taking the key with "_raw" in dbjoin and CDD in form and details.

What are your element settings? Is "Data" Value(dbjoin)/Id(CDD) set to id[recommended]?

What do you mean with "CDD... is readonly"?
What is stored in the database?

The recommended internal id is used in both cases.
The cdd is set to readonly via javascript in the requirejs(['fab/fabrik'], function () of the form js file.

 

[troester]

Where is fb_breakouts___facility_type_id_raw coming from?
Which element type, which settings?

 

[Bauer]

And how is "can't be changed after the initial add" implemented? Element ACL's?

Sent from my Nexus 7 using Tapatalk

Yes, the 'Type of Report' watch element (which is really the 'Type of facility' in the members lists) is set to 'Registered' for Add and 'Special' for Edit.

And actually I was wrong. It is the element labeled "Member Facilities (Included regardless of other filters)" that is using the cdd. (Even I get confused and I wrote it!) The 'Included Members' list is the dbj. So it's not the cdd members list that triggers the error it's the dbj. If I disable the dbj the error is gone.

This form is used to produce a report. Basically this form allows the user to filter the report to include data only from certain 'members' in that cdd list based on the values of certain data that is collected in the members form which is true for that member 'facility'. As the user selects what they want to filter (mostly things like a numeric range of values - or location, like region, state, or radius in miles, etc.), then as those 'filter settings' elements lose focus, the user ajax is run and returns an array of the ids for the members that match the filters set. Then only members whose id in that cdd element is in the array returned by the user ajax function are set as 'selected' in the 'Included Members' dbj element. That's all done with javascript/ajax in the form_xx.js - which doesn't even get run in the details view. Like I said, it works just fine - except when in detail view.

And actually, I'm not so concerned about this - except that this is just a 'weird bug' - because for now, the only reason I actually show that 'Included Members' dbj elment in the form is for debugging purposes. To verify that the filters set by the form change the 'selected' members in that dbj to include the correct members. But I really don't want users to ever see a list of those 'Included Members' shown in that dbj element - as they could then use this form for 'data mining', to find confidential info about their competition. Once the project goes live I was going to removed that dbj anyhow and replace it only with an element that just shows a count of how many members got selected by the filters that were set (e.g. "214 of 4,212 members selected").

To summarize again, since both the cdd and the dbj use the same WHERE clause, I know the dbj triggers the error because if I disable the dbj element the error disappears.

So now I'm just thinking of changing that dbj for 'Included members' to a cdd element also, then see if this error disappears.

I'll do that and let you know what happens.

 

[Bauer]

Sure enough.
Changing the 'Included Members' dbj to a cdd element got rid of the error.

The attached image, as proof, is more like what I was expecting to see in a details view.

If/when I get some time, I'll try looking at the dbj plugin code too - and see if I can't figure why (apparently on 'some' situations) it doesn't use the raw values.