Ability to change field Data Types in View

Ashleigh Gray shared this idea 3 years ago
Idea Logged

Hi,


I was trying to append a subquery to a report with two different views by joining on the date field, but Yellowfin said the data in both fields had to be of the same type. The date field in the second view was correctly identified as a 'date' data type, but in my first view it has been picked up as a varchar. Viewing the data in the database I can see that both fields have the 'date' data type, but I can't get Yellowfin to pick up the change in data type. I can only assume it was varchar when I first made the view, as creating a new view on the same table forces Yellowfin to correctly identify this field as a date. Although I could make the view again, I have about 40 calculated measures in it so would rather not rebuild it again. I've restarted Yellowfin and refreshed my table but can't seem to get the change to go through.


Any help would be greatly appreciated!


Ashleigh

Comments (4)

photo
1

Hi Ashleigh,


Once a view has been setup, Yellowfin prefers that the underlying structure of the view remain unchanged, so that content can be created consistently on top of it. To adjust this field after the fact we have two options:


  1. Create a new view and then import the old content on top of the new. (you have mentioned this is not ideal)
  2. Adjust the underlying field's value within the configuration database.


For the second option, we will need to directly update this column's definition which can be found in the "reportfieldtemplate" table of the configuration database that you specified on initial installation. If you have not done a large amount of work with this field, then switching it should be relatively simple. However if this field is used in reports, or in advanced calculations, you will most likely receive errors within those reports after switching the value.


As with any changes to the configuration database, always back up your database prior to making changes so that you can roll back if something goes wrong. I have not had an opportunity to thoroughly test this yet, but will be able to in the next few hours.


Please let me know your thoughts on this.


Regards,

Nathan

photo
1

Hi Nathan,


I managed to work around it by exporting the view, changing the field type to DATE, importing it as a new view, then importing the old reports to point to the new view. Not the neatest solution but as a one off to fix my problem it isn't too bad. Thanks for your help!


Ashleigh

photo
1

Hi Ashleigh,


Glad to hear you found something that works, and no problem! I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan

photo
1

Nathan,

Why should I have to work around this..? Shouldn't I be able to change this? If not, it needs to be an idea. I have had to do this 3 times, just this morning in order for my sub-queries to join properly. AND, I am working in version 9 (by now, I would have thought this would be fixed).

I also tried to "convert" the field in the view to text and then to numeric. This changed the field type, but still would not join (my assumption is because it wasn't imported this way).


I should have the permission, at an admin level, to change the structure of the view.


Thanks,

Zack

photo
1

Hi Zack, just letting you know Nathan is no longer working in support, though I understand your request and we will get back to you on this.


Thanks,

David

photo
1

Hi Zack,

I've just gone through some testing and can confirm that you cannot join mismatched data types in sub-queries and at this time, field types in views appear to be fixed. The view needs to be exported and rebuilt in order to have reports make use of changes to data types.

It is a little strange that even when using a converted data type, they cannot be joined with sub-queries. As referenced in Nathan's earlier response, one current possibility would be changing the data type from the config db, but this is of course problematic if this field is used in reports, or in advanced calculations, as you would most likely receive errors within those reports after switching the value.

What I've done is created an internal enhancement request for this and set it to Highest priority. I'll provide an update to you regarding this as soon as able. I should have something soon given the priority.

Regards,

Mike

photo
1

Mike,

Awesome! Thank you. I am glad to see it is not something specific to me! ;0)


-Zack

photo
1

Hi Zack,

You're welcome. Indeed it's not. It's just one of those purposeful design choices that should be revisited and reconsidered!

Regards,

Mike

photo