Link on view field: Calculated Field

Dean Flinter shared this problem 2 months ago
Resolved

Hi,

Was trying out the new link on view field functionality for subqueries (https://wiki.yellowfinbi.com/display/yfcurrent/Sub+Queries#SubQueries-linkingviewfields) and it appears it does not work if the join field is a calculated field.

Using the example in the link above, in the subquery, if you create a calculated field that is MIN(Age At Camp) and try to join on that, you get an error when trying to run the report. In my actual use case I couldn't see any query hitting the DB and when it fails, if you select "View SQL", the pop up is blank

I had to get around it by creating a view with a virtual table that returned the MIN(ID) I was looking for

We're using v9.7.1


Thanks

Dean

Comments (9)

photo
1

Hey Dean,

I hope you are well!


I will check this out on my end / and also check this with a consultant to confirm the expected behaviour as I am not 100% sure myself

I will update you once I know more - I hope thats ok :)


Best Wishes,

Lesley

photo
1

Hey Dean,

I hope you are well :)


Just to let you know, I have had a response from the consultants on this, and they are in a similar position as I am - Since this is a new feature, we cannot confirm if this is a limitation or not.

So we are going to do some testing on our end & if we see the same results, we will raise an idea with the development team on this if you would like ?


Let me know :)

Best Wishes,

Lesley

photo
1

Hey Lesley,

Yeah sounds good to me.


Thanks

Dean

photo
1

Hey Dean,

I hope you are well :)


I have tested this, and I have replicated the behaviour when creating a calculated field at the report level > the SQL is blank

However, I also tested by creating the same calculated field at the View level, and this worked as expected when joining to the calc field from the view


Would this option work for you, instead of creating a virtual table?


Best Wishes,

Lesley

photo
1

Hey Dean,

I hope you are well & had a lovely weekend :)


I just wanted to check in here to see if you had received my last response?

And if using a View calc field worked in your case?


Best Wishes,

Lesley

photo
1

Hi Lesley,

Apologies, was off the end of last week and simply forgot to get back to you!

In this particular case a I didn't think a view level calc field would work for me as I was effectively trying to get first purchase ID for every customer, for a particular promotion. I suppose a very bespoke field could do it but it would then mean I'd need to do it every time, which I kind of did in the end I suppose but at that stage I was against the clock!

Ideally I'd want to be able to generate this on the fly using the logic of the subquery to narrow down what I needed.

Based on how the application behaves when you try this, I would suggest this is more of a bug rather than a missing feature?


Thanks

Dean

photo
1

Hey Dean,

Not a problem ! I hope you had a great time off :)


I completely get that and it makes sense to want to add it in when building a report.

Unfortunately, Since it is a new feature and hasn't worked any differently, I wont be able to raise as a defect. For me to raise a a defect with the team I have to first prove it worked in a previous build and that its not showing the same behaviour in the current build.


So, I will raise this as an idea but I will be sure to mention to the team that the behaviour is unusual and seems more like a bug

I hope this is ok!


I will let you know once I have created the Idea Post & raised with the dev team


Best Wishes,

Lesley

photo
1

Hey Dean :)


I have now created the Idea Post and also raised with the dev team

You will find the Idea post linked here < which is where all updates regarding the feature will be posted


I will go ahead and mark this ticket as complete since the updates regarding the idea will be in the idea post....however, if you do have any further questions, please feel free to let me know! I will be happy to help :)


Best Wishes,

Lesley

photo
1

Thanks Lesley!