Finding the last three records containing each value in a set of values

Questions
, ,
1

Hello everyone,

I have a table of job numbers, which are sequential based on entry time. Each Job has one or more product numbers. I'm looking for a way to find the three most recent jobs (highest job number) that contain each product number.

For example:

|JobID|ProductID|
|1980|KC0055|
|1980|KC0111|
|1981|KC0111|
|1982|KC0111|
|1982|KC0055|
|1984|KC0055|
|1986|CUSTFLATCOMP|
|1987|CUSTFLATCOMP|
|1988|CUSTFLATCOMP|
|1989|CUSTFLATCOMP|
|1990|CUSTFLATCOMP|
|1990|KC0111|
|1990|KC0055|

So for the three part numbers, I need to return:

CUSTFLATCOMP: 1990, 1989, 1988
KC0055: 1990, 1984, 1982
KC0111: 1990, 1982, 1981

I do have a ProductID table that I can loop through to get all of the ProductIDs, so I don't need to extract them from this table.

I've been racking my brain for this for a while. Does anyone have any suggestions?

Thanks very much

BrentBollmeier

2

Fairly straight forward!

Get the list of jobs in reverse JobID order for each productID. Pick the first three.

I suggest using a script to set a field instead of using a calculation field. The calculation field would be unstored and slow. Set this value for the product when a product is added to a job.

Hope this helps.

1 Like
3

Right. Since posting this, I'm thinking a nightly/weekend script that goes through and find all of the most recent jobs with the right part numbers, marking them for archive, then deleting/copying/whatever we decide to so with them.

I was trying to do with with a calulated field at first, but you're right - too slow.

I think that's the biggest hurdle coming to FileMaker from Oracle - I miss pre-compiled Oracle views where you could do that sort of thing a little easier.

Thanks for taking the time to respond.

Brent

4

Curious: why nightly or weekly instead of real time? Would performance be an issue if done when adding a product to a job?

5

Simply because rewriting the code to do that seems like the biggest hassle.

6

@BrentBollmeier Welcome tp Thesoup !

Hope you will enjoy our Community.

Please mark @bdbd post as the answer. Thanks

7

:1st_place_medal: Honesty Medal