Value list containing customer surnames from another related table

[db_tragic]

Novice here.

Designing a simple database containing info about horse owners (one owner may have several horses), and their horses. I want to be able to set a field in the 'Horse Details' table, that contains the owners surname, from the 'Owner Details' table, where I can select an already existing owner surname from a drop down list. ie adding a new or additional horse against a current owner.

This is to be performed on the iPad.

Just not sure how to set up the field correctly in the 'Horse Details' table (and any considerations I may need to make in setting up the related field in the 'Owner' table.

Any advice appreciated.

[LaRetta]

Hi! Welcome!

If a horse can have only one owner then you place the OwnerID (unique auto-enter serial or UUID) in the Horse Details table. Create a value list of Owners (all values). In left pane, select OwnerID and in right pane select Owner name. Below specify only show second value. Then place the Horses::OwnerID field with POP-UP.

In the graph, connect Owners::OwnerID = Horses::OwnerID

Now when you assign the Owner to a horse, all related information is available and, although it displays the Owner name, it inserts the ID. Just place any owner fields directly onto your Horses layouts. If this is not what you need, just speak up and we can help you further. :-)

[db_tragic]

Thanks for that LaRetta,

That explains things nicely. Main issue was figuring out how to get the current list of owners to display in a drop-down list on the Horse Details page - all good now.

Now, to extend the relationship question further:

Another table contains notes about a horse's treatment (Visit Notes), and a horse can have several treatments(ie one to many). I'm able to associate the related 'Visit Notes' to the relevant horse - no issues there, but am deciding whether I should also create a relationship between a given horse's Visit Notes, and the Owner. Given I will normally be viewing a horse's Visit Notes in the context of a chosen horse, and not normally in the context of the owner, is it still worth establishing a relationship?

My normal workflow would be to find the Owner, select a horse from the list of horses owned, and then go to the selected horse's visit notes. (Alternatively, I could just search for a particular horse, and go straight to the Visit Notes from there - a few options here). Either way, at this stage, I am unable to come up with a 'question' of the data, that would require me to view the Visit Notes, as related to the Owner - the Visit Notes would always have to be viewed in relation to the horse they applied to.

Any thoughts?

[LaRetta]

Actually, if I understand correctly, you already have the necessary relationship to display a horse's (in fact all horses belonging to an Owner) visits right on the Owner's layout. Base the portal on Visit Notes. But in general no need to add table occurrence ahead of time - add only as needed. :-)

[db_tragic]

Thanks LaRetta,

I figured it probably wasn't necessary, but always worth asking! :grad: