AnsweredAssumed Answered

Build secondary tables from a primary data import

Question asked by bigbeetle on Apr 8, 2014
Latest reply on Apr 9, 2014 by 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.