3 Replies Latest reply on Mar 28, 2011 9:58 AM by philmodjunk

    Creating a Table with data from another table

    PaulBurton

      Title

      Creating a Table with data from another table

      Post

      I have a table with data and want to create a linked table with a subset of the rows from the larger table.  

      Trying to write a script, but the fields in the new table will not populate.

      I used the set field command - set field (target field in new table ; field in old table), but it does not work.

      Ideas?

        • 1. Re: Creating a Table with data from another table
          philmodjunk

          What relationship have you defined between the two tables?

          Exactly what parameters did you use with your Set Field Step? (you will likely need a set variable step as well as a set field.)

          How do you know what "subset of the rows in the larger table" you need to copy from?

          When you do this, you have two very different options to consider:

          1) You can copy the data. If so, changes to data in the larger table will not result in updates of the data you copied unless you create a script to do so.

          2) You can link to the data. If so, any changes made in the larger table will automatically appear in layouts where you have linked to this data as it is referring to the exact same records and fields.

          • 2. Re: Creating a Table with data from another table
            PaulBurton

            I would like to link the data.  I have the two tables related on Country.

            The script I am using, which does not work:

            Go to Record/Request/Page [First]

            Loop

            Set Field [Revenue::Country; Data::Country]

            Go to Record/Request/Page [Next; Exit After Last]

            End Loop

            • 3. Re: Creating a Table with data from another table
              philmodjunk

              What relationship do you have between Country and Data?

              When you use set Field, the references inside it's parameters are controlled by what layout you are on at the time when the script is performed. If you refer to two different tables, then the relationship between the layout's table occurrence (Table occurrences are the boxes in Manage | Database | Relationships) as selected in Show Records From in Layout Setup... control what record, if any is accessed for any reference to a table other than this specified table.

              You will need to change your script. I don't know enough to be sure this is the correct fix, but it may give you an idea on how to fix it:

              #should be performed on layout based on Data
              Go to Record/Request/Page [first]
              Loop
                 Set Variable [$Country ; Value: Data::Country]
                 Go To Layout [Revenue]
                 new Record/Request
                 Set Field [Revenue::Country ; $Country ]
                 Go To layout [Data]
                 Go To Record/Request/Page [ next ; exit after last]
              End Loop