This may be the same advice that Steve is sharing, but I'd take a look at using a Summary report with Sub Summary layout parts to omit the older instances of the same certificate/license. By removing the body layout part, you can get one row of data for each group of records and if you sort those groups by a date field in descending order, a field in the sub summary should show data from the most recent instance of that record.
I'm not sure how I can sort my relations to get this, but I will try and see.
I have tried using sub summary parts, but this doesn't seem to help if I change the date filter.
In my example report, the fire certificate due 2017.01.01 should be omitted which I can do by sub summaries. But if I add a filter to check everything due let's say up until March 2017, it will show again because the 2018.01.01 is filtered out, hence it's the newest.
But as there are a newer (even though it's filtered out) the old shouldn't show up at all. Any ideas?
I don't see any relationships in your example screen shot. What I see is a list view of 4 records. Sorted relationships would not play any role in what I am suggesting nor would you use any portals for this data either. You'd use a list view layout based on that portal's table with finds and sorts controlling what records are used to contribute data to your report.
My apologizes, but I'm not following you now. What I tried to tell previously was that Steve's link suggested sorting by table relations.
The sub summary approach (on a report) works OK without any filtering, but if I filter on dates, there might be "old certificates" showing because the report shows all the newest certificate within the filtered recordset, even though there are a newer one outside the recordset.
For all I know you understand my problem perfectly well, but it's me being to lost to get you points, but let me try to explain differently:
Table tbDocs records:
This should be listed in a report grouped by:
If I understand you correctly, you suggested to add a new sub summary TypeOfCert below SerialNo and sort this decending due date to get only the newest which works, but as said, does not work well if the report is filtered on dates, as other "newest" might turn up instead.
Sorry if I completely out of bounds here...
I can create a self-join relationship and with a calculation field identify all duplicates on TypeOfCert. But is it possible to encapsulate that into a calculation where it only calculate duplicate if both SerialNo and TypeOfCert is duplicate?
The purpose of using the Sub Summary layout part is to hide the older records for the same certificate. You would remove the body layout part and just keep the sub summary layout part.
Your sub summary layout part could specify "TypeOfCert" to group records by that type.
Your specified sort order could be:
and you'd specifiy a descending order for Date.
Yes, if I use the sub summary as you suggest, the report the report will fire up only the latest of each TypeOfCert. In the attached example, only the red fire certificate will show up. But if I change the due date filter (green) to October 3. 2017, the one in blue will show up instead.
Let's say we are in 2017, and I need a report for everything due for recertification in 2017. Using this method, the sn12345 asset will be called in for a recertification of the fire certificate, even though this certificate actually is superseded with a newer one, not due until 2018.
I finally managed to solve this:
- New calculated field (SerialNoAndTypeOfCert) to concatenate serialno and TypeOfCert
- New calculated field to (DueDateAndID) concatenate due date and ID (highest number is the current certificate)
- New table occurence (Cert|Same)with a self-join relationship between the new field in step 1
- Sort table decending on DueDateAndID)
- New calculated field to check for duplicate (DupCheck) using calc: Cert::DueDateAndID= Cert|Same:: DueDateAndID ;"Unique";"Duplicate"
- Added Unique as a set field in the report script
- Using body part
Thanks for all help!
Glad you got it working, but I'm puzzled as to why you would be filtering your records by that particular date in the first place as you would want the "most current" data on each asset.
Something is probably lost in translation here :)
If I have one particular equipment which we call in every second year for recalibration, this will have a new certicate in the system every 2 years. Let's say we have one
- September 10. 2010 - due September 10 2012
- September 11. 2012 - due September 11 2014
- September 6. 2014 - due September 6 2016
If I on November 14 2014 wants to find out which certificate are due for recalibration the next two months, I will filter on find due from * to December 14 2014. Using the subset variant, this would show the newest record in the find recordset, the one due in September 2014. This has already been recertified (on September 6) and we don't want to call this one in before September 2016.
Does that explanation make sense?