Show quotes or estimates that have NOT been converted to a bill.
Conversions are only recorded from v7.1.1
Create as export spreadsheet and use query below to export to csv
Conversions are only recorded from v7.1.1
Create as export spreadsheet and use query below to export to csv
select BM."Ref_Number" "Transaction Num",
case when BM."Type" = 7 then 'Quote'
else 'Estimate' end "Bill Type",
CM."First_Name" "First Name",
CM."Last_Name" "Last Name",
(select LIST(DISTINCT AM."Name")
from "Bill_Items" BI
join "Animal_Main" AM on AM."Ref_Number" = BI."Itm_Sub"
where BI."Bil_Number" = BM."Ref_Number") "Patients",
BM."Total" "Total",
BM."Dis_Date" "Creation Date",
SM."Id" "Staff"
from "Bill_Main" BM
join "Client_Main" CM on CM."Ref_Number" = BM."Cln_Number"
join "Staff_Main" SM on SM."Ref_Number" = BM."Cur_Staff"
where BM."Dis_Date" between :Datelo/*Created From*/ and :Datehi/*To*/
and BM."Type" in (7,8)
and "InBitValue" (512, "Bill_Prop") = 'N'
and BM."Acc_State" <> 59
case when BM."Type" = 7 then 'Quote'
else 'Estimate' end "Bill Type",
CM."First_Name" "First Name",
CM."Last_Name" "Last Name",
(select LIST(DISTINCT AM."Name")
from "Bill_Items" BI
join "Animal_Main" AM on AM."Ref_Number" = BI."Itm_Sub"
where BI."Bil_Number" = BM."Ref_Number") "Patients",
BM."Total" "Total",
BM."Dis_Date" "Creation Date",
SM."Id" "Staff"
from "Bill_Main" BM
join "Client_Main" CM on CM."Ref_Number" = BM."Cln_Number"
join "Staff_Main" SM on SM."Ref_Number" = BM."Cur_Staff"
where BM."Dis_Date" between :Datelo/*Created From*/ and :Datehi/*To*/
and BM."Type" in (7,8)
and "InBitValue" (512, "Bill_Prop") = 'N'
and BM."Acc_State" <> 59