Sorting Repeated Values & adding # of times occur

[Taylor]

Ok, here's what I'm having problems with.

We have an Inquiry database that when someone calls to inquire about a product, we just type in the company inquiring and the catalog number. Once this happens, the catalog references the product file and it brings up all the information.

I need a script that will count the number of times that each different product number comes up and display how many times each product has been inquiried about. I also need to do something like this for our orders, but that's another thread.

Our product numbers are 6 digits (all numbers)...

THANKS FOR ANY HELP!

[bruceR]

Time to get out the manual and study basic Filemaker design, and dump the repeating fields. You can't get there using repeats. You probably need a Contacts table, Products table, and Inquiry table. Each inquiry record holds a ContactID, ProductID, date, perhaps some other information. Then you can easily do all kinds of reporting. Most of it won't require any scripting at all.

[Wendy T]

I may be be wrong, but I don't think the subject line of this message refers to repeating fields.

My advice is to control the interface used for inquiring about products. Use globals for your search fields, click a button to perform the find, which also records the information on the product searched. 2 ways of recording the search come to mind:

(1) A number field in the Products db -- add 1 to it each time it is queried.

OR

(2) Another database -- create a new record each time there is an inquiry, writing the productID, date, and whatever other information you require. Your existing Inquiry db may suit this, but I'm not clear from your description what its structure actually is.

The second option would take a bit longer to set up, but would allow for better detail in reporting product queries in the long run.

cheers,

Wendy

[bruceR]

Drat. You're right, it does look like I jumped to conclusions on this one, sorry.

There are a variety of ways to get there, but the basic one is a subsummary report. The layout wizard will show you how to do this using grouped values when doing a list style report. The global filer method is good also, but if you haven't dived into subsummary reporting it is an important area to learn about.

You didn't mention it, but you probably also want some kind of filter on date. How many times was the product inquired about this month? This year? How many times were products from supplier X inquired about? etc.

The simplest way to do this is with a self relation. In your inquiry database, you have a product ID field. Create a relation where Inquiry is the database on both the left and right side of the relation. Call this relation Same Product. The field will be ProductID on both sides. Then create a calculated field CountProduct, = count(Same Product::ProductID). The up side is that this is very simple and gives you the number you're looking for. But it doesn't allow for any date filtering.