Why another record or another table? Why not just use the status in the first table to determine whether or not the additional fields for purchased item data become visible or editable?
The only reason I can think of for moving these fields to a separate table is if the number of fields is becoming a problem.
Note: an onFieldExit script could be used to Clear the purchased data fields if the status is changed from "purchased" to something else, if that matters.
We need the purchased videos to be in a different table to improve our control over our stock. The Research Videos Table has hundreds of thousands of records that are not relevant for the company. Once a video is purchased, the file is inserted in our HD and we begin to distribute it. So, it's important to be on a different table so we can better analyze how many videos we have, how much we spent on each video, all media companies that licensed the video and how much it has made for us. Before the video is purchased, the research records are vague entries that are not relevant for us.
Does that make sense?
We wouldn't want to Clear the purchased data fields if the status is changed. We would like to be able to automatically create a new record on the related table once the status turns into purchased. Is that possible? Is there a script or calculation that could enable that?
You can script the process, capturing the data from the prospective record into variables, switch tables and create the record from the variables.
Have the script run as a trigger onFieldModification when the status is changed to Purschased.
It seems like you're on to something. I'm very new to FileMaker. Do you mind breaking these steps down to me and explaining how I can do each thing?
Here is a rough idea for creating a record based on one in a different table.
This one requires a relationship between the source table and the new table for owned items based on:
Primary Key in source table = Foreign Key in target table
When viewing the source table, run a script which:
- captures the ID of the source record using Set Variable ($id, id_field ; whatever that is)
- Go To Layout (to a layout based on the table for the other records where you want to add that info)
- Create New Record
- Set Field: xField (foreign Key field) ; $id (the ID you captured from the source table
In that table, have the fields which you want to auto-enter set to do so from the related source table record (auto enter via lookups).