smart invoice number

[silhougal]

hi all,

i'm having some problems with a calculation field being an invoice number field.

scenario:

my ideal invoice number would be a series of numbers where the first 5 digits are my actual project number, the next 2 digits are just month of current year and the last 3 digits should tell me how many invoices i have created for the project so far.

everything was simple until i tried to put some calculations to generate the last 3 digits. i can't tell FM to identify the difference between projects. for instance, i have 3 projects 01001, 01005 and 02011 which i want to create invoices for. 01001 has 3 previous invoices, 01005 has 2 previous invoices and 02011 has none previously.

all these invoices are kept in 1 invoices file. how do i tell FM to generate the invoice numbers in the following order:

0100106004, 0100506003 and 0201106001

but not

0100106004, 0100506005 and 0201106006

am i clear, anyone?

is there a similar post previously?

cheers,

Danny

[ernst]

Hi Danny,

The kind of invoicenumbers that you want to generate are not allowed by our Dutch tax office, but maybe they are in your country...

Anyway, I think you should generate the numbers in a script. You will also need a least a separate projectnumber field and a self-join-relation from projectnumber to projectnumber.

Your script should do something like:

new record (new invoice)

set field (projectnumber, yourprojectnumber)

set field (serialnumber, projectnumber & month(status(currentdate)) & count( self-join-relation(projectnumber)))

Actually the monthbit of the above script would need some fiddling to add a leading zero when month is smaller then 10, but I suppose you get the idea...

Regards,

Ernst