Calculated fields and subquery confusion
12 August, 2015
Hello,
We run periodically into problems and confusion when it comes to calculated fields and subqueries. If we want to create calculated fields in the master query, that is based on contend from a subquery then depending on what you choose, the availability of fields to work with in the formular editor changes.
Is there a brief guideline what the rules are when it comes to calculated fields and subqueries. So far I think I identified the following.
1. You can only use fields that are defined as query fields, not the view fields.
2. If the field from the subquery is a calculated field, you can only use standard fields from the master query, not the calculated fields from the master query.
Is this correct and are there more "rules"?
Here is our current problem.
We calculate the revenue in the report as article_count * article_price and created a calculated field.
We created our masterquery with some filters and the subquery with only a subset of the filters.
Now we want to create a ratio between revenue_master_query/revenue_subquery!
That is not possible, since revenue_subquery becomes unavailable after selecting revenue_master_query in the formula editor and vice versa.
The only solution I can think of at the moment is to create the revenue formula on the view level instead, would that work?
Many thanks and best regards,
Ingo
We run periodically into problems and confusion when it comes to calculated fields and subqueries. If we want to create calculated fields in the master query, that is based on contend from a subquery then depending on what you choose, the availability of fields to work with in the formular editor changes.
Is there a brief guideline what the rules are when it comes to calculated fields and subqueries. So far I think I identified the following.
1. You can only use fields that are defined as query fields, not the view fields.
2. If the field from the subquery is a calculated field, you can only use standard fields from the master query, not the calculated fields from the master query.
Is this correct and are there more "rules"?
Here is our current problem.
We calculate the revenue in the report as article_count * article_price and created a calculated field.
We created our masterquery with some filters and the subquery with only a subset of the filters.
Now we want to create a ratio between revenue_master_query/revenue_subquery!
That is not possible, since revenue_subquery becomes unavailable after selecting revenue_master_query in the formula editor and vice versa.
The only solution I can think of at the moment is to create the revenue formula on the view level instead, would that work?
Many thanks and best regards,
Ingo
Hi Ingo,
Extremely sorry for the delayed response, this post has slipped through the cracks.
The good news is we are going to revamp our YF forum early next year, so this should not be a problem in the near future.
Your understanding of the calc fields is spot on!
-When building a calc field at the master query, you can use fields from the view, or sub-query, including calc fields created at the sub query. .
-When building a calc field at the sub query, you can only use fields from the view, not calc fields created on the master query.
In order to get the results you're after, you should be creating the calc field at the view level, then you can drag that view field into both the master & subquery, and add your filters.
I hope this covers everything you're after, and please let me know if you have any questions or issues with this.
Thanks,
David
Extremely sorry for the delayed response, this post has slipped through the cracks.
The good news is we are going to revamp our YF forum early next year, so this should not be a problem in the near future.
Your understanding of the calc fields is spot on!
-When building a calc field at the master query, you can use fields from the view, or sub-query, including calc fields created at the sub query. .
-When building a calc field at the sub query, you can only use fields from the view, not calc fields created on the master query.
In order to get the results you're after, you should be creating the calc field at the view level, then you can drag that view field into both the master & subquery, and add your filters.
I hope this covers everything you're after, and please let me know if you have any questions or issues with this.
Thanks,
David