Calculating Next Serial Number

[CKonash]

Hi,. First post here so sorry if I don't give enough info.  

I have a fire dept database I made and manage. In our Event Report table I have a Timestamp field for recording when fire incidents occurred.  

I also have an Attendance table used to record members attendance for each call, the Event report table and attendance table is replaced to the Event Report table.  

Normally when the scrip runs to create an attendance record for a member at a call it currently sets a field in the attendance record as "Incident". 

i need to modify the script set to Determine the time the call came in and set the field in the attendance record as "Incident Day" or "Incident Night" based on the Timestamp from the event report.  

Night incidents are 00:00-06:00 and 18:00-23:59:59

and Day incidents are 06:00-17:59:59

right now I have the event type field Auto enter as "Incident Day" and members entering attendance for a night event need to change the field to "Incident Night" via a drop down list.   My goal is to remove the drop down list and set the event as Day or Night in the background.  

 

Is is this easy to do?  

Thank you you for your time.  

Chris.  

[comment]

This is very difficult to follow. It sounds like you have two tables: one of these (Events?) has a timestamp field, and you want the related record in the other table to show either "Day" or "Night"? If so, this should be a calculation field, and it could be =

If ( 6 ≤ Hour ( Events:Timestamp ) and Hour ( Events:Timestamp ) < 18 ; "Day" ; "Night" )

The result is Text, of course.

 

A more elegant solution would calculate a Number result: 0 if Day, 1 if Night. This is easier to summarize, in case you need to know how many nights there were.  Here the calculation could be =

Hour ( Events::Timestamp ) < 6  or 18 ≤ Hour ( Events::Timestamp )

 

[CKonash]

Hello, 

I apologize for being confusing.  However you did follow enough of my encrypted question enough to give me the info I needed.  I used your second recommendation and it worked right away.   Thank you so much!!!.  I will definitely be coming back to this forum if I have anymore questions.  I really appreciate your help!

Chris. 

[CKonash]

Hi.  

One more question regarding our Fire Dept database.  I have a Table of "Event Reports"  Under this table we have 5 categories that events can be (Incident Day, Incident Night, Drill, Meeting, and Special)  I am trying to figure out a way to have an event number auto increment by 1 by looking up the last event number that was used.  The field is called "Incident #" and is only populated during either an Incident type of Event Report (i.e.. there is no number in the field when the Event Report is listed as a Drill, Meeting, or Special)

The data format for the field starts with the 2 digit year and then a dash then the event number.  (15-425) 

Is there an easy way for a script to look for the last incident # or highest incident number and set the field in the new record to the next number?

 

Thank you in advance. 

Chris

 

 

 

[comment]

Quote

I am trying to figure out a way to have an event number auto increment by 1 by looking up the last event number that was used.

This is difficult to set up reliably (i.e. without any chance of generating duplicates). If you don't have a crucial need for this, I would suggest you give every event a serial EventID (auto-entered serial number). Then, if you like, you can display an "Incident #" using a calculation (result is Text) =

Right ( Year ( Timestamp ) ; 2 )  & SerialIncrement ( "-000" ; EventID )

Incidents will not be numbered consecutively (so what) and will not restart at the beginning of a year (so what).