We have a bounce report from our email blast provider. It only has email addresses. I'd like to run a jet formula against it to find the code, name etc.
NL(,"Customer","E-Mail","No.",B2) returns nothing.
I've tried copying the email fields to notepad and pasting them back into excel with no change.
Is there something I'm missing on why I can't do this on E-Mail (but can on other fields)?
Thank you.
Date
Votes
9 comments
-
Jet Reports Historic Posts Official comment Thanks for all the help. It caught me off guard to find something Jet could not do.
The solution I used that worked was:
On a new tab, create a contact list with Jet, first column being the E-Mail field.
Then use VLOOKUP to reference the email address against the Jet list.
Success! -
Jet Reports Historic Posts After thinking about it I think I can't filter on the field (the way I'm trying) is because of the @ sign in the email address.
That being said, what can I do to make Jet Reports work with the @ sign and allow me to filter on it? -
Jet Reports Historic Posts So just to be clear, you aren't trying to filter on the Customer No. field but rather on the Customer E-mail field, is that right? Because the @ character is a special filter character, you can't filter by a value with @ in it without telling Jet Reports that your filter is a literal filter by either putting @@ in front of your filter or by surrounding your filter with single quotes like this:
"@@me@me.com"
"@@"&C2
"'m3@me.com'"
"'"&C2&"'"
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts I just re-read my post, and I typed it wrong the first time. Try again:
A2 = an email address (from our returned emails list)
in B2 I have =NL(,"Customer","No.","E-Mail",A2) and I don't get the customer code (I've visually checked the email and it should return a customer code)
As per your suggestion I tried =NL(,"Customer","No.","E-Mail",""&A2&"") and still don't get a return from NAV.
I agree the @ sign in A2 must be a problem, I just can't get around it.
More ideas? -
Jet Reports Historic Posts No you didn't read my post quite right. You need a single quote around the reference to cell A2. Not "" but "'". I know it's hard to see the single quote in there, but it's there and that's the important thing. It's probably easier just to use @@ so make your formula this:
=NL(,"Customer","No.","E-Mail","@@"&A2)
Regards,
Hughes -
Jet Reports Historic Posts Thank you for your patience!
I've tried:
=NL(,"Customer","No.","E-Mail","'"&A2&"'")
=NL(,"Customer","No.","E-Mail","@@"&A2)
and copied the email address from NAV into my spreadsheet and did an IF statement to see if they match and they match, but I still don't get a return of the customer code.
What else can I provide to help you help me? -
Jet Reports Historic Posts So I just consulted with a colleague about your issue, and filtering by an e-mail address just can't be done in Navision. In fact, there's no way to do it in the Navision client either. Sorry to lead you down the wrong path here. Apparently it just can't be done period. Mark this one up to a bug in Navision itself.
Regards,
Hughes -
Jet Reports Historic Posts I think I may have a trick to help you. If you replace the "@" from your emailaddress with a "?" or "*", then Dynamics NAV accepts the filter. I tried it on my Dynamics NAV 2009 R2 and it works :D
Attached is a small report that gives away the formulas I used. Please let us know if it works for your version of Dynamics NAV, thx. -
Jet Reports Historic Posts Hey great idea! What Navision filtering can't accomplish, creativity in Excel can. ;-) So even though the original approach doesn't work, you found a way to make it work through creative use of Excel. That's part of why Jet Reports is a great solution and I applaud you for your use of it.
Regards,
Hughes
Please sign in to leave a comment.