auto-enter month

[calski]

This is probably a really simple request, and one which someone can probably answer in 10 seconds without any thought, but...

I am self-employed as a freelance translator and my actual file organisation system works in the following manner, recording the file path of the file as follows:

[3 letter client/agency ID code]/[YYYY]/[MM]/[my own reference no.]

This is how I identify where the file is stored and it also serves as my own reference for putting on invoices.

Previously I used to enter all of this information manually, e.g. ABC/2010/04/21a-6608.50,

where ABC is the client code, 2010 is the year, 04 is the month, and the remainder is my own reference code.

I have now linked the client code to my contacts database, and so this is entered automatically once I enter the client in the previous field.

What I would like to know how to do is for FM to automatically, upon creation of a new record, enter the month (MM) in the field named "Month", and the year (YYYY) in the field named "Year". I do not want to have to enter these cells - I just want it to happen automatically.

I tried setting them as "Date" fields and then formatting the date so that it would only show the month/year numbers (depending on the cell), but this didn't work and nothing was automatically entered (despite having selected 'auto-enter' date in the field defining menus).

Any suggestions please? Perhaps a way of calculating the month or year using a calculation might be a solution?

[David Jondreau]

Year(Get(CurrentDate))

and

Right(0 & Month(Get(CurrentDate));2)

[calski]

Thanks for the reply. That works, however, it has changed ALL of my records so that the date is 2010 (year) and 04 (month). What I need it to do is to record the year and date upon creation of the record - so all entries I make this month will have 2010 / 04 and they will stay the same next month when I go back to look at these records; whereas, all new records next month will obviously have 2010 / 05.

Does that make sense? It's essentially the creation year and month that I want.

Strangely however, when I try set the field type to date and select auto-enter creation date (and then adjust the formatting so that it only shows the year or month), it does not auto-enter the data!!

Any ideas?

[David Jondreau]

Putting those calcs in an auto-enter calc box will give you correct results for all new records. Make sure you mark them "Do not replace".

For existing records...Have you captured the creation date anywhere?

[calski]

Yes i do have the creation date - it's stored in a field where i record the "start date" for the project - it's an auto-complete field.

As you've mentioned it, is there any way that I can use these dates to fill in the "Year" and "Month" fields on my existing records?

[David Jondreau]

Yes. In the calcs above, substitute Get(CurrentDate) for the name of the creation date field.