Formula Field as a Cascading Search field

[BeffreyJezos]

I want to use one of the formula fields in my table as a search field for my tabular report page. I can include it to the search fields but when I changed its form element to any cascading element, it wouldn't allow me to use it for the "Field for value". It isn't available in the options to select from.

I also tried using a Virtual field and same thing happened. The formula field is just a concatenation of 2 text(255) fields. I don't want both of those fields in the table since two columns takes a lot of space.

Any ideas how to do this?

[GoodBoy]

Hello @BeffreyJezos,

I believe the solution in this post might work for you as well. 

 

[Tubby]

Hi there~

The reason why it doesn't allow you to use the formula field as the "Field for value" of the Cascading Elements is because of it's character limit. Since you are concatenating two Text(255) fields in the formula field, the system assumes that you will exceed the character limit of Cascading Elements which is 255 characters.  GoodBoy's solution might work for you if you are sure that the two Text(255) fields combined will not exceed 255 characters any time soon. Otherwise, it will cut the exceeding characters in your formula field.

A workaround that I have for this that will not remove any characters from the formula field  is to create criteria under the formula field and change their form elements to the cascading element of your choice but have them use the two fields that you are concatenating as their "Field for value" so they wont exceed 255 characters. For the first criteria, use the original parent field that you are planning to use. For the second criteria, use the first criteria as the parent field to avoid any filter only records that contain the text on the first criteria.  Lastly, change the comparison type to "Contains" to avoid "No records found." message when you are not search for the exact values. 

Here's a sample screenshot:

 

Hope this helps!
~Tubbs

[BeffreyJezos]

Wow that was quick! 

Thanks guys. I think I'll try @GoodBoy's solution first as I do not think I will be hitting that 255 character limit soon and I am kinda in a rush to finish this. I will try @Tubby's solution later on after my presentation.