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