Auto Entered Incrementing Record Number

[CKonash]

Hello, 

2nd post here on the forum, thanks to everyone for their help!

I have a few databases for local fire departments and they all reference their incident reports with an incident number.  They like to have this number start at 1 on January 1st and restart at 1 each year.    I have a field (incidentYear) that auto enters the year of the incident.  I also have a field  (IncidentNumber) which the departments enter manually.  The number starts at 001 and they manually increment up 1 number for each call.  The IncidentYear field and IncidentNumber field are then combined in a calculation (IncidentNumberDisplay) field so we can sort, reference, and search for them later.  

My question is how do a write a script that will look for the count of calls already created that year and generate a new record with the next number?

This is all being used on ipads so a processing heavy script that will be slow to calculate is something I am trying to avoid.  

I tried using the auto enter serial number feature but that doesn't reset in January and it doesn't take into account people that create a call in error and then delete it.   

Any help you can offer would be amazing. 

Thanks

Chris

[Fitch]

This is a question that comes up regularly. Not sure there's ever an ideal answer, but here's a few options:

1. Create a table of incident numbers and a script that "takes a number" when creating a new report.

2. ExecuteSQL( select max(number) from table where year = ? ; ""; ""; Year( Get (CurrentDate) ) ) + 1

3. Set Date( 1; 1; Year( Get (CurrentDate) ) ) into a global field with your startup script; make a relationship to your reports date field, and use Max( related::number) + 1

HTH

[NLR]

There are one or two ways I've done this type of thing.  The sample I've just made doesn't use a script.

A relationship and three fields are used:

theYear:   A number.  An auto entered calculation = Year(Get(CurrentDate)

Relationship:  Based on theYear=theYear.  I named it BaseTable_SameYear

IncidentNo:  A number. An auto enter calculation which replaces existing value.  Evaluate always = Last ( BaseTable_SameYear::IncidentNo )+1

YearlyIncidentNo:  A calculation of type text = theYear & " - " & IncidentNo

It's important when defining IncidentNo, to evaluate always.  This ensures things work for the first record.  I've opted to replace existing value, the reason being that it allows a record to be duplicated without causing the IncidentNo to also be duplicated.  Incidentally, the use of the Last function is much faster than using the alternative Max function, which is noticable when there are a large number of records.

Ralph

YearlyBasedRecordNo.fmp12.zip

[doughemi]

All of Fitch's solutions are good ones, but be aware that in a multiuser solution, there is a very real danger that numbers will be duplicated if requested at about the same time (if the first one isn't committed before the second one is requested).

[CKonash]

Thanks everyone.   Ralph, I installed all the items you mentioned and made the relationships.  Everything is working well with it auto generating the incremental incident number however, sometime pretty regularly it will skip a number.  Any ideas what might case that?

Thanks

Chris