ShowAll Questionssorted byDate Posted
Bambino

# Custom Summary Formula question

So, I am running a custom report on some custom objects that I made. The first object is Patient-Item, this object is for a purchase of an item by a patient. It contains the Patient ID, Item ID, Quantity, and Date Purchased. The second object is Item, which contains Item ID, Description, and Unit Cost.

For my Custom Summary Formula, I want to display the Total Cost by multiplying the Patient-Item: Quantity by the Item: Unit Cost.

So far I have come up with this formula:

( PAT_ITEM__c.PI_QUANTITY__c:SUM )   *  (FK_Item__c.ITEM_UNIT_COST__c:SUM)

This formula will work when I summarize the information by Patient-Item: Patient-Item ID and Item ID: Item ID.

But when I summarize the information by Patient ID and Patient-Item: Patient-Item ID, it gets all screwy. It shows the correct amount for each individual patient-item, but the formula is incorrect for an individual patient's total-cost on all item purchases.

For example, one patient bought 2 items at \$2.00 each and 1 item at \$7.99, and the total given to me is \$29.97. I understand that this is correct according to my formula, but what I want it to show is ((2*\$2.00)+(1*\$7.99)), which is \$11.99.

But for some reason, another patient bought 1 item at \$25.00 and then another instance of 4 items at \$25.00 each, and the total given to me was \$125.00, even though the formula should have come up with ((1+4)*(\$25.00+\$25.00)) = 5 * \$50.00 = \$250.00

So I am thinking that the formula I am looking for looks something like this:

(( PAT_ITEM__c.PI_QUANTITY__c )   *  (FK_Item__c.ITEM_UNIT_COST__c)):SUM

But this gives me several error messages,

First: "extra ":""

If I remove the ":", then I get: "extra SUM"

If I remove the "SUM", then I get: "PAT_ITEM__c.PI_QUANTITY__c does not exist. Check Spelling."

I would like to be able to summarize my information by Patient ID and Patient-Item: Patient-Item ID so that users can find a Total Cost for several Patient-Items being purchased by the same Patient, and if possible on the same date or within a short period of time (which I have defined so far as Today in the Time Frame box on the Run Report screen).

So I guess I was just wondering if someone could help me out with my formula syntax. I've checked the Tips and I can't seem to find anything that makes sense.

Thanks, and I hope this post wasn't too long.

Lori_

I'm confused by your second example:

But for some reason, another patient bought 1 item at \$25.00 and then another instance of 4 items at \$25.00 each, and the total given to me was \$125.00, even though the formula should have come up with ((1+4)*(\$25.00+\$25.00)) = 5 * \$50.00 = \$250.00

\$25+\$25+\$25+\$25+\$25=\$125, not \$250

Shouldn't the formula be?

(PAT_ITEM__c.PI_QUANTITY__c  *  FK_Item__c.ITEM_UNIT_COST__c):SUM

Just a thought