Generate identifying number after a record is created

Hello;
For this scheduling app I’m trying to make for my little photography
company…

I would like to assign a unique, incrementing number to records in my
database, but NOT at the time the record is created.

In our company’s workflow, assignments are given a unique identifier
number, and that number also tracks with each image. For example, an
Assignment done today might be identified as 09-557, and an individual
image in that Assignment might be identified as 09-557-12.jpg. The
next Assignment to be logged in would be 09-558 and so on. I can
extract the “09” bit from today’s date, so its really just the 557,
558 and so on I need to generate. We start with 100 on January 1 of
any year.

Currently when our photographers come back from an assignment, they
create a record in our log (FileMaker Pro at present) and a new number
is created by FMP at that time. (The individual image file names are
done in Photoshop - it’s just the overall assignment log number I want
the database to handle.)

Going forward with a new Rails app, I want to create the Assignment
record when the customer books us, but not assign the number until the
photographer comes back from the assignment. At which time the
photographer would edit the Assignment record in the Rails app, enter
any details or expenses and THEN generate the number.

It would be nice if they could click a button or a link while in the
Edit view for an Assignment record to create a log_number, which would
be one greater than the previous log_number. Once an Assignment had a
log_number, that link or button wouldn’t appear, avoiding the
confusion of assigning more than one number to the same Assignment
record.

I think I can figure out how to generate the number when the record is
created, but I’m stumped as to how I would create that number at some
point AFTER the record was created. Ideally while in the record’s edit
view.

Any pointers as to how to approach this?

Thanks.

Steve

Steve Nelson wrote:

Hello;
For this scheduling app I’m trying to make for my little photography
company…

I would like to assign a unique, incrementing number to records in my
database, but NOT at the time the record is created.

Easy.
Use the regular key as just that - the standard key - and create another
column in the table which you can edit, assign, etc etc, as you please
:slight_smile:
In some “configuration” table, keep the number for log jobs…
Run a cron job on January 1st to reset the number to 100.

Works for you?

Thanks Aldric;

I think I’m stuck on the mechanics (if you can call it that in a
software app) of prompting for a number to be inserted into a database
column on demand, in an edit view. Maybe you addressed that and I’m
too slow to have caught what you meant.
BTW, I do already have a log_number column in my table. Just can’t
come up with a solution for how to populate it.
The idea of a configuration table is a good one, as is the cron reset.
Steve

On Nov 5, 12:41 pm, Aldric G. <rails-mailing-l…@andreas-

Hello Marnen,

I appreciate your insight - it sounds like the voice of experience to
me.
:slight_smile:

I don’t actually intend to use this “log_number” attribute as a key in
the database. An Assignment will always be unambiguously identified by
the id it was “born” with. The log number is important in the analog
end of our business, but as far as the database goes it’s of similar
importance to, say, the floor number in the client’s shipping address
if that makes sense. We might sort by log_number, we might search for
a log_number and we will certainly use it as a reference when we
invoice a job and when we rename the files. But I’m not considering it
(the log_number) to be a key - just an attribute like the date and
time of the shoot.

Does that help clarify my goals?

Steve

Steve Nelson wrote:

Hello Marnen,

I appreciate your insight - it sounds like the voice of experience to
me.
:slight_smile:

I don’t actually intend to use this “log_number” attribute as a key in
the database. An Assignment will always be unambiguously identified by
the id it was “born” with. The log number is important in the analog
end of our business, but as far as the database goes it’s of similar
importance to, say, the floor number in the client’s shipping address
if that makes sense.

Then stop using the log_number in the analog end! Just use the primary
key. Give it out when the record is created, use it as you were using
the log_number before…you get the idea.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Steve Nelson wrote:

Thanks Aldric;

I think I’m stuck on the mechanics (if you can call it that in a
software app) of prompting for a number to be inserted into a database
column on demand, in an edit view. Maybe you addressed that and I’m
too slow to have caught what you meant.
BTW, I do already have a log_number column in my table. Just can’t
come up with a solution for how to populate it.

I’d advise you to drop this idea altogether and just use the primary
key. You’ll be in for fewer headaches if you have one unambiguous
identifier.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

2009/11/5 SNelson [email protected]:

Thanks Aldric;

I think I’m stuck on the mechanics (if you can call it that in a
software app) of prompting for a number to be inserted into a database
column on demand, in an edit view. Maybe you addressed that and I’m
too slow to have caught what you meant.

Is that not just a field on an edit form for the record? Look at the
methods generated by script/generate scaffold to see the conventional
way of doing it.

Then stop using the log_number in the analog end!

That, sadly, is not an option. I thank you for your time and interest
and will continue to try to figure out how to make the new app
integrate with a system that is already in place.

Steve

Steve Nelson wrote:

Thanks Aldric;

I think I’m stuck on the mechanics (if you can call it that in a
software app) of prompting for a number to be inserted into a database
column on demand, in an edit view. Maybe you addressed that and I’m
too slow to have caught what you meant.
BTW, I do already have a log_number column in my table. Just can’t
come up with a solution for how to populate it.
The idea of a configuration table is a good one, as is the cron reset.

You could avoid the cronjob if you had a column for the year and one for
the number and then just find_or_create_by_year the right record and
increment the number by one (it could have a default (means also
initial) value of 99).

Regards T.

T. N.t. wrote:

You could avoid the cronjob if you had a column for the year and one for
the number and then just find_or_create_by_year the right record and
increment the number by one (it could have a default (means also
initial) value of 99).

Regards T.

T, that sounds like a case of being too clever. How would you think of
this solution if you came upon the working app and had to do
maintenance? A default value of 99 on a field is quite odd and you’d
probably have to guess to figure out why it’s set that way.

Aldric G. wrote:

T. N.t. wrote:

You could avoid the cronjob if you had a column for the year and one for
the number and then just find_or_create_by_year the right record and
increment the number by one (it could have a default (means also
initial) value of 99).

Regards T.

T, that sounds like a case of being too clever. How would you think of
this solution if you came upon the working app and had to do
maintenance? A default value of 99 on a field is quite odd and you’d
probably have to guess to figure out why it’s set that way.

This seems rather a general problem of the application. Why beginning a
number on the first day of a year with 100? You anyway have to know the
reason for this oddity (or at least that it exists), when you need to
understand the application.

T.

Hello T

This seems rather a general problem of the application. Why beginning a
number on the first day of a year with 100? You anyway have to know the
reason for this oddity (or at least that it exists), when you need to
understand the application.

To clarify at least one point - the first assignment of a new year
begins with 100. It’s not a matter of the first day of the calendar
(since we’re generally closed for business on January 1!). Put another
way, 09-100 might have happened on Monday, January 5.

And why a new year begins with 100 instead of 1 is a decades old
convention, established long before I was employed here. I’m guessing
that it made the company look a little more prosperous around
February.
:wink:
Staying with the established numbering convention is important. For
one thing, it serves as an informal internal sales metric. “Oh my. By
this time last year we were at 475 but this year we’re only at 415…”

Older companies (ours is about 80+ years old) with established,
functional methodologies which were implemented long before computers,
are interesting to work with aren’t they?

2009/11/6 SNelson [email protected]:

that’s even a smart approach) from within a form escapes me.
You could use an Ajax request to the controller to provide the next
number, or just a button that submits the form and comes back to the
edit page with the number filled in. (You can have multiple submit
buttons on a form and take different action in the controller based on
which button is pressed).

Colin

Thanks Colin

Is that not just a field on an edit form for the record?

Yes, exactly.

Look at the
methods generated by script/generate scaffold to see the conventional
way of doing it.

I’ll do that. Guess maybe I’m over-thinking this issue (I do that a
lot). To have the number generated when the record is created is
something I think I could figure out.
Generating the number (only once for any given record) through some
mechanism such as a link or button to click at some arbitrary point
after the record is created, while within the edit view… that’s
where I’m stuck.
I was thinking I would have a Class variable in the Assignment model,
and a Class method that would contain an attribute accessor which
would increment the variable by one. But triggering that function (if
that’s even a smart approach) from within a form escapes me.

Thanks for everyone’s help and interest.

Steve

Steve Nelson wrote:

Staying with the established numbering convention is important. For
one thing, it serves as an informal internal sales metric. “Oh my. By
this time last year we were at 475 but this year we’re only at 415…”

I have had to deal with situations very similar to this a number of
times. Here is how I’ve dealt with it:

  1. I create a table for maintaining unique numbers with a row for each
    unique prefix.

±---------------------+
| id | prefix | serial |
±—±-------±-------+
| 1 | 09 | 1763 |
| 2 | 10 | 100 |
±—±-------±-------+

  1. Each row contains the last “serial number” used for each prefix.

  2. Use a find_or_create_by_prefix to either find or create a row for the
    current prefix (the year in your case).

Make sure your serial number column defaults to your starting value (100
in this case).

  1. When creating a new “photo” record, concatenate the prefix + “-” +
    serial number as your log_number.

  2. Increment and update your last used serial number.

Note that steps 3,4 & 5 must be wrapped in a single database transaction
to be protected from race conditions.

I am not a big fan of these types of solutions. It is also my policy
that identifying values should never encode any information other than
object identity (such as a year in this case). However, I also
understand that sometimes your hands are tied and have to come up with a
means to satisfy the needs of the client.

And why a new year begins with 100 instead of 1 is a decades old
convention, established long before I was employed here. I’m guessing
that it made the company look a little more prosperous around
February.
:wink:

Been somewhere similar (old place with established practices), and seen
similar stuff, but they encoded the year and used 4 digits, like
75-0001.

The responses at to why were:

  1. When we were using file cabinets, it made it easy to order them, and
    they all looked the same, and we don’t expect to ever have more than
    1,000 X’s in a year.
    X’s had 4 digits, and Z’s had three digits since we did very few of
    those. You knew what it was about just by looking at the ID. (ed:
    boggle)

  2. When we first went to computers, someone tried IDs like 75-1, 75-2,
    etc, but eventually sorting them got messed up…

So four digits for X’s, and three for Z’s it is, even today on a
browser-based application (no, not in Rails) using Sybase (not MySQL or
Postgres or whatever). Some places change VERY slowly. You almost have
to wait for someone to die before you can make a “big” change – “big”
meaning “not how it is done now”

I want to say thank you to everyone who has chimed in.

Robert and Colin - those are concrete suggestions I think I can use as
a starting point. Colin - I didn’t know I could have multiple submit
buttons within a form. (Oh, the mayhem I can create now!)

Robert and Ar Chron - thanks for appreciating that I have preexisting
constraints to conform to.

I’m grateful for all your suggestions and interest.

Steve