×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help with selection formula
2

Help with selection formula

Help with selection formula

(OP)
I have a single field that has invoice#'s and Credit Note# {*.DOCNUM) in one table, this field is linked to 2 fields in 2 other tables an invoice table and Credit note table (where IN's are separate from CN's).  

The goal is to return the order number associated with the DOCNUM.  I've created the formula below which returns the order number of DOCNUM's that are invoices but not for DOCNUM's that are Credit notes, can anyone help.

Thanks in advance,

Rinder


If {OECOMM.DOCNUM} = {OEINVH.INVNUMBER} then {OEINVH.ORDNUMBER}

else

if {OECOMM.DOCNUM} = {OECRDH.CRDNUMBER} then {OECRDH.ORDNUMBER}

RE: Help with selection formula

How are these tables joined in the visual linking expert? Is this SQL Based? Are you using outer joins?

What is your current selection formula?

What is the result?

Remember that a "selection formula" determines which records are included in the report.  It does not return a value, and can't be printed as a column on the report.   

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Help with selection formula

(OP)
Ken,
Bear with me on the terminology, I'm relatively new at this...
The Docnum field in Table 1 is joined to the Invoice# field in Table 2 and Creditnote# field in Table 3 (Table 2 and 3 have an order# field which I'm trying to display).  

The Database is pervasive Sql, but the joins are defaulted to "=", I don't have the option to change.

The current selection formula:
{OECOMM.SALESPER} in {?FROMSALESPERSON} to {?TOSALESPERSON} and
{@dTransDate} in {?From Date} to {?To Date}
Returns a list of invoices and Credit notes and $ amounts for each sales person in a defined period of time.

The report works great, I'm just trying to add a column which gives me the order# associated with the Inv# or CrdtNote#.  

Any interesting discovery is if I reverse the IF statement in my pervious post, the report returns the order#'s associted with the Credit Notes only, as opposed to before which returned the order#'s associated with Inv#'s.

Thanks,

Rinder

RE: Help with selection formula

I am concerned about the equal join, which should (in normal SQL) eliminate records if they don't have BOTH an invoice record and a credit not record.   Make sure you aren't missing any records that have only one or the other.  

Your formula behavior is common when you run into null values.  Try:

If IsNull({OEINVH.ORDNUMBER})
then {OECRDH.ORDNUMBER}
else {OEINVH.ORDNUMBER}

Ken Hamady
http://www.kenhamady.com/
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

RE: Help with selection formula

If you are using left outer joins with the tables, then simply doing this should work.
If (NOT IsNull({OEINVH.INVNUMBER})) AND {OECOMM.DOCNUM} = {OEINVH.INVNUMBER} then
  {OEINVH.ORDNUMBER}
Else if {OECOMM.DOCNUM} = {OECRDH.CRDNUMBER} then
  {OECRDH.ORDNUMBER}

If you are not using left outer joins, probably the easiest solution is to change that.  If you need help with the syntax for that, just cut and paste your query from Database|Show SQL Query.

Malcolm

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close