PDA

View Full Version : Script Misbehaving in V9


ABC123

Tim Sutherland
03-29-2008, 10:18 AM
I've got the following script attached to a button on a form that searches for invoices that are ready to be created. A single customer may have 30 products that are flagged to be invoiced and they should just appear in the result dialog box once. The user then may click on that customer and create one invoice for all the products that match the search criteria.

This script worked fine in V8. In V9 it displays the customer name once for each product that is ready to be invoiced.

It would be really difficult to create a test app to be able to show how this functions so I am going to include the code along with a couple of screen shots hoping someone can see what I am overlooking.

In the attached picture (v9) Smith & Johnson should only be displayed once in the browse).

Thanks!



'Date Created: 20-Oct-2005 05:21:01 PM
'Last Updated: 23-Oct-2005 10:22:23 PM
'Created By :
'Updated By :

'Prompt for password and store result in a variable called 'pw'.
'DIM SHARED pw AS C

'Enter_pswd:
'pw = ui_get_password("Password Required","Enter Password:","********","********")
'IF pw = ""
' END'
'END IF
'IF pw <> "123"
' msg = "You have entered an incorrect password." +crlf(2)+ "Please try again or click 'Cancel' to quit."
' ui_msg_box( "Incorrect Password", msg, 16 )
' GOTO Enter_pswd
'END IF

'------------------------------------------------------------------------------------
'Get list of all Billto Names to with products that match the filter criteria.
'------------------------------------------------------------------------------------
'Get list of IDs from products table.
idlist = table.external_record_content_get( "products", "Billto_Id", "Billto_Id", "Completed .and. .not. Done .and. unique_key_value()" )
IF idlist = ""
ui_msg_box( "NOTHING TO BE INVOICED", "No products' to be invoiced.", 16 )
END'
END IF
'Based on IDList, get names.
inv_names = table.external_record_content_get("billto","Name + space(50) + ' -- ' + Billto_id","Name","Billto_Id$\"" + idlist + "\"")

'Show them in xdialog so user can select them.
'chcs = inv_names
dlg_text = <<%dlg%
{ysize=.3}{units=F}{font=font_face,fsize,n}{sp};
{font=font_face,fsize,b}{'Select client(s) to be billed:}{font=font_face,fsize,n};
{justify=center}<Select All> <Clear All>;
{justify=left}
[%m%.50,15chcs^#inv_names];
{line=1,0};
<*15OK> <15Cancel>
%dlg%

dlg_text = xd_set_fonts( dlg_text, "A", 9 )

DIM dlg_result as C
dlg_result = ui_dlg_box( "CLIENT NAMES", dlg_text, <<%code%
IF a_dlg_button = "Clear All"
chcs = ""
a_dlg_button = ""
ELSEIF a_dlg_button = "Select All"
chcs = inv_names
a_dlg_button = ""
END IF
IF a_dlg_button = "OK"
IF chcs = ""
ui_msg_box( "SELECTION REQUIRED", "Please select names to be invoiced or press 'Cancel' to quit.", 48 )
a_dlg_button = ""
END IF
END IF
%code%)

IF dlg_result <> "OK"
END'
END IF

'Get last invoice number. (Compare both tables JIC)
tpi = table.open( "Invoices" )
query.filter = ""
query.order = "val(padl(invoice_no,8,\"0\"))"
query.options = ""
tpi.query_create()
tpi.fetch_last()
inv_inum = alltrim( tpi.Invoice_no )
tpm = table.open( "Menu" )
tpm.fetch_goto(1) 'JIC
Inum = tpm.invnum 'Current invoice number - numeric
IF val(inv_inum) <> Inum
msg = "The last invoice number in the Invoice table, " + inv_inum + ", does not match "
msg = msg + "the last invoice number shown in the Menu table: " + ltrim(str(Inum)) + crlf(2)
msg = msg + "If you continue, the number " + Inum + " will be used as the last number." + crlf(2)
msg = msg + "CONTINUE?"
mresp = ui_msg_box( "WARNING", msg, 32+ui_yes_no )
IF mresp = ui_no_selected
tpi.close()
tpm.close()
END'
END IF
END IF

tps = table.open("products")
tps.index_primary_put("Billto_Id")

TotInv = 0
TotInv_amt = 0
i = 0

'Build invoices for each Billto_ID selected.
lcnt = line_count( chcs )
FOR qx = 1 to lcnt
this_line = word( chcs, qx, crlf() )
this_name = alltrim(word( this_line, 1, " -- " ))
this_id = alltrim(word( this_line, 2, " -- " ))
Inum = Inum + 1 'Get NEXT inv num
Inumc = padl(ltrim(str(Inum)),8,"0") 'Convert to character padded left with "0"
TotFee = 0 'Total Fee for CURRENT Invoice record
tps.fetch_find( this_id )
WHILE tps.Billto_id = this_id .and. .not. tps.fetch_eof()
IF tps.Completed .and. .not. tps.Done
'********* Insert invoice no into COMPLETED products record ************
TotFee = TotFee + tps.Fee
tps.change_begin()
tps.invoice_no = Inumc
tps.done = .T.
tps.change_end( .T. )
END IF
tps.fetch_next()
END WHILE

'************* Create New Invoice
'Fill in NEW invoice fields
tpi.enter_begin()
tpi.billto_id = this_id
tpi.invoice_no = inumc
tpi.invoice_date = date()
tpi.status = "Open"
tpi.invoiced = .F. 'T = invoice has been sent to the customer
tpi.total_fee = TotFee
tpi.total_paid = 0
tpi.Billto_name = this_name
tpi.enter_end( .T. )
totinv = totinv + 1
TotInv_amt = TotInv_amt + TotFee

'Update the invoice number in Menu after each invoice in case the routine crashes for some reason.
tpm.change_begin()
tpm.invnum = inum
tpm.change_end( .t. )

statusbar.percent( qx, lcnt ) 'no easy "nrecs" when selecting multiple bill-to IDs.
NEXT

Code = ui_ok + ui_information_symbol
msg = "Created " + ltrim(str( totinv, 6 )) + " NEW invoices for a total of $" + ltrim(str(TotInv_amt,10,2,",")) + "."
response = ui_msg_box( "Invoicing Complete", msg, Code )
IF response = ui_no_selected
END'
END IF

tpm.close()
tpi.close()
tps.close()

'Close current form or browse window.
IF is_object( parentform.this ) 'Just to make dev work easier.
parentform.close()
END IF

END'

FUNCTION XD_set_fonts as C ( dlg_text as C, font_face="Arial" as C, fsize=9 as N )
DIM GLOBAL g_std_font_face as C
DIM GLOBAL g_std_font_size as N
IF g_std_font_face <> ""
font_face = g_std_font_face
END IF
IF g_std_font_size <> 0
fsize = g_std_font_size
END IF
SELECT
CASE font_face = "" .or. font_face = "A"
font_face = "Arial"
CASE font_face = "MSS" .or. font_face = "A"
font_face = "MS Sans Serif"
CASE font_face = "V" .or. font_face = "A"
font_face = "Verdana"
CASE font_face = "T" .or. font_face = "A"
font_face = "Tahoma"
CASE font_face = "TR" .or. font_face = "A"
font_face = "Times Roman"
CASE font_face = "C" .or. font_face = "A"
font_face = "Courier"
END SELECT

dlg_text = stritran( dlg_text, "font_face", font_face )
FOR qx = 10 to 1 step -1
eval( "fsize" + ltrim( str( qx ) ) ) = eval( "ltrim(str(fsize+" + ltrim( str( qx ) ) + "))" )
dlg_text = eval( "stritran(dlg_text, \"fsize" + ltrim( str( qx ) ) + "\", fsize" + ltrim( str( qx ) ) + ")" )
NEXT
XD_set_fonts = stritran( dlg_text, "fsize", ltrim( str( fsize ) ) )

END FUNCTION



Appreciate any advice,
Tim

NoeticCC
03-29-2008, 10:35 AM
What happens if you execute the same query/filter directly on the relevant table(s)? Although I'm sure I saw another post where text in an Xdialog was repeated inexplicably...

Tim Sutherland
03-29-2008, 12:01 PM
Forgot to mention - this behaved normally in the trial version of V9 (1369-3159)

Selwyn Rabins
03-29-2008, 01:52 PM
I don't see how it is possible that your code could ever have worked in any version of a5, because your code that does the query to populate the list in the dialog does not specify unique values. Your code is:


inv_names = table.external_record_content_get("billto","Name + space(50) + ' -- ' + Billto_id","Name","Billto_Id$\"" + idlist + "\"")

Does the 'billto' table have duplictate names in it? I presume it does.

Your filter expression does not specify that you want unique records only.

If you want unique values you must use the unique_key_value() function in your filter rexpression.

For example, the following can be tested in Alphasports, Interactive window:



?table.external_record_content_get("customer","bill_city","bill_city","left(bill_city,1) < 'd' .and. unique_key_value()")

If you remove the '.and. uniuqe_key_value()' from the filter, you will see duplicate city names.

Tom Cone Jr
03-29-2008, 01:54 PM
Tim, without sample data our hands are pretty well tied.

Using AlphaSports I was able to determine that the unique_key_value() function seems to be working ok.

Using build 1391_3165 I see this in the interactive window, when building a list of invoice numbers that contain at least one item record with an extension value greater than $20.00. Since many invoice have multiple records with item extensions greater than $20, I would expect to see duplicate invoice numbers in the list if the function was broken. I don't. This suggests something else is going on at your location.


my_list = table.external_record_content_get("invoice_items","invoice_number","Invoice_number","Extension > 20 .and. unique_key_value()")
?my_list
= 000001
000002
000003
000004
000005
000006
000007
000008
000009
000010
000011
000013
000014
000015
000016
000017
000018
000019
000020
000021
000023
000024
000025
000026
000027
000028
000029
000030