I think a summary report with sub-summary parts based on Location will give you the same information in a more flexible format.
Add a subsummary part with fk_ItemID as its break field ("sorted by")
Add a subsummary part with fk_locationID selected as its break field
Place fields about the specific Item in the first subsummary part.
Place your location field and your summary field in the second subsummary part.
Delete the body part.
Sort your records by fk_ItemID, fk_LocationID
and you should get the report you want.
If you'd like to learn more, here's a link to a simple tutorial on setting up summary reports that you may find useful:
Creating Filemaker Pro summary reports--Tutorial
Edit note: just made the above link live so folks don't have to copy and paste to pull it up.
Message Edited by PhilModJunk on 01-12-2010 12:31 PM
Ok, I'm not getting this to work at all. I think I'm missing something. First off I should create the sub summary parts on the Location table? When I create the layout for this which type of layout should it be? standard form or List/report? How where do I place my calculation so that for each item I can see how many are in each of the multiple locations.
Thanks for you help something isn't clicking right.
The layout parts are created on a layout not a table. In this case, it would be a layout based on your item transaction table.
There are several ways to create this report. You can start with a standard blank layout and add the parts. That's how I wrote my instructions. I've found that the wizard for creating this type of layout to be more trouble than its worth in most cases, but you can get the same results through that approach.
In either case, make sure you've selected list view for your layout when finished. You can limit the view to this option in Layout Setup... if you want.
The calculation is the same summary field you've already defined. Simply by placing it in the sub-summary part turns it into a sub-total for a given item stored at a given location. That looks a bit like a smoke and mirrors job when you first create this kind of approach, but it does work.
I'd suggest playing around with the tutorial I mentioned in my previous post. It should be a good way to figure out how this type of report works and some of the basic options you can take advantage of with this type of report.
My problem is that if I delete the body part I don't get any data. My fields need to be in the body part in order for the fields to display data. Is there a setting that I missed?
Actually, all your fields will be in the sub-summary parts and your data will thus still be visible. You can keep the body part if you want to also include each transaction record as a separate row on your report but this is not strictly necessary. You do need an additional summary field that summarizes cItemBal. I assumed you had such a field, but don't see it in your original post now that I double-check. Define a summary field, Balance, as "total of" cItemBal in your Item Transaction table. Specify the running total options you need if you are also going to show a running total on one layout or another.
Heres the "no body" version:
---Sub summary when sorted by fk_ItemID---
---Sub Summary when sorted by fk_LocationID---
[Location::Location Description] [Balance]
This gives you a report like this:
1234 Acme Yellow Widget
Warehouse #1 5,000
Warehouse #2 6,000
1235 Acme Red Widget
Warehouse #1 500
Warehouse #3 700
If this isn't what you had in mind. Post an example like the above that shows what you want.
Thanks I don't know what I was thinking yesterday. I didn't sort the report properly for the sub-summaries to behave correctly! Whoops!
One thing I am seeing and I'm sure it's user error. Is that when I have an item with multiple transactions there is a larger space between the different locations. There are records with no visible data in-between. Any Idea of what I'm doing wrong there?
Are the records "with no visible data" supposed to be there? I have no way of knowing why such records would exist in this type of table.
You can set up a find that omits these records from the found set--that's simplest.
You could use sliding fields, but that's more work for the same result.