Instead of repeating fields, try using a filtered, horizontal portal approach.
Define a related table of stages linked to your Job table by a Job ID field.
For each column of stage information place a one row portal. Each column uses a copy of the same portal but each is set up to display a different row of that portal.
Then use portal filtering to limit the displayed stage records to the desired date range. In your example, the filter would limit your stage records to those dated from June 15th to June 25th.
(Portals can be filtered in versions earlier than filemaker 11--it just takes a bit more work as you have to set up the filter in the portal's relationship instead of it's portal setup.)
Hmmm, interesting thoughts...
The one thing I don't get with this method is how to populate the portals. For example, the information I currently have saved in the database is the Project end date and how many days each stage will take. How would I use this/translate it to fill all of the portals?
I wouldn't use this layout for data-entry. I'd create a second layout based on your job table for creating the stage records.
Each stage record will need: Date, stage, and Job ID
From a "job" layout, you could have three global fields to specify stage, date start, date end.
Enter or select values in those three fields and you can write a script that creates the stage records for that Job, stage and date range.
Try the following: make your job records to calculate strings of different length and color for each phase:
Drafting Bar =
TextColor( Repeat( "█", Drafting Duration ), RGB( 255, 0, 0 ) )
Here Repeat() is a custom function like
Repeat( string, number of times ) =
Case( 0 < number of times; string )
& Repeat( string, number of times - 1 )
(This is not the best Repeat(), but it will do the job.)
To get a multi-color bar you concatenate the bars. The job now is to position. The position is determined by the job's start date (unique for each job) and the start of the displayed range (a global field). If the difference is positive, you need to prepend several blocks of background color; if it's negative, you need to lose some blocks from the beginning.
Hope it's clear; if it isn't, I'll try to make a sample.
Why not use repeating fields?
The advantage of repeating fields is you just have to size one box and the rest nicely line up beside them.
For example, to create your date headings just create a repeating global field with the calculation:
Get(CurrentDate) + Get(CalculationRepetitionNumber) -1
Boom, you're done.
For your line items, use a repeating calculation field matching schedule dates with the formula shown above. Then apply conditional formatting. If you want to have date ranges, include the start date in the above formula and use conditional formatting to make any days after the end date invisible.
With a horizontal portal, you can use conditional formatting to specify field fill colors to get the color effects as requested.
How about using the charting feature of FM11?
That's a truly creative idea, but can you get a horizontal bar that is divided up into three different color sections?
If so, then it might be worth a try.
I can in Excel/OpenOffice, :smileywink:
The horizontal portal solution is definitely the most flexible. The killer is copy and pasting 90 or so of them in a row, and then setting the portal row number for each one. I guess things could be worse...
Why do you need 90? By your example post with a 15 day chart, you should only need 15.
PS. when setting up your related portal records, you may need some blank records with dates that precede the first "Drafting stage" record so that the first such record will appear in the correct column under all possible date ranges specified for the chart. Otherwise, if that first record is dated Jan 12 and you set your portal filter to show records starting with Jan 1, this record will appear in the first column instead of the 12th column.
Because management wants me to go out as far into the future as possible. Combining that with a requirement of easily readable text, FileMaker's maximum layout width constrains me to about 3 months. Thanks for the tip about the empty fields.