Problems with Reports - How can I tabulate related summary results between tables?
I feel a bit stupid here. I'm trying to get used to Filemaker's ways of managing data, functions, calculated fields and so forth, so I feel a little intimidated by what I don't know... I have a strong background in SQL server, Microsoft Reporting Services, Crystal Reports but I just can't seem to wrap my head around how to make suitable reports in Filemaker...
What I'm trying to do...
I'm trying to take summary results from one table and subtract them from summary results of another in a report. Any ideas how I can do this?
I'd like to create an "Items In Inventory" report based on one incoming inventory line item table and another outgoing inventory table. Assuming that all distributed inventory was added in to the database through line items, I should be able to take Summary Field totals of Count of Items Received for records grouped on Item ID and subtract Summary Field totals of Count of Items Distributed. If I were to take pseudocode here to explain:
For Each Distinct ItemID
Let RemainingInventory = sum(ReceivedInventoryLineItems.CountOfItemsReceived) - sum(DistributedInventory.CountOfItemsDistributed)
(Output Inventory Record....)
Table A: Catalog (Contains all items [ItemID is defined here] which can be received from vendors or supplied to membership)
Table B: ReceivedInventoryLineItems (Each record contains an ItemID found in Catalog, Count of Items Received, etc.)
Table C: DistributedInventory (Each record contains an ItemID found in Catalog, Count of Items Distributed to which member)
Tables A and C are separate and need to remain so.
Filemaker Pro Used: 9 Pro Advanced
Database Hosting: Filemaker Server 9 (Not sure if enterprise)
Operating System: OS X and Windows XP
My Experience with FileMaker: Newbie (6 months to 1 year, mostly with UI design behind my belt)
Thanks in advance for any help you can recommend...