4 Replies Latest reply on Nov 5, 2014 9:06 AM by RedDot1

    Creating New Records from Existing Fields

    RedDot1

      Title

      Creating New Records from Existing Fields

      Post

      I have an fp7 database which has 8 fields whose respective names are dates.  I would like to create, for each record in my database, 8 new records with the dates from the Field names plus the values that are in the 8 fields into a new text field.  So currently I have:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
       Field 1Field 2Field 3Field 4Field 5Field 6Field 7Field 8
      Field Name:1/20051/20061/20071/20081/20091/20101/20111/2012
      Value in Field:  RedGreenBlueWhiteRedGreenBlueWhite

       

      For each record in the database I would like to put the field names into a new field called "Date" together with that field's current value into a new text field called "Note".

      The result would be that I would have 8 new records for each current record.

      For example, Field 1 above would become a new record with "1/2005" in the Date field and "Red" in the Note field and so on.

                                                                                                                             
      Value in Date Field:1/2005
      Value in Note Field:  Red

       

      I am sure there is a simple Loop script to do this but I am not familiar enough with Loops to write it correctly and would greatly appreciate any suggestions.

       

        • 1. Re: Creating New Records from Existing Fields
          philmodjunk

          What do you want to do with the original records in the original table once this is done?

          Do you want each set of 8 records to be linked to the original record from where the data was entered originally? (Thus making it possible to use a portal or portals to display the data from the new table in place of the original fields.)

          Does your current table have a field that can serve as the primary key for such a relationship?

          A looping script could loop through the fields on a layout to do what you want but the above details need to be specified first. And care will be needed as many standard script methods for creating the new records will interfere with the looping part of your script. (But I know a way to avoid that.)

          • 2. Re: Creating New Records from Existing Fields
            RedDot1

            Thanks PhilModJunk.

            What do you want to do with the original records in the original table once this is done?

            I will delete them plus go through the resulting database to delete all records that do not have a value in the Date field.  There will be no original records that need to be retained unless they have one or more Date field entries.  I currently have 100 records some of which have no value in the current Date Name field.   So while I initially would create 8 new records for each current record and therefore will have 900 records, ultimately I probably will delete almost half ending up with maybe 450.

            Do you want each set of 8 records to be linked to the original record from where the data was entered originally? (Thus making it possible to use a portal or portals to display the data from the new table in place of the original fields.)

            I am trying to keep the resulting database as simple as possible.  This sounds more complicated but if in fact it is simpler to accomplish the goal I will do this as long as I can later delete the original and Date-Blank records as described above.

            Does your current table have a field that can serve as the primary key for such a relationship?

            Yes, I have one field but since I don't think I will need a portal or to retain the original records, this may not be necessary.

             

             

             

             

            • 3. Re: Creating New Records from Existing Fields
              philmodjunk

              Then the simplest solution is to use Import Records to import your data one field at a time--8 imports in all. This can be done manually or in a script. immediately after importing the data, you can use Replace Field Contents to put the field name value into the entire set of related records.

              • 4. Re: Creating New Records from Existing Fields
                RedDot1

                Thanks again PhilModJunk.