Announcement

**Garth Groft** · 04-03-2015, 04:46 PM

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.

Attached Files

custcode update stan2_fetch.txt (6.5 KB, 2 views)

**Stan Mathews** · 04-03-2015, 04:57 PM

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

Sorry, I got confused. Let me take a look..

Do you mean that the counts aren't displayed? If so move the

end

program line to after the msgbox() line. All code stops when an end statement is encountered.

**Garth Groft** · 04-03-2015, 05:14 PM

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

it stops at the line of code:

cnt = cust.query_create()

no such field

now i commented out all the new lines and moved the msgbox up before the end. still gives the same error. i also tried using your original code in a new script and get the same error.

' 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()
msgbox("Notity","Done",1)
end

**Stan Mathews** · 04-03-2015, 05:19 PM

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

Ok, the line cust.query_create() uses the values created by

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

Did you happen to change the field name of companynam? If so you must make a matching change to the filter line.

**Garth Groft** · 04-03-2015, 05:28 PM

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

no, the field name of companynam was not changed.

i zipped the two tables (attached).

i assume Alpha is not backing up the various changes to the script.

Attached Files

warrreg.zip (3.04 MB, 7 views)

**MoGrace** · 04-03-2015, 08:50 PM

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

Hi Garth,
I took a look at the tables you zipped - you have to include the customers.fpt file for the memo field or Alpha can't read the table. I used DBFplus - Free to export the data into Excel so I could import it into Alpha to create a new customers table.

You will however have a hard time matching the warrreg table to your customers when 518 warreg records have blank customer_i fields and 27 have blank serial_no fields; some others were just blank or with dates only.

I copied the CUSTCODE and CUSTOMER_I fields from warreg to a new table warr_cust and deleted the duplicates and blanks. That at least narrowed down the comparison to only 688 warr_cust records to 446 customers records (I also removed 6 CUST_ID duplicates from the customers table). Now browses for the 2 tables can be put side by side to see what else needs to be changed before you can post your CUSTCODE. I would start with the customers table and fix that so the CUSTCODE field is unique as well as remove the duplicate records with spelling differences in COMPANY_NAM.

A third table might be needed to add the customer_i names together with the company_nam records so you can sort them and determine which ones are truly new customers. You could create new custcodes and customerid for them and then THAT table could be posted to the warrreg table. I would add a field to this new table to hold the original customer_i data that you will NOT change so you can match all 688 records back to warr_cust and then warr_cust to warr_reg. I would also add a field to warrreg to hold the corrected company_nam. When you are done the old field can be renamed and the new one changed to customer_i.

Hope that all makes some sense - if not let me know.

**Stan Mathews** · 04-03-2015, 09:54 PM

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

Change the script to

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

cust.companynam is not the field name. customers.companynam is allowable but not necessary.

And

warrreg.change_begin()

warrreg.change_end()

Three r's not two.

Also change to FILE_RW_EXCLUSIVE. Shared is effectively the same as having the browse open. It means watch out for whatever else might happen.

**Stan Mathews** · 04-03-2015, 10:51 PM

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

This version completes in about ten seconds.

Code:

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_EXCLUSIVE)
cust.index_primary_put("cmpnynm")

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

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

warrreg.close()
cust.close()
end

**Garth Groft** · 04-03-2015, 11:07 PM

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

thanks to you both.

yes these are the same changes made early on. i must have inadvertently reverted the code back to the original version.

there is still something else broken. processing goes further, stopping at:

if cnt.records_get() > 0

the error is "incorrect data type" (image attached)

i also added the customer memo file to the zip file for the two tables. the tables are usable as they re for testing, but as MoGrace pointed out, a clean up is needed before they will be usable for "production".

i was ask to run the script on the existing tables, then do the clean up and run it again. all the steps MoGrace identified must be done.

you can see from the warrReg.custcode contents that the script actually worked (aside from misspelled campanynam, empty custcodes and duplicate customers). it will work again soon as the script is fully restored.

i am not fully understanding creating new scripts and where they are stored. i am also saving then in text files outside of Alpha.

Attached Files

**MoGrace** · 04-04-2015, 01:56 AM

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

At the control panel, on the code page, selecting new will prompt to create a global script or function and will save it there. You can then call those scripts and functions from a button on a form, another script, or from the interactive window. You don't need to save them as text files as they are then part of the database library files.

**Stan Mathews** · 04-04-2015, 12:46 PM

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

Garth,

It is often tempting to dive in and get to work but it seems you would benefit from spending some time with the tutorials in the built in documentation or the wiki.

http://wiki.alphasoftware.com/HomePage

**Garth Groft** · 04-04-2015, 03:31 PM

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

thanks, i appreciate your working on this so late into the evening and holiday weekend.

i have been looking around the help a lot for tips and use the tutorial as well. sometimes i try to use a feature like the script "Save As" button and the dialog box does not make sense.

i see the changes you just made, such as,

cust.index_primary_put("cmpnynm"). i am not sure i could have found this on my own.

i was able to run your script, but i had to used shared files because i am opening the tables within my web app. it still takes only 10 seconds.

i added my expanded msgbox:
msgbox("Notify","WarrReg.Custcode field update complete"+ crlf()+ "cntCustcodesFound = "+ cntCustcodesFound + crlf()+ "cntCustcodesNot Found = " + cntCustcodesNotFound,0)
and this is working too. i looked up the msgbox() syntax and used the interactive window to find 0 yields "OK button".
the Alpha books i have do not even mention msgbox. they cover the Web app equivalent.

i added my record counts for:

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

cntCustcodesFound = 0
cntCustcodesNotFound = 0
cntCustcodesEmpty = 0
cntEmptyCustcode = 0

this also was successful.

then i tried adding a record count for empty customers.custcode field.
this gives an error "missing or invalid keyword" on the if statement.

if LEN(TRIM(cust.custcode)) = 0
cntCustcodesEmpty = cntCustcodesEmpty + 1
endif

Garth

**Garth Groft** · 04-04-2015, 03:34 PM

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

robin, thanks. i will explore your tips. i do prefer a global script for this situation..

**Garth Groft** · 04-04-2015, 03:47 PM

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

Robin,

I inherited this project from another developer. I was asked to develop a web app which i did using Alpha Anywhere. as we approached beta testing i was presented with a new design for the Web app. This is very do-able. However, the changes necessitate structure and data changes to the WarrReg and Customers tables and to table maintenance app developed in an Alpha Version 10 desktop app by the prior developer. As you pointed out, a serious clean up is in order especially for the associated Web app to work. i will share your suggestions with the client. The client wants any table changes to be done by them, even the removal of dups. So the clean up will be a shared project and not as straight forward as your own cleanup.
Garth

**Stan Mathews** · 04-04-2015, 07:56 PM

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

Not endif, two words, end if.

Announcement

The Alpha Software Forum Participation Guidelines

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment