3 most recent

[timmy]

Each record in my database has a field "Project Number"

Multiple records can/do have the same Project Numbers.

I want to define a field that will indicate if a particular record is one of the 3 most recent entries with that Project Number. Does that make sense? Thanks,

Tim.

[LiveOak]

I'll have to think some about this one. The latest records is easy. Create a self relationship based upon project number. Create a calculation field equal to Status(CurrentRecordID) and call it "CurrentRecordID". Create a flag for latest record:

Flag (calculation, number) =

CurrentRecordID = Max(SelfByProject#::CurrentRecordID)

This will flag the newest record for that Project Number.

Now, how to do the latest three....to identify the first three is again easy using separate serial numbering for each project (SetField(ProjectSerial, Max(SelfByProject#::ProjectSerial) + 1) for a new record.

I suppose this could be combined with the Max function to identify the last three as Max(SelfByProject#:ProjectSerial), Max(SelfByProject#:ProjectSerial) - 1, and Max(SelfByProject#:ProjectSerial) - 2.

A combination of Project# and ProjectSerial would uniquely identify these records.

-bd

[Vaughan]

Errr, this might be too obvious: the last three records in the database when it is unsorted?

[proton]

quote:

---

Originally posted by timmy:

Each record in my database has a field "Project Number"

Multiple records can/do have the same Project Numbers.

I want to define a field that will indicate if a particular record is one of the 3 most recent entries with that Project Number. Does that make sense? Thanks,

Tim.

---

Timmy,

That sounds like a report to me. You have a whole group of records and you want to find the last three of a particular project (number). If this is what I want to do, then I would create a field (date type), and set it to auto enter the creation date, and create another field (time type) and set to autoenter the creation time. So everytime I create a record it automatically generates value(s) in the calculation field that records the date and time it was created. Then if I wanted to see the last three records, I would find all records with the project number and sort by the date and time fields, and view the results. Voila!!

[LiveOak]

I think the requirement was to mark the last three entries of each project, not just find them. Say you want to find the last three entries for all 1500 projects in an 150,000 record file, using a find/sort by project would be a little prohibitive. I suppose you could reprocess all the records for a specific project every time you entered a new record:

Find project

Unsort

Go to Record/Request/Page (last)

Set Field (Mark, 1)

Go to Record/Request/Page (previous)

Set Field (Mark, 1)

Go to Record/Request/Page (previous)

Set Field (Mark, 1)

but this could be a little slow.

-bd

[timmy]

LiveOak is right. I want to mark the last three records of each project number, not just find them. I have this functioning for the last record:

there is a relationship "SelfProjectNum" that relates the ProjectNumber in the database to itself. the field that marks the last record of each project number is named "Latest"

"Latest" is a calculation:

Max(SelfProjectNum::counter)=counter

where is counter is an increasing serial number on each record.

this works perfectly returning 1 (true) if the record has the max counter and 0 (false) for all others.

how do i do this with the THREE latest is the problem. hope this clarifies what i want to do. thanks for all the help,

timmy.

[BobWeaver]

Ok, how about:

Latest3 = (Max(SelfProjectNum::counter)=counter) or

(Max(SelfProjectNum::counter) - 1 =counter) or

(Max(SelfProjectNum::counter) - 2 =counter)

[timmy]

hehe, yeah. thanks. i thought of that after my last posting. seems pretty obvious. thank you,

tim.