View Full Version : * Primary Key Perplexity *


Al Phil
11-13-2007, 05:33 PM
Hi - I'm trialling v8 with a serious view to buy. Currently developing a Beta of a little dbase for my oil & gas client. I'm not an I-Techie but have used and designed dbases in a previous life (about 12 years ago).

But I'm stuck on a basic issue and I'd be very grateful for some pointers from my local (UK) community...
So I'm trying to go the ascetic route in every table of this dbase: each table has as its primary key (well, that's what I'm calling it) a simple self-incrementing number, which is naturally unique, but also without contextual meaning. A bit like a serial number.

OK, then I create a set which neatly links each table using these arbitrary but unique identifiers (keys); great. The problem comes when I enter data into the set using the default form: I obviously need to enter *numbers* to identify the particular records I want to retrieve - not what I'd like at all... Even if I set these key fields to be accessed via a pull-down object (in the form), I will naturally still only see numbers...

If I could link the tables using these numerical PK's BUT get the pull-down object to show the *related* TEXT fields (descriptors) that would be first prize.

I'm probably over-complicating my design, but if any fellow-user can make sense of my plight, I'd be very very grateful!

PS: Does Alpha5 (v.*) have a strong (rigid) sense of primary key?


Stan Mathews
11-13-2007, 06:00 PM
Not sure why you posted this in the UK users forum so I'll butt in.

I obviously need to enter *numbers* to identify the particular records I want to retrieve Not at all. You can have indices on any field and retrieve records quickly by making one ot these indices the currently controling index and retrieve records based on the contents of the indexed field.

The user never need see the linking key field.

Attached is a simple single table example of finding by indexed field.

Tom Cone Jr
11-13-2007, 06:11 PM
Imagine a simple two table set. The parent (primary) table is linked to related records in the child table. The link in the set design is one to many, so many child table records may be linked to each separate parent table record. Ok?

Your design must have unique primary key fields in the parent table. This primary key field would be used to define the link and a field corresponding to the primary key field must be present in each child table record. Call this second field a "foreign key". The foreign key field is the link that ties a record in the child table to the corresponding record in the parent table.

The foreign key in the child table should never be set to autoincrement. The autoinc field values would break the link with the desired parent table record.

Alpha Five is smart enough to make entry of the necessary foreign key field values automatic when new child table records are entered by the user. Just do the data entry through a set based form. Easy as pie. You should not try to display the foreign key field on your set based form. Nor should it be editable by the user.

If you need to break a link by changing a foreign key field use a separate form based only on the child table.

Alpha Five supports something else that you may find helpful. "Coded Lookups". check the help file for the details, but they wouldn't be needed in order to populate the link field values in new child table records, so long as the data entry is occurring through a form based on your set.

Al Phil
11-14-2007, 02:57 PM
Thanks to all of you for your responses... I have done further research based on your leads - and some great threads on this message board. So I have a little return offering - a static pic of the issue and how to set up a solution (with references)... Hope this helps others in the same predicament - many thanks again to the community.
Philip Oliver