2 Replies Latest reply on Sep 18, 2015 3:52 PM by luiza.b.almeida

    Automatically create new record on a table based on field value from related table


      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!