6 Replies Latest reply on Mar 23, 2013 1:17 AM by mercator

    Easy 1:m relationship adding/editing/deletion?



      Easy 1:m relationship adding/editing/deletion?



           I have the following challenge and was wondering if someone can help:

           I have a table

           Item(Item ID, short name, description, literature reference)

           which I fill with information. No problem doing that. Afterwards, I would like to populate the following table

           Relationship(Relationship ID, Item 1, Item 2,  description, literature reference)

           by first selecting an item from the first table, which will then become the value of Item 1.

           Subsequently, I would like to see ALL entries of table Item, checkbox the ones I would like to add to the Relationship table plus a description and literature reference per checkbox item. Those should then populate the Relationship table.

           To illistrate, it should look like the follwing

           Relationship 1, ITEM 2

              CHECKED Item 1, Comment XYZ, www.amazon.de

              NOT CHECKED Item 2

              CHECKED   Item 3, Comment XYZ, www.amazon.de

              NOT CHECKED Item 4

              NOT CHECKED Item 5

              CHECKED   Item 6, Comment 123, www.google.com

           Of course, when I do the checkboxing, I'd like to see the short name of the items, not the IDs, so it is easier to select. Note as well that I expect to have 500 or so entries in the Item list.

           Needless to say that I would love to be able to edit the Relationship table in a similar manner (with checkboxes at any time, adding and removing items).

           Can this be done and how? Any example is well appreciated.



        • 1. Re: Easy 1:m relationship adding/editing/deletion?

               Nothing here sounds impossible, but I'd like a clearer understanding of what you are trying to do here.

               From a "big picture" perspective, what are you trying to do with this portion of your database?

               Why do you have two item fields in the Relationship table?

               I could be very wrong here but it looks like you are trying to pull up different groups of Item records to link to a common Relationship record and I think the same item record might need to appear in the list for more than one relaitonship record.

               If so, you'll need a different data model to make that happen as you'll have a many to many relationship here not a one to many.

          • 2. Re: Easy 1:m relationship adding/editing/deletion?

                 Hi PhilModJunk,

                 Thanks for your reply. Yes, I want to have a many to many relationship. Basically, I'd like to have an way of editing data for a kind of travelling salesman problem:

                 The Item table contains all the cities
                 The Relationship table allows me to specify if there is a road between two cities and how far it is between them

                 That's the short version of my problem. My actual problem is that I want to capture cause and effect data, where the effect can again be a cause resulting in another effect.

                 Thanks for your help,


            • 3. Re: Easy 1:m relationship adding/editing/deletion?

                   Then you need three tables rather than two. The third table lies between the related tables and serves as a "join" table. In your "traveling salesman" example, this third table would store the distance between the two linked cities.

                   Just to make this even more interesting, the "two tables" that are being linked are actually two Tutorial: What are Table Occurrences? that refer to the same data source table. This makes for a "many to many" "self join" relationship.

                   Cities----<Mileage>-----Cities 2

                   Cities::__pkCityID = Mileage::_fkCityIDStart
                   Cities 2::__pkCityID = Mileage::_fkCityIDEnd

                   With this setup, a portal to Mileage can be placed on a Cities layout to list all the cities to which the salesman traveled. The names of the cities and any other city specific data can be shown in this portal by adding them from Cities 2 to the portal row.

                   Here's a many to many demo file you may find helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              • 4. Re: Easy 1:m relationship adding/editing/deletion?

                     Hi Phil,

                     Thanks for the demo, which I will look into as soon as I get up tomorrow. Hopefully, I will then understand why I need three tables instead of two... :)

                     Again, thanks and good night!

                • 5. Re: Easy 1:m relationship adding/editing/deletion?

                       Actually, you have two tables, but three Tutorial: What are Table Occurrences?.

                  • 6. Re: Easy 1:m relationship adding/editing/deletion?

                         By the way: Thanks, Phil, for your help. I have downloaded the database and had a closer look at it. While I am fir with DB theory, I do miss one or the other piece of knowledge regarding FM implementation, so I did not manage to implement the frontend to an Ishikawa cause and effect database (with the cause being an effect of an another cause-effect relationship) in a "good looking", easy-to-enter manner. Sorry that the Ishikawa cause and effect term did not pop up earlier. Wishing you a very good weekend!