beckham Posted November 29, 2016 Share Posted November 29, 2016 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 More sharing options...
Lee Smith Posted November 29, 2016 Share Posted November 29, 2016 Hi Beckham, Are they filed in the same folder? If so, just the view by folder by date, last modified. Link to comment Share on other sites More sharing options...
beckham Posted November 29, 2016 Author Share Posted November 29, 2016 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 More sharing options...
Steve Martino Posted November 29, 2016 Share Posted November 29, 2016 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. Link to comment Share on other sites More sharing options...
beckham Posted November 30, 2016 Author Share Posted November 30, 2016 Hi Steve it is one field but your right the -06 or -07 is a revision number Link to comment Share on other sites More sharing options...
webko Posted November 30, 2016 Share Posted November 30, 2016 This would need to be scripted - perform a find using the 12345, sort by the field, and then isolate the final record.... Link to comment Share on other sites More sharing options...
beckham Posted December 1, 2016 Author Share Posted December 1, 2016 i webko many thanks for the advise, how would you isolate the final record? Thanks again! Link to comment Share on other sites More sharing options...
comment Posted December 1, 2016 Share Posted December 1, 2016 (edited) 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 2 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 2831 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 accountSign in
Already have an account? Sign in here.
Sign In Now