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

    Multiple dates in a field



      Multiple dates in a field


      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:








      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,


        • 1. Re: Multiple dates in a field

          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

            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

              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:



              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

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

                • 5. Re: Multiple dates in a field

                  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.
                  • 6. Re: Multiple dates in a field

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


                    Count (PortalTable:: DesignID)

                    • 7. Re: Multiple dates in a field

                      Thanks guys!


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