Determine record with highest value

[ejpvi]

This should be simple.. I am just not finding the function for it.

If I have a Field with item numbers like: q0001.... q0002.... q0003

I don't want them to be auto-serialized because there are situations where I don't want the "q"

So I am trying to write a script that determines the highest value with a "q" in it and gives you the next one up.

Any suggestions?

[LaurenKuhlman]

Ah good one. Lots of ways to do it but I like this one:

Make a new calculation field. I am going to call it qCounter

qÇounter = 

Case (

   Left ( serialNumber; 1 ) = "Q";

   GetAsNumber ( serialNumber )

)

Then, make another field. I am going to call is qMax.

qMax = Max ( qCounter )

When you create a record, place 

"Q" & qMax

in the field.

I should have noted serialNumber is your field with the item numbers in it.

Edited July 30, 2009 by Guest

[LaRetta]

Without a self join relationship, I don't believe that Max() will help you find the max number through records.

I would seriously consider why you need this, ejpvi, and would be looking at alternatives, since using Max() to determine the next number in a series (using a join) can break in mutli-user mode.

Edited July 30, 2009 by Guest

[ejpvi]

What would cause it to break in multiuser mode? Is it because if multiple users are trying to add a new item number at the same time?

[ejpvi]

I am curious... do you think it is possible to populate a "Custom Dialog Box" with the next value.. and either the user can click accept or change it to something else?

I use pretty strict editing privileges on my item numbers.. so I usually restrict access to a script that has full edit ability and have users enter into a dialog box, then it is all locked down after it is committed.

[LaurenKuhlman]

LaRetta caught me. Make a cartesian (the X) self-relationship.

I attached a sample file. As far as the custom dialog box. It sure is possible. I would add some error checking for dups and whatnot.

qMax.fp7.zip

Edited July 30, 2009 by Guest

[ejpvi]

Well, this field I am trying to auto-generate through a script is not the "key". I have hidden a Master key that serializes automatically when a record is created. What this field represents is one of two item labels.

So in my table for every record, I have an unchanging "Master Key Index" no one can touch this, this is how I tie everything together.

I have a "Sample Item #" it is the item number given before it becomes a product. this is what I am trying to create the script for.

Then on the same record they can populate an "item #" when the item is considered active.

So "sample Item #" and "item #" are not the keys tying the database together, they are used though to label their products. Each has its own purpose, so that is why we need both, and not just one field.

Sample Item Numbers start with a "Q"

Real Item numbers start with anything from "N" to "W" so I just use a dialog box that allows them to populate the "item #", there would be too many options to always populate with a "W"

Each of these sample items are in the same table, because they fill in most of the necessary information of an active item, and then they may want to "promote" an item from sample to Real... hence populate the item #... and still keep track of what the sample item was... so that doesn't get overwritten.

The important thing here is...

Not all items will pass through the "sample" phase, so I need to be able to create a record in the product table without the sample items incrementing, so I decided to make a button that creates a new sample item number label, instead of auto-enter serializing it.

I hope that makes sense.

[comment]

The simple way would be to make Sample Item Number a calculation =

"Q" & SerialID

or rather something like =

Case ( Type = "Sample" ; "Q" & SerialID )

These values would be unique, but not consecutive. IIUC, this shouldn't be an issue, since when you promote a sample you'll create a gap anyway.

Edited July 31, 2009 by Guest

[ejpvi]

Well, I came about a solution in a wierd way. Probably not common practice... but it works for me

I created a table called counter. I have a variable called Dev Item Counter, and I made it auto enter serialized.

When someone runs my "new sample item" script, it hops over to that table... adds a records, records the value as a variable, then deletes the record... then populates my table back on my original layout.

Works great... don't think I can think of any issues... unless someone clicks it at the exact same time..

I do hate having an entire table and field for the sole purpose of a counter... but for this it seems to do exactly what I need.