5 replies to this topic

[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]

jimlongo, on 02 February 2012 - 10:21 AM, said:

What am I doing wrong?

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

Year ( Transactions::D_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 by comment, 02 February 2012 - 10:37 AM.

[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]

Quote

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?

Quote

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 by jimlongo, 02 February 2012 - 02:40 PM.

[comment]

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