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

formula for email document table field

formula for email document table field

formula for email document table field

I am trying to figure out how to have Crystal Reports retrieve the data in one field in a table if another field in that same row in the table matches a specific value. I'm not sure if I'm writing the formula incorrectly or if I did something wrong when I linked the table. Ultimately, I would like to display on the printed invoice whether the invoice is to be emailed or sent via US mail. The formula I am using is as follows:

if (not(isnull({AR_CustomerDocuments.CustomerNo}))) AND ({AR_CustomerDocuments.Document}="S/O Invoice" AND {AR_CustomerDocuments.EmailDocument}="Y") then



"US Mail"

The problem is that the CustomerNo field may or may not be in the AR_CustomerDocuments table, or it may be there several times for several different "Document" types. I am only interested in the "EmailDocument" field for the specific row which has the correct customer number from the invoice and the correct document type ("S/O Invoice") in the "Document" field. It seems that the formula retrieves data from the correct field, but isn't always in the correct row for the correct document type.

Here are screen captures of the table in question and my links:

Does anybody have any suggestions on how I can accomplish this? I must be missing something.
Thanks in advance!


RE: formula for email document table field

You might want to leave off the "else" clause, since it probably is not always true for other rows, but otherwise the logic looks correct. You might want to simply use your "if" clause (without the if/then) as your record selection formula, so only those records that meet your criteria appear.

If this isn't what you are aiming for, then you should show a sample that illustrates what is working incorrectly. Also cannot tell what kind of joins you are using or the purpose of the item code field.


RE: formula for email document table field

These look like Sage100 tables. Keep in mind that the table SO_InvoiceWrk is an empty table and only gets populated during invoice processing, then empties out again.

RE: formula for email document table field

Thanks for the suggestions.

It is a Sage 100 report indeed. This is for an invoice that prints before it is posted, so the Wrk table works for us in this case.

I tried leaving out the "then" part, but I am ending up with a blank field instead of the "Email" that should appear in that space. What kind of sample would be helpful to illustrate the problem?
To simplify things, I did away with the formula and only inserted the "EmailDocument" field from the the result on the report is "N". This would be correct if I wanted it populated from the A/R Statement row or the S/O Order row, but not for the S/O Invoice row. I'm just not sure how to force the data to be read from the row which refers to the correct "Documnet" type.
As for the joins...

RE: formula for email document table field

If you are using equal joins for all tables, then the customer number will never be null, assuming that the invoice always contains the customer number. I only suggested removing the USMail option because if the ARCustomer Number was null but the Email field was "Y", then it would return "US MAIL" because of the null value, but presumably this would be incorrect.

I can't see why you would be getting a blank for (for example) NTT001 when all criteria in your formula are met, although you have unnecessary parents in the formula. The only parens you need are for the isnull(). Parens around clauses only become important when clarifying what you mean by "or" statements. If you want to be able to capture some sort of result for null customer numbers, you would need to use left joins, and you'd need to explain what you want to happen when there is a missing customer number.

You haven't explained what the problem is. Please show some samples of actual rows where you are getting an unexpected result, along with one or two where the formula is working.


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