creating new row in joined data

[uktran]

If I have a tableA joined with tableB via a column, and if I open up a row in tableA where there is no corresponding record in tableB, the joined fields are empty but they look editable.

Is it possible so that editing the tableA record, if data is entered into the joined tableB fields, a new row is created in columb with the data stored?

 

[Jaanus]

Depends which table is parent and which one is child in your list join settings. Maybe someone can answer if you post a screenshot

 

[cheesegrits]

That should happen, regardless of which way the join is. But describe the settings in more detail anyway. If it's easier, record a Jing screencast showing your issue.

-- hugh

 

[uktran]

i have table_a which i guess is the parent, with the main data.

I have a Left join to table_b, repeatable=no

The join is on a field with no special settings, which contains an email.

the only other published field in the joined table_b is a textarea called emailinfo, with information relating to the email.

so whenever an email appears in table_a which is not in table_b, the emailinfo field looks editable, but filling in it, doesn't create a new record in table_b as I might expect/

both tables have s'pecial' access rights on all settings, both list and element.

 

[Jaanus]

The join is on a field with no special settings, which contains an email.

What do you mean with this?
If your parent table is table_a and child table_b
then normally your list join settings should be
from table_a to table_b
from id to table_a_id
- assuming your table_a primary key is id (table_a.id) and the foreing key in table_b is table_a_id (table_b.table_a_id). The foreign key should be also an integer field.

 

[uktran]

the tables are joined by a text field

so tablea has a list of people

id, name, fruit
1, john, apple
2, john, banana
3, bob, football
4 phil, BMW

and tableb contains some generic infomation about the person

id, name, info
1, john, likes fruit
2, bob, into sport

so if I join the tables by name, i get this data

id, name, fruit, info
1, john, apple, likes fruit
2, john, banana, likes fruit
3, bob, football, into sport
4 phil, BMW, [blank]

as phil has no record in table be, the joined column is blank.

however, when editing phils record in tablea, the joined 'info' field looks editable, and is confusing as if a user tries to add data to the info field, and saves teh record, no details are saved in this info field as the row doesn't exist in tableb,

so the problem is, a new record should be created in tableb when the joined form in tablea is saved, ideally only if data is endered in the joined field,

 

[cheesegrits]

the tables are joined by a text field

Can you be a bit more specific? Not sure if you really mean the join is text? You really should be joining using the PK on the "parent", with an FK (foreign key) on the "child" pointing to it, and the PK should be an auto--inc integer. Although in the case of a non-repeated join, you can have the FK on the parent pointing to the PK on the child.

And you still haven't really answered the question.

So is your join using an FK on the "parent" (table a) which points to the PK on the "child" (table b), or vice versa?

In other words, is it table_a.table_b_id => table_b.id, or table_a.table_a_id => table_a.id?

Or is it neither, and there are no primary keys involved?

If you aren't pointing your joins to PK's, then it basically won't work. Fabrik makes the assumption that one end of a join is always going to be the PK of a table.

 

[uktran]

ah yes I'm trying to join via two non-PK fields, which both contain an email. No problems, thanks very much for explaining why its not working.

 

[cheesegrits]

Mostly our fault for allowing that configuration to be chosen in the list join UI.

We intend to fix the issue with that "one end must be a PK", but the code involved is very scary, and we've just never quite felt comfortable trying to change it.

-- hugh