Updating a text field each time a date field entered or changed

[davidcom]

I'm new to FM and to this forum. I hope this was the right place to put this question. I figured it would probably involve functions.

 

So here's my situation. I have a customer database that is continuously growing. Once a month I send a mailing to certain customers (but not all customers). I've created a check box for "Send maling" that I tick for customers who I want to send to. So at the end of the month, I do a search for all customers with a "Send mailing" check box ticked. I would like to find a way to keep track of the following:

 

The customer that was sent the mailing

The date at which the customer was sent the mailing

The history of dates that a given customer was sent a mailing (a sort of "Mailing history" field)

 

So let's say in August I send a mailing to John, Chris and Sally. I would like record that those three received a mailing in August. Well, simplistically I could just make a date field "Maling sent" and choose 8/31/13.

 

However, in September, the mailing list grows, this time sending again to the original three + two additional customers (Rob and Steve). So now if I change the date field to 9/30/13, the field will show that all 5 customers received a mailing in September, but I will lose trace of who received a mailing in August.

 

What I need is a way to be able to do a look up of customers who will be sent the mailing, enter the date of the mailing and have that date be added to a field that records all previous dates that each particular client received a previous mailing. I need the current mailing date to be applied to the whole group of customers in the current lookup.

 

So using my example names above, at the end of September the records for John, Chris and Sally would have "Mailing History" fields that note:

 

Mailing History:

8/31/13

9/30/13

 

And Rob and Steve would have their own fields that note: 

 

Mailing history:

9/30/13

 

Then at the end of October, the date 10/31/13 would be added to each customers "Mailing history" field, and so on.

 

I hope this is clear. An experienced FM user will probably have a better solution.

 

Thanks.

 

 

 

 

 

 

 

 

[doughemi]

You need a Mailings table. It should be related to Customers by Customers::id = Mailings::CustomerID.  Each record in Mailings would be a record of one mailing to one customer.  Fields would include customerID, mailing date, subject, plus whatever data you wish to track about that mailing to that customer.

 

You would then be able to create a portal to Mailings in a layout based on Customers.  All the records in Mailings pertaining to that customer would be shown in the portal.

[eos]

Doug –

 

what you describe is a CustomerMailings join table, only without the join part. I think a Mailings table would also be required, in which to store subject and date.

[LaRetta]

Well said, Doug.  Using a related table will also provide ability to look at your customer's sales history through their customer record and determine which specific mailings might be most profitable by comparing sales immediately after a mailing.  Ah, so many ways to manipulate and use data if organised properly!

 

Don't forget to include what was mailed in this new table.

 

And David, welcome to FMForums!  You've truly came to the best site for help.

Hi Oliver, good point but having the 'other side' (the Mailing side) isn't required although it can come in handy ... depends upon the need.

 

Sometimes its okay to duplicate information if limited particularly if the mailing or publication itself is its own unique identifier and if the mailing itself does not have additional information to track.

[davidcom]

Thanks to everyone for your input. LaRetta, thanks for the warm welcome.

 

I have to admit that the suggestions made a head explode a little. I've got some learning to do!

 

Speaking of which, can anyone suggest an online tutorial or video tutorial that will cover the elements discussed? (join tables, portals). Of course, knowing that I am new to all this, any other suggestions for learning how to implement what was suggested would be appreciated.

 

Thanks again!