Setting up fields

[nfregistrar]

I do registrations for a summer boy's camp. I have a number of related files. One file (History) keeps data on campers: Address,age,when they went to camp, church affiliation etc. Each record is a boy. Another file (Camp)is used to enter registration data for the current year. Each record is a boy registered for that year. It relates to the History file (via Name, Relationship is called History)for age,address, church etc. The church field is the problem. A boy may be affiliated with a church or may not. Also some of the churches have a unit number associated with them because they have a boys group in their church. Thus 3 possibilities: Boy with church with unit no., boy with church with no unit number, boy not with church.

I have a third file (Units)that has unit numbers and the church that each is associated with. There are over 250 entries) The Camp file form that I use for registrating has (amoung others) a Unit and Church field. The Unit field is the History Unit field and can be changed in the Camp form when a boys registers if the registration form shows that the boy's affiliation has changed, or added for a new boy. I want the Church field to be either a lookup from the Units file(related by Unit no.,Relationship is called Units) or be the church entry in History or be left blank if there is no affiliation or be able to be typed in for a new boys with no unit no. I have tried using the calculation: Church=If(Unit<>"",Units::Church,If(History::Church<>"" ,History::Church,"")) If it is a straight calculation then I can't change it in the Camp file. If it is a Text Calculation, I can at least change it or enter in church data (for new or changed non unit churches) but if the Unit field is changed, it doesn't redo the calculation if there is already data in the field. Also I don't know how to get the Church field automatically updated (for the non unit church case) in the History file from the Camp file. There is no set number or know list of nonunit churches so it is not practical to issue or know phony unit numbers.