[SOLVED] Where clause in "Data - where" tab of databasejoin element

[marcq]

Hi,

I have created a Form with a databasejoin Element (checkbox layout) in order to list records from a repeated group table (gprh_fabrik_user_enrollment_repeat_choix_formation).

I'm trying now to display only the records that has been checked previously (databasejoin Element : gprh_fabrik_user_enrollment___choix_formation) when I'm editing the form.

I need to retrieve (display) only the records from the gprh_fabrik_user_enrollment_repeat_choix_formation .parent_id which are equal to the form Id (gprh_fabrik_user_enrollment___id).

I have read carefully the "Database join element" wiki and tried to add a where clause in the "Data - where" tab field, but I where unable to succeed. Example :

WHERE gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id = {thistable}.id

id = 4 and I have two records with parent_id = 4 in the gprh_fabrik_user_enrollment_repeat_choix_formation table.

I'll get one record displayed, but I should get two, since two record has been checked previously.

Would appreciate if someone could explain me how to do it.

Thanks in advance,

Marc

 

[troester]

{thistable} is a placeholder for the table your dbjoin element is pointing to (NOT the table your form is linked to).
So try
WHERE {thistable}.parent_id = {rowid}
or
WHERE {thistable}.parent_id = {your-form-table___id_raw}

 

[marcq]

{thistable} is a placeholder for the table your dbjoin element is pointing to (NOT the table your form is linked to).
So try
WHERE {thistable}.parent_id = {rowid}
or
WHERE {thistable}.parent_id = {gprh_fabrik_user_enrollment___id_raw}

Thank you for your explanations troester, but both solutions doesn't work. No records are displayed. Any ideas how I could solve this ?

 

[cheesegrits]

Which copy of the choix_formation element is this? You seem to have five, and I don't see any WHERE clauses on any of them (except one that orders it).

Let me know which one (element ID) and which form that is on.

-- hugh

 

[marcq]

Which copy of the choix_formation element is this? You seem to have five, and I don't see any WHERE clauses on any of them (except one that orders it).

Let me know which one (element ID) and which form that is on.

-- hugh

Hi Hugh,

Form id is 31 - Inscription formation - En attente

Concerned element :

http://webamazingapps.com/projects/...on=com_fabrik&view=element&layout=edit&id=943

Thanks

 

[cheesegrits]

OK, because this is a checkbox join, which because it allows multiple selection has a many-to-many map table between "this" table and the target table, you need to extract the ID's form that ...

WHERE {thistable}.id IN (SELECT choix_formation FROM gprh_fabrik_user_enrollment_repeat_choix_formation WHERE parent_id = '{gprh_fabrik_user_enrollment___id_raw}')

-- hugh

 

[marcq]

OK, because this is a checkbox join, which because it allows multiple selection has a many-to-many map table between "this" table and the target table, you need to extract the ID's form that ...

WHERE {thistable}.id IN (SELECT choix_formation FROM gprh_fabrik_user_enrollment_repeat_choix_formation WHERE parent_id = '{gprh_fabrik_user_enrollment___id_raw}')

-- hugh

Understood ! It is working as it should. Thanks Hugh again,bye, Marc

 

[cheesegrits]

I'm not sure this is the right approach to take though, I'd have thought just making it read only for edit would work better.

-- hugh

 

[marcq]

I'm not sure this is the right approach to take though, I'd have thought just making it read only for edit would work better.

-- hugh

Your approach meet my needs Hugh : total is displayed in list and form and registered into the database. Thanks ! Cheers, Marc