Automating the linking of two Things from different Data Types

Need help Database
1

Is there a best practices way (perhaps a workflow?) to automatically link objects from two different data types when they are not linked at creation, i.e. created at separate times, by separate users?

  • I have two data types, Products and Photos.

  • Products have the following fields:

  • Name
  • Product Code
  • Price
  • List of Photos
  • Photos have the following fields:
  • Filename
    *Status
  • Product

  • Photos will often be created with only a Filename, unlinked to any product

  • I’d like the system to:

  1. When a user creates a Photo with a “name” field matching a Product’s “Product Code”, link the Photo to the Product (so I can display them effectively in elements, etc).
  2. When a user makes changes to a thing (Photo), and after those changes, the Photo’s “name” field matches a Product’s "Product Code"value, link the Photo to the Product.
  3. When a user makes changes to a thing (Photo) that is already linked to a Product, and after those changes, the Photo’s “name” field NO LONGER matches a Product’s “Product Code” value, unlink the Photo from the Product.

Bonus points if there’s a way to “scan” on a schedule to make sure nothing was missed by the system!