solutions wanted Membership Database

[123mal]

I am trying to write a membership database to look after my school alumni organisation. All fields are held on one table. I have two problems I am struggling to resolve -

1. One field, Deceased, has a pop up menu. I would like it so that when 'Deceased' is selected from the value list for this field that 3 other fields are then emptied and replaced with the word 'Deceased'. The other 3 fields also have pop up menus, but the value lists are different although they all contain the word 'Deceased'.

2. I have another field called Sub Amount which is numeric. When an amount is entered, i.e. the value in the field is > 0, into this field I would like the Membership Status field to be cleared and the word 'Current' to appear.

Can anyone help me here? The alumni organisation is a charitable organisation that raises money for scholarships, etc.

Thanks

Malcolm

[comment]

Both are possible by setting the fields to auto-enter a calculated value, replacing existing value.

However, there is something fundamentally wrong here: the fact that a person is deceased should be entered and held in one field only (preferably a Date field). No other fact about the person should require modification. If you have two fields storing "Deceased", you have a redundancy and a potential conflict.

If you need to display different information based on a person's Deceased status, use either a calculation field or conditional formatting.

[123mal]

Thank you for your advice. I take you point about Deceased status and will amend my database accordingly.

As with a great deal of software these days it comes without a manual and the online help is, well, not that helpful. I am struggling to come to terms or find any good information with calculated values/fields.

I simple need to be able to write something that has the effect that -

if field 'Deceased' = "Yes" then clear field 'Subs Status' and insert "Expired"

or

if field 'Subs Paid' > "0" then clear field 'Subs Status' and insert "Current"

I know it will be very straightforward but have searched the internet and tried to work it out myself to no avail! It is so frustrating.

[comment]

it comes without a manual and the online help is, well, not that helpful.

AFAIK, there is no manual and the online help is it.

I simple need to be able to write something that has the effect that -

if field 'Deceased' = "Yes" then clear field 'Subs Status' and insert "Expired"

As I said , this is possible by setting the Subs Status field to auto-enter a calculated value, replacing existing value. The technique is described here:

http://www.filemaker.com/11help/html/create_db.8.18.html#1029444

The calculation itself could be =

If ( Deceased = "Yes" ; "Expired" ; Subs Status )

However, I still think it's not a good idea.

[123mal]

Thanks. I did read that bit from the FileMaker website. I must be thick!

I'll give it a go and see if I can make it work. I'll also have another look at the 'Deceased' issue.

Malcolm

[123mal]

I've cracked it and everything has fallen into place. We had a problem with mail being sent to the address of deceased members so rather than auto enter deceased in the Subs Status field when Deceased = Yes i have emptied the address field.

The world is now a brighter place - thank you and Happy Christmas.

[comment]

when Deceased = Yes i have emptied the address field

If data management were a religion, you would now have a mortal sin upon your conscience. Consider:

1. User needs to mark a member as deceased. She mistakenly selects the wrong member when doing so. She catches her mistake - but that member's address has already vanished into thin air.

2. The boss has a bright idea to send a letter to the deceased members' families on the first anniversary of their death. She has to hear from her data person that no dates of death are on record and furthermore, all addresses of the deceased members have been "emptied".

Why not simply perform a find before sending mail? Repent! Repent!!

[123mal]

Point taken, although the scenario of us needing to 'contact the dead' or their relatives are extremely unlikely. Secondly back-ups of the database are taken on a daily basis using Time Machine so the information is never fully wiped.

If I could trust other users of the database not to write to the deceased, your method (which us the one I would use) would be fine. Unfortunately other users are less thorough and I have got sick of having to make (quite rightly) grovelling apologies to the families of deceased members when they receive a letter from us.

Perhaps my next task is to learn how to develop a button runs a script to produce a report that sorts only living members whose subs status is current and for whom we have an address and then prints an Avery label run. I will then have a look at the security options to tie down what other users can do so that I can avoid making these awkward phone calls.

I am getting there and your advice and help is very much appreciated. Whilst I redevelop the database the old one is still in use and the new one will not go on line until I am happy with it. The new one will take on board much of your very helpful advice.

Once again many thanks.

[comment]

Perhaps my next task is to learn how to develop a button runs a script to produce a report that sorts only living members ...

Most certainly. You should not have to "trust other users" and depend on their thoroughness.

[123mal]

I'm on the case. I'll see if I can manage without your help - fingers crossed. :B