1 Reply Latest reply on Mar 13, 2009 9:12 AM by philmodjunk

    Show one record for each unique customer name



      Show one record for each unique customer name


      We have a data base where each record represents a roll of fabric that was ordered.


      What I am trying to do is to do a search where my results show one customer name (one of their orders) for a given query. This is in an effort to make a list of all of the customers that have purchased products from us in a given year. 


      For example, if I want to search for a showroom in one field, and a date range for another field, do I put something in the customer field (i.e. operator "!" or something similar), then show omitted? This obviously doesn't get me what I wanted but in this case, I would hope that my results would come back with one record from each unique customer for a given date range. Is there a way to get a more targeted search for that customer field? 


      I just wanted to give you all an idea of what I am trying to get. Thanks!

        • 1. Re: Show one record for each unique customer name



          I've read your question and have been mulling it over for awhile. Maybe some other clever person can figure a way for you build the find request you want manually, but I can't think of a way to do this.


          I can think of a way to use a script to eliminate duplicate entries once you have generated the found set that displays all the required records (except it still has duplicate entries).


          In the script:

          1) Sort your found set so that records with matching values are adjacent. You also will want to sort it so that where there are duplicate entries, the instance you wish to retain is listed first (or last) in each cluster of matching records.

          2) loop through your found set and use omit to remove duplicates from the found set (This part can be tricky as you will need to write the script to correctly handle what happens if the last record needs to be omitted. Omit moves you to the next record unless you are omitting the last one.)


          You can use a variable or a global field to store the value from the previous field so you can compare it to the current record to determine whether it should be omitted.


          Once you have the script working for you, you can either put a button on the layout that omits duplicates or you can include it as part of a larger script that takes user input and performs the find for them.


          Hope that helps.


          Phil Caulkins