Often, check lists are better managed as related records with one record for each check list item.
How about something like this:
"IsEmpty ( TableName::" &
Substitute ( FieldNames ( "FileName" ; "LayoutName" ) ; "¶" ; ") + IsEmpty ( TableName::" ) &
Put in the correct LayoutName, TableName, and FileName.
I did this in a rush so it probably needs debugging but I think you'll get the idea?
Edit: Replaced "Put in the correct FieldName..." with "Put in the correct LayoutName..."
Love that! Elegant and small!
What goes in place of "FieldNames?"
I tested it as a calculation returning a number in one of the lesson files from FM Pro Advanced as follows:
"IsEmpty ( Meeting::" &
Substitute ( FieldNames ( "05a_FTS_Meetings" ; "Meeting_Detail" ) ; "¶" ; ") + IsEmpty ( Meeting::" ) &
It returned a "?" on each record. I am not understanding what is going on.
The "evaluate" lets me use fields and strings to build a calculation. "FieldName(x;x) gets me a return delimited list of all the fields on the "LayoutName" layout. The Substitute changes the RETURNS into a close parenthesis plus sign... okay. This should work. Isn't.
AAAH!! related fields on the layout screws it up, ala "( Meeting::meeting_PARTICIPANT::Attended)"
So this WOULD work if there were no related fields on the layout! Have to figure out how to purge them from the evaluated list!
Thought you wanted all the fields in a table, not all the fields on a layout.
That's do-able too, but yes, you would need to alter the strategy a bit.
How many related tables are on the layout in question?
At first I wanted all of the fields in a table, but Jason's solution tied it to a layout, which is preferable. Is there a way to alter Jason's to not use a particular layout, or is there a simple way to eliminate any related fields?
Instead of entering the layout name in Jason's calc, use the table name you want in the same place.
What goes in place of "FieldNames?"
Oops. Fixed now. Replaced "Put in the correct FieldName..." with "Put in the correct LayoutName..."
"FieldNames" does not get changed - that's the function.
And yes as @Philip_Jaffe pointed out you can specify a table name instead of a layout name, but you might want to set up a layout that only has the fields you want to count (i.e.: you probably don't want to count key fields, global fields, timestamp, and other utility fields). This would solve the related fields issue also.
You don't actually have to BE on that layout for the calculation to evaluate. Just needs to be the same table. Or if this calc is defined in a field in the table, then you'd leave out the "TableName::"
I strongly agree with Phil's original point.
This whole problem results from a non-normalized structure and you would be better off in the long run learning to solve THAT problem.
However; the following works. Here my actual table name for my test is Assets.
You only need to change that part of EXPR2 to make it work.
EXPR1 = Substitute ( FieldNames ( "" ; get( LayoutName )) ; "¶" ; " ) + ¶ IsEmpty ( myTableName::" ) & " )" ;
EXPR2 = Substitute( " IsEmpty( myTableName::" & EXPR1 ; "myTableName"; "Assets") ;
Result = Evaluate( EXPR2)
The items on this checklist have varied criteria, multiple different value lists and depending on other factors are sometimes not present for each record. I can't fathom how making the values related would work.
What you describe makes it seem all the more necessary to use related records for your check list. There are two reasons for doing so:
a) Better reporting, counting and analysis as you can count, search, sort, sum, average on the set of records in many different contexts.
b) you can customize your check lists to list different sets of check list items as the needs of the user change.
Generally speaking, you need two more tables to pull this off. One stores the "master list" of check list items. One links specific check list items to a specific check list record and also has a field to record whether or not the item has been "checked off" on the list.
There's a lot more to it than that as you'll need a way to manage your checklistitems and determine which to associate with a given check list--which usually requires more tables and relationships than I've shown here that would be used to manage those lists.