Increment text string +1

[Wickerman]

Okay, I've wrestled with this niggling problem on and off for a few weeks and admit defeat.

I have a Parent-Child set up with a Child-creation system set up from the Parent context. The user fills out a few global fields and then presses a "Create" button that goez to the child table, makes a new record, enters the global values into the appropriate fields, and then returns the user to the parent table and clears the global fields for another go.

One of the global fields is for a Child ID#. This number is a classic museum Accession Number of the format YYYY.#### -- so that in any year, the museum can assign 9999 unique numbers

2009.0001

2009.0002

2010.0001 etc.

Okay, so I want to have a little button that automatically enters the next "available" ID# number in the global field -- that is, the "highest" number +1. (I have the ID# is a text field, though).

The Calculation I have built to do this works well for the first *10* numbers of a year -- for instance through 2009.0010 -- but then hits a snag and delivers this hideous string: 2010..001

So, here's my Calc:

Year ( Get ( CurrentDate ) ) & "." & Right ( "0000" & Right (Max ( Collections::y_Number_Collection ); 4) +1 ; 4 )

I'm sure it looks pretty inelegant, but I'm pretty clunky and new at calcs, so I'd love to hear more natural approaches to this -- but I'd also like to understand what's going amiss here. It looks like my calc isn't "seeing" the final "0" when it tries to go from 2010.0010 to 2010.0011 -- it ignores it and grabs .001 instead of 0010 after correctly grabbing the 'Max' existing ID#. But why?

Thanks for any help.

[comment]

The best way to do this is not to. For the computers, an auto-entered serial number works best. For the humans, a date and a name is much more convenient than a code.

[Wickerman]

Well, I can appreciate that, but what we are talking about is an accession numbering system that is very common in museums, and one which has been in place at this particular archive for many years. This is not a primary key for making relationships, it is something that gets coded onto physical objects and printed onto labels and used in many ways within the organization. They would not find it at all convenient convenient to use a different system.

This is really puzzling me, because I would have imagined this to be a fairly routine bit of calculation work. Even if it is impossible to do what I am asking, it would be valuable for me to understand *why* my solution doesn't work, as it would contribute to my understanding of calculation functions.

[comment]

The issue that you have described is solvable. However, I don't know how to calculate "the next available number" RELIABLY. There's always a danger of generating a duplicate.

Now, your calculation doesn't work because the Max() function works strictly on numeric values - and, numerically speaking, "2000.0010" is actually 2000.001 (trailing decimal zeros are discarded).

[TheTominator]

The Calculation I have built to do this works well for the first *10* numbers of a year -- for instance through 2009.0010 -- but then hits a snag and delivers this hideous string: 2010..001

So, here's my Calc:

Year ( Get ( CurrentDate ) ) & "." & Right ( "0000" & Right (Max ( Collections::y_Number_Collection ); 4) +1 ; 4 )

It looks like my calc isn't "seeing" the final "0" when it tries to go from 2010.0010 to 2010.0011 -- it ignores it and grabs .001 instead of 0010 after correctly grabbing the 'Max' existing ID#. But why?

Yes, you are correct that it sees "2010.001" instead of "2010.0010". It is because the value is being returned by the Max() function which is a numeric function. The number is thus expressed as 2010.001.

You need to convert it back to a string to keep the last zero on there or take a different approach and treat 2010.001 as a real number (increment by .0001) and pad the right with any needed zeros.

I think this does it.

Let(

[thisYear = Year ( Get ( CurrentDate ) );

maxIDString = Max ( Collections::y_Number_Collection );

maxIDList = Substitute(maxIDString;".";"¶");

maxIDYear = GetValue(maxIDList; 1);

maxIDSeq = GetValue(maxIDList; 2)];

Case(

thisYear > maxIDYear; thisYear & "." & "0001";

thisYear & "." & Right("0000" & (maxIDSeq + 1); 4)

)

)

Edited July 10, 2010 by Guest

[LaRetta]

As mentioned, it is an issue of reliability. Is this a multi-user system?

[comment]

How about:

Int ( n ) & SerialIncrement ( ".0000" ; 10000 * ( Mod ( n ; 1 ) +.0001 ) )

[gary2k8]

Not sure if this is the solution you want; I figure a simple way -

split Date and # to two different fields;

If...

Sort Record ( # ) Ascending

Go to Record LAST

Set Var $New #; # + 1

done

[Wickerman]

Finally got back to this item -- thanks for all the helpful suggestions! Tominator's solution seems to be working fine.

[Wickerman]

Actually, I tell a lie. Tominator's solution turns out to run into the sam "Max" truncating issue at the point where MaxIDstring gets defined. So I modified the calculation somewhat, which seems to have done the trick. Instead of padding, I'm keeping the decimal in place and just adding +.0001.

Let(

[thisYear = Year ( Get ( CurrentDate ) );

maxIDString = Max ( _2_Collections::y_Number_Collection );

maxIDList = Substitute(maxIDString;".";"¶");

maxIDYear = GetValue(maxIDList; 1);

maxIDSeq = "." & GetValue(maxIDList; 2)

];

Case(

thisYear > maxIDYear; thisYear & "." & "0001";

thisYear & (maxIDSeq + .0001)

)

)

[LaRetta]

Did you miss Comment's calculation, Wickerman? It works and it is concise and efficient. :)

[Vaughan]

What happens when, after rolling into 2012, the clients announce they have to back-enter items for 2011? I've seen this happen several times.

I have never implemented this, but I think the solution is a table with records that contain Year and Serial fields, so the last number for 2011 is remembered.

It's like keeping separate journals for each year.