Prefilter Structure Question

[fudge4u]

I would like to create a prefilter to display only records of items which match a value contained within the user's profile.

I created a field in the user's profile {vp3db_comprofiler_fields___cb_dcn}. This contains a "1" or "0" to indicate the items they are permitted to view in a list.

The list I want to join has a field {new_mcdc_items___mcdc_dcn_item} which has a "1" or "0" to indicate when that item is to be displayed.

So, when the user logs in and selects the menu to see a list of items available to them, I only want to display the items where the value of "mcdc_dcn_item" matches the value of "cb_dcn" from their profile.

 

[fudge4u]

I figured out the prefilter issue by running across this thread:
http://fabrikar.com/forums/index.ph...t-using-user-id-based-attributes-value.39500/

The related filter I would like to create is to prefilter a database join element for a dropdown in one of my forms based on the same parameters. I have the database join element functioning to display all items available, but now I would like to display only the item names from that list which match the cb_dcn field in the user's profile.

NOTE: the database table referenced above "vp3db_comprofiler_fields" should be "vp3db_comprofiler".

I have a prefilter query in my list set to: SELECT cb_dcn FROM vp3db_comprofiler WHERE user_id = {$my->id}

Here is what I put in the DATA-WHERE box: WHERE {thistable}.mcdc_dcn_item = '{$q->cb_dcn}' I somehow want to get the the "cb_dcn" element from my user's profile to be the element to match with mcdc_dcn_item and populate the dropdown.

Any suggestions would be much appreciated.

 

[fudge4u]

So I have narrowed down exactly what I am trying to do. I keep getting SQL errors with everything I try.
The element I am trying to figure out is a database join element to create a dropdown menu.

HERE'S WHAT WORKS WITHOUT ERROR IN THE DATA-WHERE BOX:
WHERE {thistable}.mcdc_dcn_item = '0'
OR {thistable}.mcdc_dcn_item = '2'

But, I need a variable in the first line where the '0' is. The contents of the variable I want to compare is in the element: vp3db_comprofiler___cb_dcn for the logged-in user. The element's value is either '0' or '1'.
The constant '2' in the second line is fine.

Is it even possible to load and compare variables in SQL, or is there another approach to this problem? Thanks to anyone who might be able to give me some direction.

 

[fudge4u]

SOLVED! Here is the code that worked:

WHERE {thistable}.mcdc_dcn_item = (SELECT cb_dcn FROM vp3db_comprofiler WHERE user_id = {$my->id})
OR {thistable}.mcdc_dcn_item = '2'

Hope this is helpful information to someone needing a similar function.

 

[cheesegrits]

Sorry, I thought I'd replied a few days ago with that query. But I was on the road and working from my phone, I may have fat fingered sending it.

-- hugh