10 Replies Latest reply on May 24, 2013 8:45 AM by philmodjunk

    Many to many relationship without using a portal

    PeteSchlosser

      Title

      Many to many relationship without using a portal

      Post

           Hi,

           I'm not sure if I can explain this properly but I am trying to find a way to have two customers listed in a record that is linked to a single customers table. In other words, I need to list a primary customer and a secondary customer from the same customer table. Can this be done without using a portal? Any help would be greatly appreciated!

           Pete

        • 1. Re: Many to many relationship without using a portal
          philmodjunk

               Can you explain why you don't want to use a portal? Knowing that reason will help us suggest an alternative.

               You can list both primary and secondary customers in a list or table view where you list both customer records in a found set of two records.

               You can list both customers on a layout based on the join table instead of customers...

               Other options may be possible.

          • 2. Re: Many to many relationship without using a portal
            PeteSchlosser

                 1. I don't want to accidentally add a third, fourth etc. Is there some way to limit the number of entries in a portal?

                 2. I'd like to be able to search both fields separately. As in, when I search the main customer field i don't want the secondary customer in the found set.

                 3. Design wise, I'd like to have a little more flexibility than being restricted to portal rows.

                 4. I may need to merge the two customer names together in some cases.

                 I think that covers it! I have fairly basic knowledge of filemaker, perhaps these concerns can be addressed by using a portal? I've attached a screenshot of my layout. 

            • 3. Re: Many to many relationship without using a portal
              philmodjunk

                   Would "customer" and "end user" be the two references to records in the customers table? Otherwise, I can't link the specified many to many relationship to what you have in the layout as I dont' see any reference to a "secondary customer".

                   1) the simplest is to limit the number of portal rows and not have a scroll bar. There are also ways to make sure that this is the case with validation calcuations set up in field options.

                   2) Don't see how that applies to a a portal and you'd search for this by putting criteria in the join table field, not a field in the customer table. If necessary, additional criteria can be automatically entered when you do this that limits the found set, but I don't see where this would be necessary.

                   3) There are limitations to portals, but perhaps not as many as you think. You don't have to use the default "mini-table view" format for rows in a portal, you can resize the portal row to be much higher and then place fields within the portal row to get more of a "mini-form view" look to your related data.

                   I also mentioned in my previous post that you can base your layout on the join table instead of customers. This, for example, is how many invoicing systems print out an invoice in order to get better report layout flexibility.

                   4) don't follow that one, but using a portal does not preclude that as far as I can tell.

                   Can you provide a more detailed description of your basic set up here and the tables/relationships involved? The screen shot suggests that you have other tables involved here and I can't help wondering if this is truly a many to many relationship.

              • 4. Re: Many to many relationship without using a portal
                PeteSchlosser

                     I think I've got it. It seems to be working, I'm just not sure if it's right or not! You were right though, not a many to many relationship. I've attached a screenshot. Can you tell from that if I'm on the right track or headed for disaster?

                • 5. Re: Many to many relationship without using a portal
                  philmodjunk

                       Looks like a classice many to may self join to me. When it comes to "right" database design there are some basic questions to ask:

                       1) Is the design producing correct results in all cases?

                       2) Are there any complications/problems created by this design that might be changed/reduced/eliminated by a different design?

                       3) Is it efficient? can you enter the data quickly and accurately? Are there any delays where you have to wait for the system to respond with the results that you need?

                       If you answers are Yes, No, No, then chances are that your solution is "right"--at least for you and your current understanding of FileMaker and the needs of your users.

                  • 6. Re: Many to many relationship without using a portal
                    PeteSchlosser

                         Had to put this on hold for a bit but it seems to be working the way I want it to. Thanks! Just 2 more questions.

                         1. Is there any way to do this without having the ID/Serial Number show up on the layout? In the sample above I would ideally like to only see the customer name. Not sure if that's even possible. The layout is getting quite cluttered but I'd like to avoid having to use tabs.

                         2. Can you change a serial number in the related table and have it still maintain the link? For example, if I wanted to change the prefix or change the number of characters?

                    • 7. Re: Many to many relationship without using a portal
                      philmodjunk

                           1) you can hide the ID number, it need not be visible. If this field is your field for selecting from a value list, here are two methods you can use to hide the field:

                           a) Format the field to be a pop up menu instead of a drop down list. Make sure your value list is defined to hide the ID field an only show the name. When you exit this field, you'll see the name displayed in this field instead of the ID.

                           b) If you want to use a drop down list, Hide it behind the name field from the related table. Use field behavior to deny browse mode access to the name field and make sure that it has an opaque fill color so the ID number doesn't "show through". Size the ID field and name fields to be exactly the same size and do not select the arrow option for the ID field.

                           When the user clicks on this field, the focus passes to the ID field and the drop down list deploys. When they select a value from the field, it disappears behind the name field and the name field then displays the name of the selected person.

                           2) your ID field SHOULD NOT HAVE ANY such prefix in the first place! Editing a match field's value will destroy it's link to related records and this is the main reason why an ID field should be a strictly meaningless value and just serves as a unique identifier. In FileMaker, this is usually accomplished with an auto-entered serial number.

                           If you have a value that identifies the customer, but you are required to include additional info such as your prefix, keep this field in your customer table as a separate data field, but don't use it to link to other tables. Then you can edit the value when the need arises and not lose any links to related data.

                      • 8. Re: Many to many relationship without using a portal
                        PeteSchlosser

                             1. Perfect! Never would have thought of that. Seems obvious now!

                             2. It is an auto-entered serial. I just thought there was an easy way to change it after the fact. Not a big deal.

                             Thanks for your help and the quick responses!!

                        • 9. Re: Many to many relationship without using a portal
                          ninja

                               Best approach is to keep the autoenter serial as your key and never touch it.

                               Use another ID field that says anything you want, and change it all you want...but don't make that your key field.  Doing it this way, the ID field (not key field) would be in only one table, but be used in any related tables...related by the serial.

                               This way you can change it in one place and have it propogate through your layouts yet not have discrepancies later.

                          • 10. Re: Many to many relationship without using a portal
                            philmodjunk

                                 Yes, it's easy to change the value, but not easy to change the value without losing connections to other records in related tables...wink