Summary Report Question

[kevinincontrol]

Working on an inventory db.  There are two tables: StockTargetQtys: is a list of items to be stocked with target stock qtys. The other table, StockedITems: is the list individual items that are InStock, OnOrder, etc.  There may be items in this list that are not in the StockTargetQtys table.  There may be qty=0 of StockTargetQtys items in the StockedItems table.

I'm trying to create a report that looks something like this (include all items from StockTargetQtys whether there is a match in StockedItems or not):

HP    Target
1.5          5
InStock:Available       2
InStock:OnHold         1
OnOrder for Stock     1
OnOrder for Project   0

HP    Target
2            5
InStock:Available        5
InStock:OnHold          0
OnOrder for Stock     0
OnOrder for Project   0

Any help would be greatly appreciated.  It's FM9, I know.

Inventory.fp7

[comment]

In the file you have attached there is no relationship between the two tables. If I understand your description correctly, there should be a relationship based on a unique item id (HP?) where StockTargetQtys is the parent table and StockedItems a child.

Now, if you want the report to include parents with no children then the report needs to be produced from a layout of the parent table. And in order to include counts of children by category, you will need to add a dedicated relationship filtered by category for each category, and a calculation field counting the related records in the category.

Otherwise you would need to use a reporting table with "dummy" records for items with 0 counts in a category - which I believe would be much more complicated, esp. in your version.

Yet another option is to use a summary field placed in filtered portals. This requires very few resources  - but might prove to be too slow if your record count is considerable.

 

Edited March 6, 2022 by comment

[kevinincontrol]

Thanks for your reply.  I tried a few things, but not getting it. How do I create a dedicated relationship filtered by category for each category? Updated version attached.

Inventory.fp7

[comment]

5 hours ago, kevinincontrol said:

How do I create a dedicated relationship filtered by category

I'd like to show you, but my FMP11 keeps crashing. Try it along this way:

1. Define an unstored calculation field Stocked Target Qtys::cAvailable (result is Text) = "In Stock: Available".

2. Add an occurrence of StockedItems to the relationships graph and name it StockedItems_Available.

3. Define a relationship:

Stocked Target Qtys::HP = StockedItems_Available::HP
AND
Stocked Target Qtys::cAvailable = StockedItems_Available::Status

4. Define an unstored calculation field Stocked Target Qtys::cCountAvailable (result is Number) = Count ( StockedItems_Available::Status )

Repeat for the other 3 categories.

 

[kevinincontrol]

Thank you!  That works great and I can use these concepts for other stuff, too. Again, THANK YOU!