entering new fields in multiple databases

[irnha]

We are a non-profit with a membership database of almost 2000 records. We keep old members, current members & life members in this database. I have set up another database for deceased members, as we don't just want their data to disappear, but don't want them on the 'mailing' list anymore, either. Trouble is I add new fields to the membership database at least 3 times a year, ie, amt, method & date of payment for membership for the new year, and donations-we sent out two mailing to our members asking for donations each year, so I need amt & date for each of those, plus we work on specific projects each year, so the donations can go to different specific projects & those needs to be new fields as well.

My problem is importing a newly deceased member (with new fields) into the older 'deceased members' database that lacks all these new fields - and we want to keep that newer info on the deceased member.

I looked at relationships, but we delete the deceased member from the membership database when we enter them into 'deceased' which is the point of the two dbs, so I didn't know if that would work.

I have been here for almost 2 years, but this db was set up many years ago, but those who didn't think about how some of the data would be needed over time, so I am trying to fix the problems as I go. They used to just delete the deceased & then if others members wanted to know how to send condolences, etc, to the family, we had no record of them - very bad. This is my first FM database, at my last position, we used Access to manage a somewhat similar database, so I came in used to 'queries' instead of 'find', & still find myself having to think through what I am asking FM to give me. I'm sure there's easier ways, but that is for another discussion.

Thanks for your time. Any suggestion is welcome - would relationships work? If so, how?

[QuinTech]

Hi irnha, and welcome to FM Forums! I have two suggestions:

1. One way to approach this is to simply create the same fields in the Deceased file that you create in the Living file. Whenever you create a field in a database that already has records, that field (assuming it is not a calculation or an auto-enter) will be empty for the existing records. You can then safely import Living records into here and still preserve all data.

2. I would STRONGLY advise you not to maintain two separate files with the same structure and extremely similar populations. There will come a day when you will wish that you had put out your eyes instead of doing this. A better (but not necessarily the best) method of doing what you want is to create a field called "Living" in your main database and only send mail to those who have a checkmark in this field. As people die, rather than moving them to a separate DB, just uncheck this field.

HTH.

Jerry

[irnha]

Thanks QuinTech,

I currently only have 5 people in the deceased, so I will consider putting them back. But we currently have a hard enough time making sure that we mail to the life members & to those with a current expiration date (different fields of course). Plus we currently have 2 layouts for all the letters we send because we don't know how to prevent the extra space from appearing if for example a married couple does not have the same last name (we have individual & household membership, among others, so we have a first name & a joint first name, so dear <<first name>>, isn't always right for the letter.

The finds become very complicated in a hurry when mailing to all current members, I don't want us to forget the living box.

Thanks I will think it over - I was hoping there was a way to link the field names without linking the data, so to speak.

[QuinTech]

Well, there is always an exception to the rule.

You may find that it works better for you to maintain two databases, based on how you use your data. If so, my suggestion #2 is out the window. And that's fine. But bear in mind it does limit your ability to improve your database rapidly. If that's not an issue, everyone's happy.

As for the married name -- you might be able to get down to one layout if you just enter "Susan & John" as the first name whenever the membership type is "Family." This could possibly create other issues -- again, it gets us back into the tradeoff between database scalability, user expertise, and time available for development -- but it is a solution that COULD work. There are others ...

J

[lonntj]

We have a similar set-up, and instead of fields for each mailing, I have set up two related files to hold the mailing information. The first is a mailings database, where the information about each individual mailing is stored (number of pieces, design, letter choice, etc). The second is a joinfile, it's function is to connect the main DB (with customers) to the Mailings DB. It initially had only two fields, one that was related to the Properties DB, and one related to the mailings DB. But in your case you could easily add a few fields to track donations for each mailing. This way in the joinfile a record is created for each piece of mail sent out. Then you can put a portal in your main database that displays all the information you need for each individual. You can also use calculation fields to pull information accross from the Mailings database. Also, this solution works just fine with multiple databases of customers.

Lonn

[irnha]

thank you both,

Lonn, my biggest concern is should I fall off the planet or get hit by a bus I want the database to still be easy for my co-worker & boss to use. In addition to the deceased database I am keeping currently my co-worker is keeping a 'contacts' database with just mailing info on each person that orders products from us via the web, mail or phone & fill out slips for our raffle each summer. Once a year we mail to people in both dbs & I merge the two together to check for duplicates as many of our members order from us, or course. That in & of itself is a problem because data is not always entered the same in her db as in mine, so not all dup's are found by FileMaker, ie, St vs Saint, etc, but I am going to give her a list of the standards from the post office, which will help. When we used access at my last office, we had queries set up & many of the employees didn't understand that changing one could affect how others got their data, so I am worried getting too complicated here. I want those with no expertise to be able to work the database too.

J, what I am wondering about the two layouts is this, I actually have 2 first name fields, 'first name'-in almost every record, & 'joint first name' in those with non-married individuals, or those with different last names. We currently have a field set up, 'label name 1' to combine 'first name' & 'last name' so a letter can be Dear <<label name 1>> (this is because we have business contacts too & both first & last names are in 1 field, I'm thinking of changing that), then we have label name 2 for the joint people. Then their layout are Dear 1 & 2. What I want to know is if there is a way to set up a field to check for 'joint first name' & if so, put in the '&' sign & then the joint name, if not leave the & out. I'm guessing not, but I really don't know why it wouldn't, except that FM may be set up for business records & that wouldn't have 'joint' names.

I know that if I set up field names, that is basic enough for them to work with. When I started they did dataentry in a layout that only had names & addresses & the comment box & they added things like "didn't renew in 98" when the person joined again in 99, well that should be very obvious if you look at the dues paid fields (98 would be blank). This is what I'm dealing with.

Any help is again appreciated,

Jess

[QuinTech]

I've found there is no way to convince some people that change is a good thing. [sigh] Why do you need all these different fields when you can just put every scrap of information in the SAME field? Why make your data easy to find?

Sorry, i'll stop with the sarcasm.

Anyhow--yes, it is very simple to choose between the two fields. Make a calc field called, say, DisplayName, equal to:

Case ( IsEmpty ( Trim ( JointFirstName ) ) , FirstName , JointFirstName )

Use this field on your letter layout and you're all set.

(The "Trim" is in there as one small way of safeguarding against people who enter extraneous characters such as spaces in the JoinFirstName field.)

J

[irnha]

Again, I thank you!

You are very kind to read my very long question & respond & so quickly. I will try that & yes every now & then I do find extra spaces. But I have looked at the entire database in the first 10 mos or so I was here (when I had a chance to just look for errors vs doing my other tasks) & cleaned up many of those problems.

Thanks again,

Jess

[QuinTech]

Happy to help.

You didn't ask, but -- one thing i've often found in settings like yours is extra carriage returns. This was the bane of my existence for several years. In one egregious case, i actually created a script that ran upon opening, checking for a carriage return at the end of several likely fields and deleting it if it was there. Something like:

Replace Contents ( Name , Case ( Right ( Name , 1 ) <> "" , Name , Left ( Name , Length ( Name ) - 1 ) )

Replace Contents ( Address , Case ( Right ( Address , 1 ) <> "" , Address , Left ( Address , Length ( Address ) - 1 ) )

Make sure to check off the option for "Perform without dialog" so as not to freak people out.

J