8 Replies Latest reply on Jul 1, 2015 12:08 PM by StevenThimsen

    Comparing lists and identifying missing items

    StevenThimsen

      Title

      Comparing lists and identifying missing items

      Post

      I am trying to figure out (after 2 days of frustration) how to identify when 2 lists are matching or different.

      I have two lists in calculation fields (we can call the fields Completed and MasterList), with the values separated by carriage returns. I am trying to set up another field called NumberMissing to output on a subsummary report the number of items different between Completed and MasterList.

      The lists are currently giving the primary keys of each item (in number form).

       

      Completed:

      10

      4

      5

      9

       

      MasterList:

      4

      5

      9

      10

      19

       

      I would like NumberMissing to show "1" in this circumstance (I can pull in the useful name to show what exactly is missing once I get this figured out...).

        • 1. Re: Comparing lists and identifying missing items
          SteveMartino

          See if this gets you started, in a calculation field called "Missing":

          Case(

               Count(Table::MasterList) ≠ Count(Table::Counted);

               "Missing "  & Count(Table::MasterList) - Count(Table::Counted);  //This will show "Missing 1" (or any amount of difference)
               "Match"
          )

          • 2. Re: Comparing lists and identifying missing items
            philmodjunk

            Valuecount ( MasterList ) - valueCount ( FilterValues (Masterlist ; completed ) )

            Can be a better option if there is any possibility of getting duplicate values in the Completed field.

            • 3. Re: Comparing lists and identifying missing items
              StevenThimsen

              Thank you both - I greatly appreciate it! This gets me going in the correct direction.

              • 4. Re: Comparing lists and identifying missing items
                StevenThimsen

                After getting this working correctly, I'm having an issue filtering out specific items in my master list. Some of the items are tagged as Optional = "Yes", and I don't want them showing up in my list.

                In order to create my master list, I have a formula [ List ( course_PROJECT__list::__kP_Project ) ] looking down a relationship from Course::kP_Course and Course::Subject to Project::kF_Course and Project::Subject. This creates my master list of projects that are related to a specific course and subject within that course.

                Current layout:

                Course::kP_Course and Course::Subject -< Project::kF_Course and Project::Subject

                However, when I attempt to add in an additional relationship field of Course::g_Constant_Yes =! Project::Optional into the above grouping, my master list does not show any projects. I also tried a self-join with another TO of the Project table to filter out the optional projects, with the same result.

                I'm sure I'm missing something basic here... either that, or my entire understanding of relationships is flawed (likely!).

                • 5. Re: Comparing lists and identifying missing items
                  philmodjunk

                  What data types are g_Constant and Project::Optional?

                  The values and data types need to be exactly right on both sides of this relationship.

                  You might also consider using ExecuteSQL to generate your lists of items.

                  • 6. Re: Comparing lists and identifying missing items
                    StevenThimsen

                    The data types are all text, but the pK and fK values are numbers (type is text, however).

                    I was afraid I would need ExecuteSQL to get this done efficiently. I'm just getting into Filemaker, so it's back to the books...

                    • 7. Re: Comparing lists and identifying missing items
                      philmodjunk

                      ExecuteSQL can take the type of list building done with the List function to the next level and simplify your relationship graph at the same time. On the negative side, you have to do it with SQL and with a developer interface that can be pretty frustrating.

                      Course::g_Constant_Yes =! Project::Optional

                      so does the "g" stand for global storage or a calculation field with global storage?

                      If a calculation field, remove the global storage option and leave it as a stored, indexed calculation field.

                      Check the exact values in these two fields. It actually sounds like one of them is empty given the ≠ operator if I understand what you meant by !=.

                      • 8. Re: Comparing lists and identifying missing items
                        StevenThimsen

                        Of course that's why it wouldn't work...

                        g_Constant_Yes was actually pointing to a global calculation field in another table (resources table) which was set to always show "yes" - I changed it to a stored, indexed calculation and it works like a charm.

                        And yes, I meant ≠ when using !=.

                        Thanks so much!