Need to calculate a date by finding first record

[rcorbitt]

My company sells small ads in newspapers in the southeast US. We sell the ad, and then fax the copy to the newspaper. Ads run for a number of weeks, but the minimum is 5 weeks. One problem with this system is that the only way we know when the ad starts running is to see the newspaper tearsheet. Since these are delivered mostly by US Mail, it can take 2-3 weeks before the first tearsheet arrives. Worse, we often get the tearsheets out of order. (Example: October 14 will arrive before Oct. 7 or Sept. 30).

I have a FM 6 database that we use to keep track of when the ads run. Each advertiser, for each newspaper, has a record for the number of ads run on each date.

Advertisers that agree to "try" the ad for the 5 week minimum, need to be called and either resold for a longer period, or the newspaper needs to be notified to remove the ad from the page at the end of 5 weeks.

I can create a calc field that makes a "projected end date" that is 5 weeks away, based on the value of the "date" field. However, I need to use the date of the first time the ad runs, not just the date of the first record. (We may enter the ads from the tearsheet of Oct 14 before Sept 30.)

Any suggestions?

[RalphL]

From you description, I see tables (files) for Advertiser, Newspaper, Ad, Issue ( newspaper puplished on a certain date). I think that an Advertiser can have 1 or more Ads, but an Ad will only have 1 Advertiser. The Newspaper can have many Issues but an Issue can only be from 1 Newspaper. An Ad can appear in many Issues of many Newspapers. An Issue can have many Ads.

This means that you have a 1 to many relationship from Advertiser to Ad. You also have a 1 to many relationship from Newspaper to Issue. You have a many to many relationship between Ad and Issue, which requires a join table (file). Each record in the join table should be for one ad in one issue of one newspaper.

[transpower]

You could have calculation field using the If function which is based on the date field if the ad date is blank, otherwise it is based on the ad date.