One field needs to call up two other fields

[Tom T]

I am developing a database that will be an index for a newspaper archive. To cut down on the errors in data entry I would like to link the Date, Volume, and Issue fields in such a way that the data entry person merely selects a date from the pop-up list and this calls up the correct volume and issue number.

How can I do that?

The data entry will take place over the web and I am using Instant Web Publishing, so that I can't use any fancy scripts. This will have to be accomplished by calculations when I define the fields.

Any ideas?

Thanks,

Tom

[CobaltSky]

Hi Tom,

Well, it seems you've answered your own question.

Your question was 'how can I do that?' and the answer was 'by using calculations when defining the fields'... or at least that is one of several possible answers which, given what you've told us so far could well be the right one.

It may be that what you really meant to ask was what formulas might you use in the calculations.

However in order to answer that, one would need to know the exact format and specifications of the volume and issue numbers that you want to generate from the date, and you've not said anything about that. So I'm afraid we don't have enough to go on at this point, to be able to say any more than you've already said yourself.

P'raps if you were to post some details, someone might take a stab at it...

[Tom T]

Ray,

Thanks for replying.

Well, yes, I am looking for the formulas.

The date in the date field would be in the form of mm/dd/yy.

The volume would always correlate to the year, since there is only one volume number per year.

The issue would always correlate to the whole date. This is a weekly newspaper so one would expect 52 issues. But during parts of the year the newspaper turns into a bi-weekly, so there are less than 52 issues. At any rate, each date has only one issue number.

The value lists can have all the dates, all the volume numbers and all the issue numbers. No one would have to enter in this information, it would be in pop-up lists.

Here is a short table:

Date Volume Issue

12/24/96 56 48

01/01/97 57 01

01/08/97 57 02

Hope this is enough information to take a stab.

Tom

[CobaltSky]

Hi Tom,

Based on what you've said, the volume should be fairly easy to generate, as you could simply set it up as a calculation with a formula along the lines of:

Year(Date) - 1940

However I gather that there is no direct relationship between week numbers and Issue numbers because of sporadic reversion to bi-weekly format. If this were predictable (ie the same periods of bi-weekly issue each year) then it could be coded into a calculation - but again, you've given no details from which one could develop such a formula.

Assuming that there is no recurring pattern to the frequency of issue, it appears that you will require a reference table which lists all the issues and their corresponding dates. Such a table would need to be placed in a FileMaker file which can be accessed (via relationship) from the file in which the data entry occurs.

That being the case, the simplest method to automate data entry would be to establish an auto-entry by lookup procedure based on a relationship which matches the date entered with a date field in a related file which lists all the dates and issues.

If you are using this method, it would be a simple matter to set up an automated lookup for both the volume number and the issue number from the same reference table. Moreover you would be best to set up the value list from which users select the date so that it automatically draws its calues from the date field in the lookup file. That way you can be confident that for each date iin the list there will be a corresponding value and issue number in the related reference file.

[Tom T]

I finally solved the problem by making another database that has the date, volume and issue number only. Then I related that database to the main one. This works fine both off and on the web. Thanks for the help.

Tom