2 Replies Latest reply on Jul 16, 2014 7:11 AM by philmodjunk

    Counting records with a complicated multi table relationship



      Counting records with a complicated multi table relationship


           I am not sure if what I am asking for is possible.

           I have a 'name' table of physicians where one of the fields is a board certified specialty.  Included on this table is another field if they are a member of our organization.

           We hold an election where we merge many specialties into a summary specialty that combines various similar specialties into one specialty name that is broad enough to represent these many specialties.  In other words we might have 130 different specialties that we merge into maybe 60 different ones.

           I created another table, 'conversion',  that is a conversion table that states a certain specialty is the merged specialty

           I want to have a layout of ones t a time a merged specialties that shows in a portal, all the physicians who are board certified in a particular merge specialty. I got that portal to work perfectly.

           Here is my problem.   At the top of this layout, I want it to count how many of these folks are members and how many are not.

           I can get a total of everyone who is board certified, but I can't get the count to 'find' the physician through the conversion table and count the member? field on the name table.

           If I am making any sense and anyone has any suggestions, it would be appreciated.

        • 1. Re: Counting records with a complicated multi table relationship

               You appear to have this relationship:


               Physicians::Specialty = GroupedSpecialties::Specialty

               Is that what you have? Isn't it possible for a physician to be certified in more than one specialty? What do you do then?

               And for a given specialty group, you want a list of all certified physicians that are also a member of your organization?

               ExecuteSQL is one option.

               Here's another:

               Define a calculation field, constMember, in GroupedSpecialties that returns the same value that you enter in Physicians to show that they are a member.

               Make a new Occurrence of Physcians and link it to GroupedSpecialties like this:

               GroupedSpecialties::constMember = Physicians|SamespecialtyMember::Member AND
               GroupedSpecialties::Specialty = Physicians|SamespecialtyMember::Specialty

               Then this calculation, defined in Physicans with a "context" specified for Physicians can give you a count of all member physicians with the same specialty group as that physician:

               Count ( Physicians|SamespecialtyMember::Member )  // any field from Physicians that is never empty can be used here

          • 2. Re: Counting records with a complicated multi table relationship

                 More thoughts on this:

                 The same calculation defined in GroupedSpecialties would allow you to see a total in your portal for each specialty. A summary field and a summary report could then be used to produce a report listing each specialty group and the count of member physicians in that group. Such a report could include or omit a break down showing the totals within that group for each specialty.