You can do this using one of two ways, but the easiest way is to set up your members information fields in the INCOME table to be lookups, and set a MembersID field in the INCOME table that trigers a relationship and runs the lookups.
I cant be sure but by the sounds of it you are not using a unique identifier to match the Member record to the Income record, this would be allot more reliable and will not break the relationship if the name changes, so to start with if you don't have one go into Define Database and on your MEMBERS table and create a new number field called _UniqueID and click 'Options...' and from under the Auto-Enter tab check 'Serial Number'
Now in your INCOME create a number field called _MemberID and then go to your relationship graph and create a join between INCOME and a new occurrence of MEMBERS and link these two fields
_MemberID --=-- _UniqueID
And then go back to INCOME and for all the fields you want to lookup a value from MEMBERS click on 'Options...'' and again under the Auto-Enter tab this time select 'Looked-up value' and in the dialog that pops up input the following:
'Starting with table:' = INCOME
'Lookup from related table:' = Income_MEMBERS
and a list of fields from members will display under 'Copy values from field:' and here you select the field you want to get the value from.
Click 'OK' and get out of Define Database and go to a layout for your MEMBERS table, you need to update the records to have a number in the new -UniqueID field.
Click in the field '_UniqueID' on a layout and go 'Records > Replace field content...' and in the dialog select 'Replace with serial numbers:' and click 'Replace'
Now once this is done it would be a good idea to go back into the options for that field and tick the box for 'Prohibit modification of value during data entry'
Nob back on your layout for INCOME with the "X" relationship you can add a button to the portal rows to set the _MembersID in INCOME with the _UniqueID from MEMBERS which will then trigger the lookup.
So in Layout Mode place a button in the top row of the portal and use the Set Field script step with:
'Specify target field' = INCOME::_MemberID
'Calculated result:' = MEMBERS X RELATIONSHIP::_UniqueID
And OK that and get back to Browse mode and now when you click on the button the lookups should populate with values from the selected Member record.
I hope this helps and do ask if anything is unclear, also do make a backup of the database before doing any of the above just incase.
Thank you, Orlando, that looks like a good approach.
Am I correct in understanding that since this is done via a lookup, I can make small changes, as necessary, to any looked-up value in an Income record that came over from the MEMBERS table?
Also, if I decide to "break" the link between an Income item and a Member ID, what is the best way to do that?
Yes, because these are lookups the data can be modified on either side without affecting the other.
If you needed to break the link between INCOME and MEMBER then you just clear out the _MemberID on the INCOME side, although if you reset that number the lookup values will run again, and override the data on the INCOME side.
However if you keep the link, you can create a relationship from your main MEMBERS table to an occurrence of INCOME to display all records for that member, you could also add calculations to total all income from that member etc, so might be worth keeping that link.
Thanks so much. That solution works a treat. And your directions were crystal clear and totally accurate.
I like the idea of the link, and will probably use it to make a reverse portal of the Income records into the Members layout.
Again thank you!
Glad I could help DonorTracker.