• Bambino
  • NEWBIE
  • 0 Points
  • Member since 2010

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 0
    Replies

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.