Have a database with 3 main tables:

1. Horse owner

2. Horse details

3. Visit Notes

 

As one owner may have several horses, I have set up a simple portal on the Horse Owner layout which shows the 'Horse Name', and 'Date Last Modified' fields from the related Horse Details table.

 

Obviously for a new client, the portal window is blank (no horse/s recorded yet). 

 

I want to put a simple button in the Horse Owner layout that switches to the Horse Details layout, with a new record created and ready to fill in - and related to the currently open Horse Owner.

 

I'm not sure on the correct button set-up details to make this happen.

 

Thanks in advance

 

Chris

Set it to perform a script:

Set Variable[$owner; Horse Owner::ownerID]
Go to Layout[Horse Details]
New Record/Request
Set Field[Horse Details::ownerID; $owner]

May I suggest that you rename your tables: People, Horses, Activities, Roles and add a join table PeoHorses. Just guessing, but wouldn't it be nice to record all the people associated with a horse (Owner, Trainer, Jockey, etc.)?

Still new to scripting, but does make sense. Did try it though, and it did open the correct layout to a new record, however wasn't connected with the related (or any) horse owner. 

 

Not sure if that's to do with the script itself, or the way I've set up the relationships (primary key in 'Horse Owner' table paired with foreign key in Horse Details table - Horse Details table also has it's own primary key).

 

Thanks,

Chris

 

 

Set it to perform a script:

Set Variable[$owner; Horse Owner::ownerID]
Go to Layout[Horse Details]
New Record/Request
Set Field[Horse Details::ownerID; $owner]

 

May I suggest that you rename your tables: People, Horses, Activities, Roles and add a join table PeoHorses. Just guessing, but wouldn't it be nice to record all the people associated with a horse (Owner, Trainer, Jockey, etc.)?

Thankyou for your reply, however, the extra associations with a horse aren't really necessary. The 'Horse Owner' is principally representative of the person I will be billing for the visit.

 

Not all horses are racehorses, in which case they only have one person (owner/rider) associated with them, and where they are a syndicate-owned racehorse, I still only record the 'owner' details as being those of the trainer who is responsible for the horse, as it is he who is the primary contact for the horse, and is the one who gets billed.

 

Thanks for your thoughts though.

Regards,

Chris

 In the script, Horse Owner::ownerID should be your primary key field in Owners; Horse Details::ownerID should be the foreign key field in Horse Details.  

 In the script, Horse Owner::ownerID should be your primary key field in Owners; Horse Details::ownerID should be the foreign key field in Horse Details.  

Sorted -  the script editor inserted quotes around the Horse Details::ownerID phrase, so I was getting that literal text in the field, and not the value.

 

If I want the last name of the Owner to be auto-filled into the corresponding field within the Horse Details table during this process (ie create new related Horse), should I

1. include that step in this script, or

2. just use the Inspector/Data tab, and set the "Display Data From" and choose the Horse Owner::Owner_LastName field 

 

Cheers

Chris

Neither.  Just place a field Horse Owner::name on a Horse Details layout.  That's the beauty of a relational database: you don't have to have (or want, for that matter) the same data in two different places.

 

Besides, somewhere down the trail (   ), a horse is going to be sold, and you will only have to change one piece of data in the Horse Details table, not two or more.

Isn't that number 2? I was impressed that Chris got that. :-)

Yeah, it sure is.  I am not used to the niceties of FM12's inspector.  I'm still stuck in "drag the field tool and Specify Field" mode.

Option 2 it is! (Had in fact already used that option, but was just checking if there was an alternative worth considering).

 

Really appreciate the time you more experienced users put in here.

 

This won't by any means be my last call for help, to take it as a BIG thankyou in advance! 

 

Cheers,

Chris