I don't know if this would make much of a difference, but you might try
it to see.
CREATE VIEW vwTranDups (CustNum, tr_date, ch_code) +
AS SELECT custnum, tr_date, ch_code +
FROM tran_daily WHERE tr_type = 1 +
GROUP BY custnum, tr_date, ch_code +
HAVING COUNT(*) > 1
SELECT COUNT (*) INTO vzzz FROM vwTranDups
WHILE vzzz > 0 then
PAUSE 2 USING 'Check the next screen for duplicate charges. +
You may have duplicate charges for a patient. Highlight the +
duplicate charge and press F9 to Delete it.'=50 &vpopt
EDIT USING trancpa WHERE tr_type = 1 +
AND custnum = +
(SELECT custnum from vwtrandups +
WHERE tr_date = tran_daily.custnum +
AND ch_code = tran_daily.ch_code +
AND custnum = tran_daily.custnum )
SELECT COUNT (*) INTO vzzz FROM vwTranDups
ENDWHILE
-- You will have to test the correlated select clause - I might have
mistyped something.
Albert
Marc wrote:
Hi Albert
Here is that other block of code I talked about that should show the
Dups if
they were doubled entered into the Tran_daily table
This is why I think the code was run twice or the computer skipped a beat
or something. I would think 2 Delete Dups with Eqnull ON and this should
catch any dups in the Tran_daily file
Thanks Marc
SELECT (COUNT(custnum)) INTO vzz FROM tran_daily +
WHERE tr_type = 1 AND (CTXT(custnum)+CTXT(tr_date)+ch_code) IN +
(SELECT (CTXT(custnum)+CTXT(tr_date)+ch_code), COUNT(*) +
FROM tran_daily GROUP BY custnum,tr_date,ch_code +
HAVING COUNT (*) > 1)
IF vzz > 0 THEN
PAUSE 2 USING 'Check the next screen for duplicate charges. +
You may have duplicate charges for a patient. Highlight the +
duplicate charge and press F9 to Delete it.'=50 &vpopt
ENDIF
EDIT USING trancpa +
WHERE tr_type = 1 AND (CTXT(custnum)+CTXT(tr_date)+ch_code) IN +
(SELECT (CTXT(custnum)+CTXT(tr_date)+ch_code), COUNT(*) +
FROM tran_daily GROUP BY custnum,tr_date,ch_code +
HAVING COUNT (*) > 1) +
ORDER BY custnum,tr_date,ch_code +
CAPTION '*** Are these charges duplicates? ***'
----- Original Message ----- From: "Albert Berry"
<[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Tuesday, February 26, 2008 10:41 AM
Subject: [RBASE-L] - Re: How can make this code better ?
Marc, try adding a USING clause to the DELETE DUPLICATES command, and
specify the columns to be used for the duplicate check.
Marc wrote:
Hi
Out of 100 offices this has happened only about 6 times in a year.
I think it is from
computer hiccups but I am wanting to make my code as tight as possible.
Basically we put charges and payments in a Tran_daily file. At the
end of the day
if everything is correct we Close the day, do some updates, move the
charges and
payments to the Tran_Hist table and summaries the totals in a Recap
table with the
date on it, then delete the Tran_daily rows.
About 90% of the rows from the Tran_daily table got copied
(inserted) into the Tran_Hist
table twice for 2/8/08. This office has used my app for 10 years
and this is the first time
it has happened to them. I told them if the same code works 99% of
the time and one day
screws up it is not the code/RBase it is a computer quirk.
Below is a summary of the code, is there a better way to do this or
trap for errors?
Sorry this is so long
Marc
DELETE DUPLICATES FROM tran_daily --so this should have stopped
the dups in the Hist
SET VAR vdaycnt INTEGER = NULL
SELECT COUNT (*) INTO vdaycnt FROM tran_daily
SET VAR vhistcnt INTEGER = NULL
SELECT COUNT (*) INTO vhistcnt FROM tran_hist
-- copy tran_daily TO tran_hist
INSERT +
INTO tran_hist (custnum,date ......... +
................,modf4 FROM tran_daily
SET VAR vhistcnte INTEGER = NULL
SELECT COUNT (*) INTO vhistcnte FROM tran_hist
IF vhistcnte <> (.vdaycnt + .vhistcnt) THEN
PAUSE 1 USING 'Some data was not copied to the History file '
ENDIF
if this poped up they didn't remember it
*(insert RECAP FILE WITH TOTALS FOR DAY)
INSERT INTO recap (............ , tptpay,+
VALUES (.vdate, ................ '1',.vtvisa)
DELETE ROWS FROM tran_daily
SET VAR vrecapchar CURRENCY = 0.00
SET VAR vrecappay CURRENCY = 0.00
SET VAR vrecapchar CURRENCY = tcharges IN recap WHERE COUNT = LAST
SET VAR vrecappay CURRENCY = tpayment IN recap WHERE COUNT = LAST
IF tcharge = .vrecapchar AND totpay = .vrecappay THEN
PAUSE 1 USING 'Finished Recap Created' ICON confirm ....
ELSE
PAUSE 1 USING +
'Recap DID NOT match Check the Recap and use a Backup if
necessary' +
ICON SERIOUS BUTTON 'OK' OPTION BACK_COLOR 65535 | ...+
if this poped up they didn't remember it
ENDIF
RECALC VARIABLES
RETURN
