Empty record

[Greg Hains]

Good evening.

I have a solution whereby the client wants to see if the record is empty or not in a given table.  I know I can create another calculation field in that table doing a heap of IsEmpty() checks on all the other fields and create a flag to that effect, but there are already many fields in this particular table and this calculation field would need to be updated should any other fields be added or deleted.

How can I determine if a record is empty or not. Is there such a thing as a record length, and this can be interrogated?
Any suggestions are appreciated.

Greg

[comment]

1 hour ago, Greg Hains said:

this calculation field would need to be updated should any other fields be added or deleted.

I suppose you could get a list of field names from a layout (provided you take care to place each added field on the layout) or from an SQL query. Then loop over the list using the While() function, or a custom function, or a looping script.

Another option might be using SQL to return all field values, using the * wildcard . Then, if the returned row contains nothing but field separators, it follows that all fields are empty.

Are these good solutions? I doubt that very much. Sounds like you are trying to solve a problem that shouldn't exist in the first place. Why would a record ever be empty? Or rather allowed to be empty? What significance could an empty record have? 

 

[Greg Hains]

Hi Comment,

Thank you very much for coming back to me.  I agree, the problem shouldn't exist in the first place, and there is no doubt at all that your suggestions would work.
I prefer the SQL one.

This is what is happening:  there are several forms that the company technicians use when working on equipment. Some forms are filled out, some are not. I am only wishing to have the forms that have any information filled out, so hence wanted to establish the best way to detect an empty record. 

Thankyou again, I'll see how I go.

Cheers,
Greg

[comment]

11 minutes ago, Greg Hains said:

I prefer the SQL one

Just beware of using SQL in a calculation field - it can get very slow in some circumstances. If this task can be delegated to a script, that would be preferable.

 

14 minutes ago, Greg Hains said:

Some forms are filled out, some are not.

Maybe you could place the form's fields in a portal, so that a record is created only when some of the data is entered. 

 

[Greg Hains]

Hi Comment.
(Sorry about the delayed response.)

It's peculiar (to me).  Some of those SQL queries are lightning fast and some are slower than an "essay" of Find commands.

I'll give both your options a spin and see how I go.

Thankyou again.
Greg

[comment]

On 7/18/2021 at 6:50 AM, Greg Hains said:

Some of those SQL queries are lightning fast and some are slower than an "essay" of Find commands.

I have no personal experience with this, but others have reported ExecuteSQL() being slow when the user has an open (uncommitted) record in the queried table.

 

[Greg Hains]

Hi.

I've not heard of this one myself, but I might set this up to test it.

Thankyou very much again Comment.

Greg