Advanced search customizations?

[DeFields]

Ok guys, I am trying to do 2 things here:

1) I need to be able to search accounts by the last time they were modified.

2) I need to make my searchs defined between 2 time periods, for example, all accounts modified between 6/02/07 and 8/02/07. Or if it will be too involved, all accounts updated less than X amount of days ago.


I am a programmer, so I don't mind getting down and dirty, but I am not familiar with this type of system, so if someone could tell me where/what i have to edit, and perhaps give some code expamples with before and after references. I would greatly appreciate it!!!

[ivolator]

Open your Search.html file and find the section advanced in it.
Add the text fields containing the dates (from_midified_date,to_modified_date for example).You can dig on your own to figure the popup calendar (this is a separate issue that I’ll address if you want).

Open your ListView.php .
Before the line

 $lv->setup($seedAccount, 'include/ListView/ListViewGeneric.tpl', $where, $params); 
  

Do this

  
if($_REQUEST['from_date_modified'] ){
     $to_date = clean_string($_REQUEST['to_modified_date'], "NUMBER");
     $where .= ' AND accounts.date_modified >= \''.$to_date.'\' ';
 }
if($_REQUEST['to_date_modified']){
     $from_date = clean_string($_REQUEST['from_modified_date'], "NUMBER");
     $where .= ' AND accounts.date_modified <= \''.$from_date.'\' ';
 } 
  

Where to_date_modified and from_date_modified are fields in your SearchForm.html file

[DeFields]

I created the two fields in admin studio. One is called date from: and the other is called date to:

I tried with two different fields for each to test, date_modified, and from_date_modified

In either case:
The fields pop up, but when i search, nothing comes up in the results.

There must be some link of data that I'm missing here. Can you tell me how i should call the variable in admin studio?


Actually, perhaps someone could give me some code for something simpler, such as, two fields that accept a range for annual revenue, and search for companies between that range?

[ivolator]

There must be some link of data that I'm missing here. Can you tell me how i should call the variable in admin studio?

You can name them what ever you want in the example I gave you.
If you created the fields in studio then they end up like this YOUR_NAME_c
This is fairly simple example.I have tested this code with manually created fields (I just inserted the HTML straight into the Search form). First enter the data manualy. Echo your WHERE clause to see what is actually getting in as input for the sql server

[ispytodd]

from_date_modified, from_modified_date? I could be wrong, but I think there are some serious mixups in there. This thread really did get me started though.. here's my take.

I need to search for cases that were closed within a certain date range. I already have a custom field called case_closed_c.

One possible caveat- I believe that creating start_date and end_date custom fields in the studio may mess this search up. Instead I manually added html form fields to SearchForm.html. I added an extra row in the studio then manually inserted the form fields into the tables hoping not to mess up the studio too much.

1) In the studio add a blank row to the 'advanced search' section for the module you are working with. I added the row at the bottom of the form for simplicity.

2) Edit SearchForms.html in the modules directory and insert two text area fields with descriptions. My fields are called 'closed_from_date' and 'closed_to_date'. My code looked like this:

<tr>
<td width="20%" class="dataLabel"><span sugar='slot21'>Date Closed Start:</span sugar='slot'></td>
<td width="25%" class="dataField"><span sugar='slot21b'><input name='closed_from_date' id='closed_from_date'
type="text" size='25' maxlength='50'></span sugar='slot'></td>

<td width="20%" class="dataLabel"><span sugar='slot22'>Date Closed End:</span sugar='slot'></td>
<td width="25%" class="dataField"><span sugar='slot22b'><input name='closed_to_date' id='closed_to_date' type="text"
size='25'
maxlength='50'></span sugar='slot'></td>
</tr>

3) Edit Listview.php in the modules directory. Before

$lv->setup($seedCase, 'include/ListView/ListViewGeneric.tpl', $where, $params);

add

///////
/// custom date range search
///////
if($_REQUEST['closed_from_date'] ){
     $from_date = clean_string($_REQUEST['closed_from_date'], "NUMBER");
     $where .= ' AND (cases_cstm.date_closed_c >= \''.$from_date.'\')';
 }
if($_REQUEST['closed_to_date']){
     $to_date = clean_string($_REQUEST['closed_to_date'], "NUMBER");
     $where .= ' AND (cases_cstm.date_closed_c <= \''.$to_date.'\')  ';
 }
echo $where;
///////

This does not include the js calendar and you have to enter the date in yyyy-mm-dd format.

Note the "echo $where" at the end. This will echo the entire search string just below the advanced search after you submit.. Play with it. You'll notice that our custom search gets appended at the end of any standard search so I had to include the 'AND' at the beginning. If you do not supply any normal serach parameters you will have a problem because the search will effectively start with 'AND'.

As you can see this is rough, but it is working for me.. When I get around to it I'll work out the kinks. Right now only one user uses it so it's not a huge issue... big thanks to ivolator.

[veon]

Hey,
Thanks so much for the detailed explanation,I was working out on the same kind of logic for search on two range fields for Price.
This has helped me a lot
Thanks a ton again.