Search Across Fields

[KenDJPA]

I have a table where I am listing people associated with an event. When there are two people associated with the same event each name is entered into a separate field. Is there a way to create a data page to search if a certain person is listed in either of the fields?

[casyana]

I have a table where I am listing people associated with an event. When there are two people associated with the same event each name is entered into a separate field. Is there a way to create a data page to search if a certain person is listed in either of the fields?

You may use the following steps to do this:

1. Include two fields, that have people associated with the event in the Search Page (use OR logical operator between the search fields). Use text field form elements for both of them, later you need to hide them with help of HTML blocks.

2. Add virtual field to the Search page. This field will be used for person name, that we want to search for.

3. Add the following JavaScript in the Footer:

function update_name()

{

var name = document.getElementById('cbParamVirtual1').value;

document.getElementById('Value1_1').value = name;

document.getElementById('Value2_1').value = name;

}

document.getElementById('caspioform').onsubmit = update_name;

4. Open this DataPage in Firefox and check with help of firebug, that fields from the step 1 have correct ids: Value1_1 and Value1_2:

If ids are different, update script accordingly.

5. Test that search performs correctly - you need to enter people name only into virtual field.

6. Edit DataPage and hide fields from the step 1 with help of hidden table: http://howto.caspio.com/customization/h ... pages.html

Hope this helps.

[KenDJPA]

Thanks! That worked, but the limitation of having to set it up as an "OR" choice limits the ability to have other criteria to limit the search. Any way to do an "OR" just within these 2 fields and have other fields connected to search with an AND ?

[casyana]

Thanks! That worked, but the limitation of having to set it up as an "OR" choice limits the ability to have other criteria to limit the search. Any way to do an "OR" just within these 2 fields and have other fields connected to search with an AND ?

Yes, you are right. But, looks like there is no such possibility to combine AND and OR in the search in Caspio.

[bahar_vm]

Good news is that combination of AND and OR is on the roadmap of the second upcoming release. The release is not dated yet but it should be in Q4 of this year.

Best,

Bahar M

[KenDJPA]

Thanks. Thought of a work around to make this work. Adding a new field to contain all names associated with event that will be populated along with the separate fields through the script below. Then I will be able to search for COMBINEDFIELDNAME as AND other fields at same time, but not display that field in results.

document.getElementById('Submit').onmouseover = function(){

var name1 =

document.getElementById('cbParamVirtual1').value;

var name2 =

document.getElementById('cbParamVirtual2').value;

document.getElementById('InsertRecordCOMBINEDFIELDNAME').value = name1 + " " + name2;

document.getElementById('InsertRecordUSER1FIELD').value = name1;

document.getElementById('InsertRecordUSER2FIELD').value = name2;

};

[bahar_vm]

great solution! you are concatenating multiple field values into a new field and using the new field for search.

[jimher]

Thanks. Thought of a work around to make this work. Adding a new field to contain all names associated with event that will be populated along with the separate fields through the script below. Then I will be able to search for COMBINEDFIELDNAME as AND other fields at same time, but not display that field in results.

document.getElementById('Submit').onmouseover = function(){

var name1 =

document.getElementById('cbParamVirtual1').value;

var name2 =

document.getElementById('cbParamVirtual2').value;

document.getElementById('InsertRecordCOMBINEDFIELDNAME').value = name1 + " " + name2;

document.getElementById('InsertRecordUSER1FIELD').value = name1;

document.getElementById('InsertRecordUSER2FIELD').value = name2;

};

Hi KenDJPA,

I have the same issue with you except I've set my other criterias with an AND operator and want the two fields set to OR. I was trying to follow your solution but don't understand how concatenating the two fields into a new field solves the problem. Can you help me with your code above? As I understand it, you've set up two virtual fields. The user inputs into those two fields and the values get's placed into USER1FIELD and USER2FIELD which are then concatenated into COMBINEDFIELDNAME. At this point I'm lost and not sure how you were able to search by COMBINEDFIELDNAME.

Thanks for your help. If others can chime in also, I'd appreciate it.

Jim

[bahar_vm]

Hi jimher,

I think the solution provided by KenDJPA needs to be applied on the submission form when new records are being submitted to the table. This Java Script should be placed in the submission form to concatenate the field values into the new field "COMBINEDFIELDNAME", this way all records in the table will have the "COMBINEDFIELDNAME" field to store the concatenated values. So in your search form you can use the field "COMBINEDFIELDNAME" with "Contains" comparison type along with your other fields and AND logical operator. No need for Java Script in the search form in this case.

For you existing data you may need to perform the concatenation in Excel and import them back into Caspio.

Best,

Bahar M.

[bahar_vm]

Btw, I don't see the usage of virtual fields in the code, you can use the real fields and InsertRecordFIELDNAME in the submission form to reference to the real fields, read the values and write them into the combined field.

[Juancaloz]

I have a table where I am listing people associated with an event. When there are two people associated with the same event each name is entered into a separate field. Is there a way to create a data page to search if a certain person is listed in either of the fields?

You may use the following steps to do this:

1. Include two fields, that have people associated with the event in the Search Page (use OR logical operator between the search fields). Use text field form elements for both of them, later you need to hide them with help of HTML blocks.

2. Add virtual field to the Search page. This field will be used for person name, that we want to search for.

3. Add the following JavaScript in the Footer:

function update_name()

{

var name = document.getElementById('cbParamVirtual1').value;

document.getElementById('Value1_1').value = name;

document.getElementById('Value2_1').value = name;

}

document.getElementById('caspioform').onsubmit = update_name;

4. Open this DataPage in Firefox and check with help of firebug, that fields from the step 1 have correct ids: Value1_1 and Value1_2:

If ids are different, update script accordingly.

5. Test that search performs correctly - you need to enter people name only into virtual field.

6. Edit DataPage and hide fields from the step 1 with help of hidden table: http://howto.caspio.com/customization/h ... pages.html

Hope this helps.

This worked really well for me. Nonetheless, I have been unable to make it work once I try to hide the other fields. What do you suggest me to do?

Thanks!

J

[shahzaibahmed]

Yes, you are right. But, looks like there is no such possibility to combine AND and OR in the search in Caspio.

[NailDyanC]

Hi, just to add in the previous comments above.   As I understand, you want to create a search field which can search to  the 2 fields, right? If that is the case, you may also check this link:  https://howto.caspio.com/faq/reports-datapages/how-to-do-keyword-search-across-multiple-fields/

I have also another workaround, on your table, add another field and set it to Formula. Then, concatenate the fields you want to use in your report. If all your field are in Text datatype , you can use [@field:FieldName1] + ' ' + [@field:FieldName2] on your Formula Field. Then, use this formula field as your search criteria, it should look like the attached screenshot. 

I hope that helps.