hello to the whole community
I'm new and not very experienced in FM, I've come here and I think the Community is beautiful. I'm building an application for my work, I need to repeat events automatically ... but I do not know how to create the script to generate them.
I have the following tables; employees, customers, events and calendar. In the Events format I choose the period between two dates, I choose the employee and the customer where he provides services, the days of the week and the working hours, a scripts will have to look for a record in the calendar table for each day of the employees inserting employee, customer and hours.
example WEEK TYPE:
START DATE: 01-01-2018 DATE DATE: 31-12-2018
EMPLOYEE: ANNA
CUSTOMER: ROSSI
MONDAY: 4 HOURS
THURSDAY: 4 HOURS
The scipt must insert in the calendar table: all the dates of Monday and Thursday between the start and end date, the employee, the client and the hours, in the respective fields .....
Thanks for help or suggestions .....
The basic script will typically be a loop that creates records in the calendar table, incrementing a variable with a date until it is > your end date, but some details are unclear. How do you manage the hours for the days of the week?
I see:
MONDAY: 4 HOURS
THURSDAY: 4 HOURS
Does that mean that you have 5 (or 7) number fields for the days of the typical week?
I see that the example is labeled "week type", does that mean that you also have repeating events of type month, Quarter or some such? If so, how do you list the hours in those cases?
Thanks Philmodjunk, excellent observation .... we talk about 7 days
other examples:
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
2 4 4 2 0. 0. 0
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
2. 0 2. 0. 3. 0. 0
I enclose an image to better understand, for services with a longer cadence out of the month I was thinking of a further choice Bimonial, quarterly, half yearly.
I will have to create calendar only with national holidays and say if you find these dates do not create records
What I see is a check box with 7 boxes, but I can't read your language so I may be guessing wrong. That selects the days of the weeks, but then you must also specify the hours. What I see suggests that you want the same 3 hour section from 7 to 11 in records for Monday thru Friday, but nothing for Saturday or Sunday correct?
You may need to record more than National Holidays, but that's a business management decision. You can set up a table where each record is a holiday and a date field records the date of the holiday. Any script used can check dates in that table to skip any designated holidays or other business closures.
Yes it's correct 4 hour section from 7 to 11 in the records from Monday to Friday, but nothing for Saturday or Sunday. Sorry for my english I use a translator.
if instead I want to do this week every 15 days I say to skip a week.
I thought I'd use more control for jobs every 3, 6, 12 months so I need to know which one is the best way to work scripts
I don't see how you plan to use the second set of check boxes ( 1st, 2nd, 3rd ... last). That looks like you want say the "3rd Monday" of the month, but that would be for monthly appointments not weekly so I'm ignoring that field for the moment.
Likewise, your latest statement:
if instead I want to do this week every 15 days I say to skip a week.
Doesn't seem to fit your screen shot so I'm ignoring it for the moment too. The following is not intended to be the final, complete script, but should give you an idea as to how you might set up a script for this:
First, we need a relationship:
Appointments::Date = Holidays::Date
We can use that relationship to skip days when your organization is closed.
Next, you have to decide what one record in Appointments represents. I'm going to choose 1 hour, every record in Appointments represents a 1 hour "time slot". You can easily modify this to be half hour or even 15 minute intervals.
Let's also assume that the fields that specify the days of the week and the start and end time are global fields. If they aren't, you'll need to copy their values to variables before the following script will work.
#Outer loop loops through the days of the week check box
Go to Layout ["Appointments" (Appointments) ]
Loop
Set Variable [$Day ; value: $Day + 1 ]
Set Variable [$DayName ; value: GetValue ( YourTable::gDayOfWeekCheckBoxes ; $Day ) ]
Exit Loop if [IsEmpty ( $DayName ) // there are no more selected days of the week ]
#This loop loops through the appointments calendar from start date to end date
Set Value [$DayNumber ; Value: Ceiling ( Position ( "SunMonTueWedThuFriSat" ; Left ( $DayName ; 3 ) ; 1 ; 1 ) / 3 )]
Set Value [$Date ; value:
Let ( D = YourTable::gStartDate ; If ( DayOfWeek ( D ) = $DayNumber ; D ; D - DayOfweek ( D ) + 7 + $DayNumber ) ) ]
Loop
Exit Loop If [ $Date > YourTable::gEndDate ]
#This Loop loops through the hours from start time to end time, creating a record for each hour.
Set Variable [$Time ; Value: YourTable::gStartTime ]
Loop
Exit Loop If [$Time > YourTable::gEndTime ]
New Record/Request
Set Field [ Appointment::Date ; $Date ]
Commit Records
If [ IsEmpty ( Holidays::Date ) //no holiday for this date ]
Set Field [appointment::Hour ; $Time ]
Set Field [Appointment::ClientID ; YourTable::gClientID ]
Set Field [Appointment::EmployeeID ; YourTable::gEmployeeID ]
Else
Delete Record [Dialog:off]
End If
Set Variable [ $Time ; Value: $Time + 3600 // 3600 seconds = 1 Hour ]
End Loop
Set Variable [ $Date ; value: $Date + 7 ]
Loop
Note: The calculation for $DayNumber may not work in your language. It works in English because the first three letters of the name of the day of the week is unique in English. If that's true for your language, you can use it as written. If not, you may want to use a Case function to return 1 for Sunday, 2 for Monday, etc.
All field names that start with lower case g, are assumed to be global fields from the layout where the user selects this information.
Adding a number to a time value is the same as adding that number of seconds so adding 3600 to $Time is adding 3600 seconds or one hour. Use 1800 and you get half hour intervals. 900 would set you up with 15 minute intervals.
Instead of deleted appointment records that match to holiday dates, you could keep them and use the relationship to exclude the record from found sets and/or filter it out of portals. ExecuteSQL could also be used to check the date against your Holiday records and then you would not need to create the record before checking for a holiday with a matching date.
While I tested the calculation used for the first date for a given day of the week, I have not tested this script as a whole so test any script you base on this example carefully (Stepping through it in FileMaker Advanced's debugger would be ideal).
Phil you're a Great!
what I have in mind then you can do, this is the good news I do not know if I can put everything in place but I begin to work and try I'll keep you updated .... for now thanks soon