7 Replies Latest reply on Apr 17, 2009 2:26 PM by philmodjunk

    Creating tables to match State requirements

    davidr_1

      Title

      Creating tables to match State requirements

      Post

      Typically, my State will publish, as a pdf, doc, or xls, a required format for data submissions.

      Eg "fixed length record text file with field one = "first_name"; length 10 characters;  not optional, no apostrophes allowed"

       

      You get the picture. a few dozen or hundred of these fields, which need to be extracted from our Filemaker A-P database file.

      Ideally, from the secretary's point of view, I'd add a table to her database with each State-mandated field, then automate the population of the fields when possible,  design a nice data entry screen when manual entry is needed, and write a script to export the records in the text format for Mr. Schwartznegger's mainframe.

       

      Is there any way to automate the creation of such a table? I tried applescript, but Filemaker 9 replies "Event not handled". I know I can import fields from tab separated text file, but, can I import a schema or structure?

        • 1. Re: Creating tables to match State requirements
          TSGal

          davidr:

           

          Thank you for your post.

           

          FileMaker Pro, internally, can only import a schema/structure from another FileMaker Pro file.  There may be a way to do it with Applescript, but I am not familiar enough with Applescript to program it.

           

          Anybody else have an idea?

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Creating tables to match State requirements
            comment_1
              

            davidr wrote:
            Ideally, from the secretary's point of view, I'd add a table to her database with each State-mandated field,

             

            A table for each field??!  I don't see why you would need this. The most convenient way to handle this, IMHO, is to export as XML, with a custom XSLT stylesheet doing the formatting. You can have any number of stylesheets (selected by script) to accomodate multiple report formats.

            • 3. Re: Creating tables to match State requirements
              philmodjunk
                

              If you can get the file as an xls (Excel format) file, then I believe you can drag and drop that file on your Filemaker app to create an FMP version of the file. THis will set up your basic table with very basic field definitions. You can then use Import Records | File to import the table into your existing database.

               

              You'll still need to put in some work refining your field defintions. You'll probably want to set validation options on each field to match the state requirements by limiting length etc.

              • 4. Re: Creating tables to match State requirements
                davidr_1
                  

                Thanks. I'll give that a try. To restate the issue:

                The State provides an xls file which should have been a database. The file shows the names and acceptable values for a few hundred fields. I need to create tables with these fields. I need to create data validation or dropdown lists for some of the fields. I need to create eleven input screens to allow secretaries to enter these values. I also need to fill in some of this data from an existing Filemaker database.

                 

                I'm hoping I can do some of this without navigating to, and typing into multiple dialog boxes per field. Can one create tables, fields, and value lists programmatically?

                 

                I'll try the drag and drop method you outline in your message.

                 

                Thanks to all who answered,

                David Reinertson 

                • 5. Re: Creating tables to match State requirements
                  philmodjunk
                    

                  I just checked something,

                   

                  You can also open an FMP database and use Import Records | File to import data from an excel (.xls) file into file maker and have it make a new table. Unfortunately, a lot of the detail work you'll need to do after the import, will require manually interacting with a fair number of dialog boxes.

                  • 6. Re: Creating tables to match State requirements
                    davidr_1
                      

                    Thanks again. This will be easier than trying to decypher the fp7 file format. Of course, the State xls doesn't make it easy. The field names I'm looking for are in column two, so the resulting Filemaker database has one record for each field, with the field name in field two of each record.

                    Nevertheless, I think I can:

                    1. Use Applescript to change each row of the State xls file into a column in a new file, with the field name in the header row.

                    2. Then drag and drop the resulting xls file to create a new database in Filemaker.

                     

                    • 7. Re: Creating tables to match State requirements
                      philmodjunk
                         If you look up the term "transpose" in Excel's help file, you'll find instructions for doing this in Excel without having to use applescript.