I'm writing some software to keep track of entries in a Dog Show.
I have a Trial table, which is set up as follows. The Primary Key is the second column, (pkTrial) because I'm sorting by Event. Just to keep it straight in my own head. fkEvent is the Foregin Key to the Event Table's Primary Key. Each trial is created one at a time, as the user decides how many trials will exist in the Event.
Then I create a record in the Run Table as each dog is entered into a Run for each Trial. Right now I'm just having a Script automatically enter a dog into four trials (works okay for now).
What I need to do, is I need to Create a record in the Run Table for the Current Event (which works fine as I'm using a global field), and for Trials 1 through 4 (Trial Number above). But, the value in pkTrial above, needs to be inserted into fkTrial below, in order to keep the tables synced correctly. So if I want to add a dog into Event 9, Trial Number 1, from above, I need to read the value (5) from the field pkTrial above (TrialTable), and insert it into the fkTrial field below (in the RunTable).
My question is this... how do I get the value of pkTrial from the Trial Table where fkEvent equals a certain thing, and TrialNumber equals a certain thing? And then insert it into my RunTable?
I'm very new at Filemaker and am still learning a lot.
I can post the script I've been playing around with. I've tried using a Find, but that doesn't seem to work in the script. I've tried switching to a layout that is based on the RunTable, but I'm not sure how to snag the field from the exact record that I want. If I try to set a Variable, it just sets one field value, not the one I need to specify. I've looked at the ExcecuteSQL option but I am not seeming to get that to work either.
I'd appreciate any help, thanks!