6 Replies Latest reply on Sep 30, 2015 3:10 PM by mikebeargie

    Portal for related company contacts..

    AndrewJudd

      Hi all, I'm setting up a portal in my contacts table which shows related contacts who work for the same company. I have set up the attached relationship within the same table and it works fine. However I don't want to show the name of the person I am on (current record being browsed) in the portal. Is there a way to filter this or hide? It should just show other contacts who work for the same company.

       

      Hope that makes sense. thank you...

       

      Screen Shot 2015-09-30 at 21.28.04.png

        • 1. Re: Portal for related company contacts..
          mikebeargie

          you can easily filter the portal with a calculation of:

          CLIENTS::__pkClientID <> CLIENTS_RELATED::__pkClientID

          • 2. Re: Portal for related company contacts..
            mikebeargie

            or you can add the same to your relationship in addition to COMPANY = COMPANY, and use the not equal clause in the relationship management dialog. It's actually better there if you never want to show that same person in the child portal.

             

            Also, your database isn't normalized, COMPANY doesn't seem to be a unique value? What if you have two companies of the same name? something to think about.

            • 3. Re: Portal for related company contacts..
              AndrewJudd

              thanks, i used your first reply. How does the second one work? If you put not equal in the relationship the portal shows every other contact in my table? Those not with the same company name.

               

              What do you mean by "normalized?" thank you...

              • 4. Re: Portal for related company contacts..
                mikebeargie

                double click on the little = sign between those two tables, that brings up the relationship dialog. In that dialog you can add multiple "joins" of criteria. The symbol the tables are related on doesn't always need to be "=", FileMaker supports greater than, less then, equal, not equal and even cartesian (all records related both ways).

                 

                select your primary key fields on both sides, select the not equal symbol from the middle, and click the "add" button. Then save the relationship. You'll notice the "=" symbol between the two tables has now changed since you have multiple criteria.

                 

                As for normalization, you can read up on that here:

                https://en.wikipedia.org/wiki/Database_normalization

                 

                I was implying your database wasn't normalized because you're storing the company name as a field in your clients, rather than a separate table for contacts and companies. Normalizing your database would allow you to much more reliably relate your contacts to companies by primary/foreign key, where now you are relating on name. It would also allow you to see things from the context of the company (EG a portal of contacts that work for that company), rather than trying to group contacts together with a self-join.

                 

                There's not really a right or wrong way depending on what your use case is, but it makes your development a lot more "future proof" if all of your relationships are based on fields that are guaranteed unique.

                • 5. Re: Portal for related company contacts..
                  AndrewJudd

                  thanks alot, how is that better than filtering the portal? cheers

                  • 6. Re: Portal for related company contacts..
                    mikebeargie

                    with a portal filter, you're essentially evaluating things twice.