Trap the value, trap the UniqueID, do a find, then count the number of found records. If it's more than 1, find the record you started with and change the value.
SetVariable [$n ; Value: $n+1]
SetVariable [$text ; Value: YourTextField]
SetVariable [$ID ; Value: YourUniqueIDField]
Enter Find Mode
SetField [YourTextField ; $text ]
PerformFind [ ]
Exit Loop If ( Get (FoundCount) = 1)
Enter Find Mode
SetField [YourUniqueIDField ; $ID ]
SetField [YourTextField ; $text & "-" & $n ] or whatever you're incrementally adjusting it to...
Think it through and understand the concepts, then adjust as needed to fit your design. If you have few records, this should work pretty quickly. If you have a large record count, we can work on speeding it up a bit by playing with the find steps. Remember that finding '!' searches for duplicates...you can use that tool as well.
Hey Ninja thanks for your help
Ive been playing around with that for a few hours but cant seem to get it to work.
when I run the script says "The provided fine criteria is not valid. enter a valid request before proceeding" and if i click continue it just keeps counting ie MyText-1-2-3-4. I think i entered the code right.
Loop Set Variable [ $n; Value:$n+1 ] Set Variable [ $text; Value:test::Text ] Set Variable [ $ID; Value:test::ID ] Enter Find Mode [ ] Set Field [ test::Text; $text ] Perform Find [ ] Exit Loop If [ Get ( FoundCount ) =1 ] Enter Find Mode [ ] Set Field [ test::ID; $ID ] Set Field [ test::Text; $text & "-" & $n ] End Loop
Could you give an example of the text you might enter, and what you would change it to if a duplicate is found?
Meanwhile, another possible approach is to make a relationship of this table to itself based on the testText field. In your script you could then test for Count(related::testText). Makes your graph one TO more messy, but your script a lot less messy.
What I would like to do is select the 5 Letter client code from a drop down for example SMG01 I have about 150 different codes at the minutes.
which would then updates customer id field to SMG01-100510-01 (Client Code - The date - 01) (which i have working now) the thing I cant seem to do is to check if SMG01-100510-01 exists then changes the 01 to 02 etc.
the 2 fields iam using to create the code are Client Code - Date
ive tried messing around with if statments but cant figure it out
Seem to have got it working not sure if its the best way to do it.
What ive done is created 2 extra fields Sample_Code and Sample_Number_Today attached this script to the dropdown menu of the client code
Set Field [ Logbook::Sample_Code; Logbook::Client Code & SerialIncrement ( "-000000" ; 10^4 * Day ( Logbook::Date ) + 10^2 * Month ( Logbook::Date ) + Mod ( Year ( Logbook::Date ) ; 100 )) & "-" ] Set Field [ Logbook::Sample_Number_Today; Count ( Logbook 2::Sample_Code)+1 ]
then brought in the Sample_code & Sample Number into Veromara ID
Sample_Code & Sample_Number_Today
Sample_Code holds AMG01-100510-
And Sample_Number_Today Holds the counts
Is this a good way to do it are would you recommend another?
Thanks for help :)