Conditional Serial Number

[esegal]

I've searched and have not found a solution for this. I am a novice/intermediate user so bear with me if the solution is simple. I also hope this is in the right forum, please tell me if it's not.

I have a PROJECT_DB with a layout called "RFI". I have a CONTACTS_DB where each individual and their company also has a representation of three initials (Universal Consulting Engineers = UCE).

In the PROJECTS_DB, when I enter a contact into an RFI record, also linked would be the three initials for their company. What I need is the serial number to be contigent on the initials, so the first few records might be:

UCE-001, UCE-002, ACM-001, UCE-003, ACM-002, UCE-004..

whereas each company has it's own serial number series that advances one each time an RFI is linked to that company.

Oh, and I may have to delete a record before it is implemented, so I'd rather not have an auto-enter serial because if I delete the record without implementing it I end up with a gap in the sequence.

Need help soon as the DB is active and I need a solution before I create too many records.

Edited September 19, 2005 by Guest

[CobaltSky]

Hi esegal,

If your database is single-user, or if the usage patterns are such that you don't expect that users will be creating records simultaneously, then you may be interested to check out the technique file at:

http://www.nightwing.com.au/FileMaker/demos1.html#d7

It is in a format suited to an earlier version of FileMaker - but the technique will work just as well in v7 or v8.

If your database is a heavy-use multi-user solution, then you may be better off to have your serial numbers generated by script, using a process that will issue a lock before grabbing the next available number. However to make such a process fully automatic (unless your solution is locked down and fully scripted) you'd require a script triggering plug-in.

[esegal]

I've integrated the Nighwing script and it works...and then again it doesn't.

For some reason the first record came up as -002 instead of -001. As I tried to update my previous records (17 so far) they were all off by one, making record 17 is -018. When I tried to correct -002 it became -019 and so on. I can't seem to start over.

If anyone is familiar with the Nightwing script, is there any way to override the numbers?

[CobaltSky]

Yes, I'm vaguely familiar with it...

However before answering your question, I have to say that if your implementation is returning numbers other than -001 as the starting number for a given client series, then there must be something about the way you have set it up that differs from the demo from the NightWing site.

I've just downloaded a fresh copy from the site, converted it to fp7 format and it is working correctly, straight off, on both Windows and Mac. If I delete all the records and create a new record with the ClientID ABC, the serial it generates is ABC-001. If I then create a new record with clientID DEF, I get the serial DEF-001, and if I create a third record and enter the clientID ABC again, it returns a serial of ABC-002.

Assuming that is the behavior you want, then if you match your set-up to the ser-up in the demo, you should see the same behavior in your solution.

Notwithstanding that, if you need to manually edit the serial number generated by the calculations in the demo, you can do so by directly editing the value in the field named "serial" (in the demo). A change to that field will be reflected in the value in the ClientSerial field. :)

[esegal]

A it turns out the 002 is due to my reusing my previous field that had a "+1" condition on it. I fixed the field and the error using your fix, so it's fine now. I'm going to use it for a while prior to releasing it to my office.

Thanks for the assistance! This is going to be very useful tracking information across the 60+ companies on this project.

[esegal]

Thanks so far, but one more question - how can I modify the calculation so the next serial number is calculated and input ONLY for one particular record_type?

[CobaltSky]

Hello esegal,

Your question could be interpreted in a number of ways. You say calculated and input for a particular record type, but you don't say how to tell what type a record is. You also don't say whether you want records that don't have a serial calculated should be counted anyway when calculating the next serial number.

Assuming that you have a field that indicates the record type (or some other reliable test that can be calculated) and you only want records where a serial is entered to be counted, here is a method youi might use.

If you were to add a field called RecordType to the demo file and change the relationship definition to:

ClientID = ClientID

AND RecordType = RecordType

and then change the formula for the ClientSerial field to:

If(RecordType = "whatever"; ClientID & "-" & Right("000" & GetAsNumber(serial); Max(3; Length(GetAsNumber(serial)))))

...then the conditional serial numbers would only be allocated on those records where the RecordType field has a value of 'whatever'.

If you make comparable changes in your own file you may then get the behavior you are looking for.

[esegal]

That's what I was trying to accomplish. Each layout gets it's own record_type field (as well as the Contact_ID and Company relationships) so this should work well. Each record also gets a serial no matter what record_type it is. I am not savvy enough to nail these things down just yet.

Thanks to those who are.

[Stepheno]

Hi All,

I have a similar issue. If someone can help me I would appreciate it.

I have the serial number auto increment when a record is created, but when I delete a record the sequence is skrewed. How would I be able to have it delete or subtract the increment when a record is deleted?

[CobaltSky]

Hi Stepheno,

If I understand you correctly, the simplest answer would be to ensure that records can only be deleted by script, and to have your script check the last serial number and use the Set Next Serial Number [ ] script command to reset the auto-entry serial numbers accordingly. :)

[Stepheno]

Thank you so much!

[Stepheno]

Hi CobaltSky,

Woud you be able to illustrate the script? I have done some research and I had no luck. I am not too familiar with scripting.

Your help will be much appreciated :)

[Stepheno]

Hi CobaltSky,

I figured it out!!!

Thank you for the direction.