5 Replies Latest reply on Mar 21, 2015 7:59 AM by lijnbach

    Skip records with the same value

    lijnbach

      Title

      Skip records with the same value

      Post

      Hello everybody,

       

      I am looking for a way to filter records with “double value”.

       

      I have a table with information about students of a ground school. In this information there is a mail address involved. This can be their own mail address or the mail address of the parents. (depending on age).

       

      The school automatically generates mail with FMP. Works all fine. But… there are parents with 2, 3 or even 4 children.

       

      I am looking for a way to skip the records with the same mail address, but I can’t find a proper way. 

       

      Is there someone with a good idea to skip records with the same value?

       

      Thanks in advance.

       

      Hans Lijnbach.

        • 1. Re: Skip records with the same value
          philmodjunk

          Best option would be to put addresses in their own table that you then link to each student by an address ID. Then you can get a duplicate free list of addresses from a layout based on the address table rather than the student table.

          Otherwise, you'd be looking at a portal that filters out duplicates, an ExecuteSQL field that lists all addresses with Select Distinct used to filter out duplicates, a looping script that uses GetNthRecord to check for and omit duplicates or importing this data into a temp table where a "unique values, validate always" validation option filters out the duplicate during the import.

          • 2. Re: Skip records with the same value
            lijnbach

            Thanks for your advise Phil,

            I will figure this out, but if I look at the structure of the database, I think importing the data into a temp table is the most logical one (in this case).

            Thanks again for your time Phil,

            Hans Lijnbach. 

            • 3. Re: Skip records with the same value
              lijnbach

              Hai Phil,

               

              Sorry for getting back on this issue and use more of your time.

               

              I used the import function, works all fine. But you made me curious about what you wrote; “an ExecuteSQL field that lists all addresses with Select Distinct used to filter out duplicates”

               

              I thought that de “Select Distinct” only counts the unique values. So, I was wandering how to use this as a filter. 

               

              Hans Lijnbach

              • 4. Re: Skip records with the same value
                philmodjunk

                If you put DISTINCT inside the parameters of the count function, it's a count of unique values, but if you set up your query as:

                SELECT DISTINCT a.field.....

                you will be pulling up a record set of unique values--at least that's how I recall doing this previously....

                • 5. Re: Skip records with the same value
                  lijnbach

                  O.K. Thanks for your explanation Phil,

                  That is helpful. I don't directly need it at this time, but it is good to learn these things for future use.

                  Thanks again Phil.

                  Hans Lijnbach