Adding to or Matching to a Parent Table

[Ziphius]

Perhaps someone can assist with the best way to resolve this problem...

 

A simple database with two tables having a parent child relationship MANTA CATALOG < MANTA SIGHTINGS. MANTA CATALOG has only one record for each individual manta ray in the database but that manta ray may have been seen on multiple occasions (MANTA SIGHTINGS). Users will enter their sightings each day in the MANTA SIGHTINGS table with a photo of each animal sighted and add a value list of "pending" in a "photo status field".  Eventually, those photos need to be matched against the catalog to see if they are an individual already in the catalog seen repeatedly (status field = Match) or this is a new individual that doesn't match any of those in the catalog (status field = New). For matches, the foreign key of MANTA SIGHTINGS gets updated with the primary key of the MANTA CATALOG table. For new individuals, a new record is created in MANTA CATALOG and its primary key gets added to the MANTA SIGHTINGS foreign key to link them.

 

All matches and new individuals will be checked for errors by a third party so if a NEW manta is actually a match, then the foreign key needs to be updated and the new MANTA CATALOG record deleted. 

 

What would be ideal the ability to have the MANTA SIGHTINGS photo that stays fixed, next to a MANTA CATALOG photo, whose records can be scrolled through, and when a match occurs, click a button scripted to pull that parent key and populate the foreign key. If one gets to the end of the catalog and no matches were found, another scripted button can be pressed that creates the new MANTA CATALOG record and populates the foreign key with the new parent key. Any suggestions?

 

Thanks you in advance.

 

[GisMo]

It seems like you have all the right things in place and the right relationships and you can do exactly what you're wanting to do.

 

What I'm thinking is each new manta sighting record can be added with a "pending" status when they're created. Then, in another layout, you can pull up the pending records in a portal with the picture and have another portal adjacent to the pending sighting record with a list of the existing manta records with photos in the portal rows("manta catalog"). Then, flip through the catalog portal and select the appropriate manta and then pending sighting will be associated with manta record by adding a foreign key to the sighting record. The pending status will be set to "confirmed". Otherwise if not match, have a button at the bottom of the layout to "create new manta" or whatever sounds logical for your users.

 

The scripts would be very basic using "set field". If you need further help, let me know.