Unicode join script unique validation

[LindaG]

I have text field in Contacts which must be unique - well not truly unique because it is case-sensitive unique. I have this text field set to Unicode for any necessary joins so it doesn't match other IDs (pa4600 shouldn't match PA4600). This is not the real ID of course but we must maintain the 'uniqueness' of this ID because this code must be entered back into a PeachTree accounting system for now.

I created another TO of Contacts called 'Contacts Peach Dups' joining PeachID = PeachID. Upon new record creation in Contacts (on Contacts layout), User must enter data in three fields and then insert (make up) an ID in the field then click the button to 'continue'. Script then validates that it is unique OR that the field is left blank - no case-sensitive dups allowed ... but my script fails - maybe too tired.

Commit Record/Request

If [ not IsEmpty(Contacts::PeachID) and not IsEmpty(Contacts Peach Dups::PeachID) ]

Show Custom Dialog [ Buttons: OK; "Must be unique. Please try again." ]

Go To Field [ Contacts::PeachID ]

Halt Script

End If

Go To Layout [ Contacts::Main ]

If I change it to:

If [ not IsEmpty(Contacts::PeachID) and IsValid(Contacts Peach Dups::PeachID) ] it still won't work. I swear I have tried every combination imaginable except (of course) the one you give me.

I verified all fields and layout is Contacts. I verified unique IDs really don't exist. I've just spent three hours on this silly thing. All logic (probably mine) has flown out the window!

I would also like to include in the validation (if I ever get it to work), that the ID must be 6 characters long (if one is entered), besides being case-sensitive unique). Oh, please do help me with this.

[LindaG]

14 people read this and couldn't help? that's not a good sign. We can't add new contacts until I figure this out. Maybe a different way of validating, using a vl and pattern count? Or is my logic just wrong in that If statement?

I know Unicode works for me in relationships. I even tried letting them enter into a global but global can't be set to unicode. Any ideas at all that I could try? Thank you.

Linda

[Lee Smith]

Linda,

You posted this after I (and a lot of others had gone to bed), so maybe you need to wait a little longer before questioning why no answers. Also, there was no way for anyone not using v7 to tell that this was about v7, and some of us who did read it, don't have v7 and are unable to help with version specific questions. (Unicode???).

I was confused by how you worded some of it, and I was waiting to see if it was just me (lack of morning coffee), or if others showed the same confusion.

For instance, in your first paragraph you say that the numbers are unique, and then you sort of retract that by saying that they can have PA and pa 4600 as a numbers and be unique?? Case sensitive I believe you said would make them unique.

To me this is confusing, and not really a Unique code. My first thought was to suggest you use a validation by calculation field using UPPER(YourField), which would make all of them be the same (i.e. pa, Pa and PA would all be PA4600).

Hope this will get the ball rolling for you.

Lee

[LindaG]

Hi Lee, thanks for helping. I can force upper case for new entries - that's not my problem. The problem is that there are already dup IDs that I can not alter which need to match an accounting system. Thse IDs are unique if based upon case-sensitivity. With vs. 6, we used ASCII to join (and for imports) to make FM only match if they were exact. In 7, we use Unicode with the same results - or it has been the same until now.

I need to verify that - within the SAME file - new Peach IDs are unique. So, someone COULD create PA4600 even if there already exists a pa4600.

I hope that clarifys it. I've been so tired - worked on this all night - that I was afraid something is wrong in my script logic more than a problem with the unicode join. But I am unable to determine why it won't work for me.

And yes, I posted again soon because our people can not add contacts at present. This means I will be hand-entering these unique IDs once I figure this out! And I was feeling a bit desperate to get feedback on my script at least - so I could narrow down my error - and because of this problem staff can't add new Contacts. Thanks so much, Lee.

Is my script wrong? It works on debug! I don't understand what's wrong with this situation! I mentioned Unicode thinking that might be the problem, but I'm unsure about ANYTHING right now. TGIF!!

L

[The Shadow]

It looks to me like the problem is that once you have committed the record, you will always find the record you just committed, so it will seem there is always a duplicate.

The way around this is to use a global to check before putting it in the real PeachID field - the Unicode storage PeachID will still compare case-sensitive, because for each predicate its the field with the index that gets to decide how the compare will be done.

[The Shadow]

Another possibility would be to add another field that you know is unique, such as an auto-enter serial, then add a second join predicate that was serNum <> serNum on the two tables, so this record would be removed automatically.

[LindaG]

Ah thank you so MUCH!! Ray once said the right-side is all that mattered in matching these case-sensitive ASCII/Unicode IDs. I had forgotten that when I originally considered using a global.

My logic was wrong - not in my If [ ] but in - well, just my logic about the new record being considered in the validation comparison.

Once the record was created it would always evaluate as true - match itself. Of course. I made the adjustments (using a global and setting the real PeachID AFTER validation) and it works perfectly.

Oh, just saw your second post. I do have unique serials (and unique auto-enter calc IDs) but since this works, I'll stick with your first suggestion - for this one!! But I've learned from your second suggestion as well, thank you Shadow!!

I added:

Length(Contacts::zgPeachID) 6

Can't believe I didn't think of that at 4am!! Brain-tired and design don't go together very well. It all works perfectly now! You are the greatest, Shadow! Script fixed & boss just gave me the day off paid ... life is good!

L