Jump to content

This topic is 2675 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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..

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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 by comment
  • Like 2
Link to comment
Share on other sites

This topic is 2675 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.