1 Reply Latest reply on May 28, 2010 11:17 AM by philmodjunk

    Need Help exporting records with a custom format

    calliex

      Title

      Need Help exporting records with a custom format

      Post

      I am using Filemaker 11 on both PC and Mac

       

      I need some help exporting some records.  The records are lab values accumulated over days for a particular patient identified by a unique ID.  There are about 8 lab values collected. Due to a lack of experience we first set this up with individual variables for everything such as:

      Time1    FiO1       Ph1

      Time2    FiO2       Ph2

      . . . . . . . .

       . . . . . . .

      Time72 FiO72     Ph72

       

      We have since gotten smarter and are going to use a separate table setup as follows:

      ID                            Timestamp                         FiO         ph

      PUH-2006-006   11/23/2008 13:19              100         7.52

      PUH-2006-006   11/23/2008 13:19              200         5.82

       

      I then use a portal to display the values in the main table.

      We can also read all the data in from a spreadsheet.

       

      My problem is data was entered with the individual variables.  I created layout that looks like the new spreadsheet.  The problem is no matter how I try to export that layout Filemaker exports it one continuous line for each ID like this:

      ID                            Timestamp                         FiO         Ph           ID            T              imestamp                            FiO         Ph 

      PUH-2006-006   11/23/2008 13:19              100         7.52  PUH-2006-006         11/23/2008 13:19              200         5.82

       

      All the variables for one ID are on the same line.

       

      I have tried exporting it with current format and just about every other option allowed me.  Is it possible to do or will I just have to hand input those records.

        • 1. Re: Need Help exporting records with a custom format
          philmodjunk

          You could make 72 separate uses of Import Records to move the data into your new related table, but that's still a lot of work...

           

          A less labor intensive approach would be to write a script that loops through your records and fields to move the data one field at a time into new records created in your new related records table.

           

          Show All Records

          Go To Record [First]

          Loop

             Set Variable [$I ; 1 ]

             Set Variable [ $ID ; Value:  YourTable::YourPatientIDField ]

             Loop

                  Exit Loop If [IsEmpty ( GetField ("YourTable::Time" & $I ) ) ]

                  #I'm assuming Time1, Time2 etc fields are time stamp fields, if not you'll need a more sophisticated expression here

                  Set Variable [$TimeStamp ; Value: GetFIeld ( "YourTable::Time" & $I )]  

                  Set Variable [$FiO; Value: GetFIeld ( "YourTable::FiO" & $I )]

                  Set Variable [$pH; Value: GetFIeld ( "YourTable:: Ph" & $I )]

                  Go To Layout [//select layout for new related table]

                  New Record/Request

                  Set Field [Newtable::ID ; $ID ]

                  Set Field [Newtable::TimeStamp ; $TimeStamp

                  Set Field [Newtable::FiO ; $FiO ]

                  Set Field [Newtable:: ph ; $pH ]

                  Set Variable [$I ; Value : $I + 1 ]

                  Exit Loop If [ $I > 72 ]

                  Go to Layout [Original Layout]

            End Loop

            Go To Record [Next ; Exit after last ]

          End Loop

           

          Note: this script assumes that if a Time field was left empty, all subsequent sets of dedicated fields are also empty and it skips to the next record. You'll have to modify your script if this is not the case.