First of all, a big thanks for the input from everyone with my C11 migration. I’ve fixed most of the issues in C5 and am almost ready to perform a 2nd migration. Almost…
Anyway, I’m tinkering with a new app, to replace a spreadsheet, in C11 and have created a DCT in C11 and an identical db Firebird (our backend).
One of the relationships is a parent/child, CLIENT_FK_CONTACT. The CONTACT record has a CLIENTID which cannot be 0. I have added the relevant entry to the Field Priming on Insert but when it attempts to add a record I get an error from Firebird that the value of 0 in CONTACT:ClientID is invalid. If I remove the constraint in FB, the record is added, ultimately with a valid FK value.
I believe FB is behaving correctly by preventing a child record to be added with no valid parent id.
Is it me, C11, a timing thing?
To make things interesting I’m exploring the ABC templates, so things are a little foreign to me.
So are you adding a Contact when you’re adding a Client? Is the Client PK based on a server value?
If so, It’s sort of a clarion thing, because the Client won’t actually be added until you save, so it doesn’t exist yet for to pass the FK Constraint.
There’s a couple of ways around this. One is to remove the constraint and take care of the the relationship in code, or you could get the future PK value youself first. and allocate it.
Or you could use a set of edit tables, which have no constraints and only on save copy to the ‘real’ table and check and apply everything then.
Or there’s a Save Button control template that you can use and only after save do you allow to add child records.
I’ve used all the methods and sort of prefer the last 2.
I have a preference for Sean’s #2; get the future PK value yourself and allocate it.
So yes, it is a timing thing. I assume your situation is this:
You have a client form with a contact browse on it. You enter the form, put in some client information.
You press insert on the contact browse, which opens a contact form, and at this point the client_ID (which has not yet been set) is copied to the ClientID field in Contact.
You try to save the contact, get the error
If you then go back to the client form and save, the record gets saved, and the server adds in the client_id (taking the value from a sequence)
So my preferred would be:
You have a little turbosql table on the client form, and on entry to the client form you grab the next value of the sequence. You put the value into cli:client_id.
You (maybe) remove the trigger in firebird that sets client_id = whatversequence.nextval(). I say maybe because often the way those triggers are written is that they grab a value from the sequence only if the current value is null. If it’s like that, you could leave it.
Even with an established CLIENT, ie the record already exists, adding the CONTACT fails.
As far as the CLIENT:ID value goes, isn’t that value established before the record is added, ie in C5 Legacy it usually does a SET (with a high value), PREVIOUS() then add 1 to the result before adding the record ( and looped 3 times to ensure it isn’t duplicated etc)?.
I guess I’ll have to review source a little more to understand what’s going on. It is certainly different to C5.
If you want to use the Clarion templates then the new record is added in the browse procedure, so Field Priming on Insert is too late. Remove that code and in your browse procedure set it up so the browse is range limited by the Parent ID and the templates will prime the key automatically.
If you have checked Auto Number for your key on Client:client_ID, then yes, Clarion will add a client record that has the client_ID = to max(client_ID) +1 and pretty much nothing else. That record is inserted as you leave the Client browse. The action on the form is then set to Change that stub record (rather than insert).
If you are doing Clarion autonumbering on the Client file, then you should have a client_ID at the point you are trying to enter a contact, so all the things Sean suggested earlier should not be necessary.
However, IMO, Clarion auto-numbering is not a good idea with relational databases. For example, if you think that every client should have a name, and set up a check like that in the database, then Clarion trying to insert a record that has nothing but a client_ID, no name, the insert will fail, and rightly so.
PurpleEdge’s point is important if you are trying to insert from a Contact browse, and are assuming that because you press insert from an existing contact for Client123, that the contact you try to insert will also be for client123.
It’s not really clear what you’re doing that you are having trouble with, so you may need to explain a bit more.