Do you need what day is max and min, or need only max and min number ?
no, just the max and min number
I thought something but not got good idea.
If you want calculation without script, it can be done, but I think the data is static for past years, then script may be enough for the purpose...
I am sure it will have to be a script, I was just hopeful someone might have a clever sql select that would do the job!
Your solution lets me see the placements active on each day, and from there I could get the maximum and minimum. However it does require a record for every day of every year. I was hoping that a clever sql select command would give me an array of date, placements and from that I could get the max and min.
I guess I could hold the 'day' records in a Virtual List table and update them based on the year I am looking at.....
Appreciate your thoughts, I will explore that option
You know, sometimes trying to be smart builds up a great wall around you, with no escape doors. Being just practical brings the mind back to earth and solves the problem, which was the initial and has to be the final purpose.
In our calendar module we do have such a help table, with 30 years of consecutive days. Maybe it's not elegant but it's very effective !
Oh yes - been there, done that!
Thx for siplus, I tested SQL on your file.
And make another table that have permanently 334 redords for a year (Virual List),
but this SQL takes long time about x3 or x4 than script.
r = ExecuteSQL ( "
FROM Calendar334 c, PlacementTable p
WHERE c.theDate >= p.fromDate AND c.theDate <= p.toDate AND p.VenueUID = c.gVenue
GROUP BY c.theDate
" ; "" ; ";" ) ;
"min: " & Evaluate ( "Min(" & r & ")" ) & " | max: " & Evaluate ( "Max(" & r & ")" )
I add a script that loop through found set.
This doesn't use records in calendar table , relation , sort , and very fast.
1) It seems calculation in script that convert date to text formats acording to the format script created, so need reset the step if date format is differ.
(I use yyyy/mm/dd format but the siplus's script set field as dd/mm/yyyy in my environ)
2) I wonder I need GetAsNumber($min) in last step of my script.
Adding 1 to undefined variable results date instead of number if repetition is provided as date??
InterestingProblem.zip 201.4 K
Yup FileMaker can loop really quickly. Hope to get a chance to try this on my clients data in the next few weeks