Script Schema for Tests of Fields instead of Hard-Coding Them

[Rich]

Greets, all:

I have a dozens of print-related scripts but I want to use scripts--instead of using scores of hard-coded calculation and summary fields--to test for the presence of values within fields both in same table and parent-child table fields--so if not all the required fields for a particular printout have values, either in the current record or anywhere in the found set, it's flagged and another script notifies the user that none of the (found) records meet the required minimum field value requirement.

Is there an elegant way to do this all in scripts or am I stuck using calculation and summary fields? ( The calculation field sets up the test with a result of 1 or 0 and the summary field sums the calculated fields--if the sum is zero the user is shown the error message.

TIA for your help!

[comment]

I am struggling to understand what exactly are you asking here.

- You can test if a specific field in the current record is empty or not by using the IsEmpty() function;

- You can test if a specific field is empty in some records of the related set by comparing the results of Count ( Related::TheField ) with Count ( Related::MatchField ) - but this will not flag specific record/s with missing value/s;

- I don't know of a way to do the same for records in the current found set, without defining a summary field that counts the field in question. Ostensibly, you could look at the result of ValueCount ( List ( Table::TheField ) ) - but this could backfire if the field can contain carriage returns. So if you don't want that summary field, you would need to loop across the found set, record by record.

(In some specific cases you could use ExecuteSQL() to avoid the loop - but as a rule, ExecuteSQL() looks at all records in the table, so you would need to reproduce the find criteria within the calculation, which is hardly elegant and certainly not efficient.)

---

A belated afterthought:
The proper way to ensure that required fields are not left empty is through field validation. That would solve the problem at the source and save you the hassle of chasing the horses that had already fled the barn, so to speak. 

 

Edited October 15, 2018 by comment

[Rich]

Thanks, as always, for your reply. I apologize for my poor explanation skills. To put it succinctly, I want to avoid using dozens of test-for-IsEmpty-and summary fields (per table, and there are fourteen tables), just to give a go-/no-go flags for reports to print.

You're right in that e-SQL's out since it would be problemetic dealing with found sets. I just finished making a looping script but there's a big performance hit when it comes to thousands of records to plow through. However, it just occurred to me while writing this post that a "simple" solution would be an extension of what I posted earlier, to have just two fields per table: one that would be a calculation field with a litany of Case statements to test for each report's minimum field requirement, and the other a summary field of the calculated field. I could then use a very simple script to act as a switchboard to select which report to run based on a global field that has a drop-down menu of report titles; the aforementioned calculation field's result will be based on what's in the global field.

Time to get coding!

[comment]

1 hour ago, Rich said:

I just finished making a looping script but there's a big performance hit when it comes to thousands of records to plow through.

Are you really printing thousands of records at once? There are ways to speed up the loop, notably switching to Form view and freezing the window before starting. I have also seen suggestions to go to a field or use a blank layout, but I have never tested these myself.

 

1 hour ago, Rich said:

a calculation field with a litany of Case statements to test for each report's minimum field requirement,

I am afraid you have lost me here (again). I think you want the calculation field to check if some of the required fields (in the same record) are empty? If so, this could be done simply by =

Count ( FieldA ; FieldB ; FieldC ; FieldD ) < 4

This returns 1 (true) if one or more of the listed fields is empty, 0 (false) otherwise - and if you make the result type a number, you can use a summary field to sum it.

However, keep in mind that a summary field must examine every record in the found set individually, same as a loop does*. So the difference in performance may not be that great - and if you're using this within a script anyway, I would definitely lean towards eliminating the fields and letting the script take on the entire task.

--
(*) Actually, the loop here does not have to examine every record, because if you're looking for a binary result, you can exit the loop as soon as a faulty record is detected.

 

Edited October 15, 2018 by comment