10 Replies Latest reply on Jan 7, 2016 4:21 AM by embeco

    Copying Multiple Fields From One Table to Another

    embeco

      I have an Order table and I want to copy ID, name, address, city, state, zip and telephone from a Client table into my Order table. My attempts at using multiple Lookups doesn't seem to be working.

        • 1. Re: Copying Multiple Fields From One Table to Another
          tmckibbin

          I'm not sure what your goal is.

          You can make a relationship between them and display the field from Client on your Order layout. 

          ie.

          Relation might be:  Client::ID_Client  =  Order::IDf_Client

          and field on order layout may be:  Order_CLIENT::Address  etc.

          This method has the feature that the address etc. on the order will be updated whenever Client is updated.  If that's not what you want how about a auto enter look up.  Those fields will auto populate on creation and won't update when the Client is changed.

          • 2. Re: Copying Multiple Fields From One Table to Another
            BruceRobertson

            Well, it sounds like embeco is not able to make a functioning lookup.

            And, in the case of orders, lookups is probably what he really needs to do.

            And lookups are a really basic and important skill.

             

            But it will help a lot of embeco explains in more detail what he is doing.

            Better yet, include an example file or attach a clone of his original file.

            • 3. Re: Copying Multiple Fields From One Table to Another
              embeco

              (I tried to make it too simple) I am building a home inspection system and each inspection record has a client and agents, etc associated with it. I want to populate my inspection table with fields from my Client and Agent tables and I don't want this Inspection data to change if I update(or even delete the record) in my Client or Agent tables at a later date. I have related the Inspection table to the Client table by Client ID (same type of thing for the Agent table). On the inspection screen I have the Inspection::ClientName field as a popup to the Client::ClientFirstName and Client::ClientLastName fields and that is working fine. But when I try to copy Client::ClientAddress into Inspection::ClientAddress (or any other Client field) with a SET FIELD command, I get nothing.

              I have tried to run a script upon exiting the Inspection::ClientName popup field setting global variables with Client fields and they do not display anything.

              If my Popup is displaying the proper Client name (from Client::ClientFirstName and Client::ClientLastName, why isn't it a simple matter to SET FIELD Inspection:ClientAddress to Client::ClientAddress (and other Client fields) to populate my Inspection table? And when do I do this? Do I set Script Triggers on the popup field to populate the Inspection fields or what?

              • 4. Re: Copying Multiple Fields From One Table to Another
                embeco

                Thanks for the reply. I want to copy data from Client fields into my Inspection record so if I change the Client data later on it won't alter this Inspection record. I have a popup field where I select the appropriate Client record and that is working. So with that client info showing I want to populate my inspection record with other Client data such as address, city, state, zip etc.

                I need to know in more detail how the Auto Enter Lookiup works. When does it obtain the data and from what Client record does it do the "lookup." I need to select the proper client with my popup and THEN copy other Client fields on that record into my Inspection record. thanks for your reply.

                • 5. Re: Copying Multiple Fields From One Table to Another
                  tmckibbin

                  It sounds like you are on a good track.  You might attach your script

                  trigger to ClientID "on object exit".  Two possibility come to mind.  The

                  script to set field runs before ClientID is set, therefore there is no

                  relationship.  or  There is something wrong with the relationship.  Are you

                  calling the related field through related table occurrences?

                  ie.  on inspection layout script says Set field, Inspection::ClientAddress

                  as,* Inspection_Client::*ClientAddress.

                  In bold is the important part, we are calling Client info through a TO

                  relationship of Inspection_Client.

                  You may want to copy your script here and take a screen shot of your

                  relationship between these two table occurrences.

                  • 6. Re: Copying Multiple Fields From One Table to Another
                    BruceRobertson

                    Attached is a basic example that illustrates a couple of common ways of doing what you are asking about.

                    One method uses lookups ( first name, last name, address fields)

                    One method uses auto-enter calculations ( city,state, zip field)

                    Normally you would use only one of these methods, and generally I prefer auto-enter calculations.

                    But both methods are presented here for your review.

                    Note that nothing has been set up for email; you can go ahead and try to make that one work.

                    In all cases when you first enter (or re-enter) the Client ID into the order field "IDf_client", the lookup will be triggered.

                     

                    order example graph.png

                    city lookup setup.png

                    • 7. Re: Copying Multiple Fields From One Table to Another
                      embeco

                      Thanks for the reply. I thought that the popup menu selection (now containing only the first and last name) would point to the correct Client record but it sounds like I have to retrieve the Client ID in the popup and use that to point to the proper client record. I'll play around with that.

                      • 8. Re: Copying Multiple Fields From One Table to Another
                        embeco

                        Thanks for the reply. Your last sentence appears to be the key to finding the correct client record. I still have to do my homework on when to build a client additional Client TO and how to use that.

                        • 9. Re: Copying Multiple Fields From One Table to Another
                          BruceRobertson

                          1. Did you download and try the example file?

                          2. We don't have any idea at this point what your graph looks like or what fields you are using to define the relationship. Normally the ID field is used for the relationship. Your popup picker tool may present first and last names to the user - but it is the ID field which should be captured and used to set the Client ID field in the order record. This is the only field you need to set; all the other fields (name, address etc) would be looked up. This the critical point of lookups.

                          • 10. Re: Copying Multiple Fields From One Table to Another
                            embeco

                            Yes. That is what I am going to play with today. Thank you for your help. Hopefully this will get me on the right track.