7 Replies Latest reply on Feb 22, 2010 5:03 PM by firth5

    Multiple dates in a field

    firth5

      Title

      Multiple dates in a field

      Post

      I've designed a Design database, where each record is one design.  Each record has a series of approval date fields, which show a timeline going through the various approval stages.

       

      For example, for each design we have the following dates:

       

      Assigned

      Submitted

      Reviewed 

      Approved

       

       

      Right now, I have one field for each approval stage, above.

      I would like to be able to enter multiple dates in the "Reviewed" field, which would therefore track the number of revisions that design had to go through.

       

      What's the best way to do this?  Child table?  If so, please give me a jumping off point.  I have yet to use related tables in any significant way.

       

      I'd like to have a calculation field that is tabulating the number of revisions a design has gone through at all times.  

       

      Thanks in advance,

      eric 

        • 1. Re: Multiple dates in a field
          philmodjunk
            

          A child table is definitely the way to go here. A good starting point is to look up the term "portal" in filemaker help.

           

          If I were you, I'd define at least three fields in this related table:

          DesignID (match to serial number field in your parent table)

          Type (text, enter values like "reviewed", "approved" etc.)

          ReviewDate (Date field)

           

          You can add more fields to this table as you need them such as the ID of the reviewer.

           

          A calculation field like this: count ( ReviewDates:: DesignID ) will count all the related records and this might give you the number of revisions you need. If you only want to count some of the entries, that can be done as well, but requires an additional, filtered relationship.

          • 2. Re: Multiple dates in a field
            ninja
              

            IMO you should use a child table with one record per Rev level.

             

            These records could be shown through a portal and thus let you see all reviews at a glance.

             

            A rejection for modification would create a new Rev level and thus a new Assignment.

            • 3. Re: Multiple dates in a field
              firth5
                

              Ok.  Child table.   So far so good.  I have it programmed, and it's working great.  My first successful use of a portal too!

               

              Not quite getting the COUNT function though (and not sure if that's the function I want.)

               

              In the child table I have the following fields:

               

              DesignID_fk

              ts_Review  (timestamp showing the time it was reviewed)

              Review_Result  ("Approved" or "Rejected") 

               

               

              I want to add a "#_Revisions" calculation field which will total the number of records per DesignID_fk.

              What function should I use?

               

              Thanks again! 

              • 4. Re: Multiple dates in a field
                philmodjunk
                  

                Yes. That plus the match fields that link your two tables.

                • 5. Re: Multiple dates in a field
                  firth5
                    

                  I think I found what I'm looking for -- a SUMMARY field, set to "Count" the number of timestamp records.

                   
                  Problem is it doesn't seem to refresh in any discernable pattern.  I have the field outside of the portal.  Sometimes it shows the count, other times the field is blank.  If I click in the field, it will  display.  
                   
                  I tried moving the field inside the portal, and it works great.  But I don't want the Count number to repeat on every portal row... it's ugly.
                   
                  Thoughts? 
                  • 6. Re: Multiple dates in a field
                    philmodjunk
                      

                    Use the count function in a calculation defined in your Designs table.

                     

                    Count (PortalTable:: DesignID)

                    • 7. Re: Multiple dates in a field
                      firth5
                        

                      Thanks guys!

                       

                      (I had the calculation count field in the wrong table the first time I tried it.  Everything is working perfectly!!!)

                       

                      eric