Jump to content

Defining automatic serialized number...


This topic is 5749 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 by Guest
Typo fix
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...? :nahnah:

--sd

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

...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. :smirk:

LaRetta

Edited by Guest
Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This topic is 5749 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.