"If ParentTable::keyfield is empty and ChildTable::keyfield is set to auto generate"
Having the Child Table generate auto-entered serials in the field used as the key to the parent table doesn't seem to make sense. If the Child table is also going to be the "Parent" to another table then it should have a separate field that's set to auto-enter serial values which would in turn be passed on to its "Child" Table. Also, the only way the original Parent keyfield could be empty (as long as the serial is set to generate "on create") is if it was deleted after creation, which ought to an option unavailable to the user.
Having said that, and I'm sure none of it is news to you, what your post points out is interesting. I've not tried it. It seems like a recipe for disaster since the Parent could generate serial numbers later on that already exist in the Child Table.
I may have confused it a little using simple field names..
I didnt mean the Parents table Primary Key would be populated, I was going more along the lines of populating a foreign key
Ill give you another example...
Lets say we have an Invoice Table which is linked to a customer table (using above method) Invoices::CustID = Customers::CustID
In this instance, we would want the customers:custID to be the primary key
and we would want the invoices:custID to be the foreign key.
Now on a layout based on the invoice table,
* We could select from existing customers and populate the foreign key Invoices::CustID
* Or.. we could do what I was proposing initially above (via script), to get the ID
* Or as I have just found out.. we could simply put the related fields on the layout and allow entry into the fields
In which case if Invoices::CustID is empty, it becomes populated by the newly generated Customers::CustID,
Otherwise if Invoices::CustID is not empty, then we must be editing an existing customer record
The invoices::CustID would never generate, so that shouldnt cause any issues.
I also dont see any issue with Customers later becoming a parent table. Since the key is set to 'not update' and the field wont be accessible.
I believe it has worked this way since version 7, when relationships became bi-directional. Every now and then, someone "discovers" this.
I don't think there's any danger. In fact, it can be quite useful in some situations - see, for example:
Thanks for the comments and link..
Ive been playing with it quite a bit and its certainly helpful if used the correct way of course.
Im happy that Ive finally discovered it, thats for sure... Why it took me so long, I have no idea..
Ahhh . . . it's beginning to make sense now and I can see it as a technique rather than a dodgy situation. This can obviously be quite useful. "Allow creation of new Records" must be turned on for both tables, no?
Allow creation of related records (as in the example) would only need to be turned on for the child side (customers)
After further testing, it is so far proving to be very useful.. definitely something I'm glad I stumbled across.