Need Help with Script Which Updates Field in Table Based on Matching Parent Table Record - New Post
' 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.
' 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.
Comment