i am looping through a table and where records meet a filter value i want to send an email. this is done in an a.5w page.
i need to get the unique id value from a field in a table to filter the rest of the data being obtained. i have guessed at what would achieve this but clearly it's incorrect. how would i go about this please?
i need to get the unique id value from a field in a table to filter the rest of the data being obtained. i have guessed at what would achieve this but clearly it's incorrect. how would i go about this please?
Code:
tbl = table.open("[PathAlias.ADB_Path]\treview2.dbf") filter1 = "r_adviser = 'Richard Harris' " filter2 = "r_done <> 'Yes' " filter_recs = "("+filter1+") .and. ("+filter2 +")" query.description = "SendEmail" query.order = "" query.filter = filter_recs query.options = "" qry = tbl.query_create() nrecs = qry.records_get() if nrecs>0 then tbl.fetch_first() while .NOT. tbl.fetch_eof() 'get client details from review table dim reviewid as C [COLOR="#FF0000"] reviewid = tbl.record_content_get("alltrim(reviewid)","reviewid","unique_key_value()")[/COLOR] varCaseref = lookup("[PathAlias.ADB_Path]\treview2.dbf","reviewid = "+quote(reviewid),"alltrim(caseref)") varProdid = lookup("[PathAlias.ADB_Path]\treview2.dbf","reviewid = "+quote(reviewid),"alltrim(prod_id)") varReview6Date = lookup("[PathAlias.ADB_Path]\treview2.dbf","reviewid = "+quote(reviewid),"alltrim(review_6mth)") varProdEndDate = lookup("[PathAlias.ADB_Path]\treview2.dbf","reviewid = "+quote(reviewid),"alltrim(prod_enddate)") varEmpName = lookup("[PathAlias.ADB_Path]\treview2.dbf","reviewid = "+quote(reviewid),"alltrim(r_adviser)") 'get client details from casefile table tbl_cf = table.open("[PathAlias.ADB_Path]\casefile.dbf") varClientName = lookup("[PathAlias.ADB_Path]\casefile.dbf","caseref = "+quote(varCaseref),"alltrim(clientfirstname)") varClientEmail = lookup("[PathAlias.ADB_Path]\casefile.dbf","caseref = "+quote(varCaseref),"alltrim(clientemail)") varAddress = lookup("[PathAlias.ADB_Path]\casefile.dbf","caseref = "+quote(varCaseref),"alltrim(newaddress)") varLocality = lookup("[PathAlias.ADB_Path]\casefile.dbf","caseref = "+quote(varCaseref),"alltrim(newlocality)") varTown = lookup("[PathAlias.ADB_Path]\casefile.dbf","caseref = "+quote(varCaseref),"alltrim(newtown)") varPostcode = lookup("[PathAlias.ADB_Path]\casefile.dbf","caseref = "+quote(varCaseref),"alltrim(newpostcode)") tbl_cf.close() 'get client details from product table tbl_prod = table.open("[PathAlias.ADB_Path]\prod_summary.dbf") varLender = lookup("[PathAlias.ADB_Path]\prod_summary.dbf","prod_id = "+quote(varProdid),"alltrim(prod_provider)") tbl_prod.close() 'send review email to each client if eval_valid("varClientEmail") if varClientEmail > "" then dim ps.Mode as c dim ps.Timeout as n = 5000 dim pm as P dim ps as P dim from as C = "****" dim subject as C = "Mortgage Review 6 Month Reminder." if (email_smtp_open(ps, "**", **, "**", "**")) then pm.to = varClientEmail pm.from = "*****" pm.from_alias = "***" pm.attachments = "" pm.subject = subject pm.message = "Dear " + varClientName + "," + crlf()+ crlf() pm.message = pm.message + "your mortgage review with " + varLender + " " + "is due to expire" + crlf()+ crlf() pm.message = pm.message + "Property Address" + varAddress + " " + crlf()+ crlf() pm.message = pm.message + "Yours sincerely, " + crlf()+ crlf() + varEmpName + " " + crlf()+ crlf() pm.message = pm.message + "Direct Mortgage Centre" email_smtp_send(pm,ps) end if email_smtp_close(ps) end if end if tbl.fetch_next() end while end if qry.close() tbl.close()
Comment