I'm building a complex database that has several related tables all across. I would like to create a calculation that would control whether new records on Table B can get created depending on the value imputed in a specific field from Table A.
To make it more clear:
We do distribution of videos and our database on FileMaker keeps track of the videos we are researching (Table A) and the videos we have purchased (Table B). All records from Table B have a related record on Table A, but not all records on Table A have a related record on Table B (because they have not been purchased yet).
Table A has all videos being researched currently. There is a specific field "Video Status" that specifies if the video has been purchased or not.
Once the video is purchased, we would like for it to automatically create a new record on Table B, which has all the videos owned by the company. Right now, Table A and Table B are linked, but employees can easily create a new record on Table B (even if the video has not been purchased yet).
How can I make it a condition for records on Table B to only exist if the "Video Status" field = "purchased?" So, as soon as the researcher changes the status to "purchased," it creates a new related record on Table B and asks the researcher to fill out all the information about the new purchased video. Also, how can I make it towards when the "Video Status" is changed back to "in progress," the related record on Table B is automatically deleted? As to prevent new records from being created if the researcher accidentally change the Status to "purchased?"
I hope this makes sense.