Count (related table ID ), this is a aggregate function, use it in a calculation field, result number
It is always best to use the ID of the related table because it will always contain a value.
This is what I did, I have a calculation field of type number defined in the table inventory for wich I defined = count (second table::unique ID) and thie returns no value. If I use the exact same command in a layout report including only the second table, then I have the right count !
A field of type number is not a calculation field. Instead of using a field of type number with an auto-entered calculation, use a field of type calculation set to return number. The number field with auto-entered calculation won't update when the number of related records changes.
Sorry i probaby did not express myself OK, my field, lets call it CountOfLocation in my Inventory table is of type 'calculation' and the option set to it is "unstored,count(location::serial)" and I have nothing returned when I display the field CountOfLocation in a layout where the context is set to inventory
If nothing is returned, then you have no records in the related table that are linked to the current record of your layout. Otherwise, you would see a value in the field. As a test, put a portal to the related table on your layout and see if you can see any related records. Note that for this test to be valid, the exact same table occurrence name must be used in both Show related records from in portal set up and in your sum calculation.
Sorry for all this I'm a newbee with databases, you are right there might be no records related in the related table but I would still like to show the total count of locations (even if they are not related to my inventory) in a layout where I would show informations form my inventory, Is teher any way to do this ?
You can add a second relationship that will match all records no matter what the values in the fields.
Create a new occurrence of your related table.
In Manage | Database | relationships, make a new table occurrence of YourRelatedTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
YourMainTable::anyField X YourRelatedTable 2::anyField
To change the = to the cartesian join operator (X), double click the relationship line to bring up a dialog where you can change this.
Now, Count ( YourTable 2::AnyNeverBlankField )
will count records in the related table.
If you are interested in learning more about table occurrences and how they affect the function of FileMaker Databases: Tutorial: What are Table Occurrences?
YEH....... Thank you so much, I've been looking for the problem for the last 12 hours... my only problem was that I never figured out that I needed to change the = to the cartesian join operator (X) in the relationship scren.
Thanks again and have a good day