4 Replies Latest reply on Mar 10, 2015 3:54 AM by sccardais

    Lookup Question

    sccardais

      Title

      Lookup Question

      Post

      I want to copy the value of a calculated field (ID_CurrentOwner) in table LOTS to field ID_Seller in table TRANSACTIONS when a new transaction is created.

      LOTS and TRANSACTIONS are related on ID_LOT.

      A layout based on LOTS has a portal showing data from TRANSACTIONS based on ID_LOT.  When I create a new Transaction, I want to populate ID_Seller in TRANSACTIONS with ID_CurrentOwner from LOTS.

      The ID_Seller should be static. The transaction is a point in time and the seller ID will always be the same so this needs to be a Lookup or copy.

      I'm not having any luck making Lookup work. I defined ID_Seller in TRANSACTION as a Lookup based on this relationship between LOTS and TRANSACTIONS but that isn't working.

      Using FMPA 13 

       

       

        • 1. Re: Lookup Question
          philmodjunk

          But why do you need to copy this data at all?

          Given the relationship linking Transactions to Lots, anytime you need this ID from the context of a Transactions record, this relationship makes it possible to simply refer to this field in the Lots table.

          • 2. Re: Lookup Question
            sccardais

            Thought I explained why in 2nd to last paragraph of my post.

            The ID_Seller needs to be static for the same reason the selling price needs to be static on an invoice. It can't change when the price of an item changes later.

            Logically, the ID_Seller in the TRANSACTION table should be the ID_CurrentOwner in the LOTs table. To avoid mistakes, in a new transaction, I want to automatically populate TRANSACTIONS::ID_Seller with the value of LOTS::ID_CurrentOwner.

            LOTS::ID_CurrentOwner is calculated as TRANSACTIONS::ID_Buyer of the most recent transaction. LOTS::ID_CurrentOwner will change so this live relationship in LOTS works OK.

            But, the values for ID_Seller and ID_Buyer in TRANSACTIONS should not change.

            Summing up - when I create a new transaction, I open a layout based on LOTS that contains a portal into TRANSACTIONS. The portal contains fields for DATE, ID_Seller, ID_Buyer, ID_LOT (populated automatically because of relationship). I want ID_Seller to lookup the value of LOTS::ID_CurrentOwner.

            Thanks for your help. 

            • 3. Re: Lookup Question
              philmodjunk

              But unlike a price. The ID code that identifies a seller is data that should never ever change as it should be a reference to a primary key in the table where this value originates. 

              But if you still want to copy this data, it should be something that can auto-enter via a looked up value or calculation option:  Auto Fill

              • 4. Re: Lookup Question
                sccardais

                Phil:

                Thank you. To your point, yes. The ID's of the Seller and Buyer should never change in the Transaction record.

                The ID of the Current Owner does change in the LOT record.

                The ID's are pulled from a third table OWNERS that contain unique ID's, names, mailing addresses, etc.

                When a new person buys a LOT, I add their contact info into OWNERS. Then, I create a new TRANSACTION starting from a layout based on LOTS with a portal into TRANSACTIONS. ID_Seller is the ID_CurrentOwner copied from LOTS. The ID_Buyer is picked from a value list from OWNERS and should be copied to TRANSACTIONS::ID_Buyer. 

                Thanks for your help.