Complex incrementing Field

[Richard Rosen]

Each workshop registration needs a unique number,(reg_no in the mw_events.dbf) an 8 character field such as DW_01025, JB_043, MF053 and so on.

The first two characters are alphabetical, such as AH, DW, JB and so on.

The third character is always an underscore, '_'.

The fourth and fifth characters are numeric: 01,02,03 etc.
(The workshop number itself (event_id in mw_events) is the first five characters of the registration number.

The next three characters count up from 001.

The first person who registers for workshop # AH_01 gets registration number AH_01001; the next person is AH_01002. And so on.

Each workshop increments in parallel. In this example, the next person who signs up a different workshop, DW_00, becomes registration number DW_03001.

Is this enough detail? I have tried using the function LEFT(event_id,2)+dbmax("mw_events","event_id=var->cveventid","right(reg_no,5)". Alpha won't accept that.
(this will only display the highest value, but that's all the Access system we're replacing did)

Is there any easier way I don't know about?

Thanks for any ideas.

[Keith Hubert]

RE: Complex incrementing Field

Richard,

One way to do this is to break the workshop registration number into smaller sections. Now each section can be controled the way you want. Then have a calculated field which is all the parts collected together. One thing to keep in mind is by having smaller parts to your registration in seperate fields, will make filters and searches easier. Hope that gives your thoughts a new angle.

Keith Hubert
London

[whanigsberg]

RE: Complex incrementing Field

Richard,

I have a similar problem incrementing course section numbers which have complicated alpha-numeric formats. You will find your task greatly aided by the increment_value() function. I thought it was documented but for some reason I can't locate where. But it is easy enough to use.

?increment_value("AH_01001")
= "AH_01002"

?increment_value("AH_S9")
= "AH_T0"

As you can see from the examples it is pretty smart.

Bill

[CALocklin]

RE: Complex incrementing Field

Assuming you enter the workshop number into a field before creating the registration number, here's something I've used in the default value expression in my PTracker application for years:

The resulting task number for project ABCD would look something like "ABCD-002" and all I want to increment is the last 3 characters regardless of how many characters are in the project number.

TRIM( PROJ_NUMB ) +"-"+ RIGHT( TRIM( STR( VAL( RIGHT( TRIM( DBMAX( "Tasks.dbf", "Proj_numb", PROJ_NUMB, "Tasknumb") ), 3) ) + 1001) ), 3)

This sets the new value of my Task_numb field in the Tasks.dbf - yes, the same table that's used in the lookup. The DBMAX() function uses the "Proj_numb" index to find the PROJ_NUMB entered in the current record and returns the highest current "Tasknumb" for that project. The rest of it basically gets the value of the last 3 characters, adds 1, then converts it back to a character field and appends it to the TRIM( PROJ_NUMB).

This should work for you if you just substitute your workshop number for my project number and your registration number for my task number.

In my case, I like the idea of only incrementing the last 3 characters because I don't want any possibility of ever incrementing the project number.

You could probably also modify (simplify) it to use the new Increment() function. (That 'wonderful new function' is causing me problems now because I created my own Increment() function about a year ago that allows me to increment by any value I want such as Increment( "ab90",10 ) = "ac00" so now I will probably have to change the applications that use it when I upgrade them.)

[CALocklin]

RE: Complex incrementing Field

Following up on Bill's thought, I found the 'documentation' on page 46 of the release notes for build 1410-1036.

Here it is:
Increment_Value() Function � Increments a value by "1".

Syntax is:

New_value = increment_value( A Old_value)

Old_value can be a character, numeric, or date value. In the case of a character value, this function increments both the character and numeric characters in the string. For example:
increment_value("AZ-9") "BA-0"

This function is useful for creating custom auto-increment rules in Field Rules. For example, in V5, the auto-increment rule increments the highest value in the field, whereas in V4.5 Alpha Five incremented the value in the last record in the field. Say you want to create a field rule (for a field called "item_id") in V5 that behaves in the same way as V4's auto-increment rule. To do this, define a default value field rule for the field with this expression:
increment_value(previous("item_id"))

[whanigsberg]

RE: Complex incrementing Field

Thanks Cal,

I knew I had read documentation on this but couldn't remember where. I have a new political slogan: "It's in the release notes, stupid!"

Best,
Bill

[Richard Rosen]

RE: Complex incrementing Field

I got this working up to a point, as follows:

left(Events->Event_Id,5)+right(trim(str(val(right(trim(dbmax("mw_events.dbf","Reg_No_Event_Id",trim(event_id),"reg_no")),3))+1001)),3)

What this delivers is the highest value in reg_no of mw_events, incremented by 1. This happens to be TM_01039. What I need is the highest value FOR THE PARTICULAR WORKSHOP. For example, if the event selected is JB_01, I need JB_01021, not TM_01039.

I thought about using a filtered index on event_id for each workshop. As we do about 10 per year, this would be unweildy but perhaps doable. Preferable would be to filter dbmax, or use tablemax. Does this strike you?

[Richard Rosen]

RE: Complex incrementing Field

FYI, with your help I got this!

Solution:
trim(event_id)+right(trim(Str(val(right(trim(str(dbcount("mw_events","Reg_No_Even

t_Id",event_id))),3))+1001)),3)

Only took me 20+ hours. Could not have done it without your suggestions. Never got it to work in Access.

Thanks a million!