Defining automatic serialized number...

[anders_t]

Hi all!

I've got a number field where I want the value to be incremented by 1, but with an added "-06" or "-07", depending on what year it is.

The result should look something like "102-06".

Is it possible?!

thanks in advance

Anders

[mr_vodka]

Why not create a calc field with the concat. You can still use your number field as the unique serial but display the calc field to users.

The calc could be RecordID & "-" & Right ( Year ( Get(CurrentDate)); 2)

Edited December 22, 2006 by Guest
Typo fix

[bruceR]

That won't work. It should be right(year(get(currentDate);2))

[mr_vodka]

Sorry Bruce, typo. I could have sworn that I had typed year () in the forumla. I know I was thinking it while typing but it just didnt transpose through my finger I guess. My fingers must already be on holiday vacation. lol. Good catch.

[anders_t]

Thanks you guys, that worked!

[Jeffrey Whittaker]

I needed almost that exact thing, except I am new to FM and must be missing something.

I need to have a serial number in the form of

D061000

D= Name Hardcoded or from other field.

06 = Year current

1000 Serialized number.. automaticly generated.

Thanks

[Fenton]

You could do it similarly. I would do it as a text field, with an auto-enter calculation, with the [ ] Allow replace option unchecked (so it can update if you change the name; or not). But first, a couple of suggestions.

You are not really talking about a "serial number" field, from a FileMaker developer's perspective. What you're talking about is a "serial number" from a "business rules" perspective. These are not really the same thing. From a FileMaker developer (purist) point of view a real "serial" field has NO user-enterable, or at the very least, no user-modifiable data.

This is critical for maintaining relational integrity. If a user can modifiy a "serial" after entry, then there is a possibility that they can break the relational connection to child related data. In your example, the "name" is suspect. While it is unlikely to be entered incorrectly or change, especially after child data has been entered, it is possible. It is also possible that the powers that be decide to change what that 1st letter is, or use 2 letters, at a later date.

If you must have such a business serial, then only use it like any other important data field, but do NOT use it in relationships.* You can use Lookup to bring it into child files, if needed for local Finds. But then you'll have a maintenance problem if it's modified in the parent table; another reason why it's not a good idea to have user-modifiable identifiers.

1. Create a real Serial_ID field, using FileMaker's auto-enter serial options. It can be either text or number. If number, you'll need to "pad" your other "business serial number", to get a decent length.

2. Create the other "business ID" as an auto-enter, by calculation, with the [ ] Allow replace option unchecked (or not).

Case ( not IsEmpty (Name);

Left ( Name; 1) & Right ( Year ( Get (CurrentDate) ); 2 ) &

Right ( "0000" & Serial_ID ); 5 )

)

Basically, from my point of view, the whole idea is kind of silly. Why not just use a real serial number? What real use is "D" and "06". You have this data already in other fields. This all may seem overly cautious. But it is no fun if your relational ties break.

*A rare exception is if you have to import data from another source that uses this format of business ID, in which case you might need to use it in a "temporary" relationship.

[cwcrogan]

I don't mean to thread jump here, but I have a very similiar question. I have a serial number used for FM relationships, but I also need a second serial for records. This second serial, similiar to above, needs a two digit year appended to it. BUT, it also needs to reset at the start of a new year. How can I accomplish this?

Edited December 27, 2006 by Guest

[mr_vodka]

Take a look at these two thread

Thread1

Thread2

[cwcrogan]

Excellent, thank-you. I tried searching but couldn't think of the correct terms and gave up after reading countless threads.

In the first thread there are basically two methods mentioned. The first uses a self join relationship with the year field and then counts the number of records. The second uses a calc (or custom function). They both seem like they will work, but comment has reservations about the second. Do you know why? I can't see the problem. The records should have a unique user serial number that doesn't change if other records are deleted. It seems to me that the first method could change the number with record deletion.

BTW, I'm using a dedicated serial field for keys, this is just for user recognition.

[comment]

Actually, I have reservation about both. The reason is that in a multi-user scenario it is difficult to prevent a duplicate number being issued to two users requesting a new record simultaneously.

[cwcrogan]

Okay, then how would you approach it?

[comment]

I wouldn't. I see no good reason for it. On the contrary, I think resetting invoice numbers each year can reveal strategic information about the business. Numbers are for computers, anyway. Let the humans give meaningful names to their documents, and not care about the numbers.

Just to clarify: I am referring only to resetting the serial number. There's no problem with appending a year to a serial number.

[cwcrogan]

That's essentially what I'm resetting (the invoice numbers) not the true serial number.

[Søren Dyhr]

I think resetting invoice numbers each year can reveal strategic information about the business

It may still be some guessing, since only if every invoice have an avarage sum then... or do you mean the likelyness to be doing two hermeticly separate systems, one for the autorities and...?

--sd

[comment]

That's essentially what I'm resetting (the invoice numbers) not the true serial number.

I don't think that matters. The question is, is this number used for anything? If it's not used for anything, why should anyone care if it's reset every year or not? If it IS used, then the question becomes: can you afford a duplicate in a worst-case scenario? If yes, then use any method you want. If not, then I don't know of a self-adjusting method that I could recommend in good conscience. I *think* that running a script to actually reset the serial number should be pretty safe.

It may still be some guessing

Well, I didn't say it was all-revealing. But I think the annual number of sales can be an indicator of the firm's well-being. Actually, I haven't got a clue about commercial espionage; but it seems like a good argument to get the client off this idea...

[LaRetta]

...but it seems like a good argument to get the client off this idea...

Agreed. And I use it myself. By viewing a Client's check numbers, I can tell how many checks they write (at least from THAT account). Their purchase order sequence also provides information about them. But stop it? I'm unsure how unless we eliminate sequencing entirely. Invoice numbers, Customer numbers ... are ALL indicators of a business' health. I always inflate, even when starting a sequence but that doesn't stop someone noticing the pattern quickly.

LaRetta

Edited December 29, 2006 by Guest

[comment]

Granted - but if they are not my regular customers, they will have to wait a year to get this data.

[cwcrogan]

Thank-you for your input comment. I was trying to apply this technique to two different solutions. For one, I now see your point and will simply append info to the actual serial number without trying to reset it yearly. There was no real reason other than human readability. For the second, I'm still in turmoil as to how to approach this. I can't have dups, but may just need human intervention to prevent them (as the current solution has).