GetAsNumber returns fake value

[Agnes Riley]

Has anyone seen this?

 

http://cloud.zerobluetech.com/image/1C421A0p0F47

 

Is my field corrupt? See how due2 returns the proper value. I manually copied the date from due to due2.

[LaRetta]

Hi Agnes, 

 

The second field must be accidentally specified as text.  Then you will get the second incorrect result like this.  If the second field is date field, it should work as expected; at least it does for me.  

 

Of course the first example is correct because dates are numbers so GetAsNumber() returns the correct 735150 number.

[Agnes Riley]

Hi Laretta,

 

True. But my other variables evaluate as number and just not this one. http://cloud.zerobluetech.com/image/1W2o3y3C1P0n

 

Trying to see if my due date is in between the chosen dates for filter and I get the wrong results.

[LaRetta]

The other examples you show are not wrapped with GetAsNumber().  

 

ADDED:  I am still unclear on the problem.  If you wish to do a comparison, then the data types must match.

[Agnes Riley]

Due is wrapped: http://cloud.zerobluetech.com/image/3d140a2v3F1I

 

And so are date_from and date_to: http://cloud.zerobluetech.com/image/121s151U2H0m

[comment]

my other variables evaluate as number

 

They all evaluate as number - but there is a difference between =

 

GetAsNumber ( MyDate )

 

and =

 

GetAsNumber ( GetAsText ( MyDate ) )

 

The former returns the serial number of the date, the latter returns the digits from the textual representation of the date - a rather meaningless value, esp. as your values aren't padded: in your example, "1/11/2013" and "11/1/2013" will both return the same result.

 

 

 

Trying to see if my due date is in between the chosen dates

 

You don't need to translate the dates to numbers for this; you can compare dates directly.

[Agnes Riley]

Don't you think I tried comparing the dates first, Mr. Comment? :-) That didn't work, which is why I started this whole hackery.

[comment]

Don't you think I tried comparing the dates first, Mr. Comment? :-) That didn't work

 

Perhaps it would be more productive to focus on the real issue, Ms. Riley. Why wouldn't something like =

StartDate ≤ MyDate and MyDate ≤ EndDate

work for you - provided, of course, that all three values are of type Date.

[Agnes Riley]

It's fixed. The problem apparently was that I compared Due to Date_From. When I changed the order (due to a friend's suggestion), it started working. All variables are wrapped in GetAsDate now.

 

Thanks for the suggestions. :-)

[comment]

I compared Due to Date_From. When I changed the order (due to a friend's suggestion), it started working.

 

Do you mean that you see a difference between =

Due > Date_From

and =

Date_From < Due

There shouldn't be any - and if there is, then one of the types is incorrect.  Changing the order is just another type of hackery hiding the real problem - and so are the unnecessary type conversions. You should fix the problem at its source (as if you didn't know that ).

[LaRetta]

This doesn't feel quite resolved.  If you reversed the order of the fields and it now works then both fields were probably dates; you never verified although you said you had already compared the 'dates.'

 

But there is another way this can break  ...

 

 See how due2 returns the proper value. 

 

No, due2 returns incorrect value, Agnes.  If you are using the calculation you referenced in the script then it might not be breaking because the field type is text but rather because your calculation setting the variable breaks it.

 

GetAsNumber ( Substitute ( table::Due ; "/" ; "" ) )

 

... inner function evaluates first turning it into useless numbers without front slash and GetAsNumber() is left doing nothing.  Michael explains this very well.  If you perform a comparison on these types of dates it will not work ( see attached script 3 in sample file ).  Also note that some Users type dash or periods which would not substitute out properly. 

 

All variables are wrapped in GetAsDate now. 

 

All variables?  Date to date comparisons DO work in variables.  If you must wrap a single offending field with GetAsDate() such as during a migration that is one thing but otherwise it wastes evaluations. Hopefully the attached shows the other potential break if the fields are all true dates.  Sample is in fp7 and fmp12 formats.  I verified that it works the same on both 11 and 12.  

Type Cast Dates.zip

[David Jondreau]

It's unclear to me what the real problem is, but surely Substitute ( dateField ; "/" ; "" ) is not going to be helpful. For starters, single digit months and days and dates in different years are going to cause problems.