10 Replies Latest reply on Aug 19, 2013 8:47 AM by dnfmptn

    Best way to show multiple company records for a person

    dnfmptn

      Thanks to Mike_Mitchell for helping with my initial discussion post, but I've struggled with his reply due to my own lack of education in FMP12. So I thought to reframe the problem I'm trying to solve and for that, a new title for the discussion is in order.

       

      The DB is for tracking people we meet in business, adding their contact and company information, also tracking contacts with them, ie phone calls, meetings at trade shows, etc so we can properly follow up with a knowledge of the history. CRM is the grander topic but this is a simple variation.

       

      Anyway I set up the DB without knowing how to show multiple companies for one person. Solution: join table.

       

      I properly set up the join table but lost the connections of about 75 records between person and company. Mike's reply seems to point me to using a portal to the join table, not the company table as I have set up now.

       

      So here's what I am trying to achieve, if it helps:

      A tabbed interface, primarily with Person details, but also listing the person's company(ies) and a few company details as well as a portal to contacts with the person;

      A second tab would include expanded details about any given company

      During data entry, I'd like to type in the person's details, then in the first Company Name field, enter the name of the first company the person is involved with, and have the rest of the Company data fields fill in automatically when the Company is identified.

       

      At the moment, when I enter any data in the Company portal, it creates a new Company record instead of looking at existing records.

       

      So... I know what I want to do but not what to call it or how to relate the tables and layouts properly. Anyone? keywords to search for would help, and solutions like Mike provided are also welcome.

       

      Dave.

        • 1. Re: Best way to show multiple company records for a person
          erolst

          Hey Dave –

           

          here's a small sample database to show you two techniques for creating join records; first one's an entry method using a popup, the second is more advanced,

          but far more flexible, and not that complicated once you “get it”. There are lots of comments (and pictures, too!) on the layout and in the (few) scripts.

           

          Hope this helps. Nice weekend and “Gesundheit”.

          • 2. Re: Best way to show multiple company records for a person
            dnfmptn

            Hi erolst;

            thanks for the reply. the attachment wouldn't open, has some problem with the filename fp7.zip.

            • 3. Re: Best way to show multiple company records for a person
              erolst

              That's odd. Try renaming the archive.

              • 4. Re: Best way to show multiple company records for a person
                SteveMartino

                It opened fine for me...Nice job erolst.  It may be your virus software blocking it.  Try right-clicking and extract all.

                • 5. Re: Best way to show multiple company records for a person
                  Mike_Mitchell

                  Dave -

                   

                  Why don't you post a version of your existing database?

                   

                  Mike

                  • 6. Re: Best way to show multiple company records for a person
                    dnfmptn

                    erolst, Steve, Mike;

                     

                    Many thanks. I got the zip to open finally and as a newb the example is way over my head sorry to say.

                     

                    I'm attaching a screen shot of the layout I'd like to use and a portion of the relationships showing the join table.

                     

                    I have a backlog of data entry to do and my project teammates are getting a bit irritated at the delay.

                     

                    The only thing holding me back from implementing further data entry and use is my confusion on setting up the tables, portals etc so I can:

                    -when entering a new record, fill person fields, then in the company portal add a company name and have its details fill in

                    -or if the company is not yet in the db then create the full company entry on a company tab or layout so it appears in the portal

                     

                    Am I going about it the wrong way? should I not use a company portal but something else? or have the company portal point to the join table companyID, not the Company table companyID?

                     

                    [well, I was about to attach the screen shots but I don't see an attachment button here... maybe it comes up after clicking "Add Reply"]

                    ok, I had to post it and then edit it and click Update.

                    • 7. Re: Best way to show multiple company records for a person
                      Mike_Mitchell

                      Dave -

                       

                      Three things:

                       

                      1) You're correct; your "Company" portal needs to point to the join table, not the Company table. (That's why you keep inadvertently creating Company records.)

                       

                      2) You can't create a new Company record by going to a different tab on the same layout. Every layout is based on a single table occurrence (TO). Tabs don't change that; all they do is allow you to organize the information. So if you want to create a new Company, you'll need to switch to a layout based on the Company TO.

                       

                      3) You can put whatever fields you want in the join table portal, so long as they're related. You can't add the company name directly (because that field isn't in the join table), but you can add the ID and have the database reflect the name from the related Company table. erolst has already shown you two methods for doing that. Just keep in mind the only way you can create the join record is by adding the company ID and the person ID to a join table record. Everything else lives in the parent tables.

                       

                      Hope that clears it up.

                      • 8. Re: Best way to show multiple company records for a person
                        dnfmptn

                        Thanks, Mike!

                         

                        1) OK, I made a new company portal with companyID from the join table, then added fields from the Company table for company name, etc. When I enter a companyID, the other fields fill in properly, hurrah! Downside is, I need to know the companyID and I can't make new company records this way.

                         

                        2) Prior to using the join table, I had one layout initiated with Person fields, then added all the Company fields and I was able to type-tab-type all the way through for data entry as hoped. With this join table in place now it seems I need to enter the Person data then mouse over or find the keyboard shortcut or button for the company layout, enter the data, find the companyID assigned and go back to Person and put in the companyID... ??

                         

                        3) Thanks for pointing this out, that helps make sense of the join table and portal functionality. As noted, I'm struggling a bit with erolst's suggestion, I like the pop-up idea but have to look it over again.

                         

                        So there's no way to have a Company portal and enter the company name and have ID and other fields fill in, drat!

                         

                        Getting close to usability, dave.

                        • 9. Re: Best way to show multiple company records for a person
                          Mike_Mitchell

                          1) The company ID can be presented in a value list, along with the name as the second field. That's the simplest way to do it (although not necessarily the most user-friendly or idiot-resistant way). New company records can be created via scripting, where you spawn a second window with a new Company record prepared for the user to fill out.

                           

                          2) You can't just tab to the new Company data through the join table, true. (The reason it worked before is because the first related record is what shows in a related field. A join table doesn't give you that option.) In order to have the "new" option, you'll need to script the creation of a new company (such as the secondary window idea I mentioned above), then have it appear in the list of options. I often add a "New Company" option to the pick list so the user can create a new entity on the fly.

                           

                          Conceivably, you could use the company name as a key field, but it's a bad idea from a relational standpoint. What happens if you have two Company records with the same name (e.g., two branches, or two locations)? Then the database doesn't know which one you're talking about and your relationship breaks. Keys need to be unique. (It's also a really bad idea to allow users to have access to edit the key field, because if they change it after a relationship is established, they'll break the joins and all sorts of bad things happen.)

                           

                          HTH

                           

                          Mike

                          • 10. Re: Best way to show multiple company records for a person
                            dnfmptn

                            Thanks for spending so much time and sharing so much thought, Mike, I think I'm about where I need to be, I've experiemented along with your instructions and it's coming together.

                             

                            1) That's a nice presentation, now I know what that option means, and it gives me other ideas.

                            2) Darn. Not a veteran DB user, had hoped to do all the data entry in one window but I see why that won't work now. I plunked around in Manage Scripts and put three steps together to do just what you indicate there, and attach to  a button at the end of the Person entry, so that should work.

                             

                            Good point about the companies and locations, that is true on the cards I have, there could be one Person with two business cards, each with a HQ and other sites. Gads!

                             

                            I also discovered that my Company layout was based on the Person table, which I'll fix even though I'm not clear on why it's a bad idea. No need to explain, as they say, 'my brain is full' for now!

                             

                            Got to take the ideas here and in the test db and put them to work in the real one.

                             

                            Again, Many Thanks for leading me to a workable solution, Mike n erolst.