Funny, I had an issue that I did not think I could figure out by myself, so I went ahead and started writing a good description of my issue to post it on fabric forum. But by the time I was done describing it the issue was solved!
So I thought I would post it anyway, in case it may help somebody else:
I have a form entering formulas. I?m trying to show only the ingredients that ? belong ? to the current client I?m working on using the database join WHERE clause.
In order to find the current client (personne_responsable) I?m working on, I have to do this:
SELECT produit.personne_responsable FROM produit JOIN formule ON formule.produit = produit.id
Then I can lookup the ingredients that are linked with the client (personne_responsable):
select codes_internes_ingredients.personne_responsable from codes_internes_ingredients where codes_internes_ingredients.ingredient = {thistable}.id
So I tried this:
where (select produit.personne_responsable from produit join formule on formule.produit = produit.id) in (select codes_internes_ingredients.personne_responsable from codes_internes_ingredients where codes_internes_ingredients.ingredient = {thistable}.id)
But I?m getting an error 1242
Subquery returns more than 1 row
As I was doing a search on dbjoin where clause I found this
http://fabrikar.com/forums/index.ph...ab-of-databasejoin-element.42722/#post-217096
Quoting Troester from this thread:
{thistable} is a placeholder for the table your dbjoin element is pointing to
If you want to point to the table your form is linked to, try:
WHERE {thistable}.parent_id = {rowid}
or
WHERE {thistable}.parent_id = {gprh_fabrik_user_enrollment___id_raw}
So I rewrote my query, and now I have the expected result!
where (select produit.personne_responsable from produit where produit.id = {formule___produit_raw}) in (select codes_internes_ingredients.personne_responsable from codes_internes_ingredients where codes_internes_ingredients.ingredient = {thistable}.id)
So I thought I would post it anyway, in case it may help somebody else:
I have a form entering formulas. I?m trying to show only the ingredients that ? belong ? to the current client I?m working on using the database join WHERE clause.
In order to find the current client (personne_responsable) I?m working on, I have to do this:
SELECT produit.personne_responsable FROM produit JOIN formule ON formule.produit = produit.id
Then I can lookup the ingredients that are linked with the client (personne_responsable):
select codes_internes_ingredients.personne_responsable from codes_internes_ingredients where codes_internes_ingredients.ingredient = {thistable}.id
So I tried this:
where (select produit.personne_responsable from produit join formule on formule.produit = produit.id) in (select codes_internes_ingredients.personne_responsable from codes_internes_ingredients where codes_internes_ingredients.ingredient = {thistable}.id)
But I?m getting an error 1242
Subquery returns more than 1 row
As I was doing a search on dbjoin where clause I found this
http://fabrikar.com/forums/index.ph...ab-of-databasejoin-element.42722/#post-217096
Quoting Troester from this thread:
{thistable} is a placeholder for the table your dbjoin element is pointing to
If you want to point to the table your form is linked to, try:
WHERE {thistable}.parent_id = {rowid}
or
WHERE {thistable}.parent_id = {gprh_fabrik_user_enrollment___id_raw}
So I rewrote my query, and now I have the expected result!
where (select produit.personne_responsable from produit where produit.id = {formule___produit_raw}) in (select codes_internes_ingredients.personne_responsable from codes_internes_ingredients where codes_internes_ingredients.ingredient = {thistable}.id)