5 Replies Latest reply on Apr 19, 2009 11:09 AM by Orlando

    is there any easy way to do this?



      is there any easy way to do this?


      I have an INCOME table which is primarily records of individual donations and membership payments.


      I also have a MEMBERS table, which contains current membership status and contact information.


      Using the "X" relationship, the entire MEMBERS table appears in a portal on my layout for the Income table.


      The fields "name_last" and "name_first" in my INCOME table must match the actual physical records I have of income on cheques, receipts, etc.


      Consequently, I cannot have solid relationship between donor name in INCOME and my member name in MEMBERS.  But in most cases, I can visually scan the portal quickly and select the portal row that has the correct address information for the Income item. Rather than re-type the address information into each Income record, I'd like to manually select the correct portal row and press a button on that row which would insert the street, city, state, and mailing code information (as a one-time lookup) into the current INCOME record.


      What would be a simple, straightforward way of doing this?  Thank you.

        • 1. Re: is there any easy way to do this?

          Hi DonorTracker


          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


          INCOME                    Income_MEMBERS

          _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. 



          • 2. Re: is there any easy way to do this?

            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?


            Again, thanks!

            • 3. Re: is there any easy way to do this?

              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.

              • 4. Re: is there any easy way to do this?

                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!

                • 5. Re: is there any easy way to do this?
                     Glad I could help DonorTracker.