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

    Automatically inserting record set into FM table



      Automatically inserting record set into FM table



           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 the 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.


           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 use INSERT (sql) then this would be straightforward ofcourse but I understand thats not possible in FMP12.
                     Any help would be appreciated.


        • 1. Re: Automatically inserting record set into FM table

               Well I wouldn't say without any user input, someone has to specify the date range, after all. wink

               If the user were to enter the start and end dates into a pair of fields with global storage specified for them, a looping script can create the desired records. The values shown in Days and Status can be set as default values in the Data box found on the Auto-Enter tab of field options so we do not have to script anything but creating the records and assigning the dates. The End date could even be a calculation field that calculates it's date from the value in Start.

               This script assumes that the user always enters a Monday date for the start date and an end date that falls some time after the start date. You can add additional scripting to make sure that the date entered is a Monday date or your script can correct the start date to that of the following or preceding Monday if you prefer, but I'm not scripting that issue here:

               #gStartDate and gEndDate are two global fields into which the user has entered a date range
                   New Record/Request
                   Set Field [YourTable::Start ; YourTable::gStartDate ]
                   Set Field [YourTable::End ; YourTable::gStartDate + 6 ] ---> leave this step out if you define End as a calculation field
                   Exit Loop If [ YourTable::gEndDate < YourTable::End ]
               End Loop

          • 2. Re: Automatically inserting record set into FM table

                 That works. Thanks PhilModJunk