I've also found the "Highest Sample Calc" which goes a little something like this.
Case (Length ( Sample ) < 5 ;""; Left ( Sample; 5 )<90000 ; ""; Left ( Sample; 2 ) ="lc" ;"" ;Left ( Sample; 1 ) ≠ "9"; ""; 1 )
Thanks for any help. I did not create this database, I am just in charge or running it now.
The simplest "fix" would be to toss out this entire approach and replace it with an auto-entered serial number. The examples you give do suggest there may be issues to be resolved by doing this, but if you can make it work, life will be much simpler for you.
PMJ- Thank you very much for this and I DO think that
that would solve my problem. The only thing stopping me is these numbers
are linked to products and product lines. The issuance of a duplicate
number (by human error) could result in tens of thousands of dollars in lost
business due to production of incorrect product, factory confusion, massive
time delays due to our factories being oversees etc... Etc... Any other ideas.
Follow up findings.In the item creation database "Sample Request
Database" there is a button you press to create a new number. The
button starts a script part of which is a set field command. When I go to the
specify command menu within script editor I see this;
Left ( Sample Request Table::Highest Number Used; 5 )+1
This calc has, I guess, allowed us to get from 90000 to 100000 but now when
I try to create a new sample it says “Sample” is defined to contain unique
If I change this calc to;Left ( Sample Request Table::Highest Number Used; 6 )+2
It will create 100001 but after that record it repeats “Sample” is defined
to contain unique values only
"The issuance of a duplicate number (by human error) could result in tens of thousands of dollars in lost business due to production of incorrect product, factory confusion, massive time delays due to our factories being oversees etc... Etc... "
That's precisely why you should define this as an auto-entered serial number. The number is assigned to new records automatically without human input.
Find this field definition in Manage | Database | FIelds and double-click it.
Set a "next serial value" at least one larger than the largest value in your table.
That should be it for simple numeric serial numbers.
In the expression you report, the Left function makes no sense. Simply removing that function from the expression to make it:
Sample Request Table::Highest Number Used + 1
should work--provided Highest Number Used is returning the correct maximum value.
But then this is exactly what an auto-entered serial number setting produces without any special programming or summary fields such as "highest number used" and avoiding a number of potential issues that can keep the above method from assigning the correct value.
"That's precisely why you should define this as an auto-entered serial number. The number is assigned to new records automatically without human input."
I believe it is an auto-enter field. We click a button and it creates a whole new record and assigns that record a number, 1 integer larger than the previous. But it seems to be telling me it has run out of unique numbers. The question is why it wont just create 100001, 100002, 100003 and so on.
So should I remove the "highest sample number used" part of the script? Or replace it with next serial value?
It may be an auto-enter by calculation field, but it's not an auto-entered serial number field.
There's a lot I don't know about your database that might affect the correctness of my answer, but from what you've described, I see no reason why you can't simply use the auto-entered serial number (next serial value) setting instead of the calculation.
I erased the script that was designated "New Sample Request Script.I then went to Field/Control Setup|Define Database|Fields tab| Sample Field.I then went to Auto Enter|Checked the Serial Number box| Generate- On creation|Next Value 100000|Increment by one. It still wont let me create a new sample stating that "Sample" field is defined to require unique.BUT!When I uncheck the unique value box and create records it will keep creating a record with the same number, however in the Define database|auto enter tab| Next Value Box it keeps couting up ie;100004-100011.The "sample request database" which creates records in the "Sample info database" is just not communicating this increase correctly. Thanks
Details to check:
Sample, if that's the name of your serial number field should be a number field. If not, make it so by changing the field type.
Do a show all records and sort by Sample. Go to the last record and confirm what value is the largest sample number in this field. Make sure your next serial value is larger than this value.
Make sure that you don't have any other field in the table that validates for unique values--perhaps with a custom validation message that is creating some confusion here.
1. Sample is a text field
2. Perfromed the show all records commnad and then used next highest number
3. I found a field called "Sample Info Link" Type: Container, Auto Enter Tab- Everything is greyed out, Serial Number is not selected. Generate- "On creation" Next Value-1 Increase-1.
According to your original post, the field that should be set up as a serial number is named: Sample Request Table::New Sample Number.
That was a script, not a field. The field is the "Sample" (number) field.
Well your script shows
Set Field [Sample Request Table::New Sample Number; Left (Sample Request Table:: Highest Number Used; 5)+1]
which is assigning the serial values to "New Sample Number", not "Sample".
I noticed that discrepancy and am asking if you have the right field set up for an auto entered serial number.
If a validation rule set on New Sample Number enforces unique values and you are currently leaving that field empty for each new record you created, you might just see the error message you reported.
I think I follow but as you probably know I am quite novice at the developement side of this program. Everything I've learned is just through poking around practice databases and figuring it out. I am going to follow up this post with a the exact readout of what is contained under the script name "New Sample Request". Perhaps there is something in there that need not be left out. Once again thanks for your pateince and time I really appreciate it!