Automatically add record from portal as new record in different table if condition met

[ehwalker]

Hi everyone,

I've run into a problem that I'm having difficulty solving and am hoping that someone here can help.

I'm working on a database that contains information about collared and released cheetahs: info such as location points (aka 'Fixes'), kills made by the animal, captures/releases of that animal, supplemental feedings, etc.

One of the tables is titled 'Tracking Sessions' and receives a new record anytime that someone goes out to track (find) that animal in the field. I have a layout for Tracking Sessions that contains a portal to a table titled 'Tracking Notes' which contains all of the time-specific details that occur during any given tracking session. Each record within 'Tracking Notes' has a field called 'Visual' which is 'yes' (true) if the animal is physically seen during the time of this record and 'no' (false) if the animal is not seen.

I'm not even sure it's possible, but what I'm struggling with is making every 'Tracking Notes' record that is true for 'Visual' to automatically be added to another table labeled 'Fix Data' and be automatically updated if the record is updated in the 'Tracking Notes' table via the portal in the 'Tracking Sessions' layout. 

I've tried using the 'magic key' method (which I've never used before and obviously don't fully understand) and records are created but it doesn't seem to work all the time and it doesn't update existing records when changes are made, it just creates duplicates with the updated info.

If anyone has any ideas on how to accomplish what it is I'm trying to do, I'd really appreciate it. I've included an image here of my relationship graph and the relevant tables highlighted in varying colours. As you can see, I've created two new occurrences of 'Fix Data' so far. If I need to provide any further details or clarify anything please let me know.... I'm quite new to Filemaker. 

Thanks!

Eli Walker

[LaRetta]

Hi Eli, welcome to FMForums! 

You can create a script as such:

If [ Tracking Notes::Visual = "yes" ]
Set Variable [ $aju# ; Tracking Notes::AJU# ]
... repeat setting any other variables with data which must be transferred to Fix Data table
Freeze Window
Go To Layout [ layout based upon Fix Data ]
New Record/Request
Set Field [ AJU# ; $aju# ]
... repeat, setting each field with the variable values you specified in Tracking Notes
Go to Layout [ original layout ]

This script would then be attached to your Visual field in the portal using script trigger OnObjectExit.

[ehwalker]

Hi LaRetta,

Thanks for the quick response! Okay, this is along the lines of what I had thought about doing in the beginning but my concern is if the Tracking Note record was updated, the existing Fix Data record would not be updated and a new record would be created thus resulting in duplicate data. So that's why I thought forming some sort of relationship would be useful. Any thoughts?

Thanks!

Eli

[bruceR]

The global field g_magicKey selects the target record.

If you want to EDIT that record; capture the ID (fk_Note# in this case) and set  g_magicKey to this value.

Then you can use set field to update fields in the selected record.

See example file attached.

Edited October 26, 2015 by BruceR

[comment]

what I'm struggling with is making every 'Tracking Notes' record that is true for 'Visual' to automatically be added to another table labeled 'Fix Data' and be automatically updated if the record is updated in the 'Tracking Notes' table

There's something very wrong with this requirement. The basic tenet of a relational database is that each data item is stored in one place only. I am not sure what "Fix Data" represents in real life (it does not sound like an entity) and I would suggest you reconsider your structure before you look for complicated ways to make this work as requested (no simple way will be found).

I could understand this better if "visual" was a field in this 'Fix Data' table and filling it would automatically create the related record (with the relationship being based on TrackingNoteID). Then you would only need to worry about deleting this record when the field is turned back to empty.

Edited October 26, 2015 by comment

[ehwalker]

Thanks Bruce! So setting g_magicKey to fk_Note# and then performing the Set Field across the Tracking Notes = Fix Data_forT.Notes_Create relationship will update the existing record in Fix Data rather than creating a entirely new record?

Comment, a 'Fix' is a term we use in wildlife to refer to a confirmed location point of a study animal (in this case through a visual sighting of said animal). And yes, I know that each datum should only be stored in a single place within a database, but the tracking notes table contains data that sometimes is and sometimes is not fix data, so I couldn't see any other way around it.

[comment]

the tracking notes table contains data that sometimes is and sometimes is not fix data, so I couldn't see any other way around it.

A simple way would be to find the tracking notes that contain fix data.