How to set Dbjoin to use Label from Repeat Group Parent?

[margarizaldi]

Hi, I have a form with two groups:

1. Goup for the form itself with several elements including `Name` field (say: `table___name`).
2. Repeat group which (I was surprised that) its data is stored in different table (`table_30_repeat`).
   This repeatable group contains dropdown element named "Options" (`table_30_repeat___options`), parent id, and id.

Then I made another form with a databasejoin element (`table2___option_A`).
This dbjoin field must populate users' Name who selected "Option A" from `table_30_repeat___options`.
(Later I will also created the same field for the "Option B" etc. with the same way)

Is it right for me to connect the dbjoin to `table_30_repeat`?

• If yes, how to use the Name (`table___name`) as the label (doesn't matter for the value, either the name or table2___id)? The repeat group only contains `parent_id` to identify the parent data.
• If not, how is the solution? Do I need to connect dbjoin to the `table` instead? But how to make the WHERE statement?

Cheers.

 

[cheesegrits]

Think of repeat groups as one-to-many relational tables. That's what "repeated" data is, in a database. A "parent" table with a one-to-many relationship with a "child" table.

http://www.databaseprimer.com/pages/relationship_1tox/

To do what you need, you can use a "CONCAT label" on the join on table2, with a subquery to get the name from the original table, like ...

(SELECT name FROM table WHERE table.id = {thistable}.parent_id)

-- hugh

 

[margarizaldi]

Think of repeat groups as one-to-many relational tables. That's what "repeated" data is, in a database. A "parent" table with a one-to-many relationship with a "child" table.

http://www.databaseprimer.com/pages/relationship_1tox/

To do what you need, you can use a "CONCAT label" on the join on table2, with a subquery to get the name from the original table, like ...

(SELECT name FROM table WHERE table.id = {thistable}.parent_id)

-- hugh

Wonderful, works perfectly. Thanks much.

Anyway, how about the user filter (user_id = '{$my->id}') ??
Now I've added a new user element in the repeat group and it works with Update on edit = Yes, but is it good idea? Can I include the filter in the WHERE statement?

 

[margarizaldi]

Now I've added a new user element in the repeat group and it works with Update on edit = Yes

FYI: I set all user elements in the other forms as No for Update on edit. I think I need this so that when the administrator needs to edit the list data, he doesn't change the user id.

 

[cheesegrits]

Not sure what you are asking. If you need to filter by userid, then filter by userid.

-- hugh

 

[margarizaldi]

Well it doesn't matter. I can handle it Thank you very much