I am writing a Job Book program and I need to add a serial number to records
commencing with 1 at the start of each month each month.
This will be concatenated with year and month number and shown as a 6 figure
number eg the first job in June 2005 will appear as 506001.

I have worked out the 506 format but now need to add the job number. This
number must have 3 number eg. 001 or 071 or 161.

Does anyone know how I would achieve this?

Thanking you in anticipation.

Dave

(I'm using FM7v3 on PC)

tpg <meyers_home (AT) tpg (DOT) com.au> wrote:

Calc field, text functions. First, add "00" to you numbers: "00"& SNR

For your examples above, you'll get 001, 00071 and 00161.

Then use the last three characters only: right ("00" & SNR;3)

And then add the whole thing to your 506.

--
http://clk.ch

"Job" is an auto-enter serial field.
"ID" is the full concatenated serial number you're looking for.

You'll need a startup script that checks the date and resets Job to "1"
if it is the first of the month. This would require setting a global
DateFlag to make sure that it happens only once, the first time the
solution is opened:

If [ DateFlag = Get ( CurrentDate ) ]
Exit Script
Else If [ Day ( Get ( CurrentDate ) ) = 1 ]
Set Next Serial Value [ Job; 1 ]
Set Field [ DateFlag; Get ( CurrentDate ) ]
End If

Your concatenated ID will not be a calculation field itself, but an
auto-enter calculation (text) such as

Right ( Year ( Get ( CurrentDate ) ); 1) & Right ( "0" & Month ( Get (
CurrentDate )) ; 2) & Right ( "00" & Job ; 3)

Just thinking out loud here, but are you sure you want only the last
character of the year? Granted, the ID "506001" would come around only
once every ten years, but I would personally feel safer with "0506001" as
a guarantee of no duplication.

Matt

On 6/21/2005, tpg wrote:

In article <8ATte.3055$PZ6.1445@trndny08>, "Matt Wills" <I'm (AT) Witz (DOT) end>
wrote:


I haven't looked at the script, but there's always an important proviso
with Startup Scripts (which may or may not be important in this
particular case) - they do not work if the database is going to be run
on FileMaker Server. When running on a server the database is rarely
closed and opened, so the Startup Script doesn't actually get
performed.

You also have to be wary of using them if using normal FileMaker to
host databases for other people on the network. In this case it
probably makes no difference, but in other places you may not want the
Startup Script performed on computers that aren't the host computer.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

From the original description, it sounded like a one-person solution.

Matt

On 6/21/2005, Helpful Harry wrote:

Harry,

You previously wrote in answer to another enquiry here

"Create a Relationship link within the file / table to group together
all the records for the same month and year. Then you can use
Count(Relationship::Field) to obtain the number of records within
the same month instead of using the Serial field. "

My brain seems to be fuzzy in regard to how i would do this. Can you
please share yr knowledge with me and advise how this is done.

Thanks

It seems the more questions I have solved the more that remain unanswered.

How do I make the number (the result of a calculation) so that once it is
created it cannot be changed.
EG. If I use an estimated date the work to calculate the job number, I dont
want the job number to change if the date of the job changes.

Thanx again

Dave

"tpg" <meyers_home (AT) tpg (DOT) com.au> wrote

In article <1119397728.491703.316500 (AT) g14g2000cwa (DOT) googlegroups.com>,
"DataMan" <meyers_home (AT) tpg (DOT) com.au> wrote:

I don't know what you may have said before about how the database is
set-up, but here goes.

You're dating records, so there must be RecordDate field that has an
auto-enter option of Current Date, or something similar.

You can now create a new field to store just the month and year from
that field.
ie.
MonthYear Text, auto-enter calculation
= If (IsEmpty(RecordDate), "", MonthName(RecordDate) & "-"
& Year(RecordDate))

With this field you can now create a new relationship:

SameMonthRecords MonthYear = Same File::MonthYear

This means all records created in the same month and year can be
grouped together so you can count the records by adding another
Calculation field:

NumRecordsinSameMonth Calculation, Number result, unstored
= Count(SameMonthRecords::MonthYear)

Put this field on a layout and it will always tell you how many jobs
there are with the same month and year as the current record (it may
sometimes show one less if you're still entering a new record).

You can also get other summaries of the jobs in the same month using
the same method.
eg.
Total Hours = Sum(SameMonthRecords::Hours)

Average Hours = Average(SameMonthRecords::Hours)


The only real problem is if you change a record's date for some reason.
The MonthYear field won't recalculate since the auto-enter calculation
is only entered when the record is first created.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

In article <42ba3545 (AT) dnews (DOT) tpgi.com.au>, "tpg" <meyers_home (AT) tpg (DOT) com.au>
wrote:

The easiest way is to have a second JobNumber field which uses an
auto-enter calculation to store the Job Number when the record was
created.
ie.

JobNumber Text or Number field, auto-enter calculation
= If (IsEmpty(JobNumberCalc), "", JobNumberCalc)

where JobNumberCalc is the original calculated value. You can then use
this new field whereever you need to display an unchangeable Job
Number. (Remember to turn on the "Prohibit Modification" option at the
bottom of the field definition's auto-enter window.)

The only problem here could be if the JobNumberCalc is based on the
JobNumberCalc of previous records. If that's the case then changing the
date will change JobNumberCalc and cause following records to be
numbered incorrectly.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)