formula for email document table field

[kevinguthrie]

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

"Email"

else

"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!



Kevin

 

[lbass]

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.

-LB

 

[AndyMc]

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.

 

[kevinguthrie]

Thanks for the suggestions.

AndyMc
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.

lbass
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...

 

[lbass]

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.

-LB