Counting Records of a Certain Type?

[emballantine]

What is the best way to count records of a certain type in a table? 

For instance I have a table of Categories, primary key field pkCategoryName and a table of Items primary key field pkItemID. The two tables are related (one to many) via a foreign key field fkItemCategoryName (many) in the Items table that correlates with pkCategoryName (one) in the Categories table. I want to count the number of ItemID records that have fkItemCategoryName "X".

I know this has to be something fairly simple, perhaps too simple.....but I haven't found a good way to do this.

Thanks!

ExtraCredit:

This is my end goal. I have a table of InventoryItems that contain Make and Model information about items we own. One ItemID for each type of item. I have a second table (child) called Assets that contains information to each specific Asset including AssetID. So.....an InventoryItem might have the ItemID of 001 and be an Apple Mac Pro. An Asset might be Apple Mac Pro 1 with AssetID of 001, Apple Mac Pro 2 with AssetID 002, and Apple Mac Pro 3 with AssetID 003. I, also have a table of Locations with a LocationID for each location. So, a LocationID might be RackRoom1, or RackRoom2. RackRoom1 may contain AssetIDs 001 and 002. RackRoom2 may contain AssetID 003.

I want to be able to display all the Item information of Apple Mac Pro in a layout. These are things like Manufacturer, Model Name, etc. What I want to do next is display (on the same layout, though probably on a different tab) Items by Location. So....a portal where each line shows a name (ID) of the location and a count of the number of Assets that are of the Item type Apple Mac Pro. In this case one portal row would have location RackRoom1 qty 2, and the next would have RackRoom2 qty 1. And then.....I would love to have a button to click in each portal row that would list the assets in that location.

Can this be done? Can this be done on one layout? Am I correct starting the layout from the InventoryItems table? Or should it start from a different table? 

Edited June 30, 2015 by emballantine

[comment]

A calculation field in the Categories table =

Count ( Items::fkItemCategoryName )

will return the count of related items (i.e. the count of items in each category).

 

If you want to count items in a specific category only, then you need to explain more: how is this category determined, where are you when you need this, why do you need this, and so on.

[mr_vodka]

If you create a calculation from the categories parent side, you can just use Count(pkItemID) 

There are other methods including using ExecuteSQL but not knowing exactly what you are trying to achieve, I am guess the Count of the related child records should work for you.

[emballantine]

I have tried the Count function. It gets me part way there. Very helpful, but not quite there. Here is a more thorough explanation of what I want to do:

This is my end goal. I have a table of InventoryItems that contain Make and Model information about items we own. One ItemID for each type of item. I have a second table (child) called Assets that contains information to each specific Asset including AssetID. So.....an InventoryItem might have the ItemID of 001 and be an Apple Mac Pro. An Asset might be Apple Mac Pro 1 with AssetID of 001, Apple Mac Pro 2 with AssetID 002, and Apple Mac Pro 3 with AssetID 003. I, also have a table of Locations with a LocationID for each location. So, a LocationID might be RackRoom1, or RackRoom2. RackRoom1 may contain AssetIDs 001 and 002. RackRoom2 may contain AssetID 003.

I want to be able to display all the Item information of Apple Mac Pro in a layout. These are things like Manufacturer, Model Name, etc. What I want to do next is display (on the same layout, though probably on a different tab) Items by Location. So....a portal where each line shows a name (ID) of the location and a count of the number of Assets that are of the Item type Apple Mac Pro. In this case one portal row would have location RackRoom1 qty 2, and the next would have RackRoom2 qty 1. And then.....I would love to have a button to click in each portal row that would list the assets in that location.

Can this be done? Can this be done on one layout? Am I correct starting the layout from the InventoryItems table? Or should it start from a different table? 

I am attaching a simpler version of the larger solution I am working on, the Layout I am working from is called InventoryItems. Right now it shows a record for a specific Item. Great. There is a portal based on the Locations table that displays LocationID and a calculated field from the Location table (Count(_pkAssetID)) that is counting the number of assets in each location. What I want, though, is for it to count the number of assets of the type of Item the rest of the layout is referencing. Ie. layout InventoryItems is showing info for an Apple Mac Pro. The portal is displaying all the locations where there is an Apple Mac Pro, but the count field from the Locations table is calculating all the assets in that location instead of just the Apple Mac Pro in that location. I need some way to filter the assets in a location by item type and then count them. I just don't know if this would be done in a table or in the layout or a script of some sort.

Thank you kindly!

AssetInventoryLocation.fmp12

[eos]

Here's one way to do it (see attached file).

AssetInventoryLocation_eosMOD.fmp12.zip

[emballantine]

Okay, eos.  I checked out your file. I tried to deconstruct it and apply it to my larger solution. It is not quite working the same in my larger solution as it is in the example you modified.  I'm going to reattach the file you sent (which I added a little to), and my larger file.

The larger file has the same basic setup (at least the part I am dealing with right now). There is an InventoryItems table, an Assets table, and a Locations_current table, as well as the added occurrence of the Assets table Assets_forLocationAndSelectedItem. I thought I set up this the same is it is in the example, bit I am getting slightly different results. In the example you sent it does exactly what I wanted it to, gives a count for an item by location. In the larger solution it seems to be counting each entry of an item. In other words the first asset of item X has a cFoundCount of 1, the next has a cFoundCount of 2, etc. and the first asset of item Y has a cFoundCount of 1, the next 2, etc. So, the Portal that displays the locations and cFoundCount displays the first cFoundCount for that item in that location. In other words, on a record for item X in the portal displays the location and then the cFoundCount of the first asset record in that location.

I have checked all my relationships, my fields, and my script....and they all seem right as per your example. I can't figure out why the difference in result, but I am sure it's something small or simple that I am missing.

Maybe it will be more obvious to you?

The Layout is called Inventory and the tab with the Portal is called Tracking.

Thanks!

STCSVCInventory20150701.fmp12

AssetInventoryLocation_eosMOD.fmp12

Edited July 1, 2015 by emballantine
More Info

[eos]

I have checked all my relationships, my fields, and my script....and they all seem right as per your example

cFoundCount needs to be an unstored calculation.

[bruceR]

Note that the calc field cFoundCount needs to be unstored.

[eos]

I guess now there aren't any ambiguities left whatsoever …

[emballantine]

Thanks!

I knew it had to be something small but significant that I had missed. 

Having it be unstored means it has to recalculate every time? as opposed to when it was storing it...it was just storing it at the time of data entry? Is this accurate?

Edited July 1, 2015 by emballantine

[emballantine]

Also, can anyone describe to me the different uses of a global field vs a global variable? benefits of one over another? example of when one might be more or less appropriate? 

For instance: If I have the portal that I have been working on in the Inventory layout Tracking tab and want to use the location Y from one portal row as a filter for another portal on Inventory tab that will list all the assets of the item type X in Location Y, does it matter if I pass this location information to the filter via a variable or a global field? Do they act differently?

Right now I am doing it with a global variable $$CurrentLocation, and that works. I'm just curious if it would be better to do it with a global field instead. If using a global field to just hold what is essentially a variable - how do you determine the most appropriate table to place the global field in? For instance if I wanted to create a field gCurrentLocation instead of variable $$CurrentLocation what table should gCurrentLocation reside in?

*I think the hardest part of FileMaker for me is that my OCD wants there to be one right way to do each thing and I realize that is not how FileMaker is designed to work. 

STCSVCInventory20150701.fmp12

Edited July 1, 2015 by emballantine

[eos]

Also, can anyone describe to me the different uses of a global field vs a global variable? benefits of one over another? example of when one might be more or less appropriate? 

A field exists in the schema definition, its value is stored in the file; a variable is created and modified via code and exists in memory. (This also means that you can create any number of variables dynamically, which is not possible with fields.)  

You must use a global field, rather than a variable, for user input (layout field object) and as relationship match field (and you need a field as the input target for a Custom Dialog, where you'd typically use a global field; maybe one day this feature will accept a variable).

For all other purposes (scripts, dynamic header in reports …) you can use a variable. (And make sure you learn about variable scope, i.e. where is it visible, and how long does it exist.)

For instance if I wanted to create a field gCurrentLocation instead of variable $$CurrentLocation what table should gCurrentLocation reside in?

If you use the global as a relationship match field, you need to define it in the appropriate table (as you can see in the sample file). Apart from that it doesn't really matter, and is more a question of how you want to group and organize your objects.

[bruceR]

I would like to add to what EOS said; regarding portals, and fields vs variables.

FileMaker has the concept of filtered portals as a way of narrowing what data is displayed in a portal.

You could define a relationship which uses a global field to match a field in the child table.

You could define a relationship to show all invoice records for a customer, for instance, and use a portal filter calc based on a global variable.

It does make a difference - sometimes a LOT of difference - which approach you take. Relation-based match vs filter.

A filter calc says "first get me ALL the records that match this relationship. Now, I must use the $$variable based calculation to examine every one of those records and determine whether it should be displayed. Got a large record set that matches? Let me calculate for a while. Calculate calculate calculate. Hm, OK done, here's your portal full of filtered data. Oh. Did you just change something refresh the screen maybe? Oooh let me do all of that again, hold on."

[eos]

Let me add that a productive approach is to have a relationship narrow the related set to a “sensible” amount of records, and apply a portal filter to that.

Let's say you have a table Movies with a child table Cast; use a relationship to find/filter the ~40 persons that are the cast of the current movie – out of the hundreds of thousands of Cast records; then apply a filter to just these 40 records to do more fancy stuff (leading vs supporting, male vs female, credit position < 4, all over 50 …).

In this scenario, using a portal filter to do the relationship's work would have you waiting forever …

[emballantine]

Thanks! That actually clears that up a lot.