Calculated field error in purchase request module

A little gotcha that I came across awhile ago is there is an error in the Total Cost calculated field in the Purchase Requests module that forgets to take into account if a Purchasing Item is active or not. As many organisations prefer to use the InActive flag as opposed to allowing users to delete records, this can lead to erroneous reporting. Thankfully, it is easily fixed…

Open DB Admin and expand the Purchase Requests module. Right-click on the Total Cost field and select Modify Field from the popup menu. Change the SQL from:

(((select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 1) * (1+(ISNULL(BASE."TAX_%",0)*0.01) ))+(select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 0)+ISNULL(BASE."SHIPPING_COST",0))

to:

(((select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 1 and CHILD."_INACTIVE_:" = 0) * (1+(ISNULL(BASE."TAX_%",0)*0.01) ))+(select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 0 and CHILD."_INACTIVE_:" = 0)+ISNULL(BASE."SHIPPING_COST",0))

Save the field after testing it and you are good to go. Happy reporting.