When you say "7 to 20 hours", do you mean 07:00 to 20:00 represents the peak hours time range?
How do you keep track of the time range of peak hours for each network?
Do you have a table, NetworkPeaks, with fields like this?
Start Peak Time
End Peak Time
If so, you can establish this relationship
Yourtable::Newtork = NetworkPeaks::network
and this calculation will identify if a given record in your current table has a start time that falls in the peak hours range:
If (Start_Time >= NetworkPeaks::Start Peak Time AND Start_Time <= NetworkPeaks::End Peak Time ; "Peak Hours" ; "Off Peak Hours" )
I'm basing this solely on the two fields you've indicated that you have in each record: network and Start_time. If you also have and End_time--which might fall outside the peak hours interval, then this becomes a much more complex issue.
Thank you very much for taking time to answer. No, I do not have a table with peak/off peak hours for each of the networks. It is a good suggestion that I will consider as I polish the file,k specially if more and more networks are included. Meanwhile, I think I solved this calculated field:
I had to include a new calculation field (start time_leftvalue)= left(start_time;2) to get the first two digits from the time stamp field that I get from each network report: start_time=Middle ( _Start_Date_and_time ; 14 ; 8 )
Then I wrote the calculation to get the peak/off peak hours: and it is working.
Case(start time_leftvalue<7;"Off Peak";_Incarrier = "Telkom SA (Ltd)" and start time_leftvalue > 20;"Off Peak";_Incarrier = "Telkom SA (Ltd)" and start time_leftvalue ≤ 20;"Peak";_Incarrier ≠ "Telkom SA (Ltd)" and start time_leftvalue ≤ 19;"Peak";"Off Peak")
One thing that was throwing me off is that the Case calculation field wasn´t working properly when the formula was:
Case(left(start_time;2)<7;"Off Peak";_Incarrier = "Telkom SA (Ltd)" and left(start_time;2) > 20;"Off Peak";_Incarrier = "Telkom SA (Ltd)" and left(start_time;2) ≤ 20;"Peak";_Incarrier ≠ "Telkom SA (Ltd)" and left(start_time;2) ≤ 19;"Peak";"Off Peak").
I do not understand why, if the left(start_time;2) in the latter formula does the same as the calculated field start time_leftvalue. but it wasn´t working this way.
Now it is working, thank god. I will see to your suggestion as well.
Thank you again. as always you have been very helpful.
PS By the way, I got an offer to quote a data base in FileMaker Pro for am African country whole police department. If you are interested on quoting it, please send me your email so I can forward the invitation from two weeks ago. I am not that good to quote nor I know anything about FMP Server. The quote will include traveling to Africa, setting the file all over the country, training and maybe uploading information. Let me know, please.
Timestamp and time fields are not the same--hence an issue to be taken into account in these calculations. I suggest that you set up the related table as it makes it easier to both manage adding more networks and also adjusting for changes in "peak" time intervals.
left is a text function and when numbers are treated as text, "2" > "100000000" is a true statment.
Your calculation fields that extract the left characters are probably set to return "number" as their return types and this will result in numbers being compared where the other form of your case function is comparing text.
I'd use either the getastime or Hour functions to extract a number from the timestamp field that can be used to compare to values in the related table of network Peak time ranges.
And I'd use a serial number field to identify each network--using a text field in the NetworkPeaks table to link to your other records so that issues with the names used don't mess up your relationships.
Yes, sir. I used your suggestion and it is working like a charm without so much complication as I got into before. Thank you.