"It" probably just doesn't understand you – nor do I, for that matter.
Allowing any manual entry in a primary key field is just plain bad practice. I too can not understand what you are trying to achieve.
If you just need some sort of suggested field for a reference field that isn't being used as a primary key, then that's different, you can just use an auto-enter calculation, which would allow override.
do you have some screens, or perhaps a better explanation of why you need to do something like this?
Also, are you sure you're using the phrase "primary key" correctly?
LOL hahaha, Well ErLost, It confuses me as well..let me see if I can clear it up some.
In my invoices the PK field had a Serial Key counter on it.
But no matter what i did it would create new rec's and adv the key to the next in line even when i was just trying to edit a existing invoice.
So I changed it to Serial (On Commit) Increment by 1., It then would still Increment the counter even though I did not use it's suggested key
I.e. I go into Invoices it suggest key 1054 I key in 1034 becuase it had to be updated. And I resave, Invoices would replace my 1034 with 1054
and save the invoice out.
So what I am asking is there a way to let it show the suggested next Key val but NOT force it to be used in the save.
I am thankful for any help.!
@ Mike, Yes the PK = the invoice #, so yes it is the Primary Key.
As far as the auto calc process, if this is a scripted option other than being serialized it may be what I am looking for.
The reason for Manual entry for the Key field is for later revisions to prev rec's. Trying to prevent the constant saving of
NON-used Keys as well..Which FM seems to be willing to do for you no matter what as well as Auto Increment without
there being a real SAVED key in place.
You're confusing the functions of primary keys and invoice numbers. The former (can) play a critical part in your database, the latter are part of your business logic.
Unless you're a-r, you shouldn't really care about gaps in your PKs. Invoice numbers are of course something else, but those two things have no inherent connection; use a separate field and/or counter to calculate/set your invoice numbers.
As Mike said, it's best to leave the PK in peace (don't display the field on the layout, don't write to it (as opposed to read from it) in a script), unless you have good reason and know what you're doing.
Wish I could leave it alone, but the charity has strict state rules to follow and there can be no gaps in their Inv#'s (at the moment are the Invoice Keys as well)
So I have turned off the Auto increment serialization of the field and will just try to come up with something to look in the table and see what the next highest
record key is and suggest it as a default.
Not sure how to do this in FM atm, But it does seem to be surprising at it's strengths at times.
Have you considered this:
What erolst is suggesting should not violate your policies. We are talking about a backend primary key that is never displayed to the user, and never changes.
What happens when you change an invoice # and you strand any records in related tables, because they are now no longer related?
These are the types of things that are handled by true primary keys.
What you have described, regardless of requirement or not, is a reference number, not a primary key. Primary keys do not change from the time a record is first created.
I feel that there is some misunderstanding in that I am not neededing to change the KEY/s.
ATM we are using the current Invoice # as the PK, We have need to occasionally going back
and updating and invoice from time to time to fix data entry errors.
In doing so when we try to go back and pull up a invoice via the PK and we update the rec
it saves out the update as the NEXT PK in the increment set. (This we cant have)
Sorry I have confused you, I am not used to the FM way of doing things and the way it behaves.
But I do thank you ALL, for your help in adv.
Erolst has been a great source of intel on FM and things so far, my hat goes off to ya.
Also on the Link you posted is exactly what I need.
Can this be put into the Data box for a default value? or some var to act as a default value so
that we can use it to save. Then we can keep serialization off and it wont self increment with out
actually saving records. (Great link Mike)!!!
When you go back to update an invoice, you should be doing a search for an existing record. So enter find mode, enter the invoice number and perform find. This will not create new record and will not increment the counter. Once you have the record you should be able to update other fields.
While the invoice number is the primary human key. You may want a primary key field that is either numerical or a text UUID value as the real primary key for the record. It should be this key that is used in relationships. Something to consider.