8 Replies Latest reply on Jan 3, 2013 9:45 PM by philmodjunk

    Split  records into multiple records in another table

    fruquis

      Title

      Split  records into multiple records in another table

      Post

           I have a table X  with records

           Every record has a lot of fields 

           Example of one record:

           Name                                                    field Name

           Gross =calculated number                field A 

           code 1                                                  field 1

           Discount / taxes                                   field B

            code 2                                                  field 2

           Net   value A - B    =                          field C

           code 3                                                  field 3

            

           I would like to have a script to split my records from table X and import all  into table Y in the following way

           Every record from table X into three records Table Y 

           record 1   Field Name,Table X , Field D,table Y (=A,tableX),  code 4 (=1,tableX)

           record2 Field NameTable X, Field D, table Y    (=B,tableX) , code 4( =2,tableX)

           record 3 Field name TableX, Field D , table Y   (=C,tableX). code 4 (=3,tableX)

        • 1. Re: Split  records into multiple records in another table
          bumper

               if this is a one time event then the easiest is to do a find for the records you want and then do an import from table X to table Y, once for each found set. this will only import the found set each time and create new records each time. be sure to be on a layout for table Y when you start the process and import from file, selecting the file itself.

               this can be scripted, just duplicate the finds and import three times.

               as always practice this on a back up before going after the live data.

                

          • 2. Re: Split  records into multiple records in another table
            fruquis

                 Thanks !

                 But I really need a script.

                 You mentioned  duplicate the finds, I don't get it.

                 A loop ?  back anf forth to table X andY, converting mutiple records in table X , creating three records in table Y for every record in table X.

                 Global fields ? Import one record from table x  into table Y: How do I get  to the next reord in table X?

            • 3. Re: Split  records into multiple records in another table
              philmodjunk

                   Why do you need the script? This reads like something that should be a one time fix. If it is something that you have to do on a regular basis, either you are dealing with data in a less than optimum structure--perhaps something you have no control over if this is data from a source external to your database. But if it is data you have structured this way in your database, perhaps you need to restructure it so that this process is not needed on a regular basis but only as a one time fix.

                   I don't know that you need to perform any finds, it's not clear to me exactly how you need to do this. Like Bumper, I can see that you need to split your data from one record in the first table into 3 records in the second table, but can't follow what you are trying to communicate with the finall rows of text in your original post. The only find that you would need to perform as far as I can see is a find to exclude any records that have alread been "split" like this in a previous operation. Such a find can be performed manually or in a script, but you'll need to set a value in a field to mark each such record so that you can keep from exporting their data a second time.

              • 4. Re: Split  records into multiple records in another table
                fruquis

                     Table X, many records generated each month (Bills,  they come to me in this format

                     Each record   has many fields to calculate the gross result

                     Example of a record table X 

                     NAME                                                         Kitty

                     CALCULATED numberfield Gross      100            accounting code     1

                     Percentage 20%   TAXES                       20             accounting code      2

                     Net (calculated  100-20                             80          accoounting  code 3

                      

                     I would like to convert for double, triple  accounting

                     Table  Y

                     Every record from table X converted into   three records Table Y

                     List view

                     record1         Name                         Gross     100                 acounting code         1

                     record 2        Name                        Taxes         20                accounting code       2

                     record 3        Name                         Net             80                accounting code       3

                • 5. Re: Split  records into multiple records in another table
                  bumper

                       the import menu item/script step will allow you to move data from one table into another within in the same database. when asked for the file to import from you select the file itself. the records that will be moved from table x to table y are the records that constitute the found set in table x at the moment you begin the import into table y. so if you start in table x and do a find for just the records you want to import into y then that's what you are going to get. the import process will create the new records for you (there is a check box for this) PhilModJunk suggestion is simpler, since you are creating records based on the three fields you can use the same found set and just import a different field each time. note: in the scripting of this you will set the fields to be imported into y, so for the first pass you bring in the code 1s, then the second pass the code 2s and finally code 3s. So if you start with 50 records in table x to break into 3 in y, you will end up with 150 new records. note: you will still have the three fields in table y, but depending on the import two out of three fields will always be empty. At the end of the script go back to table x and put an x or whatever in your mark field to remind you that you have already imported these records, that also simplifies you find because now you are just doing a find on records with an empty mark field. the last bit of advice, watch your contexts, ie what layout are you on at any given point in the script. layout for table x when doing the find and over to table y for the import and back to table x to mark the finished records.

                  • 6. Re: Split  records into multiple records in another table
                    philmodjunk

                         Yes, but I'd modify the structure so that the data is directly entered into these separate, but related records in the first place so that I would not need to do this data import at all.

                    • 7. Re: Split  records into multiple records in another table
                      fruquis

                            

                           Split records

                            

                           Table X, many records generated each month (Bills,  they come to me in this format)

                           Each record   has many fields to calculate the gross result

                           Example of a record table X 

                           NAME                                               Kitty

                           CALCULATED numberfield Gross      100             accounting code   1

                           Percentage 20%   TAXES                     20             accounting code    2

                           Net (calculated  100-20                     80             accoounting  code 3

                            

                           I would like to convert for double, triple  accounting

                           Table  Y

                           Every record from table X converted into   three records Table Y

                           List view

                           record1         Name       Kitty                  Gross     100                 acounting code        1

                           record 2        Name       Kitty                Taxes         20                accounting code       2

                           record 3        Name       Kitty                  Net           80                accounting code       3

                            

                           I am still trying with a script duplicate record and dulpicate the duplicate. I would like  to copy values not the calculated values

                            

                            

                      • 8. Re: Split  records into multiple records in another table
                        philmodjunk

                             To repeat, I would enter the values 100, 20 and 80, with their accounting codes directly into Table Y from the start. This can be done from the context of Table X if you use a portal. By entering the data directly into Table Y, it need never be entered into Table X. By using the relationship between Table X and Y, you can always access this data from your Table X layout.