3 Replies Latest reply on Apr 9, 2014 6:44 AM by bigbeetle

    Build secondary tables from a primary data import

    bigbeetle

      I have to import log scale data as a single fixed-field file of 255 characters per row. This file has to divide into two subordinant files, a header file and a scale data file that relate on the load ticket number present in each row. The first character in each row in the original fixed-field file defines RecordType (A, B, C, S) and each of these rows must be parsed out differently so there is not a standard parse to apply to the whole file. I can import the raw data into a table and easily parse out any one of the RecordTypes with Case and Left, Middle, and Right functions. Because the data is arranged differently in each row it will be difficult to parse it all into one flat file ( there will be almost 150 fields all together) I need to import the raw data and parse each of the RecordTypes into different tables. Then I have to gather the A, B, and C tables into a header file and then parse out the S rows into a scale data file.

      I can do this using MS Access and deliver to a SQL Server database. It's a fairly smooth process.

      My company switched us to Macs a couple of years ago and we are currently required to use Parallels (good program) and virtual Windows machines.

      Filemaker offers an adequate database and much better reporting. All I have to do is figure out how to process the import of this mass of data (far too much for manual entry).

      I have attempted self-join relationships but the fields in each RecordType are different and FM won't allow creation of new fields after a self-join and, as I have said, creating all the different fields in the primary import table is complex.

      There has to be a way to do this and I would really appreciate it if someone will point me toward a solution. I just have not been able to get my head around this in Filemaker world.

        • 1. Re: Build secondary tables from a primary data import
          taylorsharpe

          I do not see why you even need self joins.  I would loop through copying the primary key field to be stored in the appropriate tables.  I do this kind of stuff with web service APIs all the time.  And FileMaker always lets you create new fields.  What you are describing is commonly done on XML and JSON imports all the time.  Do you have some sample data and the import script that you can share with us?

          1 of 1 people found this helpful
          • 2. Re: Build secondary tables from a primary data import
            bigbeetle

            Thanks for the reply Taylor.  My name is Larry Hyder and have been a SQL Server mechanic for over 25 years so you see I am in a deep rut. I do not have a website and so can't send you a link.  I could attach a small Filemaker 12 file with one table that shows what I am up to.  If that is OK, I will link my email address here at work.

             

            As I said, this is easy for me to do in MS Access and SQL Server but I have got to learn Filemaker thinking.

             

            Your help is greatly appreciated.

            • 3. Re: Build secondary tables from a primary data import
              bigbeetle

              Good morning Taylor,

              Attached is a small FM 12 database with one table that shows RecordType “A", “B", “C", “M", and “S".  M is not used.  RecordTypes are identified by the first character of the field RawData.  RecordTypes “A", “B", and “C" have data that applies to the load and “S” records have data that applies to each log segment in the load.  There are two loads represented in the data set.  Scroll down and you will see data for the second load.

              In the Browse view, you will see fields Cert#,  MWLSBBrand, and MWLSBTicket.  These fields are present in every data row and relate rows belonging to the same load and location.   SellerLoadNum is only in rows of RecordType “A".  GrossVol is only in rows of RecordType “B".  Logger is only in rows of RecordType “C".  I must gather this, validate it (log scalers make entry errors.) and post it to an archive header table.  Then the data in the RecordType “S” has to be gathered, validated, and posted to an archive data table.

              I would like to import data once, process it and post it.  As I said, I get it done in in MS Access and store in SQL Server.  Filemaker “thinks” differently.  Thanks for looking into this.

              I’m grateful

               

               

              Larry Hyder

              Forestry DBA

              Confederated Tribes of Warm Springs

              larry.hyder@wstribes.org

              (541) 553-8207