Invoice number based on date

[Robert Collins]

I have a table which I post payment entries in.

We recieve these payments every 10th and 25th day of the month.

I have a date field that holds the date and I would like the invoice ref field to automatically create when the date is entered.

for example:

Date invoice ref

25/3/2009 MCC300

10/4/2009 MCC301

25/4/2009 MCC302

10/5/2009 MCC303

and so on

any help would be appreciated

thanks

[aldipalo]

How about a calculated field c_DateofInvRef:

Case(

IsEmpty(DateField;"";

DateField & " " & InvoiceField

)

Go to storage and set to Unstored, recalculate.

hth

[Robert Collins]

thanks for the reply - I don't think I explained myself very well.

My invoice ref must be "MCC300" when the date is 25/3/2009, "MCC301" when the date is 10/4/2009 , "MCC302" when the date is 25/4/2009

in other words it adds 1 to the invoice ref number when it is the next 10th of the month and adds 1 when it is the 25th

thanks

[comment]

I still don't understand this. Why don't you simply use auto-entered serial number (with "MCC" prefix)? I see no connection between the date and the reference.

[Robert Collins]

I didn't want to use an auto enter serial number as there may be 50-100 records entered at one time and I want the invoice ref to be the same as long as they are in the same period not increment just because it's a new record.

Edited March 30, 2009 by Guest

[comment]

And what should happen if a record is entered on a day other than the 10th or the 25th of a month?

[Robert Collins]

That can never happen as the date field is a calculated date of 25/current month/current year.

the type of payments that will be entered currently only occur on the 25th day of each month.

I only want to make an allowance for the 10th day just in case the situation changes - in which case I will get the date field to round up or down to the nearest 10th or 25th of the current month.

[comment]

Try something like:

Let ( [

m = 12 * Year ( PaymentDate ) + Month ( PaymentDate ) ;

b = Day ( PaymentDate ) > 10

] ;

SerialIncrement ( "MCC000" ; 2 * m + b - 47923 )

)

[mr_vodka]

I must make a point here though that you still should really have a true serial ID in the background. Using a date like this can be very dangerous as a users can simply change their system clocks as well as sometimes a system's clock being wrong due to computer hardware/software issues ( which I have personally seen on many of occasions ).

Just some food for thought.

[Robert Collins]

Thank you very much , that's exactly what i wanted

[Robert Collins]

fair point