17 Replies Latest reply on Jan 21, 2008 3:38 AM by (Damien_Tourailles)

    Update two tables

      Hello,
      Has anyone successfully updated two tables from one page? I used the "update record form wizard" which worked for the first table, but when I added the "update record transaction" the second update does not work. Any ideas?

      Thanks!
      Steve
        • 1. Re: Update two tables
          Hi Steve U Did you get a solution to this query you had. i am in the same boat and am trying to figure this one out.

          Regards

          Matt
          • 2. Re: Update two tables
            Level 1
            Hi Matt,
            Yeah, I got an insert and an update to work by starting with the Insert Record Wizard, then using the Update Record Transaction, and then using the Link Transactions to link the two together. Just make sure the "starter event" is the same for both transactions (usually it is the form submit of the button something like "KT_Insert") and the redirect page is the same. I assume you could do the same thing with two updates. The two tables are in different databases and it still worked. Hope that helps.
            Steve
            • 3. Re: Update two tables
              Thanks for the suggestion Steve U.
              I am trying to use this to update 2 tables on the same page.

              The problem is that I don't know how to specify the primary key for update #2... In fact the primary key of #2 is the secondary key of #1. How can I put this in the transaction?
              • 4. Re: Update two tables
                Level 1
                Okay, I used this as a primary key for the second update transaction:

                $upd_users->setPrimaryKey("id", "NUMERIC_TYPE", "VALUE", $row_rsdetails['userid']);

                and it works to DISPLAY the data.
                But I can't figure how to make the second update transaction work after the first one...
                • 5. Re: Update two tables
                  Level 1
                  And this line I changed makes DW freeze... :-(
                  • 6. Re: Update two tables
                    Level 1
                    Please, someone help me....
                    I really need to find a solution to update those two related tables in a single page...
                    • 7. Re: Update two tables
                      Level 1
                      So... Nobody can help me?
                      • 8. Re: Update two tables
                        glensbo Level 1
                        Did you try to use the "Insert into two tables wizard" from the developers toolbox?

                        Regards
                        • 9. Re: Update two tables
                          glensbo Level 1
                          What about using a recordset where both tables are represented? Then use the "Create Dynamic Form Wizard"?
                          Regards
                          • 10. Re: Update two tables
                            Level 1
                            Thanks for your suggestions Henrik.

                            I already use the "Insert into two tables wizard" for the INSERT page, which works fine.

                            The problem is the UPDATE page: unfortunately, the "Create Dynamic Form Wizard" doesn't allow to select a recordset, but only tables from the DB...
                            • 11. Re: Update two tables
                              glensbo Level 1
                              I recently build a blog based on the tutorial from Interakt. I wanted to give several users the possibility to add blogs and not only comments. It took me a while before I found out that I needed to apply an extra column in the table to be used as a foreign key for the user. In the turorial only one person is able to write - now several bloggers can start writing (and edit their posts) and still they can comments as well.

                              Perhaps this is a possibility for you - to apply an extra column in your table (serving as a foreign key) giving you the possibility to join tables. Focusing your update process on only one table?

                              How do your tables look?

                              Regards
                              • 12. Re: Update two tables
                                Level 1
                                Thank you for taking the time to help me Henrik.

                                There are two linked tables that I'd like to edit. Let's call the first one "users" and the other "userdetails".
                                The "userdetails" table contains a "userid" foreign key, which refers to the "users" table. So it's a one-to-one relation.

                                Since ADDT doesn't allow to select a recordset (only tables), how could I do this?
                                • 13. Re: Update two tables
                                  glensbo Level 1
                                  Ok - both tables need to be dynamic - ie changes occur in both all the time?
                                  1)Is it then possible to change the tables - one with the 'static' information and one with the dynamic information? or
                                  2)combine the two tables into one?
                                  Have a look at this brief info from Interakt on databases:
                                  http://www.interaktonline.com/Documentation/MXKollection/#045101_workwithdatabases.htm
                                  It has helped me

                                  regards
                                  • 14. Re: Update two tables
                                    Level 1
                                    Ok, maybe it will help if I explain what all this is for.

                                    I want to create a user login system, with 2 types of users that don't have much properties in common. To do this, I use different tables.

                                    - Table "users" stores common user properties such as login, password, firstname, email, etc. This table is used for the login process.
                                    - Table "usertype1" stores properties related to the first type of users.
                                    - Table "usertype2" stores properties related to the second type of users.

                                    Both "usertype1" and "usertype2" have a userid foreign key that refers to "users".

                                    The users must be able to update ALL their info, including firstname, lastname (in table "users") AND other info in table "usertype1" or "usertype2" (according to their user type) on the same page.

                                    I hope this will help understanding what I mean...

                                    Regarding your post, I don't really understand what you mean by "static" and "dynamic" tables...
                                    My database design seems fairly simple in fact, and it's normalized.

                                    Indeed, the two related tables need to be updated at once, since info may change in both tables simultaneously.
                                    • 15. Re: Update two tables
                                      glensbo Level 1
                                      ------
                                      Regarding your post, I don't really understand what you mean by "static" and "dynamic" tables.
                                      ------

                                      I was trying to find out if it was possible to isolate information that only needed to be updated very seldom (static)
                                      and information that often needed to be updated (dynamic)

                                      But as you state in your answer it should be updated simultaneously so my next suggestion -to update in two steps- is no good either.

                                      I think that I have come to a point where I have no further suggestions

                                      Regards
                                      • 16. Re: Update two tables
                                        glensbo Level 1
                                        I have tried to figure out your need - is the principle something like this?

                                        http://www.glensbo.dk/KINDERGARDEN/InsertIntoTwoTables/damien3.php

                                        Regards
                                        • 17. Re: Update two tables
                                          Level 1
                                          Henrik, thank you very much for the time you take to help me.

                                          Indeed, I want to achieve something similar, but I have a slightly different database schema. My "users" table does not have foreign keys to "usertype1" and "usertype2", but the "usertype1" and "usertype2" both have a foreign key to "users".

                                          And I have 2 distinct update pages, one for each user type.

                                          Do you think it's possible to do it that way?