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!

How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 15, 2007 01:12PM

Hello,
I have a field that is a countdown field. I am looking for a way to have that countdown stop counting down if another trigger field gets filled in.

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 16, 2007 07:43AM

Jordan,
please post here your current formula for coundown field

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 16, 2007 12:09PM

Here is the formula I am using for my countdown. What I would like to do is when I fill in the date on another field this formula will stop counting down.

DATEDIFF
( day, getdate(), [Contract Aplication] )



Edited 1 time(s). Last edit at 05/17/2007 03:15AM by Admin.

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 16, 2007 03:33PM

Here is formula example :

IF( [another field] == null ) THEN
    DATEDIFF( day, getdate(), [Contract Aplication]
)
ELSE

    [countdown field]
END


Comments : while [another field] is empty - calculate countdown , else - use existing value



Edited 2 time(s). Last edit at 05/17/2007 03:11AM by Admin.

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 16, 2007 04:08PM

Ok I tried to use 2 eual signs == but Abacus came up with an error so I have my formula as follows, However the datediff portion is not working. Any ideas as to why?


IF
( [Contract Application Complete date] = null )
THEN
    DATEDIFF( day, getdate(),[Contract Aplication])
ELSE
    [Contract Application Complete date]

END



Edited 1 time(s). Last edit at 05/17/2007 03:13AM by Admin.

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 17, 2007 03:09AM

JordanAustin Wrote:
-------------------------------------------------------
> Ok I tried to use 2 eual signs == but Abacus came
> up with an error so I have my formula as follows,
> However the datediff portion is not working. Any
> ideas as to why?
>
>
> IF ( [Contract Application Complete date] = null )
> THEN DATEDIFF( day, getdate(),[Contract Aplication])
> ELSE [Contract Application Complete date]
> END

I'm sorry for incorrect syntax - of course you should use 1 equal sign in formula.
As for DATEDIFF - try to change condition as follows :


IF
( [Contract Application Complete date] = null OR [Contract Application Complete date] = "" ) THEN
    DATEDIFF( day, getdate(),[Contract Aplication]
)

ELSE

    [Contract Application Complete date]


END

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 17, 2007 12:16PM

Thank you for your reply, however I am unsure why but the formula is still not calculating the DATEDIFF portion. When I use only the datefiff formula then the countdown works. However when I use the IF THAN and ELSE formula the field will not countdown.

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 17, 2007 12:59PM

What type has [Contract Application Complete date] field ?

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 17, 2007 01:01PM

its a date field.
When the contract Application Complete date is filled that portion works. Its just the DATEDiff portion that doesn't work.

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 18, 2007 03:12AM

Jordan,
lets debug your formula on ACT! demo database.
Could you please compose analogous formula ( with same field types )in ACT! demo database and post it here - in this case I will have a chance to test your formula in ACT! and fix the problem.

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 18, 2007 10:35AM

ok have created the same type fields in ACT! demo. my fields are: datefield1 , datefield2 and results ( this results field obviously is where the formula will be placed also it is a Character type field)

the formula I have in the results field is:

IF ([datefield2] = null OR [datefield2]= "" )
THEN DATEDIFF( day, getdate(),[datefield1])
ELSE [datefield2]
END

This formula is doing the same as in my real ACT! database

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 20, 2007 07:24AM

Jordan,
I'm sorry I was wrong. When you compare field with NULL value you should not use equal sign.
When you want to test field is empty or not you shoul use "is NULL" keyword.
So condition will be IF( [some_field] is NULL OR [some_field] == "" )

Your formula is as following :

IF
([datefield2] is NULL OR [datefield2] = "" )
THEN DATEDIFF( day, getdate(),[datefield1]
)
ELSE
[Result]
END


Comments : if [datefield2] is empty - calculate countdown using DATEDIFF function, else ([datefield2] is not empty ) - use existing value



Edited 1 time(s). Last edit at 05/20/2007 07:26AM by Admin.

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 21, 2007 12:25PM

Ok I did as told and eliminated the "=" and replaced it with "is" as you can see in my formula below. However now the datediff portion shows up as "1/8/1900 1". Maybe I have an incorect field type? The field type is set to character, is this correct?

IF([Contract Application Complete date] is NULL OR [Contract Application Complete date] = "")
THEN DATEDIFF( day, getdate( ),[Contract Application])
ELSE [Contract Application Complete date]
END

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 21, 2007 01:31PM

What value has [Contract Application Complete date] field ?

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 21, 2007 01:35PM

[Contract Application Complete Date] i a Datefield

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 21, 2007 01:59PM

What is value ?

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 21, 2007 02:09PM

Well the value was Null thus making the DATEDIFF portion calculate based off of [Contract Application] field which is also a datefield, however the DAFEDIFF portion doesn't calculate properly. Say that [Contract Application] has a value of 5/22/2007 the datediff calculator should make a result of "1" however with the given formula it gives me the result value of: "1/2/1900 1".

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 21, 2007 02:39PM

Ok, lets go back to ACT! demo database - please check your formula on ACT! demo database and post here :

1) Formula
2) Initial fields values
3) Expected result
4) Real result

Re: How to make a field stop calculating if another field is filled

Posted by: JordanAustin

Date: May 21, 2007 03:13PM

Ok! we are almost there! I tried the formula again as you said and i recieved this error, so I changed the [results] to quotes. and it almost worked correctly, only issue is that it goes to zero (0) if datefield2 is filled.

save_formula - Cyclic link error occured in the following formula :
[results] =
IF ([datefield2] is NULL OR [datefield2] = "" )
THEN DATEDIFF( day, getdate(),[datefield1])
ELSE [results]
END

Error description : Formula can not contain target field([results]).

Re: How to make a field stop calculating if another field is filled

Posted by: Admin

Date: May 22, 2007 01:13PM

Jordan,
It seems you are using out-of date version. This issue was fixed recently - you need to download new version.
You can download new trial on our web-site. If you are registered user - please contact our support service and provide your OrderID - you will obtain full version.

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.