13 Replies Latest reply on Feb 12, 2014 12:48 PM by raphthepenguin

    Many to Many Relationship?

    raphthepenguin

      Hey there,

       

      I am once again struggeling with relationships!

       

      I have a Person Table and an Event Table.

      The endgoal is have a portal List with ALL the Events are an I can easily mark attendence of the current viewed Person record.

      On the flipside it would be great to have a List of all the People and I can mark attendence of the current viewed Event record.

       

      Now I thought this would probably a Many to Many relationship and created a joined Tableoccurance PersonEvent.

       

      So essentially its:

      Person Table: PersonEvent Table: Event Table:

      kp_Person = kf_Person

      Name kf_Event X kp_Event

      Attendance EventName

      Date

       

      I hope till here at least things are correct.

       

      Now I started with the portal. The problem is that Events are only shown when a person was there... But when I change it arround it shows attendence for all contacts!

       

      I hope I clarified the problem and would appreaciate help!

       

      Thanks

        • 1. Re: Many to Many Relationship?
          erolst

          The join table is just that; it joins two (or more) parents by connecting fk to pk in both (or all) directions. You have used the Cartesian operator to connect Events to the join table, but that doesn't show you the People of the current Event, but all People with join table records instead.

           

          Change your setup so kf_Event matches kp_Event (just like kf_Person matches kp_Person).

           

          You can use a Cartesian relationship in addition to see a list of all events on a Person layout, e.g. as a selection tool; create a new Events TO, add it with a Cartesian operator (x) to the People TO and put a portal into that relationship on your layout.

           

          Now you could use that portal to create new join records, e.g. by writing a script that captures the current person's ID, plus the ID(s) of the selected (or several selected) Event records in the Cartesian portal, then goes off to the join table and creates the new record(s).

          • 2. Re: Many to Many Relationship?
            raphthepenguin

            Thanks!

            It I made it all work now with your help!

             

            The only problem is, the Portal seems to "hang". Meaning Sometimes it does not update adding or removing an event. But when I go on another record and come back, it will be updated!

            I tried to "Flush Cache" and "Commit Records" as Scriptsteps, but the proplem still occurs!

             

            Any ideas?

            Thanks!

            • 3. Re: Many to Many Relationship?
              erolst

              raphthepenguin wrote:

              I tried to "Flush Cache" and "Commit Records" as Scriptsteps, but the proplem still occurs!

               

              That should work (assuming you mean Refresh Window [ flush cache ]). Try to reverse the order of those two steps.

              1 of 1 people found this helpful
              • 4. Re: Many to Many Relationship?
                raphthepenguin

                Thanks that did the Job for me!

                 

                I did flush cache to disk...

                Changed that to Refresh Window, and Put the commit records before it!

                 

                Thank you!

                • 6. Re: Many to Many Relationship?
                  raphthepenguin

                  Ok I think I have the same Problem again.

                  Only this time I am trying to do the whole thing the other way round! Meaning I am starting with The event and pick people to it.

                   

                  After being stupid and trying it in one portal again, I realized that I keep doing the same mistake. So I am back at the 2 portals.

                  And the one that shows all people and lets me add them to the current event works fine.

                   

                  But I cannot find the right settings for the second Portal that shows all people that are allready selected to this event.

                   

                  I have:

                   

                  Event Table:               PersonEvent Table:          Person Table:

                      kp_Event     =               kf_Event             

                                                          kf_person           =         kp_person

                   

                  the portal is showing records from the Person Table, the problem is that it shows the right amont of records, but the records that are showing are always the First record of the database, rather then the correct one!

                   

                  I hope this make sense!

                   

                  Thanks for all the help!

                  • 7. Re: Many to Many Relationship?
                    erolst

                    raphthepenguin wrote:

                    the portal is showing records from the Person Table, the problem is that it shows the right amont of records, but the records that are showing are always the First record of the database, rather then the correct one!

                     

                    Right count, wrong data is usually a sign that the fields come from a different TO than the Portal. Maybe you simply copied over the field from the Cartesian People portal and forgot to redirect it to the correct TO?

                    1 of 1 people found this helpful
                    • 8. Re: Many to Many Relationship?
                      raphthepenguin

                      I was so sure I did that! But after rebuilding the whole thing 5 times in different way that must have slipped thanks!

                      • 9. Re: Many to Many Relationship?
                        raphthepenguin

                        Sadly enough I am encountering another Problem...

                         

                        Since this second version is looking for people it would be helpful to have a search function on this layout. Now I have done that before with a self join of my people TO and it totally works!

                         

                        So I thought I just rebuild the thing on my Event Table (since Event and People are building this relationship).

                        But after I added the criterias for the Filter function (filterstart ≤ Last Name and filterend ≥ Last Name) the portal is just not showing any records! And if I type something in the Search (Filterfield) nothing shows up either.

                         

                        Is it not even possible to do this with Events - People because its not selfjoined? What else could be the problem?

                         

                        Thanks!

                        • 10. Re: Many to Many Relationship?
                          erolst

                          Since I don't know how you implemented this, it may be best if you post your file.

                          • 11. Re: Many to Many Relationship?
                            raphthepenguin

                            There is a Button "Open all Events", then "Add people" to event.

                             

                            Thanks

                            • 12. Re: Many to Many Relationship?
                              erolst

                              1. You need to set the result type of your background calcs to text …

                               

                              2. Cartesian and filtering doesn't seem to play well together; adjust your filter calculations so that an empty filter shows all.

                               

                              3. You need to have the calcs recalculate after an entry; in this case, a Commit is enough. See the OnObjectModity trigger that does just that.

                              • 13. Re: Many to Many Relationship?
                                raphthepenguin

                                Ah, I see...

                                 

                                1. and 3. I had integrated already in my previous tries. I didn't realize that I can just leave the Cartesian out (In my Joint Table I didnt). But after thinking about it it makes total sense! Both will result in showing all records!

                                 

                                Thanks alot!