Perhaps you should consider a restructuring of your database.
If I read you correctly, you have a "time line" of dates where each date records a milestone in your design/review/approval process. If you could make that timeline a table of records where 1 record = 1 milestone, you'd only need to set up one field in your layout to insert the current date. You can set up fields in that table that identify the sequence and name of each milestone.
You might also find that the Creation Date auto-enter field option will work for you.
I'm sorry, I don't follow you. Could you please elaborate?
Here's a little more information, simplified:
There are 100 designs
Each design has 10 phases
Each phase has 5 steps/approvals needed before it can move to the next phase.
I have chosen to indicate the step by a DATE field. We need all of the dates of each step to remain intact, so that a giant accounting can be shown in a report.
I can only generalize from the information given. Feel free to post back with more detail/questions/feedback.
You will need two or possibly three tables:
Design // one record = 1 Design
Phase // one record = 1 Phase (may or may not be needed)
Steps // one record records 1 review/approval date.
Design :: DesignIDpk = Steps :: DesignIDfk
Design :: DesignIDpk = Phase :: DesignIDfk
Phase :: PhaseIDpk = Steps :: PhaseIDfk
Design the steps table with fields such as:
DesignIDfk (number) //every step from the same Design should have the same value and it should match a serial number field DesignIDpk in your Design table
ApprovalDate (date) //the field we are discussing
Sequence (number) //by sorting on this field you can make sure the each step record is listed in correct order
StepName (text) //Use as a label to identify each step.
PhaseIDfk (number) //allows you to group all steps from the same phase might match a serial ID field, PhaseIDpk in a "Phase" table.
These records can be displayed in a portal, a table or a list view layout sorted on the Sequence Field.
In any of these layouts, you would have a single date field to set up as a button to enter today's date when clicked.
Let me start by saying I've designed a few databases, but nothing since filemaker 5.
And they weren't particularly complicated. I've been reading "Filemaker Pro 10: In Depth" and am catching up....
Thanks for your time, seriously. If I can grasp this, it will help a great deal with so many other aspects. I have several months to develop a huge, multifunctional database, and it seems from your email like I might not be fully grasping the power of tables. Thanks in advance.
Just so I understand where you're coming from, what does "pk" and "fk" stand for in your shorthand?
Let's simplify the example.
5 design records, numbered 1 thru 5
3 phases (rough / revised / clean) per design.
3 steps (Aaron / Betty / Charlie ) per phase, listed as the DATE the individual approved the design.
If it's not too much trouble, could you please elaborate on your previous example. Perhaps indicating the fields for the Phase and Steps table, individually.
You should clarify an important point: will ALL your designs have exactly the same phases, and will ALL phases have exactly the same steps?
Each design will all have the same phases, yes.
Each phase has the same steps, in theory, but some may be skipped.
OK, I am going to day something which is bound to raise a few eyebrows, but here goes anyway:
I believe you could manage by defining a single ApprovalDate field with 9 repetitions (3 phases x 3 steps). Place 9 buttons on the layout, all attached to the same script, with different script parameters ranging from 1 through 9.
The script could look something like:
If [ GetRepetition ( Designs::ApprovalDate ; Get (ScriptParamater) ) ] Set Field [ Designs::ApprovalDate [Get (ScriptParamater)] ; Get (CurrentDate) ] Else Set Field [ Designs::ApprovalDate [Get (ScriptParamater)] ; "" ] End If
Note that this simple method has severe limitations on finding and reporting. You won't be able to find designs that were in stage x on date y, or report how many designs were approved by Charlie in December 2008. If you anticipate such need, you will need a child table of ApprovalDates, with (up to) 9 related records for each design, and show these in a portal on a layout of Designs.
"Let me start by saying I've designed a few databases, but nothing since filemaker 5."
I recently made the "jump" from 5.5 to 10 myself. Most of what you know from 5 still applies though the improvements do give us options that FMP 5 never dreamed of.
pk = Primary key, in Filemaker, this is usually an auto-entered serial number field
fk = foreign key, in Filemaker, you define this as a data field of the same type as the matching key field. Often, these fields have the matching pk value auto-entered by enabling the "allow creation of related records via this relationship option" in the relationship definition.
A Comment indicated, you CAN use repeating fields instead of a related table. In some cases, designing the layout can be a bit easier to do--especially if you want to show a horizontal format to your time line. As Comment also indicated, extracting information from these fields in a report can be much more difficult. You will also have more work to do if your approval/review process changes. I worked with a company to set up a project management system where each step of a new product's design had to go through a series of reviews. I know that many such companies subject this process to periodic review and modification--sometimes in response to action items generated by ISO audits. Thus, that might be a significant factor to keep in mind.
"Design records, numbered 1 thru 5": Sounds like the primary key field for the design table.
Field 1: Approver ID (Name or ID number of "Aaron, Betty, Charlie")
Field 2: Phase (Rough / revised / clean)
Field 3: Approval Date
Field 4: DesignIDfk
Field 5: sequence (number this 1, 2 , 3 ... to show which step should be approved first.
Place these fields in a portal sorted by the Sequence field and you have your timeline of approval/review steps.
Yep, rasied an eyebrow. But it raised for one reason only...this is the first time I've seen a reccomendation to use a repeating field vs. a child table. You've pointed out a downside of doing this (searches and reporting various permutations). If it strikes your fancy, please feel free to expound on which method would be your own preference and why. I'd be interested to hear it.
If not inclined to do the extra typing, thanks for the brain-food just the same.
I have nothing against repeating fields, as long as they can do the required job. I believe in this case they can (with the caveats I have stated), and the implementation can be very simple.
To do this with a child table, you'd actually need TWO child tables: a "template" table containing 9 permanent records, and the "real" child table that contains the approvals. Then you will need to either show the approvals against the template grid, or make sure every new project record is immediately furnished with 9 empty child records.
Good points all.
Nor do I have anything against repeating fields. I think it comes down the evaluating the pros and cons of each option. Repeating fields may well be the best approach for this issue.
To me, it looks like an issue of simplicity vs. flexibility. Implementing repeating fields will be simpler but a bit less flexible. If flexibility is not an issue than repeating fields make a lot of sense.
I think between the two of us, we've laid out two workable options and our original poster can make the final choice.
Great information, thank you. I've been puzzling/playing with it all day, and I know I'm getting closer, but it's still not acting right.
Let me see if I have this right:
Table 1: DESIGN TABLE
Design Name (ex. Truck, Car, Bicycle)
_DesignIDpk (a serial number auto generated so that each DESIGN NAME has a unique Design ID#)
Table 2: DESIGN PHASE
_PhaseIDpk (I'M NOT SURE WHAT THIS IS!!!)** see below:
Phase (Value List: Rough/revised/clean)
Table 3: DESIGN APPROVALS
Step (Value List: to Aaron, from Aaron, to Betty, from Betty, to Charlie, From Charlie)
_DesignIDpk :: _DesignIDfk
_PhaseIDpk :: _PhaseIDfk
** phaseIDpk -- I'm not sure if this again is a unique serial number, or is somehow tied into the phase name, where Rough = 1, Revised = 2, and Clean = 3.
I'm looking to do the following:
On a DESIGN BREAKDOWN LIST layout it will show all the designs in an X/Y list:
DESIGN ARTIST ASSIGNED DELIVERED
Truck Ralph 10/1 10/30
Car Sue 10/3 10/4
Bicycle James 10/20 10/25
Underneath each Design Row would be a series of tabs for each phase: Rough / Revised / Clean
Inside each Tab would be the portal to the Step and Date
Thus, in the ROUGH Tab, you'd see:
<click for <DATE BUTTON>
You'd click (for example) "To Aaron" in the STEP field and the DATE BUTTON would insert the current date.
This is great for data entry, but in the end a report would need to be generated showing all the dates:
DESIGN NAME R_ToAr R_FrAr R_ToBe R_FrBe R_ToCh R_FrCh C_ToAr C_FrAr C_ToBe C_FrBe ETC
(R = Rough, C = Clean)
I really appreciate any thoughts or advice.
(please remember that this is a simplified example. In reality there are 5 phases, with 10 steps each, plus I need to track notes, revisions, etc.)
I will also need to cross-reference so that we can see how many designs are at what phase, how many are at each step, etc.
I wonder if it would not be best to simply make a linear chart, all in one table, with a separate field for each phase+step.
ps. it might be faster to email you my working database, and ask to adjust it. If you're willing, of course. It might be faster than this forum....
Wow, there are multiple issues here. Let's see what I can do here.
If you check back on my earlier posts, I suggested not having a phase table. That isn't strictly necessary and may needlessly complicate your database structure. It can be implemented that way, but I don't think we need to.
If you do choose to use this table, Phase :: _PhaseIDpk would be an auto-generated serial number and would be used to link a specific phase to a group of approval records. In other words, you could use it to access the approval records (Aaron, Betty & Charlie) for the Rough Phase for the Truck design.
There are two basic design issues that You need to think about and make a decision on. I won't try to say which options are best. Instead, I'll try to layout the pros and cons.
Item 1: Filemaker can be a challenge to use to set up a "cross tab" type report where data from different records is displayed in a series of columns. If you can structure your reports and data entry screens to work in vertical lists where 1 row = 1 record life is much simpler. Using a series of dedicated fields or a repeating field as Comment described, can make organizing this data into columns easier. (Note: It is possible to set up such a cross tab report in Filemaker, it just takes considerable effort to implement by adding relationships, calculation fields and possibly a special "pointer" table used to manage the relational links.)
Item 2: While using a series of fields (or repetitions) can make displaying your data in this type of format easier for reporting and data-entry. Summary reports such as "...so that we can see how many designs are at what phase, how many are at each step, etc." Will require a great deal of work to pull off. In fact, you'd probably have to copy the data into a different table just to make it happen.
You database design will be much simpler if you can work with layouts that look like this:
Design: Truck Artist: Ralph
Rough Phase Approvals:
Approver Received Released
Aaron 5/1/09 5/10/09
Betty 5/11/09 5/13/09
Charlie 5/14/09 5/31/09
Clean Phase Approvals:
Approver Received Released
Aaron 6/3/09 6/15/09
" it might be faster to email you my working database, and ask to adjust it. If you're willing, of course. It might be faster than this forum.... "
Yeah, it would, but I'm up to my ears in work from my employer and paying clients and I don't have the time at this moment.