Adding a serial number to a template in Numbers

Welcome to the forums!

I'm not sure how you could get a serial number to automatically generate without scripting however I have a cheesey off the wall suggestion that assuming you send out no more than 1 invoice per client per day would generate a unique reference number based on the date, client name and anything else you may like you add.

Say in cell B2 you enter an automatically updating date. 2 ways:
=TODAY()
or double click the cell
Insert-Date and time
then double click the date and time, choose edit date and time and click automatically update on open.

You could then reference the day and month elements to begin your number (17 Jan being 171 for example) using Day(B2) and Month(B2).
Have another cell with your clients name in it and use LEFT("thatcell",3) to take the first three letters of your clients name to add to your number.
Then in your reference number cell CONCATENATE it all together with any other text or punctuation contained in quotes:

=CONCATENATE(DAY(B2),MONTH(B2),"/","TL","/",LEFT(D2,3))

Which with todays ref number from your invoice to me would produce 171/TL/Moi

I realise this probably isn't quite what you're looking for but it might get something started!

M.

Hello

If you want to be able to sort easily documents with this kind of "number", it would be better to build the ID with the reverse structure: "YYYYMMDD" or "YYYYMMDDhhmm"

=YEAR(NOW())&RIGHT("00"&MONTH(NOW()),2)&RIGHT("00"&JORR(NOW()),2)&RIGHT("00"&HOU R(NOW()),2)&RIGHT("00"&MINUTE(NOW()),2)

Yvan KOENIG (from FRANCE jeudi 17 janvier 2008 19:22:20)

Nice numeric solution!
Quick correction though (small translation issue!)

=YEAR(NOW())&RIGHT("00"&MONTH(NOW()),2)&RIGHT("00"&DAY(NOW()),2)&RIGHT("00"&HOUR (NOW()),2)&RIGHT("00"&MINUTE(NOW()),2)

M.

Hello

Thanks. One more time a specific case I missed when writing my translation script.
Now this one is corrected.

Yvan KOENIG (from FRANCE vendredi 18 janvier 2008 08:56:56)

Moich wrote:
Welcome to the forums!
...
Say in cell B2 you enter an automatically updating date. 2 ways:
=TODAY()
or double click the cell
Insert-Date and time
then double click the date and time, choose edit date and time and click automatically update on open.

Won't either of these continue to update when the document is opened in the future? I think this behavior would be undesirable for this use. The second possibility would be workable if you remembered to "freeze" the date by turning off auto update each time after creating a new document from the template.

5|=vv wrote:
Won't either of these continue to update when the document is opened in the future? I think this behavior would be undesirable for this use.

Dead Right

The second possibility would be workable if you remembered to "freeze" the date by turning off auto update each time after creating a new document from the template.

At which point you'd be quicker typing 20001 yourself.

(actually while we're at it you'd be typing the date in anyway wouldn't you? So if you really really wanted to you could use the same formula just don't use NOW(), TODAY() or automatic update. Just put the date in its usual place)

But this aside, I fear the automatic serial number, though easily performed by printing presses since the 18 century may perhaps be beyond the scope of numbers

M.

Hello

If someone is interested, it would be easy to write an AppleScript to insert a serial number in a document.
The advantage is that it would be a frozen number.

Two ways to do that:
a - generate a number incremented by one each time you run it.
b - generate a string like the one created by the formula I passed.

The behaviour would be slightly different.

The first version assumes that the last used number is stored somewhere.
The second one is not tied to this obligation.

In fact I was able to write two scripts of a special kind.

They may enclosed in a structure changing them in Services.

Instructions to do that are given in the header of the second script.

The first one inserts the long date_time string where you want.
The second insert a serial number built according to the current datetime.


--[SCRIPT insertDateTime]
on process()
set curDate to (current date) as Unicode text
--return curDate
end process
--[SCRIPT]

--[SCRIPT insertSerialNumber]

(* From the Script Editor, save this script as a Script
Then pack it as a Service using the tool which you may download from:
http://wafflesoftware.net/thisservice/
Apply the setting: Produces output.
The Service will be directly created, installed and activated
then working with a Services aware application,
select an insertion point then go to
menu theApplication >Services > pasteSerialNumber
will insert the serial number.
*)
on process()
set cd to current date

set serial to (year of cd as text) & text -2 thru -1 of ("0" & (month of cd as number)) & text -2 thru -1 of ("0" & day of cd) & "_" & text -2 thru -1 of ("0" & hours of cd) & text -2 thru -1 of ("0" & minutes of cd) & text -2 thru -1 of ("0" & seconds of cd)
--return serial
end process
--[SCRIPT]

Yvan KOENIG (from FRANCE samedi 19 janvier 2008 21:18:12)