Help find records

[beckham]

Hi could anyone advise me if filemaker 14 can find records that are the just latest revision in my database.

I have a large product database which uses the following identifiers e.g.: (Product code)12345-(Revision number) 07           (12345-07)

But the last revision of that code could be any number 01, 02, 03 etc how do i ask the result to display just the last one (Highest 2 digit number) ?

Many thanks for any pointers on this..

[Lee Smith]

Hi Beckham,

Are they filed in the same folder?

If so, just the view by folder by date, last modified.

[beckham]

Hi Lee.

Sorry looks like my description wasn't clear...

The data example is a field in the record.. so i have a record whose field data is 12345-06 and a different record is 12345-07

If i search for 12345 it returns both.. I want it to show only the latest 07 result, but 07 could be any number depending how many revision exist.

 

[Steve Martino]

You have:

12345-06 & 12345-07

Is that one field or a calculation of 2 fields?  IOW, the identify consists of a product number, plus a - , plus  a revision number.

[beckham]

Hi Steve it is one field but your right the -06 or -07 is a revision number

[webko]

This would need to be scripted - perform a find using the 12345, sort by the field, and then isolate the final record....

[beckham]

i webko many thanks for the advise, how would you isolate the final record?

Thanks again!

[comment]

On 11/29/2016 at 7:36 PM, beckham said:

how do i ask the result to display just the last one (Highest 2 digit number) ?

This cannot be done by using find alone. Filemaker will find records that contain something. It will not do calculations or comparisons during the find.

Now, there are two ways to accomplish what you want: a slow one, and a fast one:

The slow method requires that you find the records that will participate in this (if you want to see the latest revision of all products, then do Show All Records at this stage) and sort them by the field in question, ascending. Then go to the first record and loop: If the product-code part in the current record matches the product-code part of the next record, omit the current record. Otherwise go to next record.

Note: if you only want to see the latest revision of a single product, then it can be simpler (and much faster). Find the records of the wanted product, sort them, go to first record and omit multiple records using Get ( FoundCount ) - 1 as the number of records to omit.

The fast method would require a structural change. At minimum, you need to split the field in question into two separate fields (that's always good practice, embodied by the maxim "one fact per field") . This would enable you to use the Fast Summaries method and omit an entire group of records (except the last record in the group) at once. You would also have to add a summary field that counts the records.

Even better solution would have a table of Products, where each product-code would be unique, and a related table of Revisions. Then you could get the latest revision of each product by using a calculation getting the Max() or Last() revision number. Or show it directly from the Revisions table using a sorted portal/relationship.

Edited December 1, 2016 by comment