Jump to content
View in the app

A better way to browse. Learn more.
FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Date to Status

partsgb.martinw
in Calculation Engine (Define Fields)

Featured Replies

Hello All,

I am trying to do the following. I have two date fields. First date field is ( Faxed Date ) secord field is called ( Reminder Fax date ).

The other field is called Status.

What i am trying to do is the following. From the date the first fax was sent + 60 days = status ( "Lapsed" ). From Reminder fax date + 21 days = ( "Expired " ) Both lapsed and expired will appear in my status field which is text.

Here is my [ CALC ] but it does not work. What am i doing wrong.

Case( IsEmpty( Fax Date to dealers ), "",Case( IsEmpty(Fax Date of Reminder ),"",

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed", ""))

However i want to link expired and lapsed into another field called status and not the calculation field..

Hope this makes sense..

Regards,

Martin

quote:
Originally posted by partsgb.martinw:

Hello All,

I am trying to do the following. I have two date fields. First date field is ( Faxed Date ) secord field is called ( Reminder Fax date ).

The other field is called Status.

What i am trying to do is the following. From the date the first fax was sent + 60 days = status ( "Lapsed" ). From Reminder fax date + 21 days = ( "Expired " ) Both lapsed and expired will appear in my status field which is text.

Here is my [ CALC ] but it does not work. What am i doing wrong.

Case( IsEmpty( Fax Date to dealers ), "",Case( IsEmpty(Fax Date of Reminder ),"",

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed", ""))

However i want to link expired and lapsed into another field called status and not the calculation field..

I would break the thing into 2 seperate statements, since your existing case can have multiple winning combinations.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

)

Case(

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

""

)

They can be split into multiple fields or used in the same fields and joined with an "&".

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

  • Author

quote:
Originally posted by captkurt:

I would break the thing into 2 seperate statements, since your existing case can have multiple winning combinations.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

)

Case(

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

""

)

They can be split into multiple fields or used in the same fields and joined with an "&".

Hi Kurt,

Tried what you have wrote above but it doesn't work. see script below.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

)

Case(

Case(Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED" & Status,

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed" & Status,

""

))

It does not change my status field to Expired or Lapsed.

Do you have another idea what i can try..

Regards, Martin

quote:
Originally posted by partsgb.martinw:

Hi Kurt,

Tried what you have wrote above but it doesn't work. see script below.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

)

Case(

Case(Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED" & Status,

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed" & Status,

""

))

It does not change my status field to Expired or Lapsed.

I was not making one statement. I was splitting your convoluted CASE into two parts. Those would go in seperate fields, or ifs or whatever.

My point was breaking them up is what you need to do.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

quote:
Originally posted by JerrySalem:

Have you tried just a single case statment? I don't think you need a nested case at all.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

""

)

This was the original problem. There are 2 completely different set of criteria in this and it will not work as you intend.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

Have you tried just a single case statment? I don't think you need a nested case at all.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

""

)

Try this.

Jerry

  • Author

quote:
Originally posted by captkurt:

This was the original problem. There are 2 completely different set of criteria in this and it will not work as you intend.

Hello Kurt,

OK, it seems that i cannot do this as a define field. What about putting this into scriptmaker and get this to run on a trigger via a button. I have a refresh monitor button that is used. Any ideas on the sort of script that i need to use. I feel that "If" statement will be needed and "Setfield" What do you think :?:??

Many Thanks,

Martin

[This message has been edited by partsgb.martinw (edited November 22, 2000).]

quote:
Originally posted by partsgb.martinw:

Hello Kurt,

OK, it seems that i cannot do this as a define field.

OK, maybe I wasn't clear. This can be done. It is 2 difference criteria and need to be done in 2 statements. Either within 1 or 2 fields or script statements.

Here is how I would do this:

Case(

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",""

)

This way the field is either evaluated with one of the two text answers or it is blank. You also have to be careful how you structure the case, as it will stop evaluating after the first matching item.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

Create an account or sign in to comment

https://fmforums.com/topic/2910-date-to-status/
Go to topic listing

Important Information

By using this site, you agree to our Terms of Use.

I accept

Configure browser push notifications
Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.