Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Auto enter serial number with multi key


Norma_Snockurs
 Share

Recommended Posts

I'm struggling with finding a way to auto generate a multi-key serial number using just one field.

 

Obviously it's easy using more than one field where is an auto-entered serial and the value of is the auto-entered calculation:

 

& "" & "" & "" & "" etc…

 

 

But I'd prefer to generate from one field rather than it being reliant on two fields. Return characters don't seem to work as expected when used in 'Auto enter/serial number/generate/next value'.

I've tried experimenting with 'GetNextSerialValue' function but it doesn't seem like the right approach.

 

Thanks in advance for any bright ideas.

Link to comment
Share on other sites

I don't think you can do it with auto-enter serial. You may be able to do it with auto-enter calculated value, but in order to figure out what the "next" value is you'd have to know what the current highest value is on the part that increments. You might be able to get that info using Max() across a selfjoin relationship, but it would be rather messy —*the multikey field would be a text field, and even if the incrementing part of your multikey field is the part in front, you'd have to test a lot of permutations to see if it's reliably getting the value you want, Max(TextField) doesn't behave quite like Max(NumberField).

 

I'm curious about why you don't want to use a calc field for your multikey and have a regular incrementing serial number be just part of it.

Link to comment
Share on other sites

I'm curious about why you don't want to use a calc field for your multikey and have a regular incrementing serial number be just part of it.

 

I just like to limit the amount of fields defined in any one table I guess. I think it's a good habit to have, as applying that way of thinking throughout the development of a large database could conceivably cut down on a lot of field definitions. I try to put performance very high on the list of considerations during development and I'm sure all here would agree it's a consideration to be ignored at peril. I just hate the way all those related fields force unstored calculations everywhere so try very hard never to define a field unless absolutely necessary.

 

At least in this instance I can index both fields minimally and a calculation only occurs upon record creation so I reckon I can live with the negligible performance hit.

 

I still can't help thinking 'I must be able to do this with one field' though.smiley-wink

Link to comment
Share on other sites

Well, you could script it. You know, "New Record" button that snags the maximum value of the incrementing part of the serial and sticks it in a variable, then creates a new record and sets the new rec's serial to a calculated value (multikey) that includes the incrementing part.

 

I agree about trying to keep field count as low as possible.

Link to comment
Share on other sites

I just hate the way all those related fields force unstored calculations everywhere so try very hard never to define a field unless absolutely necessary.

I'm not sure I understand how a multikey field is going to help you there. If you have a calculation over related fields, the calc is going to be unstored, no matter on what kind of fields the relationship is defined.

I am probably missing the point.

 

maarten

Link to comment
Share on other sites

I'm not sure I understand how a multikey field is going to help you there.

 

I was just clarifying the reason to AH as to why I generally wanted to define as few fields as possible. This desire isn't completely relevant to my original issue but rather the habit acquired from developing of trying, not always successfully, to take the 'path that uses least fields'.

 

I do appreciate that as soon as there is any relational component in your database the unstored calc issue begins to eat up DB performance rather too quickly for my liking and there is usually no option but to live with it. My reasoning is that the less fields you define in the first place then the less there are to end up being related and used in unstored calculations. You might have to plan your DB's a bit more smartly taking this approach however.

 

The multikey field I was talking about isn't used in any calculation. It's just an indexed static relational key. I don't think you missed the point though, from what I've seen here it's always worth getting another take on an FM issue.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use