Custom functions : referring to another table


#1

Hi,
I have developed a report which sources data from Opportunities, from Quotes (linked to those opportunities) and the Line Items Groups within the Quotes.

I understand that in the custom function I can refer to the current table using {t} and the current field by {t}.{f}, but how can I refer to a field from Opportunities in a function for a field taken from Line Item Groups?

I have tried to refer to for example ‘accounts.opportunities.amount’ to retrieve the entire opportunity value but that hasn’t returned anything.

Thanks


#2

I am not sure I understand why you would need to use custom function to get the opportunity amount. Since it is linked to quotes, you should be able to drag the amount field from the link.
In my example I start with account, I then dragged Proposal amount, then Opportunity amount.


#3

That was probably a poor example and I understand I can access the fields and drag and drop them into the report from different tables. What I meant was I wanted to use a custom function to perform a calculation by utilising two values sourced from the two different tables - one would be {t}.{f} and the other in a different table.

Specifically, I want to use a group item name (using it to represent a month value) and to add it to the close date - using date_add; to do that I’m trying a custom function with opportunity close date (my {t}.{f} in this example) and taking the other value from line_items.group name, and it’s how I refer to that field in the custom function is the question (ignoring for now having to convert / extract an integer from the string etc).

Do you know if there is a simple way to refer to the another field value if it’s in a different table (as in my example where one field is in opportunities and the other in line items?

Thanks again for your help.


#4

You may write a select query with joins if you like. Just a normal query using table and field names as if you were querying directly in your database. You may join on the {t}. {f} or {t}.nameofthefield.