Fields to appear based on entries in 2 other field

[Jim Strickland]

I'm not sure if this is the correct forum or not. This problem seems to fit in several.

We have a database called Spec Sheet. For each job that comes through our company a new record is created. This record gives all of the specifications for the new job. This was fine while we were doing mainly art jobs. Now we are also performing data pulls for customers. I have created a new layout in this file that will contain information specific to data jobs, versus art jobs. Here is my dilemma. Data contracts can have different terms. Some may be one time, some one year, 2 year, or three year. The update schedule could be different as well: One time, anually, semi-anually, quarterly, monthly, semi-monthly, or bi-weekly. I have a term field that is text format. I have an update field that is text format. I need to create a specific number of update dates based on the term and update schedule. For example: If The contract term is 2 years and the update schedule is quarterly, I need the layout to show 8 places where dates for each scheduled update can be displayed. Ideally, if I put in the first date, Filemaker will then, automatically generate the rest of the dates. Due to the fact that some term and update schedules could produce an unwieldy number of dates, I would probably only have the dates generated for quarterly, semi-annual, and annual pulls. Monthly, semi-monthly, or bi-weekly pulls would only show dates for the first pull and last pull. Is the above scenario possible? If so, is it possible to establish by a novice like me if given the necessary script steps? Would it be better to just manually create a set number of fields for update dates and just use the necessary number of fields for the chosen criteria?

[stanley]

Jim:

Are you just going to track the dates, or is there further data for each pull? The reason I ask this is that you might want to create a related table of data pulls. This related table would consist of records containing:

1. The serial number of the related spec sheet record

2. The date of the pull

3. Any info you want to record on that pull

This way you could create as many dates (records) as you want without having an unwieldy number of fields (which would all be useless, I assume, for your art job records.)

-Stanley

[Jim Strickland]

This will be for just tracking dates.

[Fitch]

Even if it's just for dates, a related table is the way to go. That way you could have as many dates as you want, and your options for meaningful reporting on those dates will be much more flexible. Basically you will have a dates table related by job ID, and a script that creates a new record in the dates table + sets the job ID and date. This script will loop, based on a number that you will need to calculate. That's the tricky part. But you'd have to do it anyway, whether you used a separate table or not.

[Jim Strickland]

Thanks.

[comment]

Perhaps this may give you an idea.