7 Replies Latest reply on Apr 27, 2014 3:19 PM by madmike6537

# Calculation to get data from a specific related field

Hello there,

I am trying to create a calculation that will get data from a specific table::field, and use that data for my caculation result.

Basically, I am billing for customer equipment, but I want to bill not only for the cost of the equipment, but times how many days its been there. So I have a billing line item record - and I have a related peice of customer equipment:

Billing_Line_Item::Equipment_KF >----- Customer_Equipment::KP

Inside of the Customer_Equipment table is a field called number of days (the number of days the equipment was at the customer's location). I want to get that data, and multiply it times my billing item cost.

First I thought I could just do this:

Total =

Billing_Line_Item::Cost * Customer_Equipment::Num_Of_Days

But it turns out, that when you do this (from what I read), it just grabs the first record in that table, not a specifically related record. But I have both the record number of the equipment, as well as the primary key, so now I am trying to do this:

Total =

Billing_Line_Item::Cost * (GetNthRecord(Customer_Equipment::Days_At_Location; record#here))

But its not working.

What am I missing? Thanks and I hope that wasnt too confusing...

• ###### 1. Re: Calculation to get data from a specific related field

But it turns out, that when you do this (from what I read), it just grabs the first record in that table, not a specifically related record

Billing_Line_Item::Equipment_KF  >----- Customer_Equipment::KP

As you can see from your cardinality icons, each line item record only has one (1) related CustomerEquipment record, so the first one is also the only one.

Note that, if NumOfDays applies to all line items, you could (in CustomerEquipment) write Sum ( Billing_Line_Item::Cost ) * Num_Of_Days

• ###### 2. Re: Calculation to get data from a specific related field

Purely from a performance point of view you may want to think of bringing over the # of days to the Billing Line Item table.  That would allow the calc to be stored and not unstored.

If you want to do reports on billing that will be MUCH faster than having a bunch of unstored calcs evaluate on each report run.

1 of 1 people found this helpful
• ###### 3. Re: Calculation to get data from a specific related field

Thanks Erolst,

I did try the first method with no success, but I will try again - I may have overlooked something. I didnt think about the fact that there is really only 1 record, not all of them. Thanks for clarifying that.

NumOfDays is really only applicable to specific billing items that involve billing for equipment. There are a lot of other billing items that dont need NumOfDays for their total. Thanks for your response, going to try this again and report back.

• ###### 4. Re: Calculation to get data from a specific related field

Thats a good point and thanks for bringing that up. This will be used in reports so this is something I will have to consider. I assume to do this I would just make a NumOfDays calculation in the billing item table and make it equal to the related NumOfDays field in the equipment screen then?

• ###### 5. Re: Calculation to get data from a specific related field

No.  Stay away from calculations.  They have their place but you have to think carefully what fields you make calculated and which ones you don't.

The process of adding a billing item and selecting an event can be entirely scripted so you can bring data over by script and store it statically.  Calculations tend to be over-used and will almost always bite you in the rear-end when it comes to perforrmance.

1 of 1 people found this helpful
• ###### 6. Re: Calculation to get data from a specific related field

Wim, in this situation, would an auto-enter calc set to replace field contents do the trick of updating whenever the related field changes, but would be stored for better performance?

Thanks for all that you contribute!

-Shawn

• ###### 7. Re: Calculation to get data from a specific related field

This first method works - I just had the wrong field related - overlooked that error, thanks for the help!