sequential numbering - should I start in Access backend or A5

[Malcolm]

I've read a large number of posts on the topic of sequential numbering, but they all start a bit ahead of where I am. Could someone get me started please?

My database helps company 'branches' keep track of their 'projects.' Each branch wants to identify their project by a 'number' once the project is 'approved.' The 'string/number' is to take the form 'number / year,' as below. Projects are assigned numbers only once they have an ApprovedDate. The table should look something like this (if it prints OK):


ProjectID BranchID ApprovedDate 'Number'
1 12 1/2/2013 1/13
2 12 1/4/2013 2/13
3 12
4 14 3/4/2013 1/13
5 14 3/5/2013 2/13
6 12
7 12 6/7/2013 3/13

I use Access as a backend. Access solutions tend to work through the 'form,' which doesn't apply in this situation. Should I plan to get Access to generate the 'number' in the projects table once the ApprovedDate is allocated, or should I generate the 'number' in A5V11 and store it in the table? If in A5 where?

Any pointers greatly appreciated.

This is all new to me, so I'm keen to try and maximise effort and time spent chasing the solution. Can anyone advise me which end to start?

[Ted Giles]

Re: sequential numbering - should I start in Access backend or A5

It should be easy enough to increment the number in Alpha.
Could you explain the numbering more clearly please?
Like which bits are unique, which are incremental.

[agillbb]

Re: sequential numbering - should I start in Access backend or A5

You seem to be on the right track, and I would agree with Ted.

You need (and probably have) these tables:
Branches (BranchID - autoinc, BranchName - char)
Projects (ProjectID - autoinc, BranchID - long integer, ProjectYear - integer, ProjectSerial - integer, ProjectName - char, ApprovedDate - date)

I would then create a left join Query view in Access that shows all the project table fields as well as the BranchName and maybe a field called ProjectCode (a string that combines the ProjectYear and ProjectSerial. e.g. '3/13'). This query view would then be used in your grid or dialog instead of the actual table.

In general, I let Access generate auto-incremented ID numbers where ever possible - as in the first table's BranchID and the second table's ProjectID. But it would make sense to let ProjectCode hold your unique calculated string like '3/13'. Your xbasic code could insert the correct year and serial numbers at the time of insert (by first querying the Projects table for the largest branch serial + 1).

If possible, could you change the project code to '13-03' instead? This would allow easier grid sorting in future.

Good luck.

[Malcolm]

Re: sequential numbering - should I start in Access backend or A5

Thanks both for your responses. a couple of points of clarification please?

"I would then create a left join Query view in Access that shows all the project table fields as well as the BranchName and maybe a field called ProjectCode (a string that combines the ProjectYear and ProjectSerial. e.g. '3/13'). This query view would then be used in your grid or dialog instead of the actual table."

It looks good until here - and if I understand it correctly Access grabs the year (e.g.13) from the ApprovedDate, and the ProjectSerial from the 'largest branch plus 1' and combines them as 13-03 (another great suggestion, thanks). This means that as soon as the user saves an ApprovedDate, the ProjectSerial is calculated and inserted into that field by Access. Access does everything. Makes a lot more sense than other solutions I've read about.

Isn't this all that's needed then? Why would xbasic need to insert the correct year and serial numbers at the time of insert - if they're already being calculated by Access and inserted - or have I misunderstood something?

[agillbb]

Re: sequential numbering - should I start in Access backend or A5

Hey Malcolm,
First, I recommend that you should use the grid to display the list, but use a dialog to add/update or delete each individual entry.
Don't let Access calculate the year or the serial. However, in your DB, once your ProjectID is PK and autonum, and your combined Year (like 2013) & Serial (like 25) is defined as a unique index, then you are safe in knowing that you can't duplicate the job entry.
You can then set up your dialog component to allow manually entering both the year and serial into separate text boxes. You can set up the server-side logic so that if these are blank, then (in afterDialogValidate) you can calcualte the current year, then (if also blank) query the DB to get the latest serail for that year, and replace the blank values with the current year and the next available serial as needed. This allows the same dialog to be used for new entries and existing edits.
The dialog is therefore simply a manual entry/edit "form" for the database table, but with some logic to figure out the current year and latest serial as needed.

[Malcolm]

Re: sequential numbering - should I start in Access backend or A5

Thanks Andy - I've got Access set up as you describe, now to get on top of the xbasic. At least I know which direction to head now. Thanks for the direction. Malcolm

[Malcolm]

Re: sequential numbering - should I start in Access backend or A5

OK Andy, if you're still there, this is what I've come up with:

My Access backend query now has the following (relevant) fields:

CaseID(PK), BranchID, ProjectSerial(this is a number incremented by each branch as above), ProjectYear(e.g.13 for 2013), ProjectCode(blank at present but ready to receive ProjectYear and ProjectSerial as 13-6 - for the sixth project for that branch in 2013)

From your guide above, what I think should happen next is that Alpha gets to concatenate ProjectYear (eg 13) and ProjectSerial(eg 6) into ProjectCode (eg 13-6) as the user saves a Project where the ApprovedDate is not null.

I'm only using a grid (as opposed to a dialog), so could you explain how and where I get to set up the server-side logic so that the afterDialogValidate) function can do this? I'm not sure if I'm understanding this correctly.

Many thanks - again

[agillbb]

Re: sequential numbering - should I start in Access backend or A5

Hey Malcolm,
Your proposed fields are duplicating information. I think you should either leave out the ProjectCode field (instead concatenate it like yy-sss in your Select queries when you wish to display the project code), or leave out the ProjectYear and ProjectSerial fields. This choice is up to you, but I prefer the first option.
Then, when you add a new record, don't use the DB's autoincrement, instead calculate the year and serial in xbasic by querying the DB, getting the MAX serial for the particular branch/year combo, and adding 1.

You'll need to have a look at some of the videos from v10.5 - you'll see some examples of xbasic events for beforeSQL commands.
http://news.alphasoftware.com/v11preview/videos_v11.htm

But I much prefer using dialogs for this type of thing and don't use the grid for editing anymore.

Andy.

[Malcolm]

Re: sequential numbering - should I start in Access backend or A5

Thanks Andy, Thanks for drawing my attention to that set of vids - I had overlooked them before.