7 Replies Latest reply on Aug 22, 2016 12:53 PM by philipHPG

# Calculate a list of whole numbers between two variables?

Hi there-

I am working on an "availability" table for my solution. The idea is that a contractor will tell us what time they are available to work on a given day (e.g. 4PM - 11PM). I would like to take the result of those two dates (which would be created in Available::TimeStart and Available::TimeEnd fields).

I would like to create a list of hours that they are available (in this case, 4,5,6,7,8,9,10) that could then be used to create individual records in a related table (HoursAvailable) allowing that person to be scheduled based on the main calendar.

Does the community have any thoughts on ways to approach this?

Jesse

• ###### 1. Re: Calculate a list of whole numbers between two variables?
1 of 1 people found this helpful
• ###### 2. Re: Calculate a list of whole numbers between two variables?

Here's a quickie custom function:

NumberRange ( start ; end ) =

Case (

start = end ;

start ;

// Else

List (

start ;

NumberRange ( start + 1 ; end )

)

)

1 of 1 people found this helpful
• ###### 3. Re: Calculate a list of whole numbers between two variables?

While the custom functions presented have their value, if you don't have FileMaker Pro Advanced, they might not be that helpful to you. Also keep in mind that you will need to convert your time to a 24-hour scale.

Here is a script you could use if you don't have FileMaker Pro Advanced:

Set Variable [ \$HourStartTime ; Value: Hour ( Available::TimeStart ) ]

Set Variable [ \$HourEndTime ; Value: Hour ( Available::TimeEnd ) ]

Loop

Exit Loop If [ \$HourEndTime <= \$HourStartTime ]

Set Variable [ \$Result ; Value: List ( \$Result ; \$HourStartTime ) ]

Set Variable [ \$HourStartTime ; Value: \$HourStartTime + 1 )

End Loop

• ###### 4. Re: Calculate a list of whole numbers between two variables?

Excellent points. And, since the OP wants to ultimately create new related records with the information, with a little modification your script can cut out the custom function middle man and get straight to business.

• ###### 5. Re: Calculate a list of whole numbers between two variables?

It looks inverted, if you want to make individual records, first do it with proper looping script, then you can easily get the list from records like as List ( table::field )

• ###### 6. Re: Calculate a list of whole numbers between two variables?

Now that I have the list, i need to come up with a script that will create a new record in related table for each hour of availability. e.g., if they are available from 5-9PM, i need a record for 5, 6, 7, and 8pm. four records.

In scripting that, I am guessing i would need some sort of variable to figure out the number of hours, then do a \$n-1 thing in a loop to get all 4 created?

• ###### 7. Re: Calculate a list of whole numbers between two variables?

If you use the script I provided, you can simply modify the scripts below in bold:

Set Variable [ \$HourStartTime ; Value: Hour ( Available::TimeStart ) ]

Set Variable [ \$HourEndTime ; Value: Hour ( Available::TimeEnd ) ]

Go to Layout [ Availability ]

Loop

Exit Loop If [ \$HourEndTime <= \$HourStartTime ]

New Record/Request

Set Field [ Hour; Value: \$HourStartTime ]

Set Field [ ... ]

Set Variable [ \$HourStartTime ; Value: \$HourStartTime + 1 )

End Loop

Go To Layout [original layout]