13 Replies Latest reply on Sep 16, 2016 6:23 PM by artpromac

# How to get the number of fields that are filled on a record ?

Hello

In a table, I have 390 fields

I am looking to get into a calculation field on each record of this table, the number of fields that are filled over the 390 (and optionally the number of fields filled into links (portal)

I thought that there is an easy fonction like the SUM function on Excel COUNTA (A, A) ?

thank you for your help if you have an idea for a simple calculation and especially without affecting too much the performance because I will have more than 10,000 records

• ###### 1. Re: How to get the number of fields that are filled on a record ?

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?

• ###### 2. Re: How to get the number of fields that are filled on a record ?
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.

• ###### 3. Re: How to get the number of fields that are filled on a record ?

Hi Phil

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

• ###### 4. Re: How to get the number of fields that are filled on a record ?

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.

• ###### 5. Re: How to get the number of fields that are filled on a record ?

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

Many thanks

• ###### 6. Re: How to get the number of fields that are filled on a record ?

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.

• ###### 7. Re: How to get the number of fields that are filled on a record ?

Thank you Phil for this point

I'll keep you informed :-)

• ###### 8. Re: How to get the number of fields that are filled on a record ?

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.

• ###### 9. Re: How to get the number of fields that are filled on a record ?

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.

• ###### 10. Re: How to get the number of fields that are filled on a record ?

Yes, that does seem to work nicely for the purpose.

• ###### 11. Re: How to get the number of fields that are filled on a record ?

Wondeful

I will do it now

• ###### 12. Re: How to get the number of fields that are filled on a record ?

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?

• ###### 13. Re: How to get the number of fields that are filled on a 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 ;-)