Formula help with multiple fields 1

[lholodnak]

Hi all, I'm using crystal 10 and oracle tables. I' trying to do a running total that does the following. But I can't seem to not select RPLCS, while selecting ESINV since they are in the same table. I get the same results where I have the RPLCS statement or not and the totals are different. I' not able to do a record select on this table because of other issues. Any ideas on how to select and not select a field in the same table. Thanks, Lisa

{SERVICE_REQUEST.SRVC_RQST_GENERATION_DT} in currentdate to
dateadd("d",-9,currentdate)
and {@Count of 2 days or less} = 1 AND
not ({WORK_ORDER_WORK_CODE.WORK_CD} in ["RPLCS"]) AND
{WORK_ORDER_WORK_CODE.WORK_CD} in ["ESINV"] and
{WO_X_WORKCD_ACTNCD.ACTN_CD} in ["REF", "REF11"]

 

[Turkbear]

Revised code, try it and see if it helps.

({SERVICE_REQUEST.SRVC_RQST_GENERATION_DT} in currentdate to dateadd("d",-9,currentdate))
and 
({@Count of 2 days or less} = 1)
 AND
({WORK_ORDER_WORK_CODE.WORK_CD} = "ESINV")
 and
{WO_X_WORKCD_ACTNCD.ACTN_CD} in ["REF", "REF11"]

Unless the same record can have both "ENSIV" and "RPLCS" as a value for WORK_CD you do not need to specfically exclude "RPLCS", just select the one you want ..

 

[lholodnak]

Thank you turkbear, but unfortunately the same record can have both 'ESINV" and "RPLCS", that is why I was trying to both. Sorry that I didn't make myself clear. L

 

[Turkbear]

Hi,
Can you describe the WORK_ORDER_WORK_CODE.WORK_CD field?
I cannot see how a single field can have multiple values in the same record .

Is is some kind of delimited string or an array,perhaps?
Anyway maybe you can just add a <> test

({SERVICE_REQUEST.SRVC_RQST_GENERATION_DT} in currentdate to dateadd("d",-9,currentdate))
and 
({@Count of 2 days or less} = 1)
 AND
({WORK_ORDER_WORK_CODE.WORK_CD} = "ESINV")
and
({WORK_ORDER_WORK_CODE.WORK_CD} <> "RPLCS")
 and
{WO_X_WORKCD_ACTNCD.ACTN_CD} in ["REF", "REF11"]

 

[lbass]

You can't have more than one result for the same field per record unless the field is an array like: "ESINV,RPLCS" . Are you trying not to sum a value for a person or account if in one of their records is "ESINV"? If so, then you could create a formula like {@RPLCS}:

if {WORK_ORDER_WORK_CODE.WORK_CD} = "RPLCS" then 1 else 0

Let's say that you don't want to sum amounts for accounts that have "RPLCS" present in one of the records. Then your running total evaluation formula field could read:

{SERVICE_REQUEST.SRVC_RQST_GENERATION_DT} in currentdate to
dateadd("d",-9,currentdate)
and {@Count of 2 days or less} = 1 AND
sum({@RPLCS},{table.account}) = 0 AND
{WORK_ORDER_WORK_CODE.WORK_CD} = "ESINV" and
{WO_X_WORKCD_ACTNCD.ACTN_CD} in ["REF", "REF11"]

...where {table.account} is a group field.

-LB

 

[lholodnak]

thank you both for your help. LB your's did the trick and got me the results I needed...L