3 Replies Latest reply on Apr 25, 2011 9:12 AM by LaRetta_1

    How do you insert values within repetitions in a Repeating Field in a FileMaker Pro 11 record from...

    OligarioLazarre

      Title

      How do you insert values within repetitions in a Repeating Field in a FileMaker Pro 11 record from MS EXCEL?

      Post

        

      Currently, I am using FileMaker Pro 11.

      I want to insert values within repetitions in a Repeating Field in a FileMaker Pro 11 record from MS EXCEL (e.g. rows (records) and columns (fields)).
      The goal is to have each repeat (i.e. repetition) import data from a separate MS EXCEL cell within the same row. 
      For example, assuming there is a header row, I want the contents in cell A2, B2, C2, D2, E2, through T2 to be imported to repetitions "14 Requirement [1]", "14 Requirement [2]"
      through "14 Requirement [20]" respectively.

      The Repeating Field is defined as follows:

      1.  The number of maximum repetitions is twenty (20).
      2.  The repeating field name is "14 Requirement".
      3.  The data type defined for the repeating field is text.
      4.  The repetitions are as follows:

      14 Requirement [1]
      14 Requirement [2]
      14 Requirement [3]
      14 Requirement [4]
      14 Requirement [5]
      14 Requirement [6]
      14 Requirement [7]
      14 Requirement [8]
      14 Requirement [9]
      14 Requirement [10]
      14 Requirement [11]
      14 Requirement [12]
      14 Requirement [13]
      14 Requirement [14]
      14 Requirement [15]
      14 Requirement [16]
      14 Requirement [17]
      14 Requirement [18]
      14 Requirement [19]
      14 Requirement [20]

      5.  My logic is as follows:

      1.  Spreadsheets accommodate only rows (records) and columns (fields).
      2.  Repetitions are separated by Group Separator character $1D (decimal 29) when exported from FileMaker Pro.
      3.  Delimit columns (fields) in EXCEL with the Group Separator character $1D (decimal 29) before importing into FileMaker PRO.
      The Group Separator character $1D (decimal 29) is place after the last character in the MS EXCEL cell.
      4.  Each repeat (i.e. repetition) will import data from a separate MS EXCEL cell within the same row. 

      6.  The following problems are occurring:

      1.  I am unable to insert all of my data.
      2.  Only data from the first cell is imported.
      3.  The following message is displayed:
      "There are less records in the data source than in the target table.  Not all records were updated."


      7.  I did not create this database and it will be extremely difficult to have the person who created it to change it.

      8.  A copy of the MS EXCEL template has been attached.

      MS_EXEL_TEMPLATE.png

        • 1. Re: How do you insert values within repetitions in a Repeating Field in a FileMaker Pro 11 record from...
          LaRetta_1

          Oh boy ... it would be difficult for ANYBODY to change it, much less the person who created it.  Data should not be contained within repetitions; that is what related records are for.

          It would be highly beneficial to change the structure.  If you cannot then you will need to either present the file for reverse engineering to a qualified Developer or contact the person who created it, since they understand the structure and requirements and could handle your request with less effort that someone unfamiliar.

          From your description, this is not the only example where multiple fields (which should be related records) or repetitions (which should be related records) exist within your solution.  Was this file migrated from older pre-fp7 files by chance?  Imports take place converting a cell to a field and a row to a record.

          Without reviewing the file, I would suggest that the Excel cells be defined in an intermediate file as fields and the import take place there.  From the fields, repetitions can be set but it will be tedious.  However, I'm just taking a WAG without seeing exactly what you have.  And no, I'm not saying you need some expert assistance here since I'm not available for hire right now anyway.

          • 2. Re: How do you insert values within repetitions in a Repeating Field in a FileMaker Pro 11 record from...
            OligarioLazarre

            Thank you for the feedback.  What do you mean by intermediate file?  Are you referring to a MS WORD file, CSV, or text file?

            • 3. Re: How do you insert values within repetitions in a Repeating Field in a FileMaker Pro 11 record from...
              LaRetta_1

              I am speaking of using another table within the same FileMaker file and import your data there into fields (map the Excel cells to the various fields - set up one field for each repetition) which will easily map) . From there, use script to translate from those fields in the intermediate table to the various repetitions.  As said, I have no information regarding your structure or the purpose of the 14 Reguirement repetitions.

              BUT ... I believe you will be adding duct tape instead of addressing the underlying issue ... repetitions should not hold data - that is what relationships are for and it is known as a one-to-many (1:n).