3 Replies Latest reply on Aug 5, 2010 7:04 AM by LaRetta_1

    Portal Dates

    mgxdigital

      Title

      Portal Dates

      Post

      Hi,

      I have a portal showing records based on date fields.

      I have 6 portals all using the same type of relationship and one of them is not working correctly.

      I have a start date and end date field that i am relating to an order date field.

      My relationship is:

      start date is less than or equal to order date

      AND end date is greater than or equal to the order date.

      This will show in the portal as all the records ordered within the 2 date fields entered.

      One of my portals for some reason is not reading the year of my dates.

      If i entere in 1/1/10 as start date and 1/30/10 as end date, my portal shows all records ordered in Jan 2010, Jan 2009, Jan 2008...and so on.

      This is the only portal doing this and i have no idea why. I've checked that all the date fields are showing the same format of date.

      Any help?

      Thanks!!

        • 1. Re: Portal Dates
          philmodjunk

          I'd check to see if your relationship is linking the correct fields in your relationship graph since you've already verified the type of the fields.

          • 2. Re: Portal Dates
            LaRetta_1

            Your order 'date' field is set as a number.  I figure you might not believe it, so here is a file which shows it:

            http://www.4shared.com/file/K506-BD6/testdate.html

            5/5/2010 would also appear in the portal, along with 3/7/2010, 4/4/2008 and many more.   Strange that some will NOT appear, such as 6/17/2009 and 2/15/2010.  I assumed it is because FM is handling 5/5/2010 as: 5 divided by 5 divided by 2010 but even those numbers should relate (6/17/2009 is .0001756800281088) and 5/5/2010 is .0004975124378109.

            I find it inconsistent but that only means that I haven't spotted the pattern (yet).

            • 3. Re: Portal Dates
              LaRetta_1

              Ah.  Strange as it sounds, FM tries to create the relationship by converting true dates on the parent side to a number (and NOT FMs standard number of number of days).  It does this:  GetAsNumber ( GetAsText ( startDate ) )

              Anyway, it's not that easy to explain but easy to view so I'm uploading another file if anyone is interested.  But it explains why it is important not to mix data types.  However, I am VERY surprised that FM converts a true date on the parent side to attempt to match to the child (if this is indeed what is happening).  Although the theory holds in all dates I've tried so far, I could be wrong.

              File here:  

              http://www.4shared.com/file/fn4kfgbC/testdate2.html

               Hmmm, I wonder if we can take this strange behavior and actually USE it. :^)

              As an aside ... if you were entering your dates as 5/5/10 (but it was into a number field), FM couldn't convert to true date of 2010.  In this case, 5/5/10 wouldn't appear to relate because it was 5510 on the child side instead of 552010.