New to the forum and Filemaker Pro but I'm hoping you all can help me out. I'm wondering if I can calculate across sub-summary parts. Or if that is even what I should be doing in the first place. Here's what I'm trying to do:
I have a table that is a full inventory of audio equipment - there is a record for every piece of equipment we own. I then have another table that is essentially a wish-list of what equipment is needed for a certain project. This table contains a field for quantity, such as "2 Meyer 700 Speakers". Now, in the Wish-List I want to distinguish which speakers are for what, so maybe I have 2 left and 2 right. I have figured out how to use Sub-Summaries to create an overview that only shows how many total (total of quantity when sorted by model number) of each item are needed.
Here's where I am stuck - I want to be able to generate a report/layout/whatever that will tell me how many of each item I need to buy or rent. Say I only have 3 Meyer 700 Speakers, I want it to calculate how many I want, minus how many I have, and give me a simple line-by-line listing of each, in this case telling me I need to rent 1 Meyer 700 Speaker.
I tried to get a calculation to do, basically, 'Total of Wish List Quantity when sorted by model number" minus " Count of model number in stock" to equal "quantity needed to rent". I'm having a problem using the sub-summary fields because it seems as if they are too variable and are using the values of full totals or full counts of model numbers, not the count as if were grouped.
Does this make sense? Sorry if this is long-winded. Any thoughts on how to do this more efficiently than I have been attempting?
Thanks very much,