1 Reply Latest reply on Jul 26, 2010 2:24 PM by philmodjunk

    Getting foreign key from related table?

    jjfcpa_1

      Title

      Getting foreign key from related table?

      Post

      I have 3 tables:

      Clients

      Loans

      Payments

      I have a Clients layout for entering clients, a Loans layout for entering loans, and a Payments layout for displaying payments.

      I have entered a client on the client layout and have a button to Add / Edit loans.  It takes you to the Loans layout so you can enter the parameters for the loan.  There are no client fields on the layout.  When I add a loan, the _kf_clients_id is empty.

      Obviously, I want it to pull the _kp_clients_id from the clients table when a record is added to the loans table.

      The tables are related as follows:

      Client to Loans - _kp_client_id -> _kf_client_id

      Loans to Payments - _kp_loans_id -> _kf_loans_id

      I tried putting a "calculated value" in the _kf_client_id field in the loans table by simply specifying the _kp_client_id field in the clients table, but that didn't work.

      I tried putting in a lookup value in the loans _kf_client_id field, but I figured that wouldn't work because it's empty when the record is created.  

      Can someone put me in the right direction.

      Incidentally, I don't want a portal on the Client's layout due to space limitations.

        • 1. Re: Getting foreign key from related table?
          philmodjunk

          Use a script like this:

          Set Variable [ $ClientID ; value: Clients::_kp_client_id ]
          Go To Layout [Loans]
          New Record/Request
          Set Field [ Loans::_kf_client_id ; $ClientID ]

          Perform this script from a "New Loan" button on the client layout. You can start it with a show custom dialog step that asks for confirmation that you really want to start a new loan if you want to make the script safer.

          A similar script can be set up for when you just want to view/edit the client's loans:

          Set Variable [ $ClientID ; value: Clients::_kp_client_id ]
          Go To Layout [Loans]
          Enter Find Mode [] //clear the pause check  box
          Set Field [ Loans::_kf_client_id ; $ClientID ]
          Set Error capture [on]
          Perform Find[]
          Sort Records [Restore ; No dialog ]