Filter portal records - date

[jimlongo]

Hi, I have a portal displaying transactions. I want to filter the transactions so that I'm only looking at the current year.

the records I'm displaying in the portal have a DATE field (the field is imported from a mysql db as mm/dd/yy hh:mm:ss) and the information in my FM db is formatted as mm/dd/yyyy

I'm filtering using the calculation

If ( Transactions:_transDate >"01/01/2012" ; 1 ; 0 )

However this is not filtering out transactions from 2011

What am I doing wrong?

Thanks,

jim

[comment]

What am I doing wrong?

"01/01/2012" is a text string, not a date. Try =

Year ( Transactions:_transDate ) > 2011

---

BTW, you say the field is a Date field and that the imported data is in mm/dd/yy hh:mm:ss format. That's not a valid format for a Date field in Filemaker, and you might have a problem there.

Edited February 2, 2012 by comment

[jimlongo]

Thanks, your solution works.

===

I thought that text imported into a field defined as Date would make it so. How would you import that data to make it a Date?

OR is there a better way to import this data?

I have another field date_of_birth that is defined as Date (mm/dd/yyyy) and imported from mysql data in the form mm/dd/yyyy - in that case it is a Date?

[comment]

I thought that text imported into a field defined as Date would make it so.

Yes, if your file is set to use m/d/y as the date entry format, and your text file has dates in the same format. But you have described a format that includes a time element. This would be best imported into a Timestamp field. If the target field is of type Date, the time element will be discarded - or (if Filemaker is unable to make the conversion) the imported data will not be recognized as date.

[jimlongo]

I see, so my date_of_birth is being used correctly?

I have another field date_of_birth that is defined as Date (mm/dd/yyyy) and imported from mysql data in the form mm/dd/yyyy - in that case it is a Date?

Last question if I import this transaction_date as a timestamp, does it allow for searching or filtering based just on the m/d/y part of the timestamp or must I always include both the m/d/y and h:m:s portion of the timestamp whenever I want to use it? (the Help docs do not cover this very extensively)

Thanks again.

Edited February 2, 2012 by jimlongo

[comment]

When searching, you can leave out the time component, or use a * wildcard.