Jump to content
  • 0

How to create a Formula based on another formula


Woolf
 Share

Question

I need to be able to calculate a date, created by a formula, which is prior to whatever the current date is. That sounds easy but I cannot figure out how to do it in my specific situation.

Because I needed to determine a date that was 2 weeks after a date entered based on two different scenarios I created this formula in my data table:

CASE

     WHEN [@field:Brand_Received] = 'Brand 1' THEN Dateadd(day, 14, [@field:Date_1])

     WHEN [@field:Brand_Received] = 'Brand 2' THEN Dateadd(day, 14, [@field:Date_2])

END

It works well to give me the date that is two weeks after the specific date given, based on unique requirements.

However, now I need to create a field that checks to see if the new Formula Date is before whatever the current date is. I have not been able to get a result for this. Caspio does not allow me to use results from one formula field as variable in another formula field. And I have not been able to figure out just yet how to combine all the variables I need to produce a valid formula.

Any thoughts?

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0
On 10/16/2021 at 10:18 AM, NailDyanC said:

Hi @Woolf, just to clarify, you would like to get 14 days before the current date? If yes, then you may use this formula:

DATEADD(Day,-14,SysDateTime())

I use SysDateTime() function to get the current date.  However, SysDateTime() only works on DataPage level.

Depending on which version of a product a person uses, I am trying to get a new date that is 14 days AFTER the original date. This I already figured out how to do.

CASE

     WHEN [@field:Brand_Received] = 'Brand 1' THEN Dateadd(day, 14, [@field:Date_1])

     WHEN [@field:Brand_Received] = 'Brand 2' THEN Dateadd(day, 14, [@field:Date_2])

END

But then I need to determine if the new date (14 days after the original date) is BEFORE whatever the current date is (which would change depending on when the user accessed the report).

Link to comment
Share on other sites

  • 0

Hi @Woolf,

I created a sample workflow that would check if the new date is before or after the current date. Here's my formula(You will be using two calculated fields):

CASE WHEN

[@calcfield:1] >= (SysDateTime())

THEN
'AFTER'
ELSE
'BEFORE'
END

The "[@calcfield:1] " is the first formula that you have that would determine the new date. Please let me know if that works for you.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...