5 Replies Latest reply on Dec 30, 2011 11:43 AM by philmodjunk

    Update a field in the master record from a window handling a related record

    johannes

      Title

      Update a field in the master record from a window handling a related record

      Post

      I have the following situation:

      tables:

      tbl_customer

      tbl_addresses

       

      Relations:

      tbl_customer::ID --> tbl_addresses::ID_customer

      tbl_addresses::ID --> tbl_customer::ID_address

       

      I have a layout that shows customer details and details of the current address. It has a button that calls a script to add a new address in a modal window. It passes tbl_customer::ID as a parameter. The script puts the parameter value in tbl_addresses:ID_customer and is supposed to put tbl_addresses::ID as a feedback into the field tbl_customer::ID_address.

       

      The script works fine except that the last step which is to update tbl_customer::ID_ddress doesn't work, presumably because the current record is the new address record and that prevents the update of the customer record.

       

      I tried SETFIELD and SETFIELD BY NAME to accomplish the task but to no avail. The field tbl_customer::ID_address remains what it was before the script ran.

       

      I would be most grateful if someone could point me to a solution for this problem.

       

      Thanks

      Hans

        • 1. Re: Update a field in the master record from a window handling a related record
          philmodjunk

          Relations:

          tbl_customer::ID --> tbl_addresses::ID_customer

          tbl_addresses::ID --> tbl_customer::ID_address

          Why two pairs of ID fields in one relationship like this? You should need only one pair here, probably tbl_customer::ID --> tbl_addresses::ID_customer. (Which pair you use depends on whether two customers can have the same address or if one customer can have more than one address in your system.)

          To answer the specific question you need to commit the record in the modal window, then you may need to refresh the window in the background. This can be done, but you'll likely see the modal window flash as it disappears and re-appears if you have to refresh the background window while the modal window is still open. Since it's a modal window you may find it acceptable to not update the background window until the user closes the modal window.

          • 2. Re: Update a field in the master record from a window handling a related record
            johannes

            A customer can have multiple addresses. So for an address I need to know to which customer it belongs and for a customer I need to know which addresses he has. That's why I thought I would need both relationsships. You are right in that I would rarely start from an address to find a customer, more likely from a customer to find his addresses.

            Which window would the Refresh script step 'refresh'? The modal window or the customer layout?

            A test with the proposed solution doesn't deliver a differet result than before. The field tbl_customer::ID_address remains unchanged.

             

            Any other ideas?

            • 3. Re: Update a field in the master record from a window handling a related record
              philmodjunk

              We have two issues, a relationship that won't work. (It won't support multiple addresses for one person.) And a window that isn't updating.

              Use this relationship:

              tbl_customer::ID --> tbl_addresses::ID_customer

              tbl_Customer::ID = tbl_Addresses::ID_Customer

              with tbl_Customer::ID defined as an auto-entered serial number field and ID_Customer defined as a number field.

              Please note that you can add a poratl to tbl_Addresses on your tbl_Customer layout and it can display a list of all addresses and you can add/remove/edit the addresses directly in this portal.

              To take a look at your two window approach, am I correct that the main window is on a Customer layout and the modal window is on an addresses layout?

              Are you adding an address for a customer that has no address or adding additional addresses for a customer that already has at least one address?

              If the latter, How is your layout in the main window designed to display more than one address for the same customer?

              • 4. Re: Update a field in the master record from a window handling a related record
                johannes

                As I said I am trying different techniques in order to have the right one tested when I need it.

                You are right, the main layout is customer details with the current address details. It doesn't matter whether I add an address or define the first address for a new customer.

                Here's the solution:

                 

                As I suspected it has to do with the fact, that the 'current record' changes when I add a new address. Hence the fields in the customer table are no longer available. What I did is this:

                 

                I used global variables to pass the various data between scripts and this works fine.

                 

                This is the flow:

                 

                Button calls script 1 and passes tbl_customer::ID as a parameter (although this would not strictly be required; I could also use the filed directly in the script).

                Script 1 sets $$V1 = tbl_customer::ID and calls script 2.

                Script 2 creates the address record and uses $$V1 to update tbl_addresses::ID_customer and sets $$V2 = tbl_addresses:ID and returns to script 1

                script 1 updates tbl_customer::ID_address and uses $$V2 for this; then the variables are cleared and the script ends

                 

                The address data on the main layout are automatically updated.

                 

                Thanks for taking the time to think about the problem.

                 

                Best regards

                Hans

                • 5. Re: Update a field in the master record from a window handling a related record
                  philmodjunk

                  That is a standard scripted method for creating a record in table 2 that is related in table 1. You put the ID number in a "bucket" carry it over to the other layout and use the data in the bucket to update one or more fields in the new record you create there.

                  You can also pass the ID as a script parameter since you are calling a script to do this.