You could probably build a custom function that gets all of the fields in a table and recursively iterates through them checking to see if they have a value or not.
here is a CF that says it gets all the fields from a table...
My question is why do you want to do this?
In a table, I have 390 fields
OUCH! I'd take a look at why you have such a huge number of fields in one record. It's very likely that you need a related table of records where, for a subset of that total, you have one related record in place of each field in that set. This, coincidentally makes it easy to use the Count() function to count the number of related records.
No worry I use a lot the related tables
This is a huge database , there is in fact 103 tables :-)
with a thousand relationships
My idea for the moment
I will first select the important fields I would like to monitor (at least 100 over 390)
andI will use a calculated field with a bunch of if
if (IsEmpty(xxx);0;1) & if (IsEmpty(yyy);0;1) & if (IsEmpty(zzz);0;1) etc +1 each time
Count ( field1 ; field2 ; field3 ... )
Will return the number of fields in the same record that are not empty.
But I repeat, over 300 fields does not sound like a good design. If for no other reason, this can result in significant performance penalties. The current design trend in FileMaker is for "narrow" tables. That is, tables that are defined to have relatively few fields. This is due to the fact that if you put even one field from that record on a layout, FileMaker downloads all 300+ fields to the client--which massively increases the amount of data to be transmitted from the host to the client each time you pull up records on that layout.
Thank you Phil for your advice
I really can't reduce this number of fields for that table
In this table there at least 250 fields calculation and summary
only 100 are numbers and text
Anyway FYI the 102 others tables have between 30 and 80 fields, more reasonable
I will try the count function
but I love the idea of Coherentkris , may be a SQL function like this below should be better and faster to perform ?
ExecuteSQL ( "SELECT FieldName FROM FileMaker_Fields WHERE TableName='" & TableOccurrenceName & "' ORDER BY FieldName" ; "" ; "" )
I'm not an expert on SQL so if someone can help, it will be precious
Since SQL has to be interpreted into "Draco" commands that are then supplied to FileMaker's "Draco" engine, I doubt that it would be any faster. The Count function will evaluate pretty quickly.
And just because you have a lot of calculation fields ballooning the size of your table, doesn't mean that this is the only option. The changes are definitely not trivial for an established working DB solution, but it is possible to replace your calculation fields with number or other data fields stored in a related table or two with scripts that update values at the time data is modified.
Thank you Phil for this point
I will think about your precious advice for the script
I'll keep you informed :-)
One method I'd suggest is using a special layout where you have placed only the fields you wish to monitor.
It doesn't need to be visible to the user.
Let's say we call it FieldMonitor.
That way, to get the field list you want, you can use FieldNames( Get( fileName); "fieldMonitor")
That list can in turn be passed to a custom function or script.
Not a bad idea and you wouldn't have to use a custom function to count the fields with data though that is an option.
You could use:
Evaluate ( "count ( " & Substitute ( fieldnames ( get ( fileName ) ; get ( LayoutName ) ) ; ¶ ; " ; " ) & ") " )
And this would return a count of all fields on a given layout that are not empty.
Yes, that does seem to work nicely for the purpose.
I will do it now
We have been wondering what you'll do with the results.
What does it mean if some records are more completely filled than others?
Does having some empty field accurately represent a valid record?
it is difficult to explain in english for me but brievly
2 main functions
1/ compare 2 duplicates records in artworks or contacts (they import a lot from different sources )
we can see at a glance without counting the 100 fields
2/ statistics: percent of filled fields on a record and
percent of which person are more rigourus ( they have to fill the maximum)
The clients need Statistics a lot in the Art Galleries
We have an auditlog tracker too, an idle time tracker , a delete tracker, a time tracker etc it is big brother ;-)