Calc Field validates but data table doesn't display due to non-mandatory in view

Larry Beasley shared this problem ago

Resolved

This issue occurs when the table is not made mandatory. I'm clearly using it in a calculated field. When I checked "Mandatory" checkbox it worked.

Files:

The same problem 0

Replies (1)

Nathan Schroeder ●

Hi Larry,

Unfortunately this is expected functionality. If the table is not forced into the report's SQL in some way, a syntax error will be thrown.

Your other option is to include a field in this report that is from the table being referred to in your query, so that the table is automatically included in the FROM clause.

Bit of an explanation:

Standard calculated fields have a lot more functionality underlying them, which allows the report builder to say "okay, this function refers to these two fields, which means that these tables are needed so lets make sure that is reflected in the query"

Freehand is more open ended, so all that happens behind the scenes is:

"okay they gave me a statement that works so I am going to throw it into the current SQL query and assume they knew what they were doing enough that it will work on its own"

The potential complexity of freehand calc fields means that a lot could go wrong if we did try to parse out used tables, so I am doubtful this will change in the near future.

Let me know if this makes sense, and sorry for the troubles.

Nathan

Reply URL

Larry Beasley ●

Very good point indeed! As I was taking the screenshots and after I typed up this brief topic I remembered that you told me a while back to check mandatory for something unrelated. I gave it a try and on this issue and of course that "fixed" the issue. Looking at my screenshot, perhaps an idea could come from this. I use freehand sql for case statements but there are a lot of cases that I don't need to. In my screenshot I use ISNUMERIC and CAST functions. Is there anyway that the "Simple" formula type feature allow for scalar functions? I imagine that initially "msshipped" scalar functions only and perhaps a config to allow/disallow those and user defined functions as well. b2e385720fc2280eb7b8ab5e497602cc

URL

Nathan Schroeder ●

Hi Larry,

Sorry for the delay here. I think the best option to employ these, especially if this is a regular calculation is to create your own custom function in which you can specify exactly how the grouping/aggregation will work.

Is this an option here?

Nathan

URL

Larry Beasley ●

Unfortunately, this is not available to third party licensees. We have no access to the ROOT folder, etc. This makes it difficult to work with parameters, etc. For example, I cannot come close to pulling off something like: https://community.yellowfinbi.com/topic/aggregate-a-text-column. I'm going to have to use freehand SQL for now. Thanks!

URL

Nathan Schroeder ●

Yea, there is certainly a lot more freedom when you have your own instance to play around with :)

URL