AnsweredAssumed Answered

Create/delete related records on Table B depending on the value imputed on Table A

Question asked by luiza.b.almeida on Sep 16, 2015
Latest reply on Sep 29, 2015 by Stephen Huston

Hello Everyone,


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.

Thank you!