Is askC suppoed to work inside a script?

[[email protected]]

Now that I have my script for sending accounts by email as an attached pdf working in Windows 10, I am trying to add a second but similar script because the adsl here often drops out before all 30 emails are sent and it's more than annoying for me as well as my customers to have to send the whole lot over and over until they are all sent successfully..
I was hoping to modify the script using AskC to find out the last successfully sent email, use that character string to look up the records until the last one sent was reached, and then start sending the remainder from there.
The table with the customer information is 'Customers2' and the field with the customer code in is a character string called 'cuscode'. They are 'numbers' from 001 to 030.
I can't get the askC part to work, it returns 'Invalid filter expression "last = "006"'. I suppose it must be a mismatch or askC doesn't work inside a script but I've been staring at it for hours and the help on askC I find very scarce.

'find next account to send by finding the last one actually sent
dim filter as c
dim order as c
dim options as c
dim description as c
dim show_all_flag as L
dim layout_name as c
dim layout_type as c
dim last as c
filter = "last = var->AskC_What_was_the_last_cuscode_sent"
order = "cuscode"
options = ""
description = "Saved Query: Last_account_sent"
show_all_flag = .f. 'user cannot turn off the query
layout_type = "browse" 'to open a form, set this to "form"
layout_name = "" 'This will open the default layout. You can also specify a named layout here

a5_open_layout(layout_type,layout_name,"account",filter,order,description,options,show_all_flag)
'should now know the last account sent as 'last'


parentform.commit()
tbl = table.current()
tbl.fetch_first()

while .not. tbl.fetch_eof()
query.filter = "cuscode = "+quote(alltrim(tbl.cuscode))
query.order = ""
'lookup("CUSTOMERS2.DBF", "CUSCODE = ", "") not got this far yet !


to_param = Lookup("Customers2","cuscode = "+quote(alltrim(tbl.cuscode)),"alltrim(email)")
if alltrim(to_param) > "" 'only execute this section if an email address is found
subject_param = "Monthly account from Laprima S.L."
message_param = "Your attached account covers the month of "+cmonth(date())+"."
'attachments_param = ""
'cc_param = a5_eval_expression("",local_variables())
'bcc_param = a5_eval_expression("[email protected]",local_variables())
if filefind.get(a5.Get_Path()+chr(92)+"Accounts "+cmonth(date()),FILE_FIND_DIRECTORY,"PN") = ""
dir_create(a5.Get_Path()+chr(92)+"Accounts "+cmonth(date()))
end if
:report.SaveAs("AccMonthly","PDF",query.filter,query.order,a5.get_path()+chr(92)+"Accounts "+cmonth(date())+chr(92)+"Acct "+alltrim(tbl.cuscode)+".pdf")
sleep(4)
'email_send(to_param,subject_param,message_param,a5.get_path()+chr(92)+"Accounts "+cmonth(date())+chr(92)+"Acct "+alltrim(tbl.cuscode)+".pdf","",bcc_param)
sleep(4)
end if 'end of section to create and send email if address found
tbl.fetch_next()
end while
fil.flush()
fil.close()

[Stan Mathews]

Re: Is askC suppoed to work inside a script?

If it does work, and I haven't used it in years, it would be

filter = "last = "+quote(var->AskC_What_was_the_last_cuscode_sent)

[[email protected]]

Re: Is askC suppoed to work inside a script?

Thanks Stan, that doesn't work, the error code says file not found. The delay in answering was I went to try it on the W7 machine but it doesn't work on that either. Can you think of another way to achieve this please?

[Stan Mathews]

Re: Is askC suppoed to work inside a script?

Instead of an ask variable I would display an xdialog or simply put a variable on a form and reference it. Define the variable for the form, make sure it is at least shared scope. Place the variable on the form. Change the line to

filter = "last = "+quote(var->form_variable_name)

[[email protected]]

Re: Is askC suppoed to work inside a script?

Thanks Stan, that will take me some time to assimilate but I'm on it.

[Stan Mathews]

Re: Is askC suppoed to work inside a script?

Of course I should have noted, enter the value desired in the form variable before running the code.

[MoGrace]

Re: Is askC suppoed to work inside a script?

Hi Trevor,
If you want to prompt the user, have a look at the ui_get_text() function

Example:
dim vLast as c = ""
vLast = ui_get_text("Last Customer Code","Enter the last customer code sent","","000")
if vLast = "" then
'user cancelled
end
end if

There is also a function named Last() so renaming your variable 'last' to 'vLast' before using it in a filter might be a good idea too.

[[email protected]]

Re: Is askC suppoed to work inside a script?

Hello again and thanks for your input, I can know get the inputted information (I went for the ui_get_text function) as vLast. I'm now having problems looking up the first cuscode and finding if it has reached the vLast value, here is what I've tried amongst many other tries;

'place lookup code here to get first cuscode
ultimate = Lookup("Customers2","cuscode = "+quote(alltrim(tbl.cuscode)),"alltrim(cuscode)")

if ultimate <> vLast
tbl.fetch_next()
end if

to_param = Lookup("Customers2","cuscode = "+quote(alltrim(tbl.cuscode)),"alltrim(email)")

if alltrim(to_param) > "" 'only execute this section if an email address is found
subject_param = "Monthly account from Laprima S.L."
message_param = "Your attached account covers the month of "+cmonth(date())+"."
'attachments_param = ""
'cc_param = a5_eval_expression("",local_variables())
'bcc_param = a5_eval_expression("[email protected]",local_variables())
if filefind.get(a5.Get_Path()+chr(92)+"Accounts "+cmonth(date()),FILE_FIND_DIRECTORY,"PN") = ""
dir_create(a5.Get_Path()+chr(92)+"Accounts "+cmonth(date()))

This doesn't throw up any error message and it goes on to make the directory and pdfs o.k. but doesn't send the emails. Do I have to use val(cuscode) and compare to val(vLast)?

[Stan Mathews]

Re: Is askC suppoed to work inside a script?

Your if...end if loop only executes once. I think you want something like

tbl_c = table.open("Customers2")
tbl_c.order("cuscode")
tbl_c.fetch_first()
while .not. tbl_c.fetch_eof()
	if tbl_c.cuscode < vlast
		tbl_c.fetch_next()
	end if
end while
'now on record of last customer code sent
'fetch one more time for first customer not sent
tbl_c.fetch_next()
while .not. tbl_c.fetch_eof()
 to_param = tbl_c.cuscode

 if alltrim(to_param) > "" 'only execute this section if an email address is found
 subject_param = "Monthly account from Laprima S.L."
 message_param = "Your attached account covers the month of "+cmonth(date())+"."
 'attachments_param = ""
 'cc_param = a5_eval_expression("",local_variables())
 'bcc_param = a5_eval_expression("[email protected]",local_variables())
 if filefind.get(a5.Get_Path()+chr(92)+"Accounts "+cmonth(date()),FILE_FIND_DIRECTORY,"PN") = ""
 dir_create(a5.Get_Path()+chr(92)+"Accounts "+cmonth(date()))
 
 'finish up your above sectionto send the emails
 tbl_c.fetch_next()
 
end while
tbl_c.close()

[Stan Mathews]

Re: Is askC suppoed to work inside a script?

You could simplify this immensely if you add a field to your table with whatever name you like, logical type. Might call it sent_file. Add a check to that field for the query, query.filter = "cuscode = "+quote(alltrim(tbl.cuscode)) .and. (.not. sent_file)"

Each time you start a billing cycle run an update (operation) to set the field to false.

Change

'email_send(to_param,subject_param,message_param,a5.get_path()+chr(92)+"Accounts "+cmonth(date())+chr(92)+"Acct "+alltrim(tbl.cuscode)+".pdf","",bcc_param)

to

rslt = email_send(to_param,subject_param,message_param,a5.get_path()+chr(92)+"Accounts "+cmonth(date())+chr(92)+"Acct "+alltrim(tbl.cuscode)+".pdf","",bcc_param)
'here add

if rslt = 1 'email_send() result for good send is 1
tbl.change_begin()
tbl.sent_file = .t.
tbl.change_end(.T.)
end if

[[email protected]]

Re: Is askC suppoed to work inside a script?

Thanks Stan, I can see how that would work. I'll start when I get time. I'll post a reply when done so anyone else may benefit from your efforts. Best regards, Trevor.

[[email protected]]

Re: Is askC suppoed to work inside a script?

Thank you Stan, it works and as you say much neater solution. Thanks yet again.

[[email protected]]

Re: Is askC suppoed to work inside a script?

I'm just trying to soak test this and if I 'press the button' all the accounts are sent. Then if I manually change just one T to F, simulating a halt or problem, it sends all of the accounts again. I was juggling with the query.filter line because the code editor says that there is an extra expression (") at the end of the line, but the error message says that it can't find the variable 'sent'. 'Sent' is what I named the extra field in the accounts table so I'm confused, that it's calling it a variable. If I take out the line query.filter = "cuscode = "+quote(alltrim(tbl.cuscode)) .and. (.not. sent_file)"
it works,but not after I've changed just one or two Ts for Fs. I.e. it will send all the accounts again, not just the ones that are logical F. Maybe an 'else' in the 'if - end if' loop?

[[email protected]]

Re: Is askC suppoed to work inside a script?

To continue, with query.filter = "cuscode = "+quote(alltrim(tbl.cuscode))' .and. (.not. sent) it just keeps sending all the accounts.
I've tried a fix with - if alltrim(to_param) > "" .or. (.not. (tbl.sent))'only execute this section if an email address is not found OR 'sent' is not logical true but without the previos line of code if fails (wrong argument) and with the first line of code here, it too sends all of the accounts again.

[Stan Mathews]

Re: Is askC suppoed to work inside a script?

What did you name the field for

if rslt = 1 'email_send() result for good send is 1
tbl.change_begin()
tbl.sent_file = .t.
tbl.change_end(.T.)
end if

Did you examine the table to see what is in this field?