8 Replies Latest reply on Feb 19, 2010 10:50 AM by philmodjunk

    Relationships with Dates

    SCOPe

      Title

      Relationships with Dates

      Post

      Currently I have a table relationship based on DateField1 = DateField2.

       

      • DateField1 is a field that will be constantly changing based on what records I want to see in a portal
      • DateField2 is Auto-Entered when the record is created


      My question is...

       

      I have a portal based on this relationship because I only want to see records for a certain date or range of dates. Seeing records for a specific date is easy, I just set the DateField1 to whatever date I want to see records for. My problem is, what if I want to see records for a range of dates?

      Example:

      02/17/2010...02/19/2010

       

      How would I go about doing that?

        • 1. Re: Relationships with Dates
          philmodjunk
            

          You can use an extra date field and inequality operators to relate to a range of dates.

           

          YourParentTable:: DateStart < YourPortalTable:: DateField2 AND

          YourParentTable:: DateEnd > YourPortaltable:: DateField2

           

          is one such example.

          • 3. Re: Relationships with Dates
            SCOPe
              

            PhilModJunk wrote:

            You can use an extra date field and inequality operators to relate to a range of dates.

             

            YourParentTable:: DateStart < YourPortalTable:: DateField2 AND

            YourParentTable:: DateEnd > YourPortaltable:: DateField2

             

            is one such example.


            Alright this works great on viewing the portal, now I'm having troubles with record creation.

            Problems:

            1. I have a DateField in the Portal Table that is auto entered on creation date, for some reason this is being filled with the EndDate field...wierd?
            2. My Foreign key is no longer being auto created either. 

            Current Relationship:

             

            ParentTable:ID = PortalTable:ID

            ParentTable:StartDate <= PortalTable:DateField2

            ParentTable:EndDate >= PortalTable:DateField2


            • 4. Re: Relationships with Dates
              philmodjunk
                

              Sounds like you are creating a new record by entering data into the portal?

               

              If so, the relationship is using the value in YourParentTable:: DateEnd to overwrite the auto-entered value in YourPortaltable:: DateField2 during record creation via portal.

              • 5. Re: Relationships with Dates
                SCOPe
                   Yes I am using the portal for the creation of records, but I would also like to use this same portal to "view" these records between the date ranges. What is the best way to go about setting this up? Two Portals? I would like to use the same portal to create and view the records as mentioned before, is there a way to do this?
                • 6. Re: Relationships with Dates
                  SCOPe
                    

                  PhilModJunk wrote:

                  If so, the relationship is using the value in YourParentTable:: DateEnd to overwrite the auto-entered value in YourPortaltable:: DateField2 during record creation via portal.


                  Is this a default behavior from FileMaker, can this be changed?


                  • 7. Re: Relationships with Dates
                    SCOPe
                       Also...is there a different way of doing the "view" part of this? Can I use some kind of FIND to view the records?
                    • 8. Re: Relationships with Dates
                      philmodjunk
                        

                      I don't see how. The entire purpose of "Allow creation of records via this relationship" is so that entering data in the related table via the parent record automatically copies over the values from the Parent record's keys so that the relationship between the two records is valid. If this is not the result you want then you have to make other changes to your system. You could add a button that creates your new record, for example.

                       

                      Freeze Window

                      Go To Layout [portaltablelayout]

                      New Record/Request

                      Go To layout [original layout]

                       

                      This will allow the auto-entered date to be entered without the "overwrite" as it does not use the relationship to create the record.