13 Replies Latest reply on Sep 2, 2009 11:49 AM by philmodjunk

    multiple "insert current date" buttons

    firth5

      Title

      multiple "insert current date" buttons

      Post

      I'm building a database where I have about 60 DATE fields.  These will be tracking a design through various approval phases.

       

      I would like each field to be a button where the user simply clicks it and it enters the current date.

       

      Is there a simpler way than either A) setting a separate BUTTON SETUP, where you have to "insert current date" and define the target field, or B) write a script doing the same thing?  

       

      I would hope that there is a way that I could select all the fields and set it one time, so that each field becomes a button that enters the current date in the respective field.

       

      I look forward to your thoughts.  Thank you. 

       

       

        • 1. Re: multiple "insert current date" buttons
          philmodjunk
            

          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.

          • 2. Re: multiple "insert current date" buttons
            firth5
              

            PhilModJunk,

            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.

             

             

            • 3. Re: multiple "insert current date" buttons
              philmodjunk
                

              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.

               

              Relationships:

              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.

              • 4. Re: multiple "insert current date" buttons
                firth5
                  

                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. 

                 

                 

                • 5. Re: multiple "insert current date" buttons
                  comment_1
                     You should clarify an important point: will ALL your designs have exactly the same phases, and will ALL phases have exactly the same steps?
                  • 6. Re: multiple "insert current date" buttons
                    firth5
                      

                    Each design will all have the same phases, yes.  

                    Each phase has the same steps, in theory, but some may be skipped.

                    • 7. Re: multiple "insert current date" buttons
                      comment_1
                        

                      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

                       

                      You'd probably want to fancy up the Else part by adding a "Are you sure?" dialog. You could also use another repeating field - either a global or an unstored calculation - for the text labels next to the dates.


                      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.



                      • 8. Re: multiple "insert current date" buttons
                        philmodjunk
                          

                        "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.

                         

                        Table design:

                        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.

                        • 9. Re: multiple "insert current date" buttons
                          ninja
                            

                          Howdy comment,

                           

                          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.

                          • 10. Re: multiple "insert current date" buttons
                            comment_1
                               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.

                            • 11. Re: multiple "insert current date" buttons
                              philmodjunk
                                

                              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.

                              • 12. Re: multiple "insert current date" buttons
                                firth5
                                  

                                Phil,

                                 

                                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

                                Fields:

                                Design Name (ex. Truck, Car, Bicycle)

                                _DesignIDpk (a serial number auto generated so that each DESIGN NAME has a unique Design ID#)

                                Artist Name

                                Date Assigned 

                                Date Delivered

                                Etc.

                                 

                                Table 2:  DESIGN PHASE

                                Fields: 

                                _DesignIDfk

                                _PhaseIDpk (I'M NOT SURE WHAT THIS IS!!!)** see below:

                                  Phase  (Value List: Rough/revised/clean)

                                 

                                Table 3: DESIGN APPROVALS

                                Fields: 

                                _PhaseIDfk

                                Step (Value List:  to Aaron, from Aaron, to Betty, from Betty, to Charlie, From Charlie) 

                                Date

                                 

                                 

                                Relationships:

                                _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:

                                 

                                    STEP                   DATE

                                <click for            <DATE BUTTON>

                                value list>

                                 

                                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. 

                                 

                                 

                                Thanks,

                                eric 

                                 

                                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.... 

                                 

                                 

                                 

                                • 13. Re: multiple "insert current date" buttons
                                  philmodjunk
                                    

                                  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

                                     Betty            6/17/09

                                     Charlie

                                   

                                  Design: Bicycle

                                  etc.

                                   

                                   

                                  " 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.