1 Reply Latest reply on Aug 16, 2015 9:32 PM by philmodjunk

    Advanced Excel Import, Advanced Calculation, or Advanced Script Issue

    KCr

      Title

      Advanced Excel Import, Advanced Calculation, or Advanced Script Issue

      Post

       

      Sorry, this is hard to label.  I have a filemaker pro advanced database & an excel spreadsheet.  The problem is that the information in the spreadsheet isn't neatly in columns.  For example...

       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
      NameDateAddressPhone NumberDate Hired
      Mike Smith8/10/14Somewhere, TX. 76522817-547-503801/20/10
           
         Training CompletedDate
         CISSP06/01/10
         Security+10/10/12
           
      NameDateAddressPhone NumberDate Hired
      Jane Doe8/10/14Nowhere, TX 76522817-547-252912/10/13
           
         Training CompletedDate
         A+5/12/10
         VCP5-DCV7/08/11

       

      In this scenario, I have four tables.  Table 1 - The import table, Table 2 - The member roster, Table 3 - The training courses required, Table 4 - The table combining the roster & the required training that the member's have completed.

       

      So, basically what I'm trying to do is figure out some way to import the data, run a script, or perform a calculation that would allow me to align the member (from table 2) with the training required (table 3) & date the training was completed (table 1).  I cannot change the Excel format, as much as I would like to.

      Any suggestions?  Anything would be helpful.  Thanks!

      Kevin

       

        • 1. Re: Advanced Excel Import, Advanced Calculation, or Advanced Script Issue
          philmodjunk

          Importing all of this data into an interim table an using a script to loop through the records in order to then move data into your table 1 (training completed) with the needed match field ID's to link it to your Personnel and your table of required training would seem the way to go. Looks like you will nested loops-the outer loop to loop once with each personnel row of data and the inner loop to loop through the rows of completed training courses.