Updating Found Records - maybe

[Sara H]

Hi everyone,

I'm sure this is easy to do and I'm just brain-blocked, but here's what I need help with. If I do a find and select certain records in my database (constituents) and then send them an email, I would like to be able to quickly and easily mark their records that they received that email.

My database is set up with a constituents table and an events table. It's the events table that should be updated to indicate the new activity.

I've been trying to do it through a portal using the Replace Records function but I realized after the fact (playing in a non-live version of the database) that wasn't what I wanted.

Can someone please point me in the right direction?

Thanks!

Sara

[bcooney]

It would be helpful to know how you are sending the emails to the constituents. Are you looping thru the found constituent records and sending an individual email using FM's Send Mail script step?

You say, "the events table should be updated to indicate the new activity." What do you mean by that? I would imagine that the event can have many activities related to it. Are activities another table? Are you looking to create a new activity that is related to its parent event that logs the sending of the email? Does each constituent also get an activity record logging the sending of the email?

Lots of unanswered questions for you to ponder. Perhaps you haven't created enough tables...

[Sara H]

Thanks for responding, and for the good questions.

The emails are sent using Constant Contact, a program external of FM. (I have a script built that gathers all the email addresses and exports them to Excel, and then they're uploaded into CC.)

In this example, I would just want to make a note in the Events table that the email went out to the Constituents (another table) who received it. That's what I meant by activity. I used a wrong word.

In this case, Events are singular things. There are many Events, but Events don't have related activities (that we care to track).

I am, to paraphrase your question, looking to create a new Event that logs the sending of the email to the Constituents who receive it.

Did this help or muddy things further? I do appreciate the assistance.

Sara

[bcooney]

OK, let's see. You need an Event record that is related to the Constituents that received the email, right?

However, can Constituents be related to more than one event?

If so, you need a new table that resolves the many to many relationship btw Events and Constituents. This is called a join table. In your case, it would have the EventID and ConstituentID. You could name it EvCon, unless a better name makes more sense to you.

Once you have your found set of Constituents, create an Event record, capture its ID, and loop thru the Constituents, creating an EvCon record and setting the EventID and Constituent ID. Then export the constituent email addresses.

[Sara H]

Thanks for walking me through this.

Okay, I set up a new join table that connects my Events with my Constituents. I named the table EvCon and assigned each event an ID number that appears in the EvCon table.

But I'm stuck again... Let's say I create a new "event" which is actually an email solicitation. I want to add this new Event to all the members who have email. Obviously I know how to find those members, but how do I quickly attach this event to them?

I apologize if I'm being dense.

Sara

[bcooney]

You're not being dense, what you want to do requires, imho, intermediate fm skills.

I'll try to put together a demo, if time permits, but in English here's what you need to script:

1. Find the constituents who will receive the email.

2. Create a new window.

3. Go to a layout based on an Event TO. (Your Event entry layout, probably).

4. Create a new Event. Pause perhaps for user entry or use Set Fields to enter relevant data (such as purpose, email content, dates).

5. Capture the event's ID in a variable, $EventID.

6. Close the new window with a button on the Event layout that Resumes the script. You are back at your Constituents (list).

7. Start a loop.

Go to Record [first]

Loop

Capture the Constituent ID of the current record into a variable, $ConID.

Go to layout EvCon

New Record

Set Field (EventID, $EventID)

Set Field (ConstituentID, $ConID)

Go to Layout (Constituents)

Go to Record Next (exit after last)

8. Export the email addresses out to the other software.

You now have an EvCon record for each constituent. From the Event form layout you could create a portal to EvCon, showing a list of all constituents who received this event email.

From the Constituents form layout, you could create a portal to EvCon by ConstituentID that shows all the events this constituent relates to.

[Sara H]

Thanks for your continuing help with this.

I tried writing up the script as you described. I thought I understood it, but ended up in a big ol' loop, so clearly I did something wrong. Also, I cannot figure out how to set the "Exit after last" part.

I realize I'm going to need some in-depth help here, so I went looking for any sort of 'local' training. No such luck. Apparently Iowa is a wasteland when it comes to FileMaker. There are classes in Chicago but the hotel would cost more than the class -)

I guess this will be a trial and error learning experience!

[bcooney]

FM offers self-paced training materials that are very good. Also, there are excellent books out there. Don't underestimate the free help to be found here at fmforums.

Here's a demo. It's very rough on interface, but functional. This is not the only way to go about your task. I've chosen to create an event from the Constituent List. You could also create an event, and then gather the constituents.

Topic_194146.zip

[Sara H]

Thanks for the help. I've tried putting your script into my database and changing the names to match my fields as required... it seems to do something (i.e., it's clearly moving through the records) but in the end I still have no new event records added. So obviously I've got something goofy.

I've spent the morning ordering more books and looking at online training resources. So between them and very patient you, maybe I'll actually figure this out someday!

Thanks again for the help.

[SurferNate]

Hi Sarah,

You might also look at FilemakerMagazine.com. Matt there does some very nice tutorials, that go fairly in depth as to things you can do with FM. I finally signed up for the membership and it's pretty cheap considering the time saved and things learned. Nightwing (www.nightwing.com.au/FileMaker/) is another place to go and Database Pros (www.databasepros.com) has a wealth of knowledge to absorb too. There are many others, these are just the ones I personally have benefitted from th most over time.

[Sara H]

Thanks, Nate! Hopefully my skills will grow to match all these resources.

Sara