4 Replies Latest reply on Apr 29, 2011 11:12 AM by LaRetta_1

    Separating multiple data in a field from an imported excel table

    CesarOrozco

      Title

      Separating multiple data in a field from an imported excel table

      Post

      Imported an excel table to filemaker pro and couple of fields have multiple data separated with a comma i.e.( 3416, 3572, 6708, 1245 ). These multiple data fields are also related and sequentially entered. how can i separate each data and maintaining the fields relationship. 

      Thanks,

      Cesar

        • 1. Re: Separating multiple data in a field from an imported excel table
          philmodjunk

          Please describe this in more detail. Do you have a set of fields or a repeating field that should receive this data or separate records in a related table. If a related table, what is the relationship you have defined between them?

          • 2. Re: Separating multiple data in a field from an imported excel table
            CesarOrozco
            Hi Phil,
            Thanks for the response, the imported excel file has couple of columns that has multiple data within the cell similar to the example above and when imported to filemaker it place it the same way. I'm a novice to filemaker and i cant seems to find a way separate these data without creating a database from scratch and re-entering these numbers manually. Creating the fields relationship in filemaker, I think I can figure out when I separate these data.
            Thank you in advance,
            Cesar
             
            • 3. Re: Separating multiple data in a field from an imported excel table
              philmodjunk

              Yes, but how do you want to "separate these data"?

              If you want to put the data in a related table (The most flexible approach), you'll need a field in your first table that serves as a primary key. Then we can devise a script that takes the comma delimitted data, loops through it and uses one value from this list and the current record's primary key to create a related record for this data.

              If you want to use several fields defined separately in the same record of the same (not how I'd do it, but I don't know all the details of your design either), a similar script can put one such value in each field.

              And a slightly different script could load repetitions of the same repeating field, but I don't recommend that approach as a related table is almost always a better way to do this.

              Here's how to do this in order to move the imported values into records in a related table.

              Define a field in the table into which you will import the data that auto-enters a serial number. I'll call it _PrimaryKey here. When you import the data into this table, be sure to enable the auto-enter option so that a new serial number is assigned to each new record. If, for any reason, you can't/don't do this, you can use Replace Field contents with the serial numbers option to put values in this field immediately after the import or for any data that you have already imported into your table.

              Define a new table, (I'll call it RelatedDate and your first table Maindata), with at least two fields: _ForeignKey and Value.

              Link your two tables in this relationship in Manage | Database | Relationships:

              MainData::_PrimaryKey = RelatedData::_ForeignKey

              Now you can run this script after importing the data:

              Freeze Window
              Go To Record [first]
              Loop
                 Set Variable [$I ; Value: 1 ]
                 Set Variable [$Values ; Substitute ( MainData::Values ; "," ; ¶ ) ] //Values is the field that recieves the list of values from Excel
                 Loop
                    Exit Loop If [ $I > ValueCount ( $Values ) ]
                    Set Variable [ $Pkey ; value: MainData::_PrimaryKey ]
                    Go To Layout [ RelatedData (RelatedData) ]
                    New Record/Request
                    Set Field [RelatedData::_ForeignKey ; $Pkey ]
                    Set Field [RelatedData::value ; GetValue ( $Values ; $I ) ]
                    Go To Layout [Original Layout]
                  End Loop
                  Go To Record [ Next ; Exit after last ]
              End Loop

              • 4. Re: Separating multiple data in a field from an imported excel table
                LaRetta_1

                I'm sure I had asked this question but it must have been lost ...

                In Excel, what is the relationship between a row and these comma-separated values (3416, 3572, 6708, 1245 ).  Also, what do these numbers represent.