5 Replies Latest reply on Oct 21, 2010 8:21 AM by agreer

    Show Unique Records with FileMaker 5.5

    agreer

      Title

      Show Unique Records with FileMaker 5.5

      Post

      Hi,

      I've attached a screenshot of the script I'm having trouble with. It's a script to show only unique records in an archive file. The sort sorts them by Unique Part Number and then Date Completed (descending) so that the most recent record is first and the previous iterations follow, and then going on to the next Unique Part Number.

      This script works until the last unique item. It "sometimes" omits the last Unique Part Number. The Previous UPN shows the previous Previous UPN rather than the one it should. It is always the last Unique Part Number that it omits - never in the middle.

      For example:

      PR1114-90 10/9/10
      PR1114-90 10/1/08
      PR1255-60 12/7/09
      PR1255-60 09/9/07

      will sometimes only show me one unique record (the top one). If there is only one Unique Part Number, sometimes it omits everything. If there were records in between it will show me all of them correctly, but sometimes not PR1255-60.

      Can anyone help shed some light on how to fix it or why it sometimes shows n-1 unique records? Thanks!

      Picture_1.png

        • 1. Re: Show Unique Records with FileMaker 5.5
          philmodjunk

          Nothing obvious jumps out at me as wrong. A few questions/comments though:

          Is Previous UPN a global field? If not, it should be.

          Is Previous UPN on the current layout? Since you use a Clear script step, the field must be present on the current layout in order for Clear to clear the field.

          If you used Set Field ["Previous UPN", "" ] instead of Clear, this is no longer necessary.

          • 2. Re: Show Unique Records with FileMaker 5.5
            etripoli

            Here's how to do it via a relationship & 2 calculation fields:

            • Relate the file to itself, based on your key field, called "Self" (Unique Part Number in your example, I believe)
            • create a calculation, "record_id" = Status ( CurrentRecordID )
            • create another calculation, "master" = Case( Status(CurrentRecordID) = Self::record_id, 1 )

            Anytime the "master" field contains a 1, it is the master record based on your related field.

            • 3. Re: Show Unique Records with FileMaker 5.5
              davidhead

              There is a flaw in your logic. Consider the sequence of records A,A,B,B. The loop starts on the first record.

              First loop: If returns false, global is set to A and goes to next record (found set A,A,B,B; global=A)

              Second loop: If returns true, record is omitted  (found set A,B,B; global=A)

              Third loop: If returns false, global is set to B and goes to next record (found set A,B,B; global=B)

              Fourth loop: If returns true, record is omitted (found set A,B; global=B)

              Fifth loop: If returns true, record is omitted (found set A; global=B)

              Sixth loop: If returns false, exit loop since it is the last record (found set A; global=B)

              So the problem occurs in the fourth loop. But the only way to exit the loop is when the global does not match the current record.

              I don't have FileMaker Pro 5.5 handy so I can test a solution with the scripting available in that version. We would do this with different techniques in the current version.

              TS_Oz, FileMaker Inc.

              • 4. Re: Show Unique Records with FileMaker 5.5
                agreer

                Ts_Oz: So if I'm understanding what you're saying, the omittance of the last record and the subsequent move to the "now" last record (which is the previous record) causes the loop to re-iterate. Then of course they match and *poof* there it goes. I get it.

                Hmmm...well, I'll go back to the drawing board and let you know how I fix it.

                Note to all (especially PhilModJunk, who has been my sounding board for some time now): I just submitted a proposal to upgrade to version 11. Keep your fingers crossed...I know I am.

                Thanks again for everyone's input!

                • 5. Re: Show Unique Records with FileMaker 5.5
                  agreer

                  I'm trying to integrate

                  Exit Loop If ["Status(CurrentRecordNumber) = Status(CurrentRecordCount)"]

                  with little success. I put it after Omit Record as well as after End If and neither solves the problem. Perhaps my logic statement isn't written correctly?