1 2 Previous Next 15 Replies Latest reply on Mar 26, 2009 4:18 PM by philmodjunk

need calculation field to return a text value if field value is < current date

Title

need calculation field to return a text value if field value is < current date

Post

If I define a field as calculation type, how do I have the calculation return a text value if the date (MON YEAR) in the field is < the current date?

• 1. Re: need calculation field to return a text value if field value is < current date

Lenny20135 wrote:
If I define a field as calculation type, how do I have the calculation return a text value if the date (MON YEAR) in the field is < the current date?

If(year(datefield) + month(datefield)/100 < year(get(currentdate)) + month(get(currentdate))/100,"Text if date < current date","Text if it is not" )

Make sure your calculation field is set to return "text".

This calculation will give you numbers like:

2008.01 for January dates in 2008

and

2007.12 for December dates in 2007.

There are other approaches, but this one should do the trick.

• 2. Re: need calculation field to return a text value if field value is < current date

Phil, thanks so much for the fast reply!  If you wouldn't mind, I need a little fine tuning.

The field will contain month and year in this format:  JAN 2009

If the month and year in the field is less than or equal to the current date, I want the calculation to replace the date in the field with the text, "NOW!".  If the month and year in the field is greater than the current date, I want the data in the field to remain unchanged.  Sorry I didn't provide this detail in my first post.  Also, what is the significance of the /100 in your formula?

Thanks, again.

• 3. Re: need calculation field to return a text value if field value is < current date

Lenny20135 wrote:

The field will contain month and year in this format:  JAN 2009

Hi Lenny

is it a text field or a date field formatted to show the date in that way ?

• 4. Re: need calculation field to return a text value if field value is < current date

You cannot have the same field hold BOTH data AND the result of a calculation performed on the data. In addition, any calculation that requires the current date must be unstored, or it will not update when the date changes.

You need to use two fields, or a field to hold the "date" and a text object with conditional formatting.

• 5. Re: need calculation field to return a text value if field value is < current date

Lenny20135 wrote:

If you wouldn't mind, I need a little fine tuning.

The field will contain month and year in this format:  JAN 2009

If the month and year in the field is less than or equal to the current date, I want the calculation to replace the date in the field with the text, "NOW!".  If the month and year in the field is greater than the current date, I want the data in the field to remain unchanged.  Sorry I didn't provide this detail in my first post.  Also, what is the significance of the /100 in your formula?

First a little under the hood explanation. Date fields in filemaker are really number fields with special formatting. You can actually use them as number fields in many expressions. Each date really stores an integer that represents the number of days from 0/0/0000 to the date displayed in the field. My expression extracts the year and month number portions of the date and combines them as a decimal number so we can compare year and month while ignoring the day portion of the date.

Year(1/20/2009) will return the integer 2009. Month(1/20/2009)/100 returns the decimal value 0.01. Adding these two produces the number 2009.01. This works for what you need since 2009.01 (Jan 2009) will be greater than 2008.01 (Jan 2008) and 2009.02 (Feb 2009) will be greater than 2009.01 (Jan 2009). Make sense?

Comment's post is correct. But you can set things up like this:

If(year(datefield) + month(datefield)/100 < year(get(currentdate)) + month(get(currentdate))/100,"NOW!","" ) and choose the unstored option for the calculation's storage options.

Now use conditional formatting to hide your date field if this field is not blank. You can hide a field by changing its text color to match the color of your layout.

• 6. Re: need calculation field to return a text value if field value is < current date
Hey thanks, Daniele, Comment & Phil.  I tried Phil's formula, substituting "datefield" with the name of the field.  The formula just returned a question mark in that field for every record.
• 7. Re: need calculation field to return a text value if field value is < current date

I would also add one of my own: if the "date" consists of month and year only, how exactly do you want to compare it to current date? If today is  Mar 26, is already "after" MAR 2009, or does that happen only on April 1?

• 8. Re: need calculation field to return a text value if field value is < current date
Sorry, thought it had to be a calculation field, so that's what it is.  Yes, anything < MAR 2009 would have to be last day of FEB 2009 or earlier.
• 9. Re: need calculation field to return a text value if field value is < current date

Lenny20135 wrote:
Hey thanks, Daniele, Comment & Phil.  I tried Phil's formula, substituting "datefield" with the name of the field.  The formula just returned a question mark in that field for every record.

Make sure that your calc returns text, not date as your return type.

• 10. Re: need calculation field to return a text value if field value is < current date

Lenny20135 wrote:
Sorry, thought it had to be a calculation field, so that's what it is.  Yes, anything < MAR 2009 would have to be last day of FEB 2009 or earlier.

No, it cannot be a calculation field, as I explained earlier. It must be either a Text field, or (preferably, IMHO) a Date field formatted to show only the month and the year.

• 11. Re: need calculation field to return a text value if field value is < current date
My suggested expression assumes that date field is of type date. If it is any other animal, it won't work.
• 12. Re: need calculation field to return a text value if field value is < current date
Phil, if it's a date field, will it still be able to return a non-date result? (NOW!)  Or does it need to be a text field?
• 13. Re: need calculation field to return a text value if field value is < current date

Lenny20135 wrote:
Phil, if it's a date field, will it still be able to return a non-date result? (NOW!)  Or does it need to be a text field?

We're getting our apples mixed up with our oranges.

I'm describing two fields

DateField--define as field of type date

MessageField--define as calculation field returning text ("NOW!" or "" depending on value of DateField).

With some clever layout tricks, you can make it look like this is one field to the user, but in reality, we're talking input(DateField) and output(calcField).

• 14. Re: need calculation field to return a text value if field value is < current date

OK, I now think I understand.  So, the basic answer to my question is: It's not possible to replace the contents of this field with text if the value of the field (Month and Year) is less than the current date, regardless of the field type.  That's what I wanted to do.  Sorry I don't have sufficient understanding yet to have asked this question more accurately.  I'm a new convert from MS Access which I've used for many years and have become proficient at that.  All my business data is stored in Access, so I'm trying to convert everything over to Filemaker as I'm switching my office over to Mac. Any other general tips in making the conversion?

The reason for my question is that every quarter I have to go through 4-500 accounts and manually update three fields in each record with expired due dates to "Now".  I was hoping to automate this so as to save time, and not have to go through manually each time. Is my conclusion correct?  Even though I might be able to tweak the layout to make it "appear" as though it's only one field, when I go to print it, I'll need to print both fields to get the appropriate data for each account.

1 2 Previous Next