Filtering datajoin list by User Field Value

[tghounsell]

I have tables of data with a Company field. Tables are pre-filtered using a value stored in a user field. Now, when a form includes a datajoin element, I want to filter the list to display only rows that match the company value.

I was able to do this successfully for drop down fields, but I'm having some difficulty doing it for dat joins. I assume I should be adding some php to the default field and setting the Eval Default to yes, but when I do that, it seems to get ignored. Also, I'm not sure whether the result should be an array, and whether it needs both an id and label field or what?

 

[lousyfool]

Provided you're storing an id as value in the dbjoin as is typical, a prefilter should work if set basically as follows:

Field: your dbjoin element(raw)
Condition: equals (or whatever you need
Value: an integer value corresponding to the dbjoin value, eventually a MySQL query resulting in the integer (see note below)
Type: no quotes

Note: For that to work, of course you want to have the Company field set up so that stored values are integers that do correspond to the values stored in the dbjoin.

 

[tghounsell]

Hmmm. Not sure I grok it. Are we talking about the default eval code field in database join? I'm assuming I have to use the default code box since it is the only one that seems to accept php and I think I need to make two queries to get the desired result. First, I need to first run a query to determine the company name based on userid, then I can apply that value as the value I want to include in the condition for my list.

 

[lousyfool]

Now I'm confused: I'm still assuming that's a J! custom user field as per you other thread, and that's what I based my reply on.

If that's not the case, you may need more than 4 or 5 lines to (re-)explain all details of your specific, more complex case and what exactly you have so far.
Actually, it may well be not "community Fabrik support" but "custom app consulting/contracting". We'll see.

 

[troester]

Now, when a form includes a datajoin element, I want to filter the list to display only rows that match the company value.

Do you mean you want to filter the "list" of values shown in the dbjoin dropdown? This can be done in the Data-where of the dbjoin element settings.

 

[tghounsell]

You're correct, a j user field value is my criteria for filtering records. But the pre-filter on the table does not seem get applied to the database join field (nor on the dropdown & checkbox fields). For dropdown & checkbox, there is an EVAL Populate field that I'm able to use to create a filtered list. But for database join, there doesn't seem to be a way of using PHP to compose the list.

If I was doing it via query, I think it would look something like this but I don't know how to express this in the interface.

SELECT id, address FROM `properties`
JOIN jos_fields_values
ON company_id = jos_fields_values.value
WHERE jos_fields_values.item_id = {$my-id}

 

[troester]

http://fabrikar.com/forums/index.php?wiki/database-join-element/#data-where

 

[tghounsell]

Got it. Thanks!