Advanced function help

[Aztra]

Hi, guys I need some help converting a work order data base from Access to FM.

Our work order numbers are department specific. Electrical as an example would use E0001…. Where plumbing would use P0001…

In Access my table used 3 fields to accomplish this.

1- Type – This is a stored value from a look up table, this is a letter that defines the type of work order . E= Electrical, P= Plumbing, C= Controls…

2- WOSeqNum – This field keeps track of previously used numbers.

3- WoNum- this is the combined number IE .. E0001

I would like to point out that I require sequential numbering. Each division uses the same numbers IE. Electrical will be E0001,E0002, as will Controls be C0001, C0002…

The code I used to accomplish this in Access was.

WOSeqNum = Nz(DMax("WOSeqNum", "TblPO_numbers", "Type = '" & Me.Type & "'"), 0) + 1
WoNum = [Type] & "" & [WOSeqNum]

Can FM do this with it’s functions? Anyone have any ideas to help me with this issue?

Any help would be appreciated.

Todd

[fseipel]

It is possible to do this by self-relating the WO database to itself. If you have a field for WOTYPE, WONumber_with_Prefix, and WOType_g, where WOType_g is a global field, this will work. WOTYPE would then be a calculated field set = WOType_g. WOType would be set to auto-enter but with an empty value, so it doesn't populate until user selects Electrical, Plumbing, or Controls, say, from a value list. WOType_g would be related to WOType in the relationship graph. You would select sort descending on the relationship.

WONumber with prefix would then be set to:

Case ( DB2::WOType_g="Electrical"; "E"; DB2::WOType_g="Plumbing";"P"; DB2::WOType_g="Controls";"C") & Case(WOType_g<>"";GetAsNumber(Lookup ( DB2::WONumber_with_Prefix ;0 )) + 1)

Setting the default value to zero on the lookup, causes the first WO of that type in database, to be serialized to 1. The check for the WOType being non-empty, prevents the WO number from being assigned before a WOType has been set.

Alternately, you may want to define the WO Types in a related table, with their prefix code, and the current highest serial number for that WO type as fields in this table. In this case you could use a script trigger, executed when WOType is specified; the script triggered, would increment the highest serial number in the related table, update that value, and assign the WOCode with prefix to the main table. This would probably be easier to maintain.

[Aztra]

Thanks fseiple, I greatly appreciate your comments and suggestions . Presently my work order types are in a related field and the prefix is carried over to the type row in the workorder table. It actually sounds like your explaining my access setup in you final coments, so it’s an fm script I need to run.

I’m trying to follow what your saying, I’m a bit confused on how it gets the next highest number, or better yet where.

I’ve attached a portion of the main table converted from Access so you can see the end result.

PO_numbers.fp7.zip

[comment]

I suggest you study these:

http://www.fmforums.com/forum/showtopic.php?tid/191695/post/273995/#273995

http://fmforums.com/forum/showtopic.php?tid/216331/post/363809/#363809