AnsweredAssumed Answered

Counting multiple records based on variable dates and returning count to field

Question asked by jl2012 on Feb 17, 2016

I have a database that tracks all of the VFX shots we have in the current film I am working on.  I am looking to get some help on creating a snapshot layout to report how many shots per vendor are estimated to be finaled for the week and how many shots actually are final.  I have a table labeled "Status Report" that has a list of all of the shots we have in the movie based on vendor, with the field for vendor being "Facility".  The table also has fields for the shot "FX SHOT #" and the date the shot is supposed to be final "POST: Final Delivery".  In another related table, "Filmout Takes" we bring in all of the submissions we get from our vendors and that matches up to the "FX SHOT #" through a relationship.  In this table, we mark what versions are final in a field called "Final Approved Version".  There is also a date based on when the submission was received which goes into the submission "Date" field.  I am looking to create a layout that would list the "week ending date", a column for each vendor that gives the sum of shots that are due for that week and a column for each vendor that gives a sum of shots that were actually finaled for that week.  The actual finaled shots would come from the "Final Approved Version" field.  I thought of creating a separate table so I could create records that contained each week ending date, but I am struggling to come up with a way to find the sum of all of the shots that were supposed to be due during that particular week.  Is this a script trigger on layout enter or should I focus on a calculation field?  Any direction would be greatly appreciated.  Fairly new at complex this is a bit of a head scratcher for me.  Thanks in advance.