Table join and pre filter

[jimkeane]

I am using Fabrik 1.06 and am trying to display a list of users who have not completed a form.

I have successfully created a join between jos_users and jos_fabrik_formdata_10 and have been pre filtering WHERE id from jos_users is IN jos_fabrik_formdata_10 uid and this gives me a list of those who have submitted the form succesfully.

However, I need a list of those who have not and therefore select filtering NOT IN, however this just leads to no records being returned.

I suspect this is because Fabrik can not link if there is no record to link from?

Is there a way to list non-respondents?

Best wishes,

Jim

 

[jimkeane]

Just a bump

Anybody got a clue about this?;D

I did some digging using a dropdown filter and obviously you can't select empty values.

A way round may be writing to a field for the users record in another joomla table or auto-creating records - both of these are beyond my experience to do though!

Jim

 

[cheesegrits]

Are you trying to create a database join element, or a table prefilter?

Either way, this should be possible. A DIY database join element query would look something like this:

SELECT id as value, username as text FROM jos_users WHERE id NOT IN (SELECT uid FROM jos_fabrik_formdata_10)

The trick is using the subquery (the SELECT in parens) to build th list of uid's from your table, which you can then do the NOT IN on.

-- hugh

 

[jimkeane]

Tried the subquery, but no joy!

Thanks Hugh,

Had a moment of stupidity initially but that works great now!

Many thanks,

Jim