4 Replies Latest reply on Sep 5, 2014 9:15 AM by mbeck65

    How to use a table to populate same fields of a second table

    mbeck65

      Hello,

      I'm a newby and apologize for the proposed problem that, mayby, could be easy to be solved... But I'm not able to do it, at the moment...

      The context is the following: I imported a table (we call it TABLE A) from another DBMS and now I would like to use the data inside this table only as a "facility" to populate my main table (TABLE B)...

      I mean: how could I perform a serch inside TABLE A (possibly without declare relationship) in order to select 1 record and copy to the TABLE B some fields of the record selected?

       

      Thanks a lot for your support.

       

      Max

        • 1. Re: How to use a table to populate same fields of a second table
          Mike_Mitchell

          Max -

           

          There are a couple of ways to mirror data from an imported table.

           

          1) If you have some kind of unique identifier or key field, you can put that key field into your main table and establish a relationship between them. (I know you said you didn't want to do this, but it's really the proper way.) At that point, you can simply reference the data from the imported data set by using a related field (TABLE A::field). This avoids copying the data into a second table, which creates certain problems (what happens if the data in table A change? Should the data in table B change too? What if they don't - which one is right?)

           

          2) If for some reason you need the data to copy across and not change if table A changes (which does happen under certain business rules), you can use either a lookup or an auto-enter calculation via a relationship. Again, put the key field in both tables and define either a lookup or an auto-enter calculation that defines the field you want in table B to copy the data from table A.

           

          3) You can write a script that performs a Find in table A, stores the data in a variable, and then inserts that value into the appropriate record in table B via a Set Field script step. You can use multiple variables for multiple fields, or you can use delimited data (such as separated by carriage returns or some other special character that doesn't exist in the source data).

           

          A lot depends on exactly what you need and what your business requirements are. If you were to provide a little more definition as to what you're trying to accomplish in terms of business results, we can probably give you more specific advice.

           

          Mike

          • 2. Re: How to use a table to populate same fields of a second table
            mbeck65

            Hi Mike,

            and thanks for your very quick answer!

            You described well my situation in the case 2: actually I'm not interested in a relationship between the 2 tables because, as I tried to explain, the TABLE B is only a facility used to avoid to fill a lot of field already availables in TABLE A but, as you told, these data could be modified in TABLE A without that TABLE B is affected from this (TABLE B cannot be modified).

            Now I'll see in the manual how to perform lookups and/or auto-enter calculation via a relationship but I continue to have my first problem. I try to describe it with a workflow:

            1. I have a layout opened on the TABLE A in order to fill my fields of a new record on this table (this means that in this moment I don't know what's the key between TABLE A and TABLE B)

            2. In order to save time, I would like to select a record of TABLE B (by setting some filter but it doesn't matter in this moment) and copy some fields of this record from TABLE B to the correspondent fields in TABLE A

            3. As you told, starting from this moment the data contained in the just filled fields in TABLE A could be modified by the user and this is the behaviour that we need. So, for this reason (please, you correct my assertion if I'm wrong) I do not have to save the "key" because I need the link between the tables only the for the purpose of TABLE A filling.

             

            Let me know what you think and thanks a lot for your answer

             

            Max

            • 3. Re: How to use a table to populate same fields of a second table
              Mike_Mitchell

              Use method 2. Put the key field in both tables, define the various fields as lookups, and then just insert the key field from the import table into the table where you want to edit. All the fields will automatically copy over for you.

              • 4. Re: How to use a table to populate same fields of a second table
                mbeck65

                Thanks Mike,

                now I'm able to do what I need.

                 

                Regards,

                 

                Max