2 Replies Latest reply on Mar 29, 2013 9:24 AM by ianmcarter

    Automatically entering record set into FMP12 table

    ianmcarter

      Hi

      I'm new to FMP12 but in past I've had extensive experience of Oracle. I would like to know how I can create a set of records in FM using without any user input.

      The application is part of timesheet application. Once the user has entered dates for 'Start of contract' and 'End of contract' I would like to give him the option to press a button and generate weekly timehseets for period between dates,

      So if he enters 04-Mar-2013 (Start) and 31-Mar-2013 (End) I would like to automaitcally create following records in my Timesheet table.

      Start.....................End.....................Days...Status

      04-Mar-2013 10-Mar-2013 0 Not entered

       

      11-Mar-2013 17-Mar-2013 0 Not entered

       

      18-Mar-2013 24-Mar-2013 0 Not entered

      25-Mar-2013 31-Mar-2013 0 Not entered

      If I were able to manipulate sql I could just create INSERT statement (joining to table static), but I understand thats not possible in FMP12.

       

       

      Any help would be appreciated.

      Ian

        • 1. Re: Automatically entering record set into FMP12 table
          taylorsharpe

          All you need to do is write a script that creates the records and puts the data you want into it.  Have you used FileMaker's scripting engine.  In many ways it is a combination of SQL and PL/SQL and basically does all of the automated actions in FileMaker. 

           

          FYI, FileMaker's ExecuteSQL only supports SELECT statements, but FileMaker is fully SQL compliant.  So you can use an external program to INSERT or DELETE or whatever other SQL functions or you can get a FileMaker plugin that lets you use SQL calls.  However, you'll end up back in FileMaker's scripting engine to use them because if you want someone to push a button, the only thing FileMaker can do to start a procedure is execute a Script. 

           

          The script would be something like:

           

           

          [Start at Employee Table I assume or wherever you Button is]

           

          Set Variable [$EmployeeID; Value: EmployeeFile::EmployeeID ]

          --- You might get the start and end dates from a field or from a Show Dialog Box or however you get the dates

          Set Variable [$StartDate; Value: <insert start date field>]

          Set Variable [$EndDate]; Value: <insert end date field>]

          Set Variable [$LoopDate; Value: $StartDate - 7]

          Go To Layout [TimeSheetLayout ]

          Loop

               New Record/Request

               Set Variable $LoopDate; Value:$LoopDate + 7

               Set Field [TimeSheet::EmployeeID; $EmployeeID]

               Set Field [TimeSheet::StartDate; $LoopDate ]

               Set Field [TimeSheet::EndDate; TimeSheet::StartDate + 6]

               Exit Loop If [TimeSheet::EndDate > $EndDate ]

          End Loop

          Go To Layout [Original layout]