Filter table using < or > a value entered by the user

Does anyone know if it would be possible to filter a table using math operators ‘<’ or ‘>’? I have a table which specifies ‘passenger capacity’, and I want to find all vehicles with a capacity < “X” (X = user entered value). I might also want to extend that to all vehicles with a capacity > “Y” So the filter would need to work across both < and > operators.

You can start to do this,  but I’m not sure the solution is going to be entirely satisfactory. 

Filters are made up of two parts,  the model condition - which actually executes a where statement in the SOQL,  and the filter source - which is the list of items that can be passed into that condition. 

Currently you can make conditions that use math operators.   For example all “All opportunites > $50,000”.    But what cannot be done in a completely straightforward fashion is a user provided source,  where the UI would have a text box for users to provide the source value for the condition. 

I can think of two ways around it. 
1. You can give filters a list of manual sources.  This would let you create a series of categories  >30K, >50K, >100K  etc.  There is no limit to the number of sources you provide,  but at some point that becomes stupid. 

2. You might be able to create antoerh field on your object whose sole purpose is to provide the source for this condition.  Create a field editor above the table with this field, and then Create a condition on the table’s model that uses the field as its value. 

This might also be solved using some javascript customization,  but I won’t go there yet. 

Thanks Rob

The introduction of the Action Framework with the Summer Release makes it possible to have filters built on user submitted values. Let me walk you through a scenario where we filter a table to show contacts whose hat size is larger than an arbitrary number enterred by the user.

1. Create a Contact model. This will be your list of contacts. Add all the fields you want to see.
Add a condition on this model that selects the Hat Size field, uses the > operator, has a blank value, and is filterable - default off. IE a normal filter condition.

2. Create a second “Hat Size Filter” model. This model is also on Contacts. It should not load model data, but should create a default row.
Choose the Hat Size field and that is all.

3. Start building out your page. I put a panel set on the page with the Contact table in the Right Wide Panel.

  1. Create a field editor on the Hat size filter in the Left Narrow panel. I typically make these “Edit mode”, And lables above the value.
    Bring the Hat Size field into this field editor. Here our users will fill in the data they want to compare against.

5. Add a page title below the field editor. Make sure this page title is connected to the Hat Size Filter model. Remove the title and subtitle values and add two buttons. One for Filter and one for Clear. These buttons will both run a sequence of actions.

**5A. Filter Actions: ** Use the following sequence of actions:

  • Activate & Set value of Model Condition. Find the Hat Size filter on the Contact model. Pass the “Hat Size” field value.
  • Query Model - Contacts.

5B. Cancel Actions: Use the following sequence of actions:

  • Deactivate Condition. Again - Find the Hat Size filter on the Contact model.
  • Query Model - Contacts.

**6. Save your page and try it out. **
Enter number 5 in the box and hit Filter: All sizes larger than that are shown. Hit Clear and all records are shown.

A note. This model does not work well if you have “Warn about unsaved changes” turned on for the page. The Hat Size model looks like an unsaved change - and will generate a warning when you navigate away from the page. But we would never want to save that change. We are only using the data to filter our contacts. So it will be best if you turn that option off in the basic page settings.

There you are. Three cheers for the Actions Framework!

This is fantastic! Is there a way to Activate & Set the model on multiple values. For example you want to see only 6 specific invoice numbers. Typing in those specific invoice numbers and them showing on the list view?

Tammy,

I’m not totally confident on this, but I think if your condition is of the type “multiple specified values”, then you can pass it semicolon separated values, like so:

Invoice1;Invoice2;Invoice5


Hi Matt,

Thanks for your reply. I had tried your suggestion with no success. I have tried every combination I can think of to be able to filter on multiple records on the fly. So far I have only been able to filter to one record. 

Tami,
I think Matt is correct that this should be working. A couple of things to check for (some of these may be no-brainers, but it’s always good to check);

  1. Make sure your model condition is of type “Multiple Specified Values”.
  2. If the condition is on a lookup field, make sure you’re passing in a record Id instead of a string (i.e. the Name of the record).
  3. When setting the condition, if you are passing in a list of strings, don’t put them in quotes. For example, when I have a condition on my Contact model filtering by Name, I use Emily;Tami;Matt for my list rather than ‘Emily’;‘Tami’;‘Matt’
  4. If you are passing in a field value (such as {{Id}} or {{InvoiceNumber}}), try with {{{}}} instead of {{}}
  5. Make sure you query the model after setting and activating the condition.

I hope some of those help.
Emily

Hi Emily,

Thanks for all the great tips. I went through each one to ensure my settings where the same and so far it seems that I have done everything correctly. Although clearly I haven’t because it is not working. When i put in my filter criteria as you suggested with the colon it only returns the results of the first search option. I have attached a picture of my results.

The box to the left is where I type in my invoice numbers. The table to the right is the data I am filtering.

Tami, thanks for the screenshot. That is helpful. From the picture, here’s what it looks like you’re doing:

  • You have 2 models (call it InvoiceFilter and InvoiceList). InvoiceFilter has a single row. It is used for the field editor and a page title with the “Filter” button. InvoiceList is used for the table.
  • When the user types a value into the Invoice Number field in the field editor and clicks “Filter”, you set the condition on the InvoiceList model using the field value from the InvoiceFilter model. Then, you query the InvoiceList model.
Does that describe what you’re trying to do? I set up a scenario similar to what I described above, and everything seemed to work fine. I’m not sure what you could be missing… I don’t mind digging for further details, but it would be good to know first if I’m understanding your use case correctly.

Hi Emily,

You understand exactly what I am trying to do. I am not sure where I am missing the mark. Is there something else that I can show you to figure out which setting is throwing a wrench in this process?

Does it matter that the field is the invoice number field which would only have one value?

Thanks,

Tami 

Note:  We have released an updated version of this method - with some javascript that ignores fields that have blank values in thism.  Here is sample XML for this method. 

Hi Rob,

I have created the same filter for multiple fields and one Apply filter button. However, now I want something where the user doesn’t fill the field value and still can be able to use this filter for other fields(Which has value populated).

PS: I have also tried your given link but seems I am missing something. I have added "snippet=“CheckBlanks” attribute on the . Maybe I am doing something wrong but not sure what.

Please let me know If you need more information! Looking forward to your response!

Thank you!

@Emily is there a way around having to use semicolons?

  1. When setting the condition, if you are passing in a list of strings, don’t put them in quotes. For example, when I have a condition on my Contact model filtering by Name, I use Emily;Tami;Matt for my list rather than ‘Emily’;‘Tami’;‘Matt’

Tami.  Here is an idea.  I’m not sure whether this will satisfy your requirement.  Set up your text box is to let users submit one invoice number at a time.  Then in your action sequence,  make the “Query model” action have the behavior “Get More - Merge in new Rows with old”.   This means you will be able to add records to your table and get records from multiple invoices. 


Thanks for the great idea! However, it breaks the filter.

I changed the condition from “multiple specified values” to “single specified value”. 

Then I changed the query model action to get more. 

The result was nothing. It would not return the single value put in the text box.