2 Replies Latest reply on May 13, 2011 2:35 PM by LaRetta_1

    search on calculated field



      search on calculated field


      I've just jumped up from FM 6 to 11 and need to come up with a way to count instances of zipcodes for large mailings.

      I know how many customers are in a particular zip code but want find zips with greater than x customers. I can all the zip codes with the number of records, but can't figure out how to search and get a list of only those with more than 5 records or less than 14 in a given zip code. The calculated field is grayed out for finds. (they are all not indexed).

      If I can figure that out, I can also get the first 3 zip digits sorted and sifted as  I need.

      mac intel, 10.6x

      Any help appreciated.

        • 1. Re: search on calculated field

          Is this all being done on a single table?

          If, so, you are probably using a summary field to count your records for each zipcode. That works for listing all records and getting a breakdown, but you can't enter criteria in the summary field to specify only records where the count is more than 5.

          You might be better off to create a second table where you have one record for each possible zipcode and make your count, find and sort from there.

          You can Import all your current records into this new table, but with Unique Values, Validate Always enabled on the zipcode field. That will filter out the duplicate values during import to set up the table with one record for each existing zipcode.

          Then, with this relationship:

          ZipCodesTable::ZipCode = OriginalTable::ZipCode

          You can use Count ( OriginalTable::Zipcode ) in the ZipCodesTable and this is a field in which you can enter > 5 as find criteria to limit your list to just those with more than 5 records in the OriginalTable.

          • 2. Re: search on calculated field

            "The calculated field is grayed out for finds. (they are all not indexed)."

            Finds can occur whether the field is stored or not; it just may be a bit slower.  First I would suggest that you find out why the zip field is greyed out; I suspect that it is a table which is not related to the current layout and thus is not accessible. What is this calculation?

            If the zip fields reside as data in the table you are searching then the field CAN be indexed.  The zip field should be text and indexing set to Minimal and uncheck auto-index.  Once you have done that, you create another table occurrence of this table (using the ++ in the relational graph) and join them as:

            MainTable::Zip = MainTable 2::Zip

            Then in your main table, a simple calculation (result is number) of:  Count ( MainTable 2::Zip ) will provide your answer.  Then search this field.  But the most important question is 'why do you want to know those records with more than 5 in a specific zip?" Even if this zip comes from a related table and thus the calculation cannot be indexed, it can be the 'key' for the left-side relationship.

            Your purpose in wanting a solution will determine the method of providing the best answer.  We need the purpose and context.  Are you sending mailings?  Are you assigning Sales Reps?  Or do you simply want to always know how many exist in a certain area (while in browse mode) when viewing any given record?