Search All Questions

[pastvne]

I have a list where I am trying to use search all.
I have two questions:

When I go into Lists > mylist > Details > Filter > Elements, I pull up a list of elements for the list that I can add to the search all. However, some of the element names listed have an asterisk at the end of the element name. There is a headnote at the top of the selector which reads "(*) not available in advanced search all. One of the fields that I want to have included in the search all is a currency field called Total_Amount. It is flagged with this asterisk.

I've looked at documentation and search for the answer as to why this field is not eligible for search all. I read something about collation issues. Can anyone explain how I could include a monetary value in a search all? (note: I don't have Extended Search All enabled).

Second question:
I have an element in which I store XML and I have included that field in my search. I search for a monetary value like 12.34 and it finds records but when I look through all of the database field values, including joined values, it seems that some of the records selected do not contain the search all value used. I'd like to understand why this might be the case...

 

[cheesegrits]

First question ... only text based fields (char, varchar, text) can be used in advanced search, as we use MySQL's "MATCH AGAINST", which only works with those types. I suspect your "Total_Amount" is probably a 'field' element set to decimal or int? In which case Fabrik will have set the underlying field type to decimal or int. If you set it to regular text, it should work.

Second question ... is the XML relevant, or you just mentioned it in case it's relevant? All I can suggest is you use Fabrik debug mode, grab the query we execute, and try it in your MySQL client of choice. See if you can figure out what's going on.

Also, make sure you have set 'Reset Filters' to Yes on any menu items for the list. Sometimes filter values can get stuck if you don't clear filters.

-- hugh

 

[pastvne]

Thanks Hugh. I think I may have set the field to decimal while trying to get the List column value to display right justified. I think that didn't help and I ended up adding a css class which did the trick. I'll experiment with changing the field back to text to see if I can have right justification using css with text field type. Thanks for the explanation.

Yeah, the XML bit is probably not relevant. Think of it as a text blob. I'll turn on the query debug and have a look.

Appreciate the help,
Bruce

 

[pastvne]

A follow-up question Hugh. In the Elements box under Lists > Details > Filter > Elements, when the fields displayed in the box have the Asterisk suffix, is that triggered when a field is not a text-based field? I may try to add to the wiki and want to make sure that is correct.

 

[cheesegrits]

Correct. I'm pretty sure we check the actual field type in the table.

-- hugh

 

[pastvne]

Hi Hugh:
As you speculated, I had used decimal for the format of this element. I experimented today with moving it to text. However, this element's purpose is to display a currency value and the user would also like to be able to order the list view by this element. If I move the element to text, then it sorts as left-to-right text instead of as a numeric value. I am thinking of these ways around the issue:

1) If I had control of the LIST population query, then I could add a casting to the ORDER clause for this element to force it to numeric. But I don't see in the element definitions where I could do this.
2) I considered a calc element where I pull the value from the total_amount element, set that a decimal type (if I'm given that control) and then publish it as the element in the list with ordering set to yes.

But I also noticed that when I experimentally changed the total_amount element to text (from decimal) and then tried to search all for the a known total amount, that it did not include my intended record in the result set. Do I need to rebuild MySQL indexes after Fabrik changes the column type in order for the query to recognize the values already in the table... or should that be automatically done by MySQL?

Thanks,
Bruce

 

[cheesegrits]

1) nope, no way to do that, and no a feature I'd want to add.

2) should work

The index should automagically rebuild in MySQL. Did you check that we actually changed the field type? Did Fabrik tell you it was going to do that when you changed the type? If not, do you have "alter fields" set to "No" in the list's advanced settings?

-- hugh