Finding and reporting on empty fields

[Ted Giles]

Has anyone got any ideas how to report on fields in a table with data present?
I know the table name, and the field names.

I need to compare migrated data with available data and identify what didn't get migrated to a new application. (Under the control of the supplier).

I plan to scan the table and report the name of each field which has data present - but only once.

So, if any field in a table of 200,000 records has data, I need to know the name of that field.

As soon as a field has been identified as having data, the current search can stop and the next field interrogated.

My simple logic would be;
Read record1, field1
If Data found - report
If not found
Read next record field1
If data found - report

Once a field has been identified with data, read next field staring at record 1.

It's driving me nuts as I'm sure there is a simple way to do this using the Query function.

Ted
7 hours in the car today as the M40 was closed. First bit of cold weather and it's mayhem!

[Stan Mathews]

Re: Finding and reporting on empty fields

I would sort the table descending by each field in turn and check that the field in question in the first record in the sort order is not blank.

fld_w_values = ""
tbl = table.open("yourtablename",FILE_RW_EXCLUSIVE)
query.filter = ""
query.order = "invert(firstfieldname)"
query.options = ""
query.description = "Check"
tbl.query_create()
tbl.fetch_first()
IF tbl.firstfieldname > ""
	fld_w_values = fld_w_values+"firstfieldname,"
end if
tbl.query_detach("Check")
query.filter = ""
query.order = "invert(secondfieldname)"
query.options = ""
query.description = "Check"
tbl.query_create()
tbl.fetch_first()
IF tbl.secondfieldname > "" 'or other test for numeric, date, logical
	fld_w_values = fld_w_values+"secondfieldname,"
end if
tbl.query_detach("Check")
tbl.close()
ui_msg_box("Found","Values in "+ fld_w_values)
'or trace.writeln(fld_w_values)

[Ted Giles]

Re: Finding and reporting on empty fields

Thank's Stan. Trouble is the number of fields/columns I need to go,through.
There are 27 tables with up to 250 fields in each - all different names e.g.
** O_ROTA_PATTERN_GROUPS.PAG_OVERLAP_DAYS
(It's in an Oracle table at present).
Is there a way of "incrementing" the fields, or reading the names into an array and cycling through them do you think?

[Stan Mathews]

Re: Finding and reporting on empty fields

This should work for all data types.

fld_w_values = ""
tbl = table.open("yourtablename",FILE_RW_EXCLUSIVE)
flds_list = tbl.field_name_get()
FOR each fname in flds_list
	query.filter = ""
	query.order = "invert("+fname+")"
	query.options = ""
	query.description = "Check"
	tbl.query_create()
	tbl.fetch_first()
	thisfield = "tbl."+fname
	IF isnotblank(thisfield)
		fld_w_values = fld_w_values+fname+","
	END IF
	tbl.query_detach("Check")
next
tbl.close()
ui_msg_box("Found","Values in "+ fld_w_values)
'or trace.writeln(fld_w_values)

[Ted Giles]

Re: Finding and reporting on empty fields

You are a star! I'll try this out first thing tomorrow. What's the weather like out West?

[Stan Mathews]

Re: Finding and reporting on empty fields

See if you like this approach.

dim answer as p
answer.count = 0
fld_list = ""
tbl = table.open("item")
flds = table.fields_get()
FOR i = 1 TO flds
	fld_name = tbl.field_get(i).name_get()
	tbl.field_statistics(fld_name, answer)
	IF answer.count = 0
		ui_msg_box("Warning","Table is empty")
		end
	END IF
	fld_type = typeof(answer.maximum)
	SELECT
		CASE fld_type = "C"
			IF answer.maximum > ""
				fld_list = fld_list + fld_name + ", "+fld_type+", "+answer.maximum + crlf()
			END IF
			
		CASE fld_type = "N"
			IF answer.maximum > 0
				fld_list = fld_list + fld_name + ", "+fld_type+", "+alltrim(str(answer.maximum)) + crlf()
			END IF
			
		CASE fld_type = "D"
			IF answer.maximum > {}
				fld_list = fld_list + fld_name + ", "+fld_type+", "+dtoc(answer.maximum) + crlf()
			END IF
			
		CASE fld_type = "L"
			IF answer.maximum = .T.
				fld_list = fld_list + fld_name + ", "+fld_type+", "+".T." + crlf()
			END IF
	END SELECT
NEXT i
tbl.close()
ui_msg_box("Found",""+fld_list)

A bit of testing reveals that date fields are reported as character.