Hi all,

I am the administrator for a newly created database being used to track photo assignments for a newspaper. To this point, i've used the auto-enter unique serial number function to generate a unique, issue-specific number to identify each assignment. The serial number was the volume number, the issue number, and the assignment number (being the next logical number (.01, .02, .03, etc)) all separated by a period.

when editors would need to start making requests for the next issue, i would modify the serial number in the field options to reflect the new issue number and reset the assignment number at .01.

My system has now reached a flaw. If a section editor enters a photo request that will run in any issue than the very next one, the serial number contains the wrong information. i want to make a calculation that will take the "volume#.issue#" that is manually entered on each record by the editor, and automatically add the ".01, .02, etc" on the end to create the unique issue-specific identification number.

i think i'm going to have to create two fields for this. one for the editor to manually enter the volume and issue numbers in, and one for the calculation result. (right?)

i think the calculation has to start with:

volume.issue & "." &

(not too sure about that)

but after that, i'm not sure how to get it add the counting number.

any help would be more than very helpful

Create a calculated field that concatenates the volume and issue numbers:

Volume_Issue_Key = (Right("00000" & Volume , 5) & Right("00000" & Issue, 5)

If an editor enters Volume 121, Issue 62, the result will be 0012100062.

Now create a self-join relationship based on this new field.

As a minimum, just to create a request number, create two global fields, Volume_Global and Issue_Global. Set up a button the takes the editor to a screen where these two fields are displayed, along with an instruction to specify for which Volume and Issue the request is being made. Add a "Sumbit Request" button at the bottom that is linked to: Perform Script (Create New Request)

You can also create other global field(s) on the same screen to capture everything needed to specify the Assignment, at once. The reason for using globals is to give the editor an option to press Cancel and not leave anything dangling in your database or any unused Request numbers. Add a Cancel button that runs a script to clear the globals.

When the editor fills in the global fields, he/she then presses the "Submit Request" button. This fires off the "Create New Request" script:

If ( not isempty (Issue_Golbal) and not isempty (Volume-Global) )

Create New Record

Set Volume = Volume_Global

Set Issue = Issue Global

Set Assignment = Max (Self Join:Assignment) + 1

Set Any Other Fields filled on Global screen...

Go to the Layout with the "real" fields

Else

Show message ("Hey Can't You Read? Fill in the fields!")

Exit Script

End If

Whoops!

I forgot the complete ID:

Create a calculated field:

THE_Number = Right("00000" & Volume, 3) & "." & Right("00000" & Issue, 3) & "." & Right ("00" & Assignment, 2)

This will yield a nnn.nnn.nn format, which can be adjusted by changing the 3s and 2s, above.