• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 35
  • Last Modified:

Crystal Reports conditional formula execution based upon field contents

I developed an Order Backlog report to forecast anticipated billing dates. Some of our orders are "progress billings"... where the order is based upon various milestones:
  • First 1/3 with Purchase Order
  • Second 1/3 at delivery
  • Final 1/3 at plant approval

The Prog Billing2, Prog Billing3 and Prog Billing3 dates are calculated from the Start Date and the 1st Shot Date using the formula: DateADD ("d", 90, {OrderDet.User_Date1})

If the Terms are something other than a Progress Billing Term code, I don't want the dates to appear under the Prog Billing2, Prog Billing3 and Prog Billing3 headings.

I've tried using IF IsNull{OrderDet.CumulativeBilling... if blank then ProgBill2, etc should also be blank. But it could be a progress billing that the first billing hasn't occurred yet. I think the better condition is to check the TERMS. If Terms DO NOT START with Prog, then suppress/blank ProgBill2, ProgBill3 and ProgBill4 dates.

Order Backlog Design View
Order-Backlog-Progress-Billings.pdf

Thanks for any suggestions.
Regards, Lee
0
Lee Ingalls
Asked:
Lee Ingalls
  • 2
  • 2
2 Solutions
 
mlmccCommented:
Try it this way

NOT({TermsField}  LIKE '*PROG*')

mlmcc
0
 
Lee IngallsDirector of IT/TS, Quality and FinanceAuthor Commented:
Michael,

Thanks for the response.
Are you suggesting I add the NOT({TermsField}  LIKE '*PROG*') to my ProgBill formula?
If so, I'm not certain how??

NOT({Orders.User_Text2} LIKE '*PROG*')
DateADD ("d", 90, {OrderDet.User_Date1})

The ProgBilling2 field is the StartDate Field plus 90 days using ProgBill2 formula:  
DateADD ("d", 90, {OrderDet.User_Date1})

The ProgBilling3 field is the 1StShotDate field plus 120 days using ProgBill3 formula:
DateADD ("d", 120, {OrderDet.User_Date2})

Regards, Lee
ProgBill2-formula.pdf
0
 
mlmccCommented:
I meant  for you to use the formula as the SUPPRESSION formula for the field

mlmcc
0
 
Lee IngallsDirector of IT/TS, Quality and FinanceAuthor Commented:
Perfect.
You are the man!
Thanks Michael.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now