Count how many fields on layout are empty

[panchristo]

Hi, can someone help me build a recursive function which will do the following:

Out of a given list of fieldnames on a layout, recursively test how many of them are empty and produce a fraction of the total number.

The field list would result from the function FieldNames(Get(Filename);Layout), where Layout will be the parameter of the custom function.

I am really awful at recursive unctions, so please help!

[comment]

Out of a given list of fieldnames on a layout, recursively test how many of them are empty and produce a fraction of the total number.

What meaning would this fraction have? If it does mean anything, I think it's fairly safe to guess that there is a flaw in the way your data is structured.

[bruceR]

Perhaps; but you don't know that. Could even be used as a progress indicator.

[comment]

A progress of what?

[panchristo]

It would be used as an indication of how many fields have been reviewed. (@BruceR: Yes, kinda progress indicator).

So, now that you know, can you help a bit please?

P.S. Don't get offended, I just need that!

[comment]

I am not offended at all. I am trying to help you - and I think that such indication, when based on FieldNames(), SHOULD be meaningless. Say, for example, that there are 5 fields for the user to fill and 3 of those are filled - that's a progress of 60%. Now if I add a calculation field and a summary field to the layout, the progress will go down to 43%.

In order for the indication to be meaningful, it must take into account only fields that are relevant to the progress being measured. The FieldNames() function makes no such distinction. Only you can tell which fields are relevant. Therefore the calculation should look something like =

Count (

FirstName ;

LastName ;

DOB ;

Address ;

City

) / 5

Of course, if you have 150 fields such as Bone1, Bone2, Bone3, etc. to track, writing such calculation can be pretty tedious. So instead of solving the real problem, you turn to a custom function to write it for you. I don't think helping you to dig yourself deeper in this hole is real help.

[bruceR]

At any rate, here's an example.

FilledFields.fp7.zip

[bruceR]

Supposing that the data model requires all fields to be filled, it can indicate how many are empty. So the user can see status as they move through the form. Or a supervisor can see status.

Also, we don't necessarily know where the data comes from. Perhaps this is imported data from some other data source. And the person using this database and responsible for data quality data can go back to the source and say, you know we're getting incomplete data sets from you.

[comment]

Supposing that the data model requires all fields to be filled

Including calculation/summary fields?

we don't necessarily know where the data comes from

Actually, we do:

http://fmforums.com/forum/showtopic.php?tid/216292/

[LaRetta]

Hey panchristo, trust me ... you'd be better off to listen to Comment from the get-go. It seems you've been going 'round this problem for quite some time. If you continue down this path, you will find madness at the end. :wink2:

[bruceR]

Including calculation/summary fields?

Actually, we do:

http://fmforums.com/forum/showtopic.php?tid/216292/

Irrelevant. That's not part of this thread.

The question of calculating filled or empty fields is a general question having utility that does not depend on the goals of an individual post or thread. It is also not a question that depends on improper design.

Also, in my example I directly addressed the issue of which fields are passed to the function. You can choose to pass all fields on the layout; or a particular set of fields; and you can have the calc automatically exclude itself.

Edited September 12, 2010 by Guest

[panchristo]

BruceR:Of course, if you have 150 fields such as Bone1, Bone2, Bone3, etc.

Comment also said:

Actually, we do:

http://fmforums.com/forum/showtopic.php?tid/216292/

As well as LaRetta...

Hehe, you got me!- Seems you 've been really following me all this time (gratitude).

It really is following the afore-mentioned topic, which I haven't been able to solve by the suggested solutions (details discussed there).

@BruceR: Your example file either doesn;t work or I don't know what to do with it.

As for the percentage calculation, I have (for another purpose) a layout with only the fields to be calculated so the calculation field will be based on that (would I have problems then?).

So, any more ideas towards the CF solution(is it really so hard)?

[comment]

Seems you 've been really following me all this time

Not really. The fact is that your request really doesn't make sense (or shouldn't make sense if your data structure is normalized). That made me suspicious, so I looked up your topics and there it was.

[comment]

Irrelevant. That's not part of this thread.

I am not going to answer that because you're being rude again. And please don't waste your time (and mine) by trying to convince me that starting a reply with "Irrelevant." is not rude. Or that it's only my interpretation of your tone. There is no tone in which you can start by saying "Irrelevant." without being confrontational.

[bruceR]

What exactly "doesn't work?". Please be much more specific. In the first record 3 data fields out of 6 data fields total are empty. In the second record 4 data fields out of 6 are empty. Seems to me that is *exactly* what you asked for.

You asked for an indicator of filled vs total fields. Seems to me that's exactly what the file provides.

Edited September 12, 2010 by Guest

[bruceR]

Not really. The fact is that your request really doesn't make sense (or shouldn't make sense if your data structure is normalized). That made me suspicious, so I looked up your topics and there it was.

This simply isn't true.

(request to know filled unfilled info counts==makes no sense.)

There are any number of reasons to know the number of filled/empty fields on a layout. Whether the data and layout are well normalized or not is irrelevant.

Your issue with PanChristo's data structure is something he should take seriously. Being able to monitor data completeness does not contradict this.

[David Jondreau]

It's not that hard, but some qualified respondents are reluctant to give answers to "bad" questions. "Bad" meaning you're trying to cure a symptom rather than an underlying problem.

Everyone's trying to help.

Anywhoo, Bruce's function works as advertised...though the name is slightly misleading. EmptyFields() may be better than FilledFields() as the ratio is empty to total and not filled to total.

[bruceR]

I am not offended at all. I am trying to help you - and I think that such indication, when based on FieldNames(), SHOULD be meaningless. Say, for example, that there are 5 fields for the user to fill and 3 of those are filled - that's a progress of 60%. Now if I add a calculation field and a summary field to the layout, the progress will go down to 43%.

Please note an important detail in the original question:

"Out of a given list of fieldnames on a layout, recursively test how many of them are empty..."

Given list. This is an ambiguous statement. It quite specifically does NOT say ALL fields. He might mean that; and might not.

The issue of reading very specific meaning (and intent; and life-defining moral character) into ambiguous statements comes up quite a lot here.

It is important - as you note - to figure out which fields to monitor.

At any rate, providing guidance to a solution that does make sense seems useful.

[panchristo]

What exactly "doesn't work?".

The zcEmpty_ratio Field doesn't show anything at all.

[bruceR]

You're right, seems to be an FM11 vs FM10 issue, investigating...

[bruceR]

There is apparently a problem with how FM10 handles the getFieldName( self ) calculation. So I changed the approach for getting the field list.

I created another layout, "Use_these" and on that layout put the fields that I want to monitor. The list of field names is pulled from that layout and is then passed to the custom function. See attached.

FilledFields.fp7.zip