ActPresentation.com HOME FAQ FORUM info@ActPresentation.com
Phorum: speed & power.

ActPresentation.com


Add-ons for Sage ACT! CRM

ACT! addons by ActPresentation.com : Abacus Calculated Fields, PicturePlus, Quick Navigation List, Employment History, Quick Search, Presentation for ACT!

GETDATE only once

Posted by: JordanAustin

Date: September 16, 2008 02:19PM

Hello, is there any way to 'GETDATE' but restrict that field from updating its date the next day?

Details:
The Field I'm adding this formula to is called "Recieved On Date" the field is blank until the checkbox "Paystubs Recieved L1" Field is checked. At which point it will give me todays date.

Formula so far:
__

IF [Paystubs Received L1] = '0'
THEN [BlankDateField]
Else GETDATE()
End
___


But the problem is the next day that field will change to the new days date. What I would like is that date to stay at the date that the checkbox was 'checked'

Thanks for any help!

Re: GETDATE only once

Posted by: Admin

Date: September 27, 2008 05:33PM

> Formula so far:
> __
>
> IF [Paystubs Received L1] = '0'
> THEN [BlankDateField]
> Else GETDATE()
> End


Every time you want to put empty value in any field you can use the NULL keyword
So instead of using artificial field [BlankDateField] you can use NULL

IF [Paystubs Received L1] = '0'
THEN NULL
Else GETDATE()
End


Or you can rewrite this formula in the following way (without ELSE clause):

IF [Paystubs Received L1] = '1'
THEN GETDATE()
End


Conditional formula without ELSE clause contains hidden ELSE NULL



Edited 1 time(s). Last edit at 09/27/2008 05:35PM by Admin.

Re: GETDATE only once

Posted by: Admin

Date: September 27, 2008 05:42PM

JordanAustin Wrote:
-------------------------------------------------------
> Hello, is there any way to 'GETDATE' but restrict
> that field from updating its date the next day?
>
> Formula so far:
>
...
>
> But the problem is the next day that field will
> change to the new days date. What I would like is
> that date to stay at the date that the checkbox
> was 'checked'


The trick here is to check the [Recieved On Date] field current value.
Here is the formula :

CASE
WHEN [Paystubs Received L1] = 0 THEN NULL
WHEN [Recieved On Date] is NOT NULL THEN [Recieved On Date]
WHEN [Paystubs Received L1] = 1 THEN getdate()
END


Please notice how to check if current value is not empty using "is NOT NULL" keyword

Re: GETDATE only once

Posted by: Admin

Date: September 27, 2008 05:43PM

The same formula with comments :

CASE
WHEN [Paystubs Received L1] = 0 THEN NULL -- if check box is unchecked we empty [Recieved On Date]
WHEN [Recieved On Date] is NOT NULL THEN [Recieved On Date] -- if [Recieved On Date] is not empty we use the current value
WHEN [Paystubs Received L1] = 1 THEN getdate() -- we can get here only if [Recieved On Date] is empty
END

Re: GETDATE only once

Posted by: Admin

Date: September 27, 2008 05:45PM

One more solution ( using IF statement ) :


IF( [Paystubs Received L1] = 1 AND [Recieved On Date] is NULL ) THEN
getdate()
ELSE
[Recieved On Date]
END

ACT! software CRM addons - best plugins for ACT contact management software ( Act! by Sage software : ACT!2005, ACT!2006, ACT!2007, ACT!2008, Act by Sage Premium for Workgroups ).
Custom addon solutions ( ACT! add ons ) development and CRM (Customer Relationship Management) software training and consulting.