Show the latest version

[Mike_FM]

Hi all,

I have created a portal in a related table and I want to apply a filter to it so that it only shows the latest versions from a found set of records and to hide the others. There is a related field within the portal called 'scan version' which displays the version number as eg 'v001' 'v002' 'v003' etc

I've been hunting around online to find a calculation that fits my needs and have played around with the 'Max' function but to no avail

If anyone can give me some pointers that would be awesome, I'm still very new to this and learning so any help is always appreciated!

If you need any more info please let me know

Thanks

 

I have just worked this out and used the following calculation

Scans::Scan Versions = Max ( Scans::Scan Versions )

and it worked!

[comment]

2 hours ago, Mike_FM said:

apply a filter to it so that it only shows the latest versions from a found set of records

I suspect you made a mistake here: a portal that shows a found set of records cannot be filtered.

 

[Mike_FM]

Ah yes, apologies calling it a found set is a mistake and also upon further testing the calculation: Scans::Scan Versions = Max ( Scans::Scan Versions ) doesn't quite work for what I need.

Diving in a little deeper...

I have two tables in my database; 1 called Counts and 1 called Scans and they are related using a match field called VFX_ShotCode (Counts table is the parent and Scans is the child)

Within the scans table I have created a field called 'scan filename' and it contains the names of all the scans in my project; they all follow this naming convention-

 

episode#_scene#_shot#_scantype&#_version#) - an example would be 101_010_0010_bg01_v001

I also have a field within the Scans table called Scan Versions which I contains a calculation that cyphens off the 'v001' of the scan filename.

I have created a portal in the Counts table to show this related scan data and as it currently stands, when I am in the Counts Table and on the record with VFX_ShotCode '101_010_0010', the following related scan data is displayed 

101_010_0010_bg01_v001

101_010_0010_fg01_v001

101_010_0010_el01_v001

101_010_0010_bg01_v002

101_010_0010_fg01_v002

However, I only want to show the latest version of each scan filename, which would look like

101_010_0010_bg01_v002

101_010_0010_fg01_v002

101_010_0010_el01_v001

Using the Max function shows me all the v002s but not the v001 of the 'el01' scan

Any advice would be really appreciated, I am still trying to understand how to create calculations that do what I need and following your previous calculations has really helped

Thanks so much,

Michael

 

[comment]

I think this could be easier if you had separate tables for each level of hierarchy:

Episodes -< Scenes -< Shots -< Scantypes -< Scans

As it is, each record in Scans needs to determine if it is the latest one in its own subgroup, when there is no pre-existing definition of the subgroup. One way to create such definition is to define an auxiliary self-join relationship linking all scans of a subgroup together - see the attached demo.

It should be also possible to replace the relationship with a calculation field in the parent table, but then the filtering formula would be quite complex - not something I would suggest to a beginner (or even an intermediate level) developer.

 

LatestScans.fmp12

Edited February 12, 2021 by comment

[Mike_FM]

This is awesome! 

So I used the Left and Right function to split out the scan filename string into new fields; cVersion and cSubGroup, 

I then duplicated the scan table and and  created a relationship between the new cSubGroup fields - I didn't realise you could duplicate table and do a self join

To further my understanding of the calculation you used in the portal filter:

In layman's terms I understand that 'Scans::cVersion = Max' means show the highest number in this field (?)

But what does ( ScansInSubgroup::cVersion ) part of the calculation mean?

Thanks again for your time and help!

 

 

[comment]

2 hours ago, Mike_FM said:

I then duplicated the scan table

I hope not! You only need to add another occurrence of the same Scans table to the relationships graph.

The filtering expression needs to be read while taking into account the context of each field reference. In plain language, it checks if the version of the examined record is equal to the maximum version of the entire subgroup.  The reference Scans::cVersion uses the same occurrence as the portal, therefore it evaluates to the version of the portal record being examined; the expression Max ( ScansInSubgroup::cVersion ) uses the newly added occurrence, therefore it returns the maximum value of all the records in the same subgroup. If these values are equal, the comparison returns true and the record passes the filtering test.