| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
FM.TableStatistics
Queries statistics on a table.
| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
| FM FMSQL | 8.2 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
MBS( "FM.TableStatistics"; FileName; TableName ) More
Parameters
| Parameter | Description | Example |
|---|---|---|
| FileName | The target database name. Can be empty to not limit query to one database. |
"" |
| TableName | The name of the table to check. | "Contacts" |
Result
Returns JSON or error.
Description
Queries statistics on a table.The plugin will walk over all records and count fields with various data types and how many empty fields are there. Skips global and formula fields.
We sum up the length of all text in text fields as well as the text content of all fields.
We sum up the size of all internal containers, so ContainerByteSize + AllTextLengths*2 plus some metadata is the size of the database file.
For internal containers we also count the streams inside, so you know how many JPEGs and other file types are in the table.
External containers in this function are these references by a file path. Containers stored external, but managed by FileMaker are considered internal ones from the point of view of the plugin.
e.g.
{
| "FileName": | "", |
| "TableName": | "Contacts", |
| "RecordCount": | 4, |
| "MinRowID": | 1, |
| "MaxRowID": | 4, |
| "ColumnCount": | 35, |
| "CountText": | 20, |
| "CountEmpty": | 107, |
| "CountNumber": | 5, |
| "CountDate": | 2, |
| "CountTime": | 1, |
| "CountTimestamp": | 1, |
| "CountContainer": | 4, |
| "CountExternalContainer": | 1, |
| "ContainerByteSize": | 1162565, |
| "TextLengths": | 132, |
| "AllTextLengths": | 365, |
| "CountDPI_": | 2, |
| "SizeDPI_": | 8, |
| "CountFNAM": | 3, |
| "SizeFNAM": | 90, |
| "CountSIZE": | 2, |
| "SizeSIZE": | 8, |
| "CountPNGf": | 1, |
| "SizePNGf": | 727569, |
| "CountFILE": | 1, |
| "SizeFILE": | 390, |
| "CountJPEG": | 1, |
| "SizeJPEG": | 434606 |
This function can take a long time for huge tables with millions of records. 64-bit FileMaker version recommended for better memory handling.
| Key | Value |
| FileName | The file name for the table. |
| TableName | The table name for this statistics. |
| RecordCount | The number of records. |
| MinRowID | The smallest record ID. |
| MaxRowID | The biggest record ID. |
| ColumnCount | Number of columns. |
| CountText | Number of fields containing text. |
| CountEmpty | Number of fields being empty. |
| CountNumber | Number of fields containing numeric values. |
| CountDate | Number of fields containing dates. |
| CountTime | Number of fields containing times. |
| CountTimestamp | Number of fields containing time stamps. |
| CountContainer | Number of all fields containing containers (includes CountExternalContainer). |
| CountExternalContainer | Number of fields containing external containers. |
| TextLengths | Length of text in text fields only. |
| AllTextLengths | Length of all text in all fields seen including numbers, times, dates, timestamps and containers read as text. |
| CountDPI_ | Count of containers with DPI information. |
| SizeDPI_ | Size of all DPI informations in containers. |
| CountFNAM | Count of containers with file name information. |
| SizeFNAM | Size of all filename informations in containers. |
| CountSIZE | Count of containers with image dimension information. |
| SizeSIZE | Size of all image dimension informations in containers. |
| CountMAIN | Count of all main item informations in containers. |
| SizeMAIN | Size of all main item informations in containers. |
| CountTIFF | Count of all TIFF images in containers. |
| SizeTIFF | Size of all TIFF images in containers. |
| CountPDF | Count of all PDF documents in containers. |
| SizePDF | Size of all PDF documents in containers. |
| CountPNGf | Count of all PNG images in containers. |
| SizePNGf | Size of all PNG images in containers. |
| CountGIFf | Count of all GIF images in containers. |
| SizeGIFf | Size of all GIF images in containers. |
| CountBMPf | Count of all BMP images in containers. |
| SizeBMPf | Size of all BMP images in containers. |
| CountEPS | Count of all PostScript documents in containers. |
| SizeEPS | Size of all PostScript documents in containers. |
| CountPICT | Count of all PICT images in containers. |
| SizePICT | Size of all PICT images in containers. |
| CountM4V | Count of all MPEG4 videos in containers. |
| SizeM4V | Size of all MPEG4 videos in containers. |
| CountWAV | Count of all Windows audio sounds in containers. |
| SizeWAV | Size of all Windows audio sounds in containers. |
| CountM4A | Count of all MEPG 4 sounds in containers. |
| SizeM4A | Size of all MEPG 4 sounds in containers. |
| CountMOV | Count of all QuickTime movies in containers. |
| SizeMOV | Size of all QuickTime movies in containers. |
| CountAVI | Count of all Windows movies in containers. |
| SizeAVI | Size of all Windows movies in containers. |
| CountAU | Count of all Unix sounds in containers. |
| SizeAU | Size of all Unix sounds in containers. |
| CountMP3 | Count of all MP3 sounds in containers. |
| SizeMP3 | Size of all MP3 sounds in containers. |
| CountMPEG | Count of all MPEG movies in containers. |
| SizeMPEG | Size of all MPEG movies in containers. |
| CountMPG4 | Count of all MPEG 4 movies in containers. |
| SizeMPG4 | Size of all MPEG 4 movies in containers. |
| CountAIFF | Count of all AIFF sounds in containers. |
| SizeAIFF | Size of all AIFF sounds in containers. |
| Count8BPS | Count of all Photoshop images in containers. |
| Size8BPS | Size of all Photoshop images in containers. |
| CountMETA | Count of all WMF images in containers. |
| SizeMETA | Size of all WMF images in containers. |
| CountFILE | Count of all raw file data in containers. |
| SizeFILE | Size of all raw file data in containers. |
| CountJPEG | Count of all JPEG images in containers. |
| SizeJPEG | Size of all JPEG images in containers. |
| CountFORK | Count of all MacOS resource fork information in containers. |
| SizeFORK | Size of all MacOS resource fork information in containers. |
| CountZLIB | Count of all compressed data in containers. |
| SizeZLIB | Size of all compressed data in containers. |
| ContainerSizes | Size of all internal containers together. |
| TotalSize | Size of all internal containers and texts. |
Version 10.0 or newer will ignore globals and un-stored calculations.
Examples
Check statistics:
MBS( "FM.TableStatistics"; "Contacts.fmp12"; "Contacts" )
Loop over all tables to find their sizes:
Go to Layout [ “TableStatistics” (TableStatistics) ; Animation: None ]
#
Set Variable [ $FileName ; Value: Get(FileName) ]
Set Variable [ $TableNames ; Value: MBS( "FM.QueryBaseTableNames"; $fileName) ]
#
# loop counting up from 1 to $count
Set Variable [ $count ; Value: ValueCount($TableNames) ]
Set Variable [ $index ; Value: 1 ]
#
Loop [ Flush: Defer ]
# your script steps here
Set Variable [ $TableName ; Value: GetValue($tableNames; $index) ]
Set Variable [ $JSON ; Value: MBS( "FM.TableStatistics"; $FileName; $TableName ) ]
#
New Record/Request
Set Field [ TableStatistics::JSON ; $JSON ]
Set Field [ TableStatistics::Name ; $TableName ]
Commit Records/Requests [ With dialog: Off ]
#
# next
Set Variable [ $index ; Value: $index + 1 ]
Exit Loop If [ $index > $count ]
End Loop
See also
Release notes
- Version 14.3
- Added TotalSize entry for FM.TableStatistics and FM.RecordStatistics.
- Version 10.0
- Changed FM.TableStatistics to ignore calculated and global fields.
- Version 9.0
- Added TotalSize, CountZLIB, SizeZLIB, CountFORK and SizeFORK fields for FM.TableStatistics function.
- Version 8.2
- Added FM.TableStatistics function.
Example Databases
Blog Entries
- Unlocking the Power of Database Analysis with MBS FileMaker Plugin Functions
- MBS FileMaker Plugin, version 14.3pr7
- MBS FileMaker Plugin, version 9.6pr1
- MBS FileMaker Plugin, version 8.6pr4
- MBS FileMaker Plugin 8.2 - More than 5200 Functions In One Plugin
- MBS FileMaker Plugin 8.2 - 5200 Funktionen in einem Plugin
- MBS FileMaker Plugin, version 8.2pr5
This function checks for a license.
Created 26th April 2018, last changed 4th July 2024
