Coping one to many records into another one to many records

M

montagne

New Member
Hi, I don't know if this is the right forum but I would have to ask the following:
I have 2 tables linked together by a code of the type one to many (A-> B). I would like that by selecting the A record and then all the linked records (B) it could be made a copy by creating a new record.
A classic example could be when a person (A) goes shopping (B). After some time, he looks at the orders he has placed and wants to place an already placed order again.
I hope I have clearly explained the problem ...
Thank you all...
 
Thanks for the reply loosyfool, the plug in you indicated is really brilliant, I'll try it.
But I don't know if it's for me. Let me explain: I have 2 tables linked by an ID code, of the type one to many, for example an invoice (denomination) in table A and the related rows in table B. What I would like to copy as new records is the denomination in table A and its rows in table B, with a new ID for both tables.
The problem is that I use the group into the list A, B.
I tried inserting the SAVE AS A COPY button in the form but it just copies the record of table A (after I manually change the ID) and changes the ID of the records of table B with the new ID that I put in table A, losing previous data.
Some idea?
 
What do you mean by “2 tables linked”? I’m assuming you’re using a list join, correct?

What do you mean by “after I manually change the ID”? If you change some row ID in phpMyAdmin or so, not much might work anymore, if anything.
 
Yes exactly,
I have a list with a LEFT OUTER JOIN between 2 tables, the data is shown as grouped. If I click to see the details, the relevant form is called up. Here the ID of table A is shown. If I click the SAVE AS A COPY button, I get an error: duplicate ID. For this reason I have to go and change the ID before pressing the copy button.
The second problem is that by doing so the rows (table B) are updated with the new code and not copied as it should be.
 
Were your lists and their elements created from scratch with Fabrik, or are they based on tables already existing in the DB?
What Fabrik element type is the ID in list A? As Primary Key (PK) in the DB table, in Fabrik it must be type "internalid" (and then not be visible/editable in the form anymore), otherwise you might run into trouble somewhere, indeed.
The same is true for list B and its PK, of course.

So, please first make sure you're using the correct element types, then add a fresh new record (in your example: add a new "order") and try again copying this new record.
 
Last edited:
Hello,
Thanks for your help. The tables already existed in the DB, and their primary keys are of type INTERNALID, while the key that binds the two tables is (is a Foreign Key) int (11). I follow your suggestion and take the keys off the form, and let's see what happens ...
 
Just FYI, I've just tested with J! 3.10.11, F 3.10 Github, PHP 7.4.21, and a list joined by another one set to repeatable.
The list copy plugin works perfectly fine, copying records with multiple repeated or just one single row, no matter if I click the copy button in one row or use the checkboxes to select one or more rows.

So, something is fishy at your end, but hard to tell with the info you gave and without looking closer at it.
 
Hi, I tried the plug in for copying but unfortunately I got an error, see attached images. I think the error is due to the fact that in the list there is the code (from table A), in the celestial row. Unfortunately I need it as the grouping is done on the basis of this data.
 

Attachments

  • fig_01.png
    fig_01.png
    111.2 KB · Views: 30
  • fig_02.png
    fig_02.png
    12.7 KB · Views: 25
I forgot: the error shown does not concern the internalID field, but the foreign key that binds the two tables.
 
I can't see all your various Fabrik list and element settings, so it's still a guessing game here at this end. (And please don't post 20 Italian screenshots here... we're already outside Fabrik support and into supporting your specific use case...)

Just to be sure:
List/table B must have both
- its own Primary Key ID column ("internalid")
- a Foreign Key integer column ("field"), let's call it here e.g. "parent_id"

List A must then have a list join (can, maybe should be INNER) from list_a to list_b, from column id to column parent_id.

If you've done that all right, I don't see why it doesn't work.
 
Hi lousyfool,
sorry for the screenshots, even if I did not understand the reason well, except because they are in Italian, I don't wear any more :)
For the rest you have framed the situation perfectly, which is as you described: there are 2 tables linked together by a numeric field (which unfortunately is not the internalID of table A)
I think I understand the problem: The problem is at the database level. in fact the link between the 2 tables is not the primary key of table A, repeated in table B, but is a numeric field of table A (orderID), which is indexed as unique. So when I copy (with the plug-in) the primary key is updated correctly, while the 'orderID' field is repeated (while it should be incremented automatically) and this causes the error.
I guess there's no way to fix it.
 
I trust you didn't take my note on screenshots too seriously, haha... it's just that it would take many of them for anyone without access to your system to get a full picture of what's going on there. Too many, actually.

As you describe it now, yes, that's not what Fabrik expects, sorry.

Ciao e buona notte! ;)
 
No, I was afraid of having violated some forum rule ....
Now I have to think about how to increment to a new 'orderID' that is the previous plus one, insert it in the field and then copy it with the plug-in ...
Thanks anyway for the help ...
good night.
 
Last edited:
You could probably just add a Fabrik integer field element to list B, name it e.g. "parent_id", then change the list join of list A (to B) to use this in list B.
This way Fabrik should work properly... though the original extension which "created" the tables will not have a Foreign Key in table B as expected ("orderID"?). Perhaps you can cheat your way out by making orderID in Fabrik a calc element returning the order ID, indeed? Then you should have them all happy, I'd think...
 
Hi lousyfool,
sorry for the delay but I had an unexpected event, thanks for the reply, always kind ....
I have read the solution you proposed several times but have not understood it. Could you explain it better?
Meanwhile, I summarize the situation from the point of view of the DB:
there are 2 tables linked by a left outer join, tab_a and tab_b. Each of them has a primary, autonomous key of the auto_increment type. in the table tab_a there is also the 'orderID' field, which links the tables in mode 1 to N. Therefore in the tab_b the 'ordeID' field is repeated many times (as many as there are rows, of the order). When I copy the records of tables A and B, with the plug-in, only the primary keys are copied and incremented, the 'orderID' field is copied as it is, thus generating an error.
The problem would be just to automatically increment the 'orderID' field in both tables. :(
 
Don't forget that, for Fabrik list joins to work, you'll need at least one leg to be a PK.
So, as far as I understand your tables/columns structure, you won't get around having a new "parent_id" integer field in list B, and joining that to list A's PK... assuming that list A's PK is an auto-incrementing integer.

The order ID or number is then a different, separate "problem":
montagne said:
The problem would be just to automatically increment the 'orderID' field in both tables. :(
Click to expand...
I get that. For obvious reasons, you wouldn't want an order copy to have the same ID or number as any other existing one anyway, no matter which code generates the ID/number, Fabrik or the original software.

Somewhere along the way you lost me on what exact format the order IDs/numbers have. Must be the same in both table A and B, obviously, but what is it, and how does it get incremented... there must be a formula, a pattern behind it.

Knowing that pattern: as already said, changing orderID columns in Fabrik to a calc element and using the right PHP code could do wonder and solve that problem.
 
Hello Lousyfool,
here I am. In your last post, you described the problem exactly. The 2 tables have 2 keys: ID, which self-increments itself, is 'orderID' which instead is incremented every time a new order is entered, from some select max ..... etc ...
Hence the problem of copying the order remains: when making a copy of the order, the 'orderID' field must also be increased (at most + 1) but automatically.
Because we remember that we start from the order list, put the flag in the COPY plug-in, and press the copy button. But this throws an error as the 'orderID' field is not changed.
Perhaps the method of using the plug-in in the list is not good, we would need to find another method. Maybe by entering the order details, where the data of both table A and B are shown, and here make the copy, after having automatically updated the field.
Thanks again for the help.
 
If everything were as Fabrik expects/needs it, it'd be working and you wouldn't have a problem.
It's not working. So, something is "off" the Fabrik standard. Yet, from what you're saying I can't tell exactly what.

Alternative approach:
1. Go to phpMyAdmin, and select Table A in the left pane, whatever its real name is.
2. In the main pane, click the "Structure" tab.
3. Take a screenshot of the PK line with the "key" icon and memorise its name ("id" or so).
4. Save the screenshot file as "A_PK.jpg".
5. In the main pane, click the "Browse" tab.
6. Take a screenshot of a few rows in the PK column ("id" or whatever you had just memorised), best including the header.
Make sure it's rows of "proper" orders, not copies that didn't work!
7. Save the screenshot file as "A_PK_rows.jpg".

Then almost the same again for table B:
8. Now select Table B in the left pane, whatever its real name is.
9. In the main pane, click the "Structure" tab.
10. Take a screenshot, containing at least these two lines (and memorise their names):
- the PK line with the "key" icon ("id" or so),
- and the FK line ("orderID" or so).
Make sure it's rows of "proper" orders, not copies that didn't work!
11. Save the screenshot file as "B_PKFK.jpg".
12. In the main pane, click the "Browse" tab.
13. Take a screenshot of a few rows in both the PK column ("id" or whatever) and the FK column ("orderID" or whatever), best including the headers.
14. Save the screenshot file as "B_PKFK_rows.jpg".

Let's see what you've got:
15. Post all 4 screenshots here.
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 97 (members: 3, guests: 94)
    Back
    Top