Creating a Budget Sheet From Certain Records
I am using FileMaker Pro 10 Advanced on OS X and am creating a database for an advertising agency to make workflow more efficient. I started using FileMaker last week, but am a Computer Science major and have basic database knowledge.
I have two layouts as of now. One is the starting point which holds the three different types of purchase orders using tabs (Print, Broadcast, and Miscellaneous). The other is a Budget Sheet which needs to be updated as new purchase orders are created. The starting point layout has tons of fields, but the ones that are relevant to the budget sheet are start and end dates, media name, client, and cost. What I need FileMaker to do is to create budget sheets for each client for every month whenever a budget sheet is requested. I was wondering if I could make the client name the primary key between the two tables. I'm not 100% sure if that will work or not. I can't mess with the design too much since these items are printed and faxed to the clients.
I have separate columns in the budget sheet for print, broadcast, and misc media types. For each purchase order there is one media name, one date, and one cost. I need FileMaker to create a budget sheet for each client for each month with every relevant purchase order's information (media name, dates, cost), but also change the correct budget sheet when a purchase order is added for that month for that client. Do these questions make sense?
I've started and stopped with a ton of scripts, tried portals, etc etc. I'm either not understanding databases fully or am missing something that seems fairly simple. Any ideas would be greatly appreciated. Thanks!