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!
Appreciate any advice,
Tim
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!
Code:
'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
Comment