Changing only 'uncompleted' field data/setting up a mailing campaign

[Starhawk]

I've created a database to keep track of a letter mailing campaign. I have 8 Fields for 8 letters set up as

Letter 1

Letter 2

and so on...

I put the start date I want to begin mailing in the field "Letter 1"

I then want to mail out the remaining Letters in two day increments, so I have a simple calculation set up to produce a date 2 days later from the previous letter.

Letter 1 12/31/07

Letter 2 01/02/08

and so on...

Now, I'd like to get a little more complicated, but Im not sure how to approach. When i have actually completed one of the mailings, I'd like to have some way to show that particular letter has been sent. Maybe a completed check box?

Then, if I'd ever want to postpone the campaign, I'm trying to figure out a way I could push out the remaining unsent letters by a certain amount of days.

For example, maybe I have

Letter 1 12/31/07 Completed

Letter 2 01/02/08 Completed

Letter 3 01/04/08

Letter 4 01/06/08

but I want to postpone Letter 3 (and subsequent letters) by (for example) 1 day. What would be a good way to go about doing this?

Im also trying to figure out a way for it to skip Sundays, but Im also searching previous threads to try to figure that out. If anyone has an idea of how to implement that as well, it would be greatly appreciated.

Thanks in advance for any assistance.

-Cameron

[LaRetta]

Welcome to FM Forums!

I have 8 Fields for 8 letters set up as

Letter 1

Letter 2

and so on...

I highly suggest you change your structure and make each letter a RECORD instead of a field.

All issues and problems mentioned can be eliminated with a good underlying structure. And what happens when/if you want to add ANOTHER letter? Make 'em records.

[Starhawk]

Thanks LaRetta, I am sure I am thinking about this the wrong way.

But each record is a complete profile of the person I am mailing to, and so I have fields for each letter and dates for when they are sent out for that particular person.

Can I still set up the individual letters as Records and then have them relate to the individual profiles?

[bcooney]

Creating a separate table for letters sounds like the right way to go, esp if you expect many different letters. You will need an identifier so that you can "pull" the right letter when you need it. If you only forsee 4 or 5 letters, you can stay with dedicated fields.

I've worked on a system that sent letters/email reminders. I would suggest a calculation field "LetterToSend" =

case

( isempty (Letter1SentDT), 1;

isempty (Letter2SentDT), 2)

and so on. Case statements resolve in order, so the first "true" that it hits will be your answer. I don't think you need a checkbox to show it's completed, rather store the date it was sent/printed (your send/print script would stamp this date).

So now you calc which letter is "on bat," so to speak. Now you need to figure out if today's the day! Have a calc field, NextLetterDue, result date. It would be LetterStartDate + yourPref #of days btw letters. In this calc you could further massage the date so that it doesn't fall on a weekend. Look to B. Dunning's custom function site for NextWorkDay type of cfs.

Then, the script that does it all. You'll probably end up with something like:

Go to Clients and find any with a NextLetterDue <= today (in case you were on vacation and didn't run the script).

Loop thru these clients, sending/printing the appropriate letter according to the LetterToSend field.

Mark the Letter1SentDT and its cousins as you print/send.

End loop.

[bcooney]

But each record is a complete profile of the person I am mailing to, and so I have fields for each letter and dates for when they are sent out for that particular person.

Can I still set up the individual letters as Records and then have them relate to the individual profiles?

I would suggest, then a text calc field in the Client table that creates the letter. It can pull data from several fields to "write" the letter (some boiler plate text, some data from the client record). Then you use this calc field to send/print. The boiler plate text can be stored in a separate table. It depends on how many chunks of text repeat from letter to letter, and how many variations you foresee.

[LaRetta]

Can I still set up the individual letters as Records and then have them relate to the individual profiles?

Yes. When designing, asking yourself this question, "Will I have MANY of ___ for ONE of ___?" Usually, if the answer is yes, it suggests a related table. Using relationships in your solution makes it much easier, it does NOT complicate it as it might appear upon first glance.

Use an ID (always) in your tables.

PersonID would be an auto-enter serial (see under auto-enter tab). Then your LETTERS table would have a field called PersonID in which you PUT that serial when you create a letter for that person.

[Starhawk]

Thank you very much! I will work on getting this set up and report back when I cant get any further.

[Fenton]

I would suggest that when you start a "campaign" for a person, you create the multiple "Person's letters" via a script, in a table; use the PersonID to tie them. You would set the "Date to send" by incrementing a date field for each record. You would also have a "Date Sent" field, which could be populated (if empty) when a script prints the letter.

Basically, if Get (CurrentDate) ≥ DateToSend and DateSent is empty, that letter is ready to send. You would Find for those two criteria.

Set Error Capture ["On"]

Go to Layout [ PeoplesLetters ]

Enter Find Mode

Set Field [ DateToSend; Get (CurrentDate) ]

Set Field [ DateSent; "=" ]

Perform Find

If [ Get (LastError) = 0 ]

Print

Replace [ DateSent; Get (CurrentDate) ]

End If

Go To Layout [ Original ]

You may want a preview step also.

This is a simple Parent-Child relationship structure. The letter contents itself would likely come from a Letters_ref "reference" table, which would have the boilerplate text. If you need to add fields from the Person into the form, this can be done, as bcooney says, using an unstored text calculation field in PersonsLetters. If you have multiple types of letters, then an ID relationship to the Letters_ref table could reference the specified text you want.

In other words, the calculation would look at Person to get the person's info, and to Letters_ref to get the other text. Unless you're actually going to type into these letters, in which case you would Lookup the text into PersonsLetters.

Edited December 31, 2007 by Guest

[Starhawk]

I now see how Novice my original question was!

I now have structured my database so there is a Table for PROFILES and a Table for LETTERS with a joined table in between called 'LINES'. Everything has a serial and the relationships are created via these serials. In my LINES Table, I can see each time a letter gets mailed out, to whom, and when. I am assuming this will come in handy when I start to create a report. Now, I am trying to structure this a little differently and I am not sure if I need a Script or if I should create more tables and relationships.

I'd now like to create a table that is called CAMPAIGNS. Each Campaign would consist of multiple letters. I'd like to have a drop down list on the Profile's layout to show all campaigns, and then when one is selected, all of the related letters show up. I'd also like to preserve the ability to select letters to send on an individual basis rather than by campaigns.

It seems I may be able to leave the relationships the way they are now, and create a script to for each campaign and the script will pick and choose the appropriate letters. On the other hand, it also seems I may be able to set up more tables related to one another. PROFILES to CAMPAIGNS to LETTERS? These are all many to many and I am unsure of how to set up the join tables in between. PROFILES to JOIN TABLE to CAMPAINGS to JOIN TABLE to LETTERS? And will this preserve the individual letter choosing I currently have?

Thanks again for any help. I have not tried any of the calculations/scripts recommended here yet as I am still setting up the structure. Each time I run into a snag feels like the ultimate brain teaser :

-Cameron

Edited January 3, 2008 by Guest

[Fenton]

I think you need another table for Campaigns. Because a "campaign" consists of multiple letters, and any given letter may be part of different campaigns. Actually, you'd need 2 more tables, Campaigns, with just the name and ID of a Campaign, and a join table, CampaignLetters.

In this table each Campaign would have multiple Letters (both IDs only), likely in some order, likely with a "# of days" between each (not dates; these are ongoing). Then you're pretty much ready. I believe the FileMaker application "Donations" has something like this.

You'd probably want to "enroll" a person for a campaign, by putting the campaign ID in their record; or by creating another "person's campaign history table" (maybe not really necessary, as you could get this info from their "letters sent" history table, which would look up a campaign ID (if appropriate). I assuming that a given person is only going to part of 1 campaign, at a time anyway.

Edited January 4, 2008 by Guest

[Starhawk]

Thanks Fenton!

When I set up the CampaignLetters joined table, am I creating one record per CampaignID and multiple fields under it for 'Letter 1', 'Letter 2" and so on? Or do the opposite and have each letter a separate record and the campaingID attached to each one?

Will these 2 additional tables relate to my current joined table (between the PROFILE table and LETTERS table) in any way?

Thank you again for answering my novice questions.

-Cameron

[Fenton]

"When I set up the CampaignLetters joined table, am I creating one record per CampaignID and multiple fields under it for 'Letter 1', 'Letter 2" and so on? Or do the opposite and have each letter a separate record and the campaingID attached to each one?"

The opposite, a separate table for "Letters_Ref" (letter templates). Never create multiple fields for multiple instances, unless there's only very few and you know exactly how many there will EVER be; and usually not even then. The power of a relational database is in its multiple tables and records. Flattening a relational database is sort of like chopping off toes.

"Will these 2 additional tables relate to my current joined table (between the PROFILE table and LETTERS table) in any way?"

Well, that depends. Yes, almost all text for a non-edited letter could be referenced, from a Letters table, back to its Letters_Ref template, with only a few "fake merge" placeholders replaced by current dynamic data. In which case the Letter_Ref ID would be critical, and connected via relationship.

Or the letter template could be Looked Up into a Letters record, as text. Then you could edit it directly. It would no longer be tied to its template; though it came from there via a relationship; and you'd likely want the connection for its own sake, for the title/kind of letter. The original reference IDs are also very useful for such things are sorting and reports.

[Starhawk]

Now a question back to postponing the campaign.

In my joined table between the profiles and letters, I have fields of

ProfileID

LetterID

DatetoSend

DateSent

Each profile will receive about 8 letters so there are about 8 lines per ProfileID scheduled.

I'd like a script that basically said "If DateSent is blank, add [number of days to postpone] to DatetoSend".

I would also like the option to do this for all profiles that are currently being sent letters (all the profileIDs in the joined table) and an option to just postpone a particular ProfileID's letter schedule.

My best guess at the moment would be to perform a FIND either all the profileIDs or just particular ones and then REPLACE the DatetoSend with DatetoSend + [number of days to postpone]. Just wanted to get some advice before I screw anything up.

[Fenton]

Yes, that sounds about right. Go to the ProfileLetters join table. Find records you want: I would think those with blank DateSent. Then Replace, by calculation, DateToSend + number of days.

Warning: always Save a Copy of your database before trying Replace. There is no "undo".

I think you should also have the CampaignID in that table. How else are going to isolate the found set to postpone? How are you going to track a campaign? When you create the letters for a campaign you should be entering the CampaignID into the records, likely via a looping script (for the x number of letters per campaign).

Edited January 11, 2008 by Guest
Save a Copy