Compare table function to display in 2 columns

[MikeData]

Compare table function to display in 2 columns with objective to make one of the tables equal to the second. "For exporting work"

First this is credited to Stan mathews, who primarily developed the code plus some others may have contribute.
Original Thread:

http://msgboard.alphasoftware.com/al...661#post599661

Herer is the latest code.


table1 = ui_get_table("Table One","Table")
table2 = ui_get_table("Table Two","Table")
Dim Tbl1 as p
Dim Tbl2 as p

if table1 = "" .or. table2 = "" .or. (table1 = table2)
ui_msg_box("Alert","Invalid table selection.")
end if

struct1 = table.external_field_name_get(table1,"N,T,W,D,R")
struct2 = table.external_field_name_get(table2,"N,T,W,D,R")

found_same = word_in_common(struct1,struct2,crlf())
found_same = f_WrapText(found_same, 25 )
ui_msg_box("Same fields",found_same)

Tbl1 = word_subtract(struct1,found_same,crlf())
unq_to_table1 = word_subtract(struct1,found_same,crlf())
unq_to_table1 = f_WrapText(found_same, 25 )
ui_msg_box("Only in "+table1+" (or different.)",unq_to_table1)

Tbl2 = word_subtract(struct1,found_same,crlf())
unq_to_table2 = word_subtract(struct2,found_same,crlf())
unq_to_table2 = f_WrapText(found_same, 25 )
ui_msg_box("Only in "+table2+" (or different.)",unq_to_table2)

-----------------------------
In small tables this work is simple.
In large tables with a lots of different fields, it takes more work.
How to use:
Select the first table you want to adjust to be equal to the second.
Second select the second table and when they display, not the first display with the equal fields but the second with different fields, for the table to adjust, I use printscreen, so I can have a temp visual to make the changes. proceed to see the second table.
I open the first table on the left and the second table on the right + position the print screen in the third view.
Do the adjustments.
Have fun.

[Stan Mathews]

Re: Compare table function to display in 2 columns

Mike,

This seems to make more sense to me. (and included the udf used)

table1 = ui_get_table("Table One","Table")
table2 = ui_get_table("Table Two","Table")


'table1 = ui_get_table("tmonuord.dbf","Table")
'table2 = ui_get_table("monuordxx.dbf","Table")

if table1 = "" .or. table2 = "" .or. (table1 = table2)
ui_msg_box("Alert","Invalid table selection.")
end if 

struct1 = table.external_field_name_get(table1,"N,T,W,D,R")
struct2 = table.external_field_name_get(table2,"N,T,W,D,R")

found_same = word_in_common(struct1,struct2,crlf())
ui_msg_box("Same fields",f_WrapText(found_same,25))

unq_to_table1 = word_subtract(struct1,found_same,crlf())
ui_msg_box("Only in "+table1+" (or different.)",f_WrapText(unq_to_table1,25))

unq_to_table2 = word_subtract(struct2,found_same,crlf())
ui_msg_box("Only in "+table2+" (or different.)",f_WrapText(unq_to_table2,25)) 
'Date Created: 31-Jan-2012 05:43:17 PM
'Last Updated: 31-Jan-2012 05:43:17 PM
'Created By  : andy
'Updated By  : andy
FUNCTION f_WrapText AS C ( vTextC AS C, vWrapN AS N )
	
	OPTION STRICT
	OPTION ENCRYPTED_TOKENS
	ON ERROR GOTO ERR_HANDLER

	
	DIM vCol1C as C
	DIM vCol2C as C
	
	vCol1C = word( vTextC, 1, crlf(), vWrapN )
	vCol2C = word( vTextC, vWrapN + 1, crlf(), Line_count(vTextC) - vWrapN )
	
'	ui_msg_box("",vCol1C)
'	ui_msg_box("",vCol2C)
	

	DIM vCount_Col1N as N
	DIM vCount_Col2N as N
	vCount_Col1N = line_count(vCol1C)
	vCount_Col2N = line_count(vCol2C)
	
	DIM vMaxLen_Col1N as N
	FOR EACH foo in vCol1C
		IF vMaxLen_Col1N < len(foo.value) THEN
			vMaxLen_Col1N = len(foo.value)
		END IF
	NEXT
	
	DIM vCountN as N
	vCountN = max( vCount_Col1N, vCount_Col2N )
	
	DIM vReturnC as C
	DIM vWord_Col1C as C
	DIM vWord_Col2C as C

	DIM x as N
	FOR x = 1 to vCountN
		IF x <= vCount_Col1N THEN
			vWord_Col1C = word(vCol1C,x,crlf())
		ELSE
			vWord_Col1C = ""
		END IF

		IF x <= vCount_Col2N THEN
			vWord_Col2C = word(vCol2C,x,crlf())
		ELSE
			vWord_Col2C = ""
		END IF
		
		vReturnC = vReturnC + crlf() + padr(vWord_Col1C,vMaxLen_Col1N," ") + chr(9) + vWord_Col2C
	NEXT
	f_WrapText = vReturnC
	END
'-----------------------------------
'Error handler
'-----------------------------------
ERR_HANDLER:
	DIM vErrN as N 
	DIM vMsgC as C
	vErrN = error_code_get()

	vMsgC = "Error # "+ vErrN + crlf() + error_text_get(vErrN)
	vMsgC = vMsgC + crlf() + "Script: " + error_script_get()
	vMsgC = vMsgC + crlf() + "Line #: " + error_line_number_get()

	trace.writeln(vMsgC)

	ui_msg_box("Error", vMsg,UI_ATTENTION_SYMBOL)
	vMsgC = ""
	
	END  
	
END FUNCTION

[MikeData]

Re: Compare table function to display in 2 columns

I welcome all changes to improve and make our live simpler and better.
Thanks Stan.

Just one detail, Please check line 22 as it seems to have a problem.
See it here...


z_compareline22.jpg

[Stan Mathews]

Re: Compare table function to display in 2 columns

Interesting. After the first call of f_WrapText() the Option Strict command remains in effect in the script itself.

You need to declare all variables in the script as well. First lines of the script should be.........

Dim table1 as C
Dim table2 as C
Dim struct1 as C
Dim struct2 as C
Dim found_same as C
Dim unq_to_table1 as C
Dim unq_to_table2 as C

[MikeData]

Re: Compare table function to display in 2 columns

It seems that the declaration fixed it and the results looks very slick.

Excellent.

I will test it further later, as of now I am desperate with the issues of beein unable to find records in the master table from queried data from the child table.