1 2 Previous Next 19 Replies Latest reply on Nov 17, 2011 12:51 PM by irvherzog

    Creating a portal

    irvherzog

      Title

      Creating a portal

      Post

      Can not figure out how to relate two tables.

      Events Table - Event # - Date - Event Details - cost - location

      Contacts Table - Contact ID - Contact Name plus other fields.

      The portal will be in the Events table and I want to add contacts from the contacts table. As I said above I can't figure out what fields to  relate. What fields do I need to add?

        • 1. Re: Creating a portal
          philmodjunk

          This is a typical many to many relationship. Presumably a contact can be linked to many events and an event can be linked to many contacts. The standard solution to this issue is to add a third "join" table in between events and Contacts that serves to link the two.

          Events---<Event_Contact>-----Contacts

          Events::Event# = Event_Contact::Event#
          Contacts::ContactID = Event_Contact::ContactID

          Here's a demo file I uploaded not too long ago. Coincidentally, it links Events to Contacts as you describe here. It has lots of commentary to help you understand how it works and shows several different approaches for designing the interface so that it's easy to link an event to a contact or vice versa: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          • 2. Re: Creating a portal
            irvherzog

            I downloaded and reviewed the ManyToMany demo. It is exactly what I am trying to do. I tried to duplicate it on my database but I am getting this message:This operation cannot be performed because one or more required related records are not available and cannot be created.

            Of course it doesn't say what the required record is. Any idea where I shoud look?

            • 3. Re: Creating a portal
              mgores

              Which operation are you performing when that error pops up?

              • 4. Re: Creating a portal
                irvherzog

                After duplicating the portal as shown in the demo and creating a table, ContactEvents, I created the portal in events. When I click on Contact ID I get a drop down list of ID names and the message occurres when I select one of the names.

                • 5. Re: Creating a portal
                  philmodjunk

                  If you have Filemaker advanced, enable the script debugger and run your script. That way you can tell exactly where this error is popping up. If you do not have advanced, you can put pause/resume steps or show custom dialog steps in strategic places before running the script to help figure out where in the script this error is triggered.

                  From what the demo does, I'd suggest taking a look at the relationship details linking the different table occurrence boxes in manage | Database | Relationships. Do this by doubleclicking the relationship line. If memory serves, some of the relationships in the demo require that "Allow creation of records via this relationship" be enabled and the lack of this property setting can trigger that error message. On the other hand, placing a portal to the wrong table or table occurrence can do that to. Some portals in the demo are to an occurrence of the join table, others are not.

                  • 6. Re: Creating a portal
                    irvherzog

                    Using FileMaker Pro11 On an iMac running Lion.

                     

                    I used the demo Many to Many as a sample and created 2 portals in my db. They are similar to the demo. Events and Contacts. 

                    In Contacts I can add and view the events for that contact but that seems backwards. They should be added in the Events table.

                     In Events I can not add Contacts to the portal that comes from EventsContacts table. I think I followed the sample exactly but I must be missing something.

                    • 7. Re: Creating a portal
                      philmodjunk

                      In Events I can not add Contacts to the portal that comes from EventsContacts table. I think I followed the sample exactly but I must be missing something.

                      Open Manage | Database | Relationships. Double click the relationship line between the table occurrence specified for your layout (events) and the table occurrence for the portal (event_contact?). Make sure that "Allow creation of records..." is enabled for the portal's join table.

                      • 8. Re: Creating a portal
                        irvherzog

                        Allow Creation of records is enabled.

                        • 9. Re: Creating a portal
                          philmodjunk

                          Then it should work. This checkbox option appears for both tables in the relationship. Make sure that this is enabled for Events_Contacts and not Events.

                          Also, make sure that the ContactID field is from the join table and not the matching copy from the contacts table.

                          If that does not resolve this...

                          The demo file starts simple and then provides more sophisticated options for better user interfaces and each layout has it's own set of table occurrences.

                          Exactly what layout design did you choose to implement?

                          • 10. Re: Creating a portal
                            irvherzog

                            Also, make sure that the ContactID field is from the join table and not the matching copy from the contacts table.

                            That was the problem. I corrected ContactID source and now both the Contact History and Event Attendees works fine. Thank you for your invaluable help.

                            • 11. Re: Creating a portal
                              irvherzog

                              I am using a many to many portal and using a unique number to prevent duplicate entries. I notice the duplicate entry validation does not take place until you exit the record or change to a different layout. 

                              Is there a way to have filemaker check or verify after each entry in a portal row instead of waiting until you leave the current window?

                              • 12. Re: Creating a portal
                                philmodjunk

                                Notice what happens if you select a duplicate and then click a blank area of your layout. That should also trigger the validation error. You can set up a script trigger to commit records when you exit the field to pop up the error message sooner.

                                This still allows the user to make the mistake before catching it with a warning message. That fact is why the demo file offers two different strategies for keeping the user from selecting a duplicate link in the first place. A diminishing value list automatically updates so that previously selected contacts for a given event no longer appear in the list. The check box list prevents this by the fact that clicking a contact's checkbox a second time removes the linking record instead of creating a second record.

                                • 13. Re: Creating a portal
                                  irvherzog

                                  Will try the script trigger but ran into another problem first. Today when I enter the Event Table and click in a field I get this message: 

                                  Before typing, press tab or click in a field, or choose the New Record menu command.

                                  It happens no matter which field I try to enter data in whether it is in a portal or any field in the layout. I click in a field and when I enter anything from the keyboard I get the message. Other tables in this file seem to work OK. Any idea what would cause this?

                                  • 14. Re: Creating a portal
                                    philmodjunk

                                    Normally, this happens when you press a key on your keyboard and the cursor is not in any field on your layout.

                                    Can you see the cursor in the field?

                                    Did you modify field behavior so that it is impossible to enter the field when in browse mode?

                                    1 2 Previous Next