5 Replies Latest reply on Oct 12, 2014 12:52 PM by FentonJones

    Compare records in related tables

    SchoolData

      Title

      Compare records in related tables

      Post

      The relationship graphic is attached.

      The desired result is to input data from the school district’s database and the ADE databases to determine if teachers are properly licensed for the courses they are teaching.

      I believe that a solution can be created in which for each record in Course «» Person the required Licenses to teach that course can be compared with the match records from Person «» License to determine if there is a match.

      Screen_Shot_2014-10-10_at_6.26.31_PM.png

        • 1. Re: Compare records in related tables
          FentonJones

          I think I (kind of) see the problem; which is, as so many more, that you need to pull some info (ids) in from elsewhere, then test them against each other. I cannot be sure it's correct, nor the "best" method.* But, since no own else has posted an solution, it's worth a try:

          A. Going from Person to Course«»License will allow you to get all the "courses" which a Person could correctly match with their licenses.
              1. Yes, they'd get more than really needed, but gets the ones needed [ for a possible match (or not) to person's courses].

          B. Person«»Course will allow all "courses" for a Person

          C. A calculation field in Person could get the above. Another (or same) calculation could see whether ONE (or more) of the "person's courses" do NOT have a match to the "person's licenses". [ Unstored calculations ]

          D. We want to see which "person's courses" do NOT match, a "not find" kind of test. I would use a "Custom Function", which requires FileMaker Pro Advanced (to add to your fine). I'd use: AntiFilterValues, by Bruce Robertson (smart guy):
          http://www.briandunning.com/cf/926

          AntiFilterValues ( "list of person's courses" ; "list of person's licenses" )
          result: "person's courses (ids) who do not match person's licenses (ids)

          * You could likely do this another (more difficult) way. From my way of thinking, if you work (like real work) with FileMaker, then you should get Advanced. It will save you for its cost; either for having Custom Function and/or Script Debugger (watch your script steps more :-).

          E. The above calculation result could be used, going thru another table occurrence of "course«»licenses" (off of "person«»courses" or from "course") to show which "licenses" would match one of, for the person to base.
              
          * An SQL calculation might be about to do this, with its own tools for "getting any courses without a license". 

          • 2. Re: Compare records in related tables
            SchoolData

            A. Going from Person to Course«»License will allow you to get all the "courses" which a Person could correctly match with their licenses.

            Correct.

            This analysis needs to occur for each record in Person«»Course. I can see the licenses a person has by placing a portal on Person«»Course layout and viewing related records from Person «» License. Futhermore, I can place another portal on Person«»Course and viewing related records from Course«»License.

            Going from Person to Person«»Course I can use a portal to view the courses a person is currently assigned.

            Since both of those portals are placed on the Person«»Course layout, I can see the two "lists" side by side and determine if the person is licensed to teach this course.

            The issue is that there are 11,635 records in Person«»Course. Accomplishing this analysis for each one manually is a little time consuming. It would take over 2 days of man hours to accomplish this work. So, I am trying to build a tool to accomplish this work without committing those resources.

            The individuals that create the records in Person«»Course are supposed to verify that the person is licensed to teach the course, however, people make mistakes. In the public education business, this is a grave error and needs to be prevented.

            Thank you for your response. At another employer I had advanced, but could not remember why, so I did not get it for this job, yet. I will upgrade Monday.

            I agree that a SQL calculation could accomplish this, but I do not know how to do that.

            Please continue to help if you can.

             

            • 3. Re: Compare records in related tables
              FentonJones

              Yes, that looks like similar to what I was saying; you're showing "licenses" [ I was showing "courses which match by people licenses" vs. "courses of the people"; then getting "licenses which do not match"; i.e., see the "not matched courses" [ which was easiest done via a Custom Function, as FileMaker does not have a "not found" via calculation].

              You don't need a CF however to show if a "person's course" DOES match. You do not have to look at all the lines and compare them manually. As you said, this must be done in Person«»Courses. Each of those "lists of data", via their own relationship, can be "brought into" Person«»Courses, via the List () function.
              z_cPersonLicenses = List ( relationship to get person's licenses )
              z_cPersonCourseLicenses = List ( relationship to get person's course's licenses )

              Then:
              z_cPersonCourseLicenses_matches = FilterValues ( z_cPersonLicenses ; z_cPersonCourseLicenses )
              [ it does not matter which is first, or which is last, either way will have the same result ]

              The reason I went for the CF to produce "show courses which do NOT have any licensing match" was mostly as is it easier to do a Find on; as you cannot do a Find on "relational records are not there" (as it cannot "see" that). But you could do a Find for courses DO have a match. It should be all the person's courses.

              I suppose a script could do the above in a Loop, which would Omit records which matched; leaving a result of "no match". However, that's way I though of the CF, as it could return the "no match" records directly.

              At least I think so. I could create a "dummy" file, to test. But it seems that you will be able to test.

              • 4. Re: Compare records in related tables
                SchoolData

                Thank you very much. That is exactly the solution I was looking for. The FilterValues field returns "how" the person is licensed to teach the course.

                If the field is empty, then that person is not licensed to teach that course.

                The only confusion was I though z_c meant a container, it means a calculation.

                I do not know how to create a script that loops, but I do know how to create a report listing Person/Course assignments that have an empty z_cPersonCourseLicenses_matches field.

                You can see in the attached screenshot that for the same match I was looking at in the earlier screenshot, the matches field shows that the person has one of the licenses needed to teach this course.

                I hope this will help others.

                • 5. Re: Compare records in related tables
                  FentonJones

                  Another thing you could add is to create a calculation field for [x] NO licenses!
                  zc_PersonCourseLicenses_NO = Case ( IsEmpty (zc_PersonCourseLicenses_matches); 1 )

                  This can be shown as [x] by using a Value List, with a value of 1 (that's all :-)

                  I believe it could be used in a Find, though it will not be fast. This could also be seen and run from the Person layout, to find all people who have this problem (since having any of their "courses" with this would be bad).

                  It could also be used to show a (red) [x] on a layout. You could also use the Conditional formatting on the zc_PersonCourseLicenses_matches field's fill of on the layout, to be red if Value is "empty". Just to make sure no one can ignore this :-|