need help with related records

[denno]

I am creating a database for tracking speakers at a lecture series. I also want to be able to send a letter to the speaker after the lecture is done.

I have a table named Speaker Info that has the fields:

Name, Lecture date, etc.

I have a button on a layout that displays the speaker info fields to fire a script named "send final letter".

I have a separate table named Letters that has a layout for the letter.

The 2 tables are related by speaker name & date.

I want the "send final letter" script to switch to the letter layout and drop in the speaker name & date but I can't seem to get this to work.

Thanks for any help...

[bcooney]

I would probably structure the system a bit differently. I'd have the following tables:

Speakers

Lectures

Events

Letters

Events is a join table between Speakers and Lectures. I'm assuming, and could easily be wrong, that you have a "Lecture Library" that may be given by many different speakers over time. Therefore, to capture the instance of a given Lecture by a certain Speaker, I'd use the join table. It would store the SpeakerID and the LectureID.

The Events table would also have the following fields:

Event Date

StartTime

EndTime

Location

flag_Complete

FinalLetterSent_dt - calc thru relationship to Letters by EventID

When the user clicks to record the event as complete, the script could ask if you'd want to send the speaker the final letter. This script would look to the Lectures table for the text of the Final Letter, or to a Templates table, or to a global text field in preferences. Then it would create a new record in Letters, inserting the SpeakerID and EventID for the Letter.

In this way, the Letters could be shown on the Speaker's form view in a portal of Letters sent to this speaker. Also, it could be displayed in a portal on the Event form.

If you want, you could create more than one Final Letter at a time by viewing the Event List view, clicking a button that finds all Events that are complete, but that do not have a FinalLetterSent (ie no relationship to Letters), and then looping thru the found set, creating letters.

Let me know how this sounds to you.

[denno]

Thanks-I don't need it to be quite as detailed as that.

I got some ideas from your reply & modified the tables somewhat (file is attached)--

1. speaker database

2. lecture info

3. letters

I'm still having a problem figuring out how to create a letter to a specific speaker. The lecture info & letters tables are related by name & date. If I manually enter the speaker & date on the letter the rest of the fields populate automatically. I'm trying to figure out if there's a way to have the name & lecture date fill in also. I tried using Set Field but that didn't do anything.

Thanks-

[bcooney]

Tables should not be related by name and date, but rather by IDs (key fields).

When you create a letter, putting the foreign key of SpeakerID and EventID will let you bring any data from those two tables into the letter.

How do you get the IDs and put them in the letter record? Use local variables.

1. User clicks button to mark event as completed.

2. Script "EventCompleted" starts.

3. It captures $SpeakerID and $EventID using Set Variable script step.

4. It changes to a letter layout.

5. New Record in Letters.

6. Using Set Field it writes $SpeakerID and $EventID to their respective foreign key fields in Letters (_kF_SpeakerID and _kF_EventID).

Edited August 20, 2007 by Guest
Added pseudo-code for script

[denno]

Thanks so much for your ongoing help; unfortunately variables & keys are totally foreign (no pun intended) to me.

Do you know of any tutorials out there on how to use them?

Your step by step is helpful but I'm afraid I need more hand-holding :

[Fenton]

A script variable is defined by a script step, Set Variable. Look it up in the FileMaker Help, in the Script steps section. You will get a clear definition and a few examples.

A "foreign key" just means an ID field used in another table, not its own "native" table, and also implies that it is used in a relationship (most ID fields are). For example an EventID would be the "primary key" of an Events table. But anywhere else it would be a "foreign key" (like a tourist :-). The term "key" just implies that a field is used in a relationship.

While these are technical terms, they are pretty common, not particularly FileMaker. We have to use words to communicate and these are the right words.

[denno]

disregard--i think i got it working!!

Thanks to both of you for the help!

Edited August 21, 2007 by Guest