Jump to content
Sign in to follow this  

Next number in a series based on another field value

Recommended Posts


This all happens in the same Table and TO.

Field1 contains a number provided by the user (1 to 15).

- All records in the TO have a number in this field.

- Several records might share the same number (there might be several records with 1 in field1, and several records with 2 in field1, etc.)

Field2 must be automatically calculated based on field1. The calculation should be:

Field1 & "-" & Record Position according to the number of records of same type present when creating the record (a typical serial number).

For instance, 1-1, 1-2, 1-3, 1-4, 2-1, 2-2, 2-3.

When the user creates a record, he/she must select a number in Field1. Then the program should assign the value in Field2. If the user select 5 in Field1, and there are already 3 records present in the TO with a value of 5 in Field1, then Field2 should be automatically assigned the value of 5-4.

The user might choose any value in Field1 at any time. For instance, might add a new record and assign to Field1 the value 2 for that record, even in there are many records with values of 1, 2, 3, 4, etc. in Field1.

Ah! One last point. The user might change the value of Field1 at any given time. Field2 must be recalculated accordingly. For instance, if there are 4 records assigned with a value of 2 in Field1:

2-1, 2-2, 2-3, 2-4

and the user changes the value of Field2 for record with value 2-2 to 2-1, all other records must renumber accordingly, like:

2-1 is the new value of 2-2

2-1 changes to 2-2

2-3 remains unaffected

2-4 remains unaffected

Could you help me with (ideally) a custom function to handle this problem?

Thank you

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.