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…

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…

@BrentBollmeier Welcome tp Thesoup ! Hope you will enjoy our Community. Please mark @bdbd post as the answer. Thanks

[image] BrentBollmeier: Simply because rewriting the code to do that seems like the biggest hassle. :1st_place_medal: Honesty Medal

The "FileMaker way" of doing this: create a relationship from your ProductID table to the Job/Product join table, matching on ProductID set the relationship to be Sorted on the JobID, sort order: Descending create a calculated field in the ProductID table: mostRecent3JobIDs = ProductID & ":" & …

With this script you can do it without any calculation or new relationship. The table name is "Jobs" I don't use the table with the Product ID's. [image] [image]

I’d recommend using a script using ExecuteSQL similar to this recommended here by @LucThomaere . Do everything you can to avoid sorted relationships, they are very bad as far as performance goes. It is always worth remembering that just because you can do something within FileMaker doesn’t necessar…

ExecuteSQL has some big problems though: very hard to debug (though there is a new function ExecuteSQLe which returns better error messages ExecuteSQLe ) field names are hard-coded, and won't update if you rename a field (you can work around this by using GetFieldName() but the syntax is somewhat…

If I rewrite the code, I'm looking at a boatload of testing, whereas a separate process can be tested on its own.

@xochi another factor that I don’t think has been defined here, is whether this is hosted on a local network and accessed by local computers, or is accessed remotely, or hosted on the cloud. If accessed via the Internet, the design of any system should be optimised for this. Hence, long chains of t…

Again, this may be a holdover from my Oracle days, but what you're suggesting is to not normalize your database the way one might in other systems. Store values in multiple places rather than relying on queries to grab them from the single place they're stored. Am I correct?

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

Questions

Topic		Replies	Views
Best Way To Implement Huge SQL Query In FIleMaker Questions sql	37	664	December 24, 2023
Increasing Performance for Complex Join Questions	19	364	March 26, 2024
Counting related records for a large data set Questions scripting	13	982	October 22, 2020
Finds through relationships are slow Questions perform-find	20	847	December 21, 2020
FMS19.6.3 poor performance on M1 mini / Ventura Questions performance	45	1164	January 18, 2024

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

Related topics