custom calculation for serial number

[brio]

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.

[AHunter3]

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

[brio]

Thanks. However, this gives me the format: 09/20/06-0001 not the 092006-0001 result desired.

[AHunter3]

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

&"-"&Serial Suffix

[brio]

Beautiful! Thanks a bunch

[wf7a]

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?

[AHunter3]

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.

[wf7a]

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

[rwalsh]

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.

[rwalsh]

Another addition to the equation this has to be usable for IWP

[AHunter3]

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.

[rwalsh]

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.

[rwalsh]

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?

[rwalsh]

Nevermind I figured it out. I just had to change the enter serial to on creation instead of on commit.

 

Thanks very much for you assistance.

[rwalsh]

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.