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

custom calculation for serial number


brio
 Share

Recommended Posts

I'm a newbie at FM. I'm trying to figure out how to set up a calculation for a serial number field that will include the date of creation as part of the serial number. Example result: 091706-0001 (first six numbers as date, last four as incremental serial. Any help is appreciated. Thanks.

Link to comment
Share on other sites

Create these three fields:

 

Creation Date, date, auto-enter Creation Date

Serial Suffix, numerical, auto-enter serial number

Total Serial Number, calculation, text, = Creation Date&"-"&Serial Suffix

Link to comment
Share on other sites

Total Serial Number, calculation, text, = Right("0"&Month(Creation Date), 2)&Right("0"&Day(Creation Date), 2)&Right(Year(Creation Date), 2

&"-"&Serial Suffix

Link to comment
Share on other sites

  • 4 weeks later...

I hope you don't mind me popping in with a question along this topic, but I have a similar calc problem but with a twist: what if you want the calculation to reset the counter at the turn of the year so in, let's say, 2007, it resets to 010107-001?

Link to comment
Share on other sites

Define numerical field, "CrYear", = Year(Creation Date).

 

Create a selfjoin of the table to itself based on CrYear = CrYear. Name the new Table Occurence of your table "SameYear".

 

Instead of Suffix being a regular serial number, set it to auto-enter a calculated value:

 

Case( not Max(SameYear::Suffix) > 0; 1; Max(SameYear::Suffix) + 1)

 

There's a checkbox at the bottom of the dialogue where you specify the calculation, that says "Do not evaluate if all referenced fields are empty". Uncheck it.

Link to comment
Share on other sites

Nuts. I can't post an attachment so here's how I put it together textually. I'm not sure where I muffed putting the calc together because when I set the Layout Setup to show records from the Reports table, I get in all three fields. When I set Layout Setup to SameYear to show its records, the Suffix field remains empty and the Year and Result fields just show 2006.

 

FYI: In my solution, I'm more interested in just the year for the prefix than DDMMYY, so I have that field defined as:

 

Year = year (get(currentdate))

 

I'm using Year field in each table for the relationship between the two tables*, Reports and SameYear. (Reports is home to all the fields are defined, so far, in my database.)

 

 

Table: Reports

 

YEAR[calculation, unstored] = Year (get(currentdate)

 

SUFFIX [number] = Case (not Max (SameYear::Suffix) >0 ; 1; Max (SameYear::Suffix) + 1)

{Autoenter Calculation, Evaluate Always, Always Validate}

 

RESULT [number] = Year & Suffix

{Autoenter Calculation --- "Do not replace existing value..." [checked] --- "Do not evaluate..." [unchecked]}

 

 

Table: SameYear

 

YEAR [calculation, unstored] = Year (get(currentdate)

 

SUFFIX [number] = Case (not Max (SameYear::Suffix) >0 ; 1; Max (SameYear::Suffix) + 1)

{Autoenter Calculation, Evaluate Always, Always Validate}

 

RESULT [number] = Year & Suffix

 

{Autoenter Calculation --- "Do not replace existing value..." [checked] --- "Do not evaluate..." [unchecked]}

 

*I resorted to referring to FM8's Help menu to describe self-joining relationships and got royally confused, so that's where I think I hosed up everything.

 

Thanks for your help and patience--I _REALLY_ appreciate it! (I could gush and say what a valuable resource you are to us novices, but you already know that. :))

Link to comment
Share on other sites

  • 2 weeks later...

I have a similar request, which I want to play to almost the same purpose.

 

Im trying to Generate A Request for Estimate #

 

The system I am trying to build is off the bases of a scripted button.

 

Here's the run down, create a record, now there are several Options to the estimate so it needs to create a duplicated record, with the same Estimate number, with the exception of adding a - 1 to the current record, and automatically going to a - 2 for the new record, and continuing the auto enter for as many times as needed 10 records, -1 -2 -3 -4.

 

So i need it to keep the current serial value, but add the extra text only when needed.

 

RFQ # = 000001

After Duplicate

RFQ # = 000001-1 with a new record of RFQ # = 000001-2 and so on. If that doesnt make sense let me know so I can explain it better.

 

But I need to make sure that when RFQ # = 000002 and the record gets duplicated it doesnt go to the next number IE RFQ # = 0000001-5, RFQ #= 000002-6 it needs to be "-1".

 

Any help would be good, hopefully a fast response.

Link to comment
Share on other sites

Try this:

 

Set Variable [$OriginalRFQ, Table::RFQ]

Duplicate Record/Request

Set Field [Table::RFQ, Case(PatternCount($OriginalRFQ, "-")=0, $OriginalRFQ&"-1", Middle($OriginalRFQ, 1, Position($OriginalRFG, "-", 1, 1))& GetAsNumber(Right($OriginalRFQ, 1)) + 1)]

 

 

Note that you're going to end up with non-unique RFQ numbers if you duplicate 12345-1 (thus creating 12345-2) and then go back and duplicate 12345-1 again later on.

 

If you need it to be unique, you need two separate underlying fields, BaseRFQ and SuffixRFQ and a calc field that combines them with a dash for display purposes; and you'd need a selfjoin relationship of BaseRFQ to itself so as to know how many of them you already have and therefore know what suffix to pop on the end when you duplicate.

Link to comment
Share on other sites

How many Fields do I need to setup for this, so far I have the basic RFQ Field and the RFQMulti field.

 

Complex calculation scripts confuse me, and thats why I am asking for the aid on this one.

Link to comment
Share on other sites

That worked, but I need it to auto enter the initial RFQ#, IE:000001, and if I try to use the AUTO-Enter feature it bumps it up to 000002 once the record commits. Any ideas?

Link to comment
Share on other sites

  • 1 month later...

I'm back to this, I never noticed this issue until I was requested to make a change to the Prefix on the RFQ#.

 

After I hit the multiple RFQ Button, it does as it is supposed to, but If I were to create a fresh RFQ now, by hitting the new button it jumps a number.

 

IE: RFQ = 000001, I hit Create Multiple RFQ # Button. I get RFQ= 000001-1 like I am supposed to. Now I hit, New RFQ, I should be getting RFQ = 000002, but I am getting RFQ # = 000003.

 

Now I looked through the script, and its because of the duplication process. But if I remove that the Multiple RFQ overwrites the original, which I dont want, but if I have to resort to that, then I will. But is there any way around this. I have the three following fields that make up the RFQ Number.

 

RFQPrefix - AutoEnter Data 06

RFQMultiple - AuterEnter Serial 0001 Increment 1

RFQ # - Calculation "RFQPrefix & RFQMultiple"

 

And the script is exactly as you wrote it.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use