Help with Auto Entry of a Field

[JenNicole]

Hello all and thanks for any help you can give. I am working on a FM database that I am creating. I am still new but a fast learner so again any help is appreciated. Here is my scenario:

I want field 'X' from table 'A' to be automatically entered from the results of field 'Y' from table 'B'.

In other words: Upon entering the data "DUES" in field 'X' of table 'A'..... I want field 'Y' of table 'B' to be automatically filled with data "CURRENT"...

Thanks again...

[bcooney]

It's actually harder to answer when you abstract out all table names and fields. Can you restate this in more specific terms.

[JenNicole]

Ok...........here we go:

when entering a payment into the database......

ntapay2::Payment For = "Dues"

then (autofill)

ntanames2::Expire Date = "Current"

I hope this explains it a little better. I appreciate your help in this matter!

[bcooney]

Anytime I deal with memberships, I have this structure:

A member has a membership expiration date. During the renewal process, I set that date (usually date of renewal plus one year).

Then, the member's status is a calculated field that looks to see if the member's expiration date is greater than today. If so, they are current.

I can then Find all members who are due to expire soon, etc.

On the other hand, and less straighforward, imho, you could look from the member record and see if there are any balances due and calc their status from there.

Other ideas, anyone?

[JenNicole]

Actually we have an expiration date! However, you cannot say greater than today simply because they set for the end of each month and some may send in payment that already have an expiration date 2 years from now, but it will need to add onto this date.

I had originally thought to make the Payment For and Expiration Date tied together, then pull the Status in from there.

I just noticed I had told you it was ntapay2::Payment For and ntanames2::Expire Date when in reality I was speaking of the following:

ntapay2::Payment For = "Dues"

then (autofill)

ntanames2::Status = "Current"

This may make a little more sense now!

[bcooney]

It does sound like for each renewal, you should have a payment associated with it, so if it's empty, then they are not current.

So, their subscription record looks like:

Date_Start

Date_End

Date_Paid

Amount

Method, etc.

[JenNicole]

This is correct, but we do also accept payments for Contributions and Merchandise purchases. Therefore, their status could very well be blank unless it pertains to a Dues payment.

We have Donor Types in the database as well. They can be a 'Donor', 'Member', 'Junior Member' or even a 'Lifetime'.

Before I get to into detail, I figure if I can get one field to auto-complete upon entry of another I will be doing great!

Thanks..........hope this helps to better understand what I am looking at. :laugh:

[bcooney]

I'd create a table of payment types (Contribution, Dues, Merchandise). Then create a value list from this table and apply it to each payment record.

Your member record will have a calc field that looks into the payments table relating by memberid and paymentTypeID (the one for Dues) to specifically see just Dues payments. If the last payment record for Dues is not paid, then they owe money. I understand that members still receive subscriptions, even if they haven't paid. So, that's a separate thing to handle.

You're not autofilling a field in one table when a field in a second table changes. You have a calc field in one table that relies on data in a related table. In your case, a member's status is calculated by looking at a payment table.

[JenNicole]

I will have to see what I can do with this then. I am dealing with a pre-existing database created by a person employed previously. The only problem I seem to be running into is the fact that is was written in C# and SQL. There seems to be some unexplainable coding in there that is almost unnecessary or will seem to block things. I don't know, I have cracking it slowly.............very slowly..........

Thanks again!..........have a great weekend! :waytogo:

Edited October 24, 2008 by Guest