Delete records older than 1 year

[Christopher Kiernan]

I was wondering if somebody could help me write a script that would delete every record older than 1 year. Thanks for any help you can provide.

FileMaker Version: 7

Platform: Mac OS X Panther

[-Queue-]

Go to Layout [{one with the date field on it}]

Enter Find Mode [ ]

Insert Calculated Result [date, "<" & DateToText(Date(Month(Status(CurrentDate)), Day(Status(CurrentDate)), Year(Status(CurrentDate)) -1))]

Perform Find [ ]

Delete All Records [No dialog]

[Christopher Kiernan]

That doesn't seem to be working to well for me. I was wondering if you couldbe more specific as to what i should do. I was ok up to the calculation part. There i keep getting errors about functions not existing.

FileMaker Version: 7

Platform: Mac OS X Panther

[-Queue-]

Sorry, didn't notice you were on v7. Replace the commas in the Date function with semicolons and Status(CurrentDate) with Get(CurrentDate) and DateToText with GetAsText.

[Christopher Kiernan]

oh ok. thanks a lot for your help.

[Christopher Kiernan]

Hi. Thanks for the help. I created the script but I do have one question. Was i supposed to create a date field for each record so that the script can determine how old the record is or does Filemaker keep a hidden record of when each reocord was added. I ask this because you can see what the script is doin when it runs; it asks for records older than one year in the find field but it looks like it is just searching one paricular field, and that particular field does not contain a date.

FileMaker Version: 7

Platform: Mac OS X Panther

[-Queue-]

You need a date field with an auto-entered Creation Date. Use that field in place of 'date' when you specify the field in the Insert Calculated Result step.

[Christopher Kiernan]

ok, but now i have another question. What if I don't want this field to be visible? Is there anyway to accomplish this with the date field hidden somehow or is there another way that perhaps doesn't include creating a date field at all.

[Ugo DI LUCA]

You still need a date field.

This field needs to be accessible for a find. You can create a layout just for this purpose, and depending upon your solution, hide this layout from the list.

If you want this field to be unaccessible at all, then you'd need a Range relationship, but it seems way too complicated for such a simple task.

[-Queue-]

You can put it on a layout, disallow entry, set it with no borders, a text color equal to the background, and make it as small as you like (5x5 pixels might be a good size). Then it will be available for the Insert step, but inaccessible and invisible to users.

[Ugo DI LUCA]

Hi again,

Just in case.

Create :

- a global text field gRange

- a relationship SjByRange gRange::YourDateField.

Then a script

SetField[gRange, DateToText(Date(1, 1, Year(Status(CurrentDate))))]

Loop

EndLoopIf[substitute(RightWords(Substitute(gRange,"/","A"),1),"A","/")=DateToText(Status(CurrentDate))]

SetField[gRange & "