Error Flag OR Unique Key

[LaRetta]

Hi everyone,

I import a large volume of data daily. This is user-entered data that is not validated (pulled from the web). There is only one way to match this data to our existing orders and that is on the Invoice Number field (this is used to match payments to orders). Online User also enters their name but you KNOW how hard it is to match on name entries exactly. So I want to make a valid key out of this - or produce an error so we can quickly view and correct any funky entries.

The Key must be either our InvoiceNumber (6-digits always starting with 0); the on-line purchase order number (4-digits), or the words "Trial". They also enter the correct InvoiceNumber but leave off the beginning 0 (that's why I have the 5 Length line in the calc. I then will match this key to our orders on multiline of InvoiceNumber or PONumber as:

"INV" & InvoiceNumber &

[bikergeek]

Add a GetAsNumber ( Invoice Number ) > 0 requirement.

[LaRetta]

Okay, that works thank you! Since it can be boolean, I didn't bother with the > 0. It's getting longer and uglier however; and it doesn't catch empty Invoice Numbers.

Case(

Length ( Invoice Number ) = 6 and GetAsNumber ( Invoice Number); "INV" & Invoice Number;

Length ( Invoice Number ) = 4 and GetAsNumber ( Invoice Number); "PO" & Invoice Number;

Length ( Invoice Number ) = 5 and GetAsNumber ( Invoice Number); "INV0" & Invoice Number;

not Length(Filter ( Invoice Number ;"0123456789")); "Trial Pak";

IsEmpty(Invoice Number); "ERROR"; "ERROR"

)

The redundant use of GetAsNumber certainly isn't needed, is it?! I wish I could move it outside the first three lines. But the first three lines are text evaluations and GetAsNumber() would throw them off, right?

[comment]

'not Length()' is the same as 'IsEmpty()'. Since you have 'not Length()' first, it never gets to 'IsEmpty()'.

How about something like:

Let ( [

num = Invoice Number ;

len = Length (num)

] ;

Case(

not len ; "ERROR A" ;

not GetAsNumber ( num ) ; "TRIAL" ;

len < 4 or len > 6 ; "ERROR B" ;

Choose ( len - 4 ; "PO" & num ; "INV0" & num ; "INV" & num )

)

)

[Leader]

You didn't mention what the incorrect results were showing, but let's take a stab at it.

Presumably you are trying to match the user data and your fabricated key with either a PatternCount or Position function?

Can they have an invoice number of the form 003333 or 000333?

I follow your CASE statement right up to the 'not length(filter' line. I know what you want to accomplish, but are you SURE about this comparand?

Finally, shouldn't the line to assemble your Invoice key look like:

= "INV"&RIGHT("00000"&Invoice Number,6)

To be safe, shouldn't you do similarly for the PO number?

[LaRetta]

Hi Comment,

I looked at the Filter() when blank InvoiceNumbers first produced no result. Those with the word "Trial" or "Trials" or any text, produce "Trial" which is correct. Those with BLANK invoice numbers produce BLANK! In theory, they should produce "Trial" if the evaluation is stopping there, shouldn't it or produce ERROR? I'm still missing the point, I think.

Hi Leader, the only incorrect results are invoices with 000000 or invoices that are blank. My second calc picked those up (thanks to BikerGeek) and changed them to ERROR. But blank invoices are still producing NO result.

The match key on right is INV###### or PO####

And no, InvoiceNumbers weren't being paid online back that far (when our number series was only 4 digits), thank God. I think I see where you are going with this however, and I'll review it again.

This will be temp measure until I pull together the online billing and ordering piece. And yes, it's a nightmare. Currently, some invoices are manually paid online, some are generated through Auth Net, etc. - no consistency whatsoever. But payments are being missed or incorrectly entered. So I decided to download the credit card authorizations to flag them as matched and paid.

I currently have 8,800 payments to match up - since start of fiscal (10/04). By providing this final match, we should know if any billings generated are wonky or missing. Funky, I know, but for peace of mind, I want to have the final credit card authorization number (and make sure dollars match) on every single invoice.

I don't think this can be fool-proof by any means. I will have a User verify the data each day as it comes through, looking for any invalids. In truth, the validation "ERROR" should be separate from the key, I think. In this way, I can have the error flag produce an error also if IsEmpty(Transactions::AuthKey) and use the relationship match (or lack of) catch unmatched entries.

I will review and rethink this process, using both your calcs and ideas. THANK YOU BOTH!! I am sure this will give me what I need.

LaRetta

[LaRetta]

Okay. It was dumb of me to attempt to combine an error check with a key. Comment, for some reason your calc (copy/pasted into my definition) still failed to produce Error A on blank Invoice Numbers. That has me baffled. The rest of your calculation worked perfectly. But, since I then wanted to test the validity of the key also, I used part of your calc to create my key, ie:

Let ( [

num = Invoice Number ;

len = Length (num)

] ;

Choose ( len - 4 ; "PO" & num ; "INV0" & num ; "INV" & num )

)

Then I created my relationship (It really doesn't matter what the key says if it's invalid) and then created an Error calc of:

If(IsEmpty(Transactions::zzcKeyAuthNet); "Error")

I simply can't wait to transition this entire piece to web publish and shopping cart. But I'm simply not ready (in many ways). Thank you both so much for helping me through this!!

LaRetta

[Leader]

Since you had the foresight to use a CASE statement and you've given us the clue that the big problem is with a blank invoice number, I think your problem may be readily solvable, and that Comment was on the right track.

Since I haven't tried this yet, I may be corrected by someone, but I don't think IsEmpty() would return true if the field contained " ". (blanks instead of null)

Therefore, how about a line in your CASE that uses WordCount(Invoice Number)?

[LaRetta]

Hi Leigh,

I'm still unclear on why it wouldn't produce the error on blank Invoice Numbers. I had checked and there are no spaces in them. I had performed a search for Invoice Number using = and it produced them (but the error key still produced nothing). After receiving this response from you, I created two calculations (number) ... IsEmpty(Invoice Number) which produced 1 on them and WordCount(Invoice Number) which produces blank. The remaining Invoice Numbers contain data.

I've already split the error from the actual key so I can also catch invalid key matches. It would save one additional field if I could incorporate this error into the key itself so I'd like to do that if possible. Pipe dream?

And I want to understand why neither my or Comment's calculations produced the right results (on blank Invoice Numbers). I would certainly appreciate solving the puzzle for my own peace of mind, so I think I'll duplicate the error key in this thread again and see what happens.

Or am I missing your point completely? I've been a bit brain-dead tired ...

LaRetta

[LaRetta]

Okay, here's the offending file, stripped of all sensitive data (and most of the records). I don't understand the logic on why our Case() calcs are failing to identify blank Invoice Numbers and produce "Error."

I wish I could just shrug my shoulders and say, "Oh well" but it will continue to haunt me until I understand why it won't work. My new key is broken of course, because the Transaction file is no longer attached but all the original test calculations are there.

LaRetta

AuthorizeNet.zip

[comment]

LaRetta, LaRetta, ... uncheck "Do not evaluate if all referenced fields are empty".

[LaRetta]

oh

that silly little box which is incorrectly worded, huh. it should say, "... if ANY referenced fields are empty" not ALL.

I SWEAR I looked at that.

So sorry to take everyone's time. You can BET I won't EVER forget to look at that stupid little checkbox again - ever - no matter how tired or stressed or rushed or anything! Ever.

This is why my spirit hates to stop until I figure these things out - I would have missed this WONDERFUL hard lesson if I had just shrugged my shoulders and moved on. I'd have preferred to learn this lesson quietly sitting at my own keyboard however ... instead of publishing it for the world to see!!

Okay. Everyone - Always think about that too-low-to-hardly-notice stupid, incorrectly-worded but extremely critical checkbox down there!!

Thank you, Comment. I loved opening my sleepy eyes to your Forum response email and feeling that wave of horror hit me; I could even feel you roll your eyes. Now I will crawl off to work.

Egg-on-face LaRetta

[comment]

I was NOT rolling my eyes. Perhaps shaking my head ever so slightly...

And you are right about the checkbox being bad design - along with "Do not replace existing value...". Someone from Apple's HUI dept. should pay a visit to the daughter company.

[Leader]

Whew! Thanks, Comment for not revealing I rolled my eyes.

Nah, LaRetta, you're being too hard on yourself. First, we admired your persistence to solve something that was bugging you. It's people like you that should build space shuttles and take over homeland security.

But also, it's so easy to overlook the obvious and it's easy to forget things we once learned. It's tougher yet if you switch around a lot on verious computer platforms, languages, and databases.

It's helpful to have multiple eyes looking at a problem. Several of the people here, Comment, Queue, Cobalt, Chopper, to name a few, have a way of remembering the obscure, bringing nearly forgotten knowledge to bear on a problem, and sometimes just pointing out the obvious.

I don't look forward to the day that I have to ask a question in which the answer is elementary, but it's what we have to do to move foreward.

Don't beat yourself up!