Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

[Garth Groft]

hello experts,

i need someone to knows Xbasic to help me get this script (attached and listed below) working. this is my first attempt to write a script.
i am just trying to update a field in a child table one table by searching for the matching record in the parent table.

the is a very simple task and should be easy to code. it can be done very easily using FoxPro (see below).

i will pay for consulting services if anyone is interested in helping me on this and my Alpha Anywhere forms as well.

' _________________________________________________________________________________________

' This xbasic script file updates the custcode field in WarrReg table from custcode field in Customers
' The custcode field will be used to link these two tables together.
' Garth Groft 2015.4.2
' Program - WarrReg.custcode update script by Garth Groft.
' This program is based on example found in Alpha Anywhere Xbasic Workshop by Dr. Peter Wayne, Chapter 20.

' Date Created: 2015.3.6
' Last Updated: 2015.4.2
' Created By : Garth Groft
' Updated By : Garth Groft

' This program scans the WarrReg table from first to last record.
' For each WarrReg record, a query is run on the Customer table where RTRIM(customer.companynam) = RTRIM(WarrReg.customer_i)
' If the query finds the matching record, WarrReg.custcode is set to Customer.custcode.
' If not found, WarrReg.custcode is set to "no match".
' It is possible there are WarrReg.customer_i not present in Customer.companynam.

option strict
dim cust as p
dim warrreg as p
dim warrregCustname as c
dim query.options as c

dim shared query as p
dim shared query.filter as c
dim cnt as n

cust = table.open("customers",FILE_RW_SHARED)

'not used - query.filter = "RTRIM(customers.companynam) = RTRIM(warrreg.customer_i)"

' this filter contains warrregCustname (defined below as warrregCustname = warrreg.customer_i).
' the query will result in just one record.
'query.filter = "RTRIM(customers.companynam) = warrregCustname"
query.filter = "RTRIM(cust.companynam) = warrregCustname"

query.options = "T"

warrreg = table.open("warrreg",FILE_RW_SHARED)
warrreg.fetch_first()
while .not. warrreg.fetch_eof()
warrregCustname = warrreg.customer_i
cnt = cust.query_create()
warreg.change_begin()
if cnt > 0
'warrreg.custcode = customers.custcode
warrreg.custcode = cust.custcode
else
warrreg.custcode = "no match"
end if
warreg.change_end()

warrreg.fetch_next()
end while

warrreg.close()
cust.close()
end



' Working simple FoxPro Version ...
'USE c:\actwarrregvfp\customers.dbf IN 0 SHARED
'SET ORDER TO customerid IN Customers
'USE c:\actwarrregvfp\warrreg.dbf IN 0 SHARED
'SET RELATION TO Warrreg.customerid INTO Customers ADDITIVE

'*!* Note WarrReg cusstomerid needs meaning numbers entered.
'SELECT WarrReg
'SCAN
' REPLACE IN WarrReg custcode WITH Customers.custcode
'ENDSCAN

'*!* alternate coding method:
'*!* SELECT Customers
'*!* REPLACE ALL custcode WITH LEFT(customername,4) + STR(customerid,4)

[Stan Mathews]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

Try it as.

option strict
dim cust as p
dim warrreg as p
dim warrregCustname as c
dim query.options as c

dim shared query as p
dim shared query.filter as c
dim cnt as n

cust = table.open("customers",FILE_RW_SHARED)

'not used - query.filter = "RTRIM(customers.companynam) = RTRIM(warrreg.customer_i)"

warrreg = table.open("warrreg",FILE_RW_SHARED)
warrreg.fetch_first()
while .not. warrreg.fetch_eof()
	warrregCustname = warrreg.customer_i
	' this filter contains warrregCustname (defined below as warrregCustname = warrreg.customer_i).
	' the query will result in just one record.
	query.filter = "RTRIM(companynam) = "+quote(warrregCustname)
	query.options = "T"
	cnt = cust.query_create()
	warreg.change_begin()
	if cnt.records_get() > 0
		'warrreg.custcode = customers.custcode
		warrreg.custcode = cust.custcode
	else
		warrreg.custcode = "no match"
	end if
	warreg.change_end()
	warrreg.fetch_next()
end while

warrreg.close()
cust.close()
end

If you create an index on the customer table, tagname cmpnynm, expression companynam then you can use the index in the script and it should be much faster than multiple queries.

option strict
dim cust as p
dim warrreg as p
dim warrregCustname as c
dim query.options as c

dim shared query as p
dim shared query.filter as c
dim cnt as n

cust = table.open("customers",FILE_RW_SHARED)
cust.index_primary_put("cmpnynm")

'not used - query.filter = "RTRIM(customers.companynam) = RTRIM(warrreg.customer_i)"

warrreg = table.open("warrreg",FILE_RW_SHARED)
warrreg.fetch_first()
while .not. warrreg.fetch_eof()
	warrregCustname = warrreg.customer_i
	warreg.change_begin()
	if cust.fetch_find(warrregCustname) > 0		'warrreg.custcode = customers.custcode
		warrreg.custcode = cust.custcode
	else
		warrreg.custcode = "no match"
	end if
	warreg.change_end()
	warrreg.fetch_next()
end while

warrreg.close()
cust.close()
end

Also just edited the line

query.filter = "RTRIM(cust.companynam) = "+quote(warrregCustname)

to be

query.filter = "RTRIM(companynam) = "+quote(warrregCustname)

The filter is being applied to the customer table by way of the cust.query_create() so the table designation is unnecessary.

[Garth Groft]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

i made you changes. i also had to change warreg to warrreg on change.begin and end. (i should use camel syntax warrRregfor clartiy.)

it is still running. there are over 100,000 record process. i hope it is not in a loop. i should display a record counter.

i want to it this way but did not know how.

i do not see yet where in the code warrreg.customer_i is looked up in the customer table.

[Stan Mathews]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

i do not see yet where in the code warrreg.customer_i is looked up in the customer table.

Which version?

In the query version the line

query.filter = "RTRIM(companynam) = "+quote(warrregCustname)

restricts the selection of records to only those where the customer table companynam = warrregCustname. If the query finds a record ....

In the index version the line

if cust.fetch_find(warrregCustname) > 0

looks up the warrregCustname in the index. If it is found the record pointer is positioned to the corresponding record in the table and the record number is returned.

[Garth Groft]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

Now Alpha is no longer responding. for starters, i better change the code to process only a few records, like 10 to make sure the record pointer is advancing and expected result are captured. do you know the command to limit the do while loop to just 10 records?

thanks

[Garth Groft]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

i am only using the index version.

looking at the warrReg table, processing proceeded to the end or near then end. about half of the custcode records have valid and correct custcodes. the other half have "no match" which is likely due to the company names not entered the same in the two tables. the program cannot fix this - its a manual task. some record have a blank. because some the warrreg company names are empty and bacause the customer custcode is missing (no longer a customer) for some customers.

[Stan Mathews]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

Just make the first line of code

debug(1)

and run it. Then you can step through the script with F12 and you can enter expressions at the bottom to watch the values change. cust.Recno() and warrreg.recno() for record numbers. warrregCustname to see it change, etc. You can halt the script at any point you want.

To stop at a certain record number automatically
dim reccnt as N
reccnt = 0
while .not. warrreg.fetch_eof()
reccnt = reccnt + 1
if reccnt > 10
cust.close()
wqarrreg.close()
exit while
end if

rest of script

[Garth Groft]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

thank you very much. i think we are almost done. this is a wealth of information. i never expected to receive this kind of through response and so quickly.

this will make for me a very good friday and happy easter.

i do not know why alpha stopped responding. it actual finished i think.

where do i put the warrreg.recno() to watch the record number moving? where in the bottom of what are you referring to? do you mean in my open warrReg browse?

can i display a Windows message box telling me processing is done?

[Stan Mathews]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

i do not know why alpha stopped responding

Alpha spends very little effort telling you that it is doing what you told it to do. Alpha not responding is frequently encountered with lengthy processes.

where do i put the warrreg.recno() to watch the record number moving?

When the debug window appears you will see the code which you can step through. At the bottom of the debug window there will be two boxes. One labeled expression and the other value. You can enter an expression and watch the value change as records are fetched by the code. After entering the first expression use the enter key to move down and create a new line, repeat as desired.

http://wiki.alphasoftware.com/Script+Debugger

can i display a Windows message box telling me processing is done?

Portions of scripts are multi-threaded and you may get the done message before the script is actually finished but you can use the line

msgbox("Done")

as the last line if you want to try it.

Just noticed your comment about

in my open warrReg browse?

Having a browse of a table open while processing is being done is sometimes permissible but hardly ever advisable. Doing so forces Alpha to be aware of changes that might take place in the browse and not devote full attention to the processing.

[Garth Groft]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

awesome. thank you again.

i thought the browse was necessary. this may be what overworked alpha.

i may make the msgbox at the end of the process also tell me how many custcodes found, not found, empty custcodes.

i know how to keep track of these counts.

can i have multiple line message with CHR(13) being a carriage return?

[Stan Mathews]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

Crlf() shortcut for chr(13) + chr(10)

There are other means to speed up processing in a script such as yours. Using the optional table open mode parameter FILE_RW_EXCLUSIVE will help. You can also enclose the change section with the .batch_begin() and .batch_end() methods attached to the appropriate table pointers. See the documentation for specifics.

[Garth Groft]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

stan, i added more features and now it is not running. so i commented out my new features and still not working. i did not save the version that worked.

cnt = cust.query_create() yields error no such field.

i do not see where to attach the code (below) or the 2 tables.


' Update_custcode_stan2
' fetch version
' Date Created: 06-Mar-2015
' Last Updated: 04-Apr-2015
' Created By : Garth Groft
' Updated By : Garth Groft

' This xbasic script file updates the custcode field in WarrReg table from Customer.custcode
' Garth Groft 2015.2.15
' Ref: Alpha Anywhere Xbasic Workshop by Dr. Peter Wayne, Chapter 20.
' The program scans WarrReg from first to last record.
' For each WarrReg record, a fetch is performed on the indexed Customer table seeking RTRIM(customer.companynam) = RTRIM(WarrReg.customer_i)
' If the fetch finds a matching record, WarrReg.custcode is set to Customer.custcode.
' If fetch is not found, WarrReg.custcode is set to "no match". It is possible there are WarrReg.customer_i not present in Customer.companynam.
' This script file needs to be run from within alpha's script editor.


option strict
dim cust as p
dim warrreg as p
dim warrregCustname as c
dim query.options as c

dim shared query as p
dim shared query.filter as c
dim cnt as n

'dim cntCustcodesFound as n
'dim cntCustcodesNotFound as n
'dim cntCustcodesEmpty as n
'
'
'cntCustcodesFound = 0
'cntCustcodesNotFound = 0
'cntCustcodesEmpty = 0

'There are other means to speed up processing in a script such as yours.
'Using the optional table open mode parameter FILE_RW_EXCLUSIVE will help.

'You can also enclose the change section with the .batch_begin() and .batch_end() methods attached to
'the appropriate table pointers. See the documentation for specifics.

cust = table.open("customers",FILE_RW_SHARED)

'not used - query.filter = "RTRIM(customers.companynam) = RTRIM(warrreg.customer_i)"

warrreg = table.open("warrreg",FILE_RW_SHARED)
warrreg.fetch_first()
while .not. warrreg.fetch_eof()
warrregCustname = warrreg.customer_i
' this filter contains warrregCustname (defined below as warrregCustname = warrreg.customer_i).
' the query will result in just one record.
query.filter = "RTRIM(cust.companynam) = "+quote(warrregCustname)
query.options = "T"
cnt = cust.query_create()
warreg.change_begin()
if cnt.records_get() > 0
'warrreg.custcode = customers.custcode
warrreg.custcode = cust.custcode
'cntCustcodesFound = cntCustcodesFound + 1
else
warrreg.custcode = "no match"
'cntCustcodesNotFound = cntCustcodesNotFound + 1
end if
warreg.change_end()
warrreg.fetch_next()
end while

warrreg.close()
cust.close()
end

' make the msgbox at the end of the process
' also tell me how many custcodes found, not found, empty custcodes.
msgbox("Notity","Done",1)
'msgbox("Done"+ crlf()+ "cntCustcodesFound = "+ cntCustcodesFound + crlf()+ "cntCustcodesNot Found = " + cntCustcodesNotFound)

' Notes:
' ______________________________________________

'In the query version the line
'query.filter = "RTRIM(companynam) = "+quote(warrregCustname)
'restricts the selection of records to only those where the customer table companynam = warrregCustname. If the query finds a record ....
'In the index version the line
'if cust.fetch_find(warrregCustname) > 0
'looks up the warrregCustname in the index. If it is found the record pointer is positioned to the corresponding record in the table
' and the record number is returned.

' ____________________________

'Just make the first line of code

' debug(1)

' and run it.
'Then you can step through the script with F12 and you can enter expressions at the bottom to watch the values change. cust.Recno() and warrreg.recno()
'for record numbers. warrregCustname to see it change, etc.

'________________________________________

'Alpha spends very little effort telling you that it is doing what you told it to do. Alpha not responding is frequently encountered with lengthy processes.
'When the debug window appears you will see the code which you can setp through. At the bottom of the debug window there will be two boxes.
'One labeled expression and the other value. You can enter an expression and watch the value change as records are fetched by the code. After entering the first expression use the enter key to move down and create a new line, repeat as desired.
' http://wiki.alphasoftware.com/Script+Debugger

'can i display a Windows message box telling me processing is done?
'Portions of scripts are multi-threaded and you may get the done message before the script is actually finished but you can use the line
'msgbox("Done")

'Having a browse of a table open while processing is being done is sometimes permissible but hardly ever advisable. Doing so forces Alpha to be aware of changes
'that might take place in the browse and not devote full attention to the processing.

'_______________________________________
'
'Just make the first line of code
'
'debug(1)
'
'and run it. Then you can step through the script with F12 and you can enter expressions at the bottom to watch the values change. cust.Recno() and warrreg.recno() for record numbers. warrregCustname to see it change, etc. You can halt the script at any point you want.
'
'To stop at a certain record number automatically
'dim reccnt as N
'reccnt = 0
'while .not. warrreg.fetch_eof()
'reccnt = reccnt + 1
'if reccnt > 10
'cust.close()
'wqarrreg.close()
'exit while
'end if

'_____________________________

'When the debug window appears you will see the code which you can step through. At the bottom of the debug window there will be two boxes. One labeled expression and the other value. You can enter an expression and watch the value change as records are fetched by the code. After entering the first expression use the enter key to move down and create a new line, repeat as desired.
'http://wiki.alphasoftware.com/Script+Debugger
'rest of script

'__________________________________

'i thought the browse was necessary. this may be what overworked alpha.
'i may make the msgbox at the end of the process also tell me how many custcodes found, not found, empty custcodes.
'i know how to keep track of these counts.
'can i have multiple line message with CHR(13) being a carriage return?
'Crlf() shortcut for chr(13) + chr(10)

'____________________________________

'There are other means to speed up processing in a script such as yours.
'Using the optional table open mode parameter FILE_RW_EXCLUSIVE will help.
'You can also enclose the change section with the .batch_begin() and .batch_end() methods attached to
'the appropriate table pointers. See the documentation for specifics.

[Stan Mathews]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

I don't see the code you are currently using, only a portion of the discussion.

Start a new post and copy in the current script.

[Garth Groft]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

the revised not working script is attached.

do you need the tables. it was working so you probably do not need them. i could zip them to reduce the size.

garth

[Garth Groft]

Re: Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record

i do not know how to create a new post !!

code:

' Update_custcode_stan2
' fetch version
' Date Created: 06-Mar-2015 09:24:28 AM
' Last Updated: 02-Apr-2015 02:59:12 PM
' Created By : Garth Groft
' Updated By : Garth Groft

' This xbasic script file updates the custcode field in WarrReg table from Customer.custcode
' Garth Groft 2015.2.15
' Ref: Alpha Anywhere Xbasic Workshop by Dr. Peter Wayne, Chapter 20.
' The program scans WarrReg from first to last record.
' For each WarrReg record, a fetch is performed on the indexed Customer table seeking RTRIM(customer.companynam) = RTRIM(WarrReg.customer_i)
' If the fetch finds a matching record, WarrReg.custcode is set to Customer.custcode.
' If fetch is not found, WarrReg.custcode is set to "no match". It is possible there are WarrReg.customer_i not present in Customer.companynam.
' This script file needs to be run from within alpha's script editor.


option strict
dim cust as p
dim warrreg as p
dim warrregCustname as c
dim query.options as c

dim shared query as p
dim shared query.filter as c
dim cnt as n

dim cntCustcodesFound as n
dim cntCustcodesNotFound as n
dim cntCustcodesEmpty as n


cntCustcodesFound = 0
cntCustcodesNotFound = 0
cntCustcodesEmpty = 0

'There are other means to speed up processing in a script such as yours.
'Using the optional table open mode parameter FILE_RW_EXCLUSIVE will help.

'You can also enclose the change section with the .batch_begin() and .batch_end() methods attached to
'the appropriate table pointers. See the documentation for specifics.

cust = table.open("customers",FILE_RW_SHARED)

'not used - query.filter = "RTRIM(customers.companynam) = RTRIM(warrreg.customer_i)"

warrreg = table.open("warrreg",FILE_RW_SHARED)
warrreg.fetch_first()
while .not. warrreg.fetch_eof()
warrregCustname = warrreg.customer_i
' this filter contains warrregCustname (defined below as warrregCustname = warrreg.customer_i).
' the query will result in just one record.
query.filter = "RTRIM(cust.companynam) = "+quote(warrregCustname)
query.options = "T"
cnt = cust.query_create()
warreg.change_begin()
if cnt.records_get() > 0
'warrreg.custcode = customers.custcode
warrreg.custcode = cust.custcode
cntCustcodesFound = cntCustcodesFound + 1
else
warrreg.custcode = "no match"
cntCustcodesNotFound = cntCustcodesNotFound + 1
end if
warreg.change_end()
warrreg.fetch_next()
end while

warrreg.close()
cust.close()
end

' make the msgbox at the end of the process
' also tell me how many custcodes found, not found, empty custcodes.
'msgbox("Notity","Done",1)
msgbox("Done"+ crlf()+ "cntCustcodesFound = "+ cntCustcodesFound + crlf()+ "cntCustcodesNot Found = " + cntCustcodesNotFound)

' Notes:
' ______________________________________________

'In the query version the line
'query.filter = "RTRIM(companynam) = "+quote(warrregCustname)
'restricts the selection of records to only those where the customer table companynam = warrregCustname. If the query finds a record ....
'In the index version the line
'if cust.fetch_find(warrregCustname) > 0
'looks up the warrregCustname in the index. If it is found the record pointer is positioned to the corresponding record in the table
' and the record number is returned.

' ____________________________

'Just make the first line of code

' debug(1)

' and run it.
'Then you can step through the script with F12 and you can enter expressions at the bottom to watch the values change. cust.Recno() and warrreg.recno()
'for record numbers. warrregCustname to see it change, etc.

'________________________________________

'Alpha spends very little effort telling you that it is doing what you told it to do. Alpha not responding is frequently encountered with lengthy processes.
'When the debug window appears you will see the code which you can setp through. At the bottom of the debug window there will be two boxes.
'One labeled expression and the other value. You can enter an expression and watch the value change as records are fetched by the code. After entering the first expression use the enter key to move down and create a new line, repeat as desired.
' http://wiki.alphasoftware.com/Script+Debugger

'can i display a Windows message box telling me processing is done?
'Portions of scripts are multi-threaded and you may get the done message before the script is actually finished but you can use the line
'msgbox("Done")

'Having a browse of a table open while processing is being done is sometimes permissible but hardly ever advisable. Doing so forces Alpha to be aware of changes
'that might take place in the browse and not devote full attention to the processing.

'_______________________________________
'
'Just make the first line of code
'
'debug(1)
'
'and run it. Then you can step through the script with F12 and you can enter expressions at the bottom to watch the values change. cust.Recno() and warrreg.recno() for record numbers. warrregCustname to see it change, etc. You can halt the script at any point you want.
'
'To stop at a certain record number automatically
'dim reccnt as N
'reccnt = 0
'while .not. warrreg.fetch_eof()
'reccnt = reccnt + 1
'if reccnt > 10
'cust.close()
'wqarrreg.close()
'exit while
'end if

'_____________________________

'When the debug window appears you will see the code which you can step through. At the bottom of the debug window there will be two boxes. One labeled expression and the other value. You can enter an expression and watch the value change as records are fetched by the code. After entering the first expression use the enter key to move down and create a new line, repeat as desired.
'http://wiki.alphasoftware.com/Script+Debugger
'rest of script

'__________________________________

'i thought the browse was necessary. this may be what overworked alpha.
'i may make the msgbox at the end of the process also tell me how many custcodes found, not found, empty custcodes.
'i know how to keep track of these counts.
'can i have multiple line message with CHR(13) being a carriage return?
'Crlf() shortcut for chr(13) + chr(10)

'____________________________________

'There are other means to speed up processing in a script such as yours.
'Using the optional table open mode parameter FILE_RW_EXCLUSIVE will help.
'You can also enclose the change section with the .batch_begin() and .batch_end() methods attached to
'the appropriate table pointers. See the documentation for specifics.