Jump to content

How to search the calculated field?

Recommended Posts

I have a calculated field in the results page which is using formula "Datediff(DAY, GetUTCDate(),[@field:DateField])" to calculate the Expiration Days of a DateField. I want to add this calculated field in the search form to search a range of Expiration Days. Is that possible? Thanks.

Link to comment
Share on other sites

You can have two Virtual fields to records the rang of the expiration days.

Select DateField in the search page as the real search value, and add criteria using the Insert button to achieve the range searches. You can also change the logical operator to AND and create range searches.

In the footer, insert JavaScript code to calculate the Virtual expiration days, and pass the value to the real search field to implement the search.

Refer how to insert the current date and time into a field: http://forums.caspio.com/viewtopic.php?f=14&t=12153

and how to add number of days to today's date http://stackoverflow.com/questions/3818193/how-to-add-number-of-days-to-todays-date

Sample code

<script type="text/javascript">
function f_default(){
var FromDate = new Date();  // get the current date for From field
var ToDate = new Date();      // get the current date for To field
var numberOfFrom = document.getElementById("cbParamVirtual1").value;  
var numberOfTo = document.getElementById("cbParamVirtual2").value;

// add from days and to days to the variables FromDate and ToDate 
FromDate.setDate(FromDate.getDate() + parseInt(numberOfFrom)); 
ToDate.setDate(ToDate.getDate() + parseInt(numberOfTo)); 

//Format FromDate and ToDate to mm/dd/yyyy
var From_day = FromDate.getDate();
var From_month = FromDate.getMonth() + 1;
var From_year = FromDate.getFullYear();
var To_day = ToDate.getDate();
var To_month = ToDate.getMonth() + 1;
var To_year = ToDate.getFullYear();

// If the search field is empty, real search criteria will be also empty. Otherwise, the real field is equal to the CurrentData + From expiration Days
if (numberOfFrom == "")
document.getElementById("Value3_1").value = "";
document.getElementById("Value3_1").value = From_month + "/" + From_day +"/" + From_year;

if (numberOfTo == "")
document.getElementById("Value3_2").value = "";
document.getElementById("Value3_2").value = To_month + "/" + To_day +"/" + To_year;

//On submitting the webform, the function f_default is executed 
document.getElementById("caspioform").onsubmit = f_default; 


Change all occurrences of the cbParamVirtual1, cbParamVirtual2, and Value3_1, Value3_2 in the script to the appropriate element id which can be found through a debugging tool such as FireBug. FireBug is a free FireFox plugin that can show you exactly where your JavaScript is breaking.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...