Jump to content
Sign in to follow this  
Rich

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

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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 by comment

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
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 by comment
  • Thanks 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.