Filling in information from another table

[IRETH]

I have a form that includes a variety of information that is recorded on intake. There are two fields that are unique, one is DistrictID and the other is StudentId. Among others, the fields that I am interested in transferring to another table are the DistrictAddress1, DistrictAddress2, DistrictCity, DistrictState, and DistrictZip.

In a second table I have Billing Information. Included in this table are StudentID, DistrictID, DistrictAddress, DistrictCity, DistrictState and DistrictZip. I want to be able to have the fields populate automatically when I enter in the StudentID.

I set up a relationship based upon the StudentID, but I just can't get the information to enter into the fields in Billing automatically when I type in the StudentID.

Can anyone help?

 

[JeanW]

What you need here is that your fields, other than StudentID, are lookups.

In fielddefinition, make those fields lookups, based upon the relationship StudentID = StudentID.

This way you can populate the fields with the data based upon the used relationship.

Fill in StudentID, DistrictID is a lookup field to your table where the initial data is, based upon StudentID.

The value StudentID will be = to the value StudentID in your first table, hence the relationship has a matching value, the lookup will be triggered, DistrictID will be the same value in your billing table.

For a billing table, be sure to use a lookup for your fields other than StudentID, to have always the latest values. Otherwise your historical data will be wrong...

 

[IRETH]

Yes, I did that; however, the information does not copy over. Honestly, I must admit I'm stumped. It seems a simple thing.

How do I assure that fields always have the latest value?

 

[JeanW]

I suppose you have 2 tables: Students and Billing.

The main table is Student with:
studentID
DistrictAddress1
DistrictAddress2
DistrictCity
DistrictState
DistrictZip
Other

The second table is Billing with:
studentID
DistrictAddress1
DistrictAddress2
DistrictCity
DistrictState
DistrictZip
Other

Make a relationship 'StudentID' between table Student and table Billing.

Define your fields,
DistrictAddress1
DistrictAddress2
DistrictCity
DistrictState
DistrictZip

in the Billing table as AutoEnter lookups, based upon the StudentID relationshíp, starting with table Billing, lookup from related table Student.
In the left pane you will have the fieldlabels from the Student table, make your choice for each field (not the studentID, because this the keyfield)...

Make a new record in Billing, fill in an existing value, and the lookup fields will give you the values from the Student table.

Why a lookup and not just a related field ?
Suppose a student is moving to another address/city.
You change the value for the address in the Student table.
All the related fields will change also, which will break the history.
A lookup will always take the existing value through the relationship, it will only take the new data when you make a new record, leaving the 'older' data unchanged.(unless you change the trigger field, studentID in this case, but there is no reason to do that)

See what a change in price would do to invoices, should the unitprice be a related field and no lookup. All the older invoices will reflect the new price, hence make another final amount on older invoices. Result: angry accountant and happy tax office....

HTH

 

[IRETH]

Thank you. I was doing that, but not getting the information. I think my problem was that I made ID = ID as well as all the other fields.


Thanks for your help.

Jan