1 Reply Latest reply on Nov 23, 2009 9:51 AM by philmodjunk

    Problems with Reports - How can I tabulate related summary results between tables?

    tualatinchris

      Title

      Problems with Reports - How can I tabulate related summary results between tables?

      Post

      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....)
      End For

       

      Table Setups:
      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...

      -Chris










        • 1. Re: Problems with Reports - How can I tabulate related summary results between tables?
          philmodjunk
            

          "Tables A and C are separate and need to remain so."

           

          I agree, but have you considered making Tables B and C one table? That approach may make life much simpler for you.

           

          Here's how I set up an inventory log of items removed and added to inventory:

           

          Date

          ItemID

          ItemsAdded

          ItemsRemoved

          cBalance : ItemsAdded - ItemsRemoved

          InvOnHand : Summary, Running total of cBalance, restart when sorted by ItemID

           

          You can create a list time layout that looks and functions much like an accountling ledger for logging and reviewing inventory changes. You can also either create a summary report based on this combined table or base one on table A and use the Sum Function to sum the cBalance field to compute the total on hand for each item listed in Table A.