Generate sequential number based on selected sequence

Question

I have a website dropdown that is populated from a SQL query. The values in the dropdown are from a table column and are like:

ABC-0123
ABC-0124
ABC-0125
ABC-0126.01
ABC-0126.02
ABC-0127
DEF-0123
DEF-0124
DEF-0125.1.01
DEF-0125.1.02
DEF-0125.2

I have a button to generate a new number based on what is selected in the dropdown. For example, if ABC-0125 is selected, ABC-0128 would need to be created since it's the next number in sequence. If ABC-0126.01 is selected, ABC-0126.03 would need to be created.

I'm looking for ideas on how to perform this. I considered just using the dropdown or querying the database directly.

I've split the selected value as a start:

        String strDocFamily = drpDocFamily.SelectedValue;
        string[] strDocTiers = strDocFamily.Split('.');

This may be open ended, but I'm looking for some suggestions on how to proceed. Thank you.

ABC-0127 already exists. Do you mean ABC-0128 as the next value? — DeanOC, Nov 06 '17 at 20:13
Why does ABC-0127 follow on from ABC-0125? Shouldn't ABC-0126 follow on, or is the increment two? Please could you clarify. — Benedict Bunting, Nov 06 '17 at 20:15
what platform are you using, webforms, winforms, WPF? add tags — derloopkat, Nov 06 '17 at 20:15
Webforms. ABC-0126 would be skipped since the sequence ABC-0126.01 already exists as well as ABC-0127. The next series would be ABC-0128. — user8322022, Nov 06 '17 at 20:24
What depth can the sequence go to? Would 'DEF-0125.1.01' cause 'DEF-0125.1.01.01' to be added, or 'DEF-0125.1.03'? — Benedict Bunting, Nov 06 '17 at 20:32
Little disappointed that this was listed as to broad as it was a pretty straight forward question. Had an answer for you that wouldn't require the splitting of the column in the DB. — Sorceri, Nov 06 '17 at 23:28
@Sorceri tell me about it. How can you ask a question wrong? — user8322022, Nov 07 '17 at 13:07

Benedict Bunting · Answer 1 · 2017-11-06T20:27:17.113

0

A solution might be to split the storage of the data into two or more columns, one for the alphabetic part ('ABC') another for the 'family' (0128 - the first set of digits) and others for the other tiers. You could then directly look up the maximum value of the 'family' column based on alphabetic. For example:

SELECT MAX(family-number)
FROM table-name
WHERE alpha-part='ABC';

edited Nov 06 '17 at 20:27

answered Nov 06 '17 at 20:20

Benedict Bunting

318
2
8

1

That looks like a feasible solution. I can't split the columns in the database but I might be able to create a temporary table from a query. – user8322022 Nov 06 '17 at 20:25
If you are looking to achieve a similar effect within C#, you could look into using a radix sort to sort the items by all of their sections i.e. ones the are in order, you can take the last element that has the required values in the opening sections, then increment it. – Benedict Bunting Nov 06 '17 at 20:30

score -1 · Answer 2 · answered Nov 06 '17 at 20:29

-1

Here is a UniqueID generator that can be easily adapted to generate a similar sequence to the one you need.

https://stackoverflow.com/a/39312025/2495728

answered Nov 06 '17 at 20:29

Jonathan Alfaro

4,013
3
29
32

Generate sequential number based on selected sequence

2 Answers2