Populate Field using Calculation

[steigrafx]

Okay, I admit it, I have no idea what I'm doing.

I have a field called "30 Days." The field is pre-populated with the words 30 Days, with the text matching the background so as to be invisible.

In Layout mode, I've conditionally formatted the field to be white letters on a red background if certain conditions are met, i.e., the difference between the current date and the "date assigned" field is greater than 29 but less than 60, and the "Final" field is empty.

Here is my calculation:

Get (CurrentDate) - MJONES::Date Assigned > 29 and Get (CurrentDate) - MJONES::Date Assigned < 60 and MJONES::Final < 0

I know there's got to be a better way. I can't sort on the field since every field contains the same text. How can I change the "30 Day" field to a calculated field using this formula? I just don't know how to tell the formula to insert the words "30 Days" into the field.

Thanks,

Kevin

[comment]

Try =

Let ( 

diff = Get (CurrentDate) - MJONES::Date Assigned

;

Case ( 29 < diff and diff < 60 and IsEmpty ( MJONES::Final ) ; "30 days" )

)

I have a field called "30 Days."

I hope not - because that's not a valid name for a field.

[steigrafx]

Thank you, thank you, thank you.

The calculation worked perfectly, and I've also modified it for my 60 Day field. You're a lifesaver.

I know I was warned when I created the fields that they were not valid, so I'll change their names.

I've only been using FileMaker for more than a month and I learn something new every day. I'm sure I'll be able to "recycle" your calculation for other uses.

Again, thank you.

Kevin

[comment]

I've also modified it for my 60 Day field.

Couldn't you use the same field?

Let ( 

diff = Get (CurrentDate) - MJONES::Date Assigned

;

Case ( 

diff > 60 and IsEmpty ( MJONES::Final ) ; "60 days" ;

diff > 29 and IsEmpty ( MJONES::Final ) ; "30 days" 

) 

)

[steigrafx]

I'm a complete novice at this. The only database experience I have is with an app called Advanced DB-Master for DOS. Needless to say, it's nothing like FileMaker.

Thanks again. I truly appreciate your help with this as I was thrown into this mess at the last minute and have been floundering.

I hope I can count on your if I need more help.

Thanks,

Kevin