I'm setting up a database to track visual effect shots for a short film.

The MAIN table has the shot number and a field for the approved version.

The VERSION table has fields for serial number, shot number, version number, and a field for approved/not approved. The tables are linked by shot number.

I only want the "approved" version number from the VERSION table to appear in the "approved version" field on the MAIN table.

There must be a clever way to do this that I just can't think of!

Add a global field to Main and set its value to "Approved".

Make a relationship to Version

Shot Number = Shot Number

AND

Global = the field for approved/not approved

You could define a calculation field in Versions =

Case ( Approved ; ShotID )

then define a new relationship based on this field.

However, what you describe allows for more than one version to be approved. A better way would be to select the version by a script that sets an ApprovedVersionID field (in the Shots table) to the selected VersionID value.

Excellent, thanks! And so simple