Adding "a,b,c etc" to duplicate field data.

[Chemadian]

Hey everyone, i have a field that acts as a unique identifier (audit lot#) to each record and i know i know just use the auto-enter serial number, but in this case it is not sufficient. Just to clarify so i don't receive the wrath from the filemaker faithful this audit lot # does NOT act in anyway as a primary key, i am using the proper auto-enter calculation for this! The person who the solution is being built for has been using this particular serialization format for 10,000+ years or whatever and doesn't want to change, so who i am to say otherwise!

Basically this field is built out of the records name field...being that it takes the first letter out of the first three words and adds the current date at the end. This is all working fine i am just looking out for the possible but unlikely situation of creating two identical records on the same date. So for example if two identical records are made today they could both end up with the audit lot # of MSS20110811.

What i am looking to do is somehow perform a search making sure that if there happens to be any duplicate lot #'s that it will automatically add an A,B,C etc to the end depending on how many duplicates there are... so the first duplicate of MSS20110811 would be MSS20110811A etc.

I'm sure there is a fairly simple solution to this problem but the things i have tried are not working out. any help is appreciated!

[bcooney]

" i am using the proper auto-enter calculation for this! " do you mean an auto-enter serial number?

Couldn't you just make the audit lot# unique? And what if a name changes?

[Chemadian]

Ya sorry for the confusing "auto-enter" definition, i did mean an auto-enter number.

Couldn't you just make the audit lot# unique?

you mean use an auto-enter serial number for the audit lot#? Like i tried to explained in my first post there is no technical reason why i couldn't use an auto enter serial number, except for the fact that the owner does not want the audit lot # formatted like that.

And what if a name changes?

Without getting into a long explanation it doesn't matter if the name changes because the audit lot # is further tracked using a different method and will still be able to reference the correct record.

[bcooney]

Can't think of any "fairly simple solution," sorry. My approach would be to "check out" the uniqueness of value via scripting, and concatenate whatever you want if the uniqueness check fails.

[Chemadian]

Ya, i was thinking something along the same lines, just couldn't get it to work the way i wanted...thanks anyways!

cheers

[joaocarlo]

Initially You can adapt any of the following techniques:

Option 1 - Using a Custom Function: http://www.briandunning.com/cf/718

Option 2 - Using a Script: AtoZ.zip (from Databasespros.com)

Option 3 - Read this article from FileMaker Support…

A question,… you want this:

MSS20110811A

MSS20110811B

...

MSS20110811Z

...

MSS20110811AA

MSS20110811AB

...

MSS20110811AZ

...

MSS20110811AAA

MSS20110811AAB

[comment]

Read this article from FileMaker Support…

This is not a good method to use. if two users are creating a new record roughly at the same time, they will get the same result and produce a duplicate. It's also very clunky, but that's beside the point here.

[Vaughan]

The FileMaker article was written in the FMP 5 days...

[comment]

Nothing has changed since then.

[Chemadian]

Joaocarlo,

Thanks for those references. I haven't had a lot of time to play around with either solution but from the initial look-over they both seem to offer good starting points. I will keep you updated on how implementing these solutions go!

cheers

[Chemadian]

I've finally had some time to try and implement the custom function found on BrianDunning.com. When i add the custom function to my existing calculation for the "AuditLot#" field the result i now get is a question mark (?).

The fields size is not an issue so its most likely a problem with the calculation. I didn't change the CF at all, just pointed the parameter "mynum" to the AuditLot# field...I'm still fairly new to FM and calculations cause me a lot of grief so it might be a problem with the way i'm joining the CF to the rest of the calculation. When looking at the CF it seems that it would make sense to use a simple "&" to join the CF to the rest of my calculation but i'm probably missing something! Here is the calculation i am working with ( I obviously cannot take credit for the rest of this calculation I pieced it together from other posts so a big thanks to this forum, its helped me a ton!)

Let([

threeWords=LeftWords ( FormulaName ; 3 );

firstWord=LeftWords( threeWords ; 1);

secondWord=LeftWords ( RightWords (threeWords;2);1);

thirdWord=RightWords(threeWords;1);

letter1=Left(firstWord;1);

letter2=Left(secondWord;1);

letter3=Left(thirdWord;1);

abr=letter1&letter2&letter3

];

Upper(abr& Substitute (( Right ( "0" & Month ( DatePrepared ) ; 2 ) & Right ( "0" & Day ( DatePrepared ) ; 2 ) & Right ( Year ( DatePrepared ) ; 2 )

) ; "-" ; ""))

)

&

NextLetter ( AuditLotNumber )

[Chemadian]

Ok, i've taken some time off from this problem but it's time for me to revisit my old friend!

So looking at my previous post it's no wonder it isn't working properly. First, it seems like there should be some sort of search function that will look for any matching "AuditLotNumbers". Then if it finds a duplicate it needs to apply the custom function, if it doesn't then just create the "AuditLotNumber" as usual.

I am looking for some advice on how to go about searching the current record "auditLotNumber" field against the rest of the records.

Any help is appreciated!

Cheers