3 Replies Latest reply on Mar 17, 2014 10:29 AM by philmodjunk

    Parsing data out of an imported fixed-field data file

    Woodborer

      Title

      Parsing data out of an imported fixed-field data file

      Post

           I receive data from several sources in different formats.  One source sends data as a 255 character fixed-field text file.  There is only one field  I have imported the file successfully into FM 12 and now must parse out the data fields using an extraction calculation (Middle;<imported table>; start character; number of characters).  The first character of each line is a RecordType code.  There are four different ones.  I have a layout that works that shows all RecordTypes but I need to have a layout for each different RecordType as the data fields are different for each.  I am having trouble setting up relationships for layouts that address the same source table and constraining the RecordType to a certain value in building layouts to do this.  For example, the source table has RecordType in ("A", "B", "C","D","S")  I need to have layouts for each RecordType in which the extraction calculation is different.  For what I have tried, FM is throwing me error messages about relationships.   

           My experience is in SQL Server, Access, etc. and I can do all the above there, but I see advantages to processing this data and reporting in FileMaker.

           I will be grateful for any help

        • 1. Re: Parsing data out of an imported fixed-field data file
          philmodjunk

               Why do you want to put the data all in one table if the fields for each record type are different?

               That can be done, but it may not be the most optimum design.

               Whether using a SQL based database or FileMaker you have three basic design options:

               1) Parse the data into separate tables, one for each record type

               2) Parse the data into a record that has every needed field for every record type. Fields not used for a given record type are left blank.

               3) Combine 1 and 2 by having a unified table of only the fields common to each record type and related tables for the fields unique to a given record type.

               The details in how you manage the different layouts for the different record types will vary a bit with whether you use options 1, 2 or 3, but they all will entail either setting up different layouts for each record type or a layout that can adapt to the needs of each layout type.

               As an Example, an OnRecordLoad trigger can perform a script that checks the record type and changes layouts to the layout designed for that record type. Or, (for options 2 or 3) it can select a panel of a tab control that contains the fields needed to correctly display a record for that record type.

          • 2. Re: Parsing data out of an imported fixed-field data file
            Woodborer

                 I started working option 1 going into separate tables and got into trouble with the relationships.  Option 2 is best because I have to match fields with a table in SQL Server.  How to set up the calculation for parsing that specifies a RecordType had me puzzled for a bit but it looks like it will work in the Specify Calculation window.

                 I'm a decent SQL mechanic and am struggling some to get my head around FM methods.

                 Thank you very much for the kind help.

            • 3. Re: Parsing data out of an imported fixed-field data file
              philmodjunk
                   

                        I'm a decent SQL mechanic and am struggling some to get my head around FM methods.

                   That's a common experience for people with experience using SQL. You reach for a familiar SQL based "tool" that you'd use in another system only to find that FileMaker has a completely different way to get the same result. The "paradigm shift" can be frustrating at times.

                   That said, you may find that using the ExecuteSQL function something useful when you can get the results that you want within its limitations.