Best way to create this relationship?

[Thumper87]

ok so i have a couple tables (Reptiles , Medical) i want to find the best way to relate the medical records to the reptiles, im assuming this is a one to many seeing as one reptile can have many reptile records and each medical record can only be assigned to one reptile.

Please note that in the reptiles table the field called Reptile_ID is not a "ID" field like Record_ID, its more of a "Name" field but since breeders dont use names we use ID's (EX: M.PJ.3.34.11)

I had a relationship once set up by relating Medical::Reptile ------ = ------Reptiles::Record_ID

it was working fine for a while then i ran into problems when creating another layout to list only a specific reptiles medial records in a listview, only showing the records with field medical_record_status equal to "Active"

Im having a hard time understanding relationships, no matter how many videos and guides i read i find it very confusing, so not sure if my previous relationship was right, but if it wasnt could you please help me understand the relationship i should be using in this case.

Thanks,

Justin

[RalphL]

In the Reptile table record ID is the primary key, you need a foreign key in Medical that is the same type, i.e. number and has the same value as the primary key in Reptile. So add a field to Medical. Now make a relationship from Reptile::record Id to Medical::new field. Set the relationship to allow creation of new records in Medical via the relationship. Set up a portal in reptile with this relationship. There will be an empty portal row use this to create new records in Medical.

[LaRetta]

One small correction ... the ReptileID is the unique primary key of the Reptiles table. Justin, I would suggest that you change record_ID to something else. IDs refer to key fields and usually indicate an auto-enter serial number. record_ID isn't. Maybe call it ReptileCode?

BTW, your key fields should all be type number... as Ralph suggests, you must match same data type on both sides of a relationship. :^)

Ooops ... you have a Storage_ID as the unique ID of the Reptiles table? It is important that the primary key of a table is the same name of the table so you know it means it is its unique, serialized ID. So ReptileID would be primary key (unique, auto-enter serial) for Reptiles table and Medical_ID would be unique auto-enter serial for the Medical table.

When you add a field into Medical and call it ReptileID (type number) then what Ralph explains in the relational graph makes more sense ...

Reptiles::ReptileID = Medical::ReptileID

I highly encourage you to NOT use the record_ID but rather use internal serials to hold your relationships together. This external record_ID might be entered incorrectly and, if you later change it, you can break any relationships using it. By using a table's unique identifier (auto-enter serial), you never risk it breaking.

And when you see ReptileID in the Reptiles table, you know it is the PRIMARY key (or parent key). When you see ReptileID in any other table, then you know that it is the FOREIGN key (or child key).

[Thumper87]

Thank you Laretta, that really helps me from getting confused with the field names in my tables, i created the above relationship im having some troubles with it, im gonna play around with it tonight and see if i can get things working, ill post my results back here.