3 Replies Latest reply on Dec 24, 2010 8:07 AM by philmodjunk

    Working with dates

    Jon1009

      Title

      Working with dates

      Post

      Hello All,

      I have a database for logging samples in. I have the following tables structer

      Jobsheet                     Samples                      Sites                        Classification

      JSID ----------[ = ]--------JSID                             SID ----------[ = ]--------SID

                                           SID ----------[ = ]--------SID

      DateTime                     Result                                                          Start Date

                                           Out                                                            Finish Date

                                                                                                             Result

      What Iam trying to do is each sample is related to a site and though out the year each site result changes depending on the month

      for example site 1         from (StartDate) 01/01/2011 to (FinishDate) 01/04/2011 (Result) = B

                                           from (StartDate) 02/04/2011 to (FinishDate) 01/12/2011 (Result) = C

      I have these dates listed in the Classification table.

      I would like to somehow check the result in Sample Tables is correct for the time of year via the date of test in the Jobsheet table and change the Out field to alert me of this

      What would be the best way to achive this?

      Cheers

      Jon

      FM11pa

        • 1. Re: Working with dates
          philmodjunk

          Not sure why your Start and Finish dates are in the Classification table. I'm going to assume there's  good reason for this.

          You'll need to construct a different set of relationships to match the date in JobSheet to the dates in Classification first, then link to the Samples table.

          JobSheet----<Classification-----<Sites----<Samples   ( ---< means one to many )

          JobSheet::cDate > Classification::StartDate AND
          JobSheet::cDate < Classification::EndDate

          cDate has to be a field of type Date. Since DateTime appears to be a timestamp field, a calculation, GetasDate ( DateTime) can be defined for cDate. Then a reference from a JobSheet Record to Samples will only refer to Samples that were taken in the given date range.

          That's one possibility, You can use a Filtered Portal to Samples to only show sample records with a matching JSID. This requires FileMaker 11. If you don't have 11, a slightly different approach will be needed.

          • 2. Re: Working with dates
            Jon1009

            Thanks again Phil,

            The reason I have the Classification if a sepreate table is that they might be up to 10 diffrent classifictation for the same site thoughout the year so I have a portal on the Sites layout for adding how ever many is required. That would be the best way to do this?

            I tried the method you mentioned but I couldnt get the results I was after.

            However I managed to do what I was after by using some of your solution:-

            Creating cDate in the samples tables and adding a Classification TO Related via

            Sample::SIN=ClassificationTO::SIN AND

            Sample::cTestDate>Classification::StartDate AND

            Sample::cTestDate<Claddification::FinishDate

            Happy Holidays, :)

            Jon

            • 3. Re: Working with dates
              philmodjunk

              Sounds like you came up with a better solution. Glad my idea sparked yours.