HELP!!!

[jointv]

hi, lets cut to the chase...I work at a small marketing firm and we use a combination of filemaker 5 and filemaker server 5.5, we use these to track our customers and current jobs. we have a form we use for our job jackets (a job number, contact info, and some misc details on the project). Now for the problem our web developer/multimedia designer/IT guy/knows how to use filmaker guy recently left the firm and now I (i am a graphic designer) am left to pick up the slack until we find a replacement. so my question is our job jacket is labeled with a number, the year followed by 3 digits (04-***)...well its 05 now and we need to change the year in our job numbering system. On the filemaker 5.5 side the job number field is not editable and this is where my problem starts. Basically i need to be pointed in the right direction here on how to either change and restart the job number feild (05-001....) or is there a way to use the old database/form (sorry not sure of the terminology) to create a new form for the new year. Thank you in advance

[DykstrL]

Go to define fields (you must be logged in with a master password) and find the definition for the job number field. It is probably set as a serial number where the number starts with "04-" then a number. Just set reset the serial number to be "05-001" and increment by 1.

If you want to get fancy and not have to do this again:

Create another field "serial_number" and set it as text and serial number "001" and increment by 1.

Change your job number field to Auto-enter a calculation:

Right(Year(Status( CurrentDate)),2) & "-" & serial_number

and click "Prohibit modification of value"

[SoCalMacDude]

Or, if the guy was not too bright, he may have just placed the number "04" on the layout next to the job number. If so, go to layout mode and just change it. Of course, that would change all previous numbers, but let's hope he was not that stupid!

I'm sure Addict's answer is correct! Good luck!

[Sxeptomaniac]

This thread was a help to me recently. I just had to make a serial number similar to this in a version 6 database. The one problem with the current system is that the serial number part of it doesn't roll over at the beginning of each year. It took a little work, but I think I found a decent, though maybe messy, system to make it work.

I used four (unmodifiable) auto-entered fields and a selfjoin relationship to do this:

ReportYear

ReportSerial

ReportSerialLookup

ReportNumber

relationship named "ReportYear Selfjoin" , which joins ReportYear to itself, with the related records sorted by ReportSerial in descending order.

The fields are defined as follows

ReportYear - text - Auto-enter calculation = Right(Year(Status( CurrentDate)),2)

//thanks to DykstrL for showing this part previously

ReportSerial - number - Auto-enter calculation =

Case(IsEmpty(ReportYear selfjoin::ReportYear), "001" , ReportSerialLookup + 1 )

//This must be a number field, in order for it to sort correctly (otherwise it stops working once you hit 10. It will increment by 1 each time, but roll over to 001 again the first time a new year is created.

ReportSerialLookup - text - Lookup: Use relationship "ReportYear Selfjoin" to lookup ReportSerial

If no match, copy "001"

//This keeps track of the last number used, and will also rollover each year.

ReportNumber - text - Auto-enter calculation = ReportYear & "-" & Choose( Length( ReportSerial ), "" , "00" , "0") & ReportSerial

//this combines the ReportYear and ReportSerial numbers together in the final format, and makes sure RepportSerial has at least 3 digits.

I noticed one effect of doing it this way is that if you delete the last record created, the next one will not have a gap, as the typical format. I imagine that could be a bonus or a liability, depending on what you prefer.

If anyone has a better way to do this, please don't hesitate to show me, as this one seems a bit messy. I'll have to do this in Dev 7 soon, so maybe someone has a good idea to make a simpler version there, too.

[-Queue-]

Here's a slightly different, simpler method for version 7.

ReportYear and ReportYear Selfjoin - same as above (except 'Status' should be 'Get' in version 7)

ReportSerial - text - auto-enter calculation with 'Do not evaluate if all referenced fields are empty' deselected =

Right( "00" & ReportYear Selfjoin::ReportSerial + 1; 3 )

ReportNumber - text - auto-enter calculation ReportYear & "-" & ReportSerial

You can use the same method in 5.5 and up, but you'll need to use

Right( "00" & NumToText(GetField("ReportYear Selfjoin::ReportSerial") + 1), 3 )

for ReportSerial.

[Ron F]

Thanks, this is exactly what I was looking for. Great Job!!!