Ref Formula

[alea]

I am a complete newbee to approach and I have been asked if I can fix/change a field. I have tried but am baffled!

My company used to have a field called Reference which was the first field to appear on the data entry screen and it was a combination of a record counter and a year reference.

Last year someone broke it whilst trying to reset it back to reference no. 1 for the year 02 it was just appearing as 1/ and then they have manually typed in 02 for the year.

Can someone tell me the easiest code to use to get it working again for reference 1/03 without losing the references for all the previous entries.

 

[paulbent]

You should be able do this through Create - Field Definition, assuming this is how it's set up. Select the record counter field, click Options and there is probably an auto-serial number defined on the Default tab - reset the start value to 1.

Then select the Ref field, it should be type Text and on the Default tab there should be a creation formula defined, something like:

Combine(NumToText(CounterField, '#0'), '/', Right(NumToText(Year(Today()), '0000'), 2))

The effect of this is that each time a new record is started, the auto-serial counter will be combined with the last two digits of the current year, separated by a forward slash and inserted in the ref field which can be made read-only on the data entry form.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk