8 Replies Latest reply on Dec 31, 2012 2:40 PM by philmodjunk

    flag_unique Problem



      flag_unique Problem


           If anyone is familiar with the flag_unique method, please help!


           I am trying to create a field flag_unique that contains a 1 for the first instance of the field you're sorting by. So there should be mutliple types of the field you're sorting by, for example, Product Type. The calculation for flag_unique is supposed to be 


      Let ( x = GetNthRecord ( salesperson ; Get(RecordNumber) - 1 ) ;

      If ( salesperson <> x ; 1 ; "" )

      )   //   end let


           However, when I do this, flag_unique only works for the first Product Type, the other Product Types, get 1 in all their instances for field flag_unique. It is not reiterating for all Product Types? I'm not sure how to fix this problem.

           Source for expression: http://www.filemakerhacks.com/?p=25

        • 1. Re: flag_unique Problem

               For this to work your table has to be sorted on the Product type and your calculation field(flag_unique) needs to be unstored.  To make your field unstored click storage options then click "Do not store calculation results - recalculate as needed. 

               This is your calculation.

          Let ( x = GetNthRecord ( ProductTypes ; Get(RecordNumber) - 1 ) ;

          If ( ProductTypes <> x ; 1 ; "" )






          • 2. Re: flag_unique Problem

                 It's sorted by Product Type and the calculation is unstored. I also replaced the calculation with your version, minus the // end let, but it still had the same problem, only does 1 for first instance for the first Product Type.

            • 3. Re: flag_unique Problem

                   Why do you need this value in the first instance of every record?

                   If you are trying to count unique instances, there are other methods that can be used--including one approach that uses ExecuteSQL for those that have FileMaker 12.

              • 4. Re: flag_unique Problem

                     Yes! I'm trying to count unique instances, how do you do the approach that uses ExecuteSQL, since I do have filemaker 12?

                • 6. Re: flag_unique Problem

                       Am I using it correctly?

                       I created a new field "instance" in the table and I entered in this expression as a calculation:

                  ExecuteSQL ("SELECT COUNT ( DISTINCT YourFieldNameGoesHere ) FROM YourTableOccurrenceNameHere" ; "" ; ""  )

                  As field name, I used the field that I'm sorting by, and for Table Occurence, I used the table that the sorting field is in, which is different from the table that I'm using for the layout and the table "instance" is being created in.

                       Currently it's not working, where is my error?


                  • 7. Re: flag_unique Problem

                         I'll let Phil answer your ExecuteSQL question.  The "//" means comment, which will not change anything.   You got to have a typo somewhere.    The sort field and the fields in the let statement must  be the same field.   The let statement in plain language.   X is the Product Type of the prior record.  Then the if statement say if Product Type on correct record is different from Prior record (x) then put a 1 in the field.  So your Let statement or if statement is incorrect or your table is not sorted correctly.  If you view your layout in table View you will see  all product types together, if not then it is not sorted by product type.  I downloaded their sample and created my own sample and my sample works just like the sample on the website link.

                    • 8. Re: flag_unique Problem

                           You'll need to describe your data and tables in more detail. Including a paste of your ExecuteSQL calculation will also help.

                           "It's not working" gives me almost nothing to go on. HOW is it not working? Do you get a question mark, the wrong results or ???