Jump to content


Caspio Ninja
  • Posts

  • Joined

  • Last visited

  • Days Won


Posts posted by casyana

  1. Please, put your parameters in single quotes in order to make your criteria working, and add N infront, if there can be non alphabetical characters in your parameters. So it will look like:

    where Date>=N'[@BeginDate]' AND Date<=N'[@EndDate]'AND PreCall_SupervisorLocation=N'[@Location]' AND PreCall_Supervisor=N'[@Supervisor]' AND PreCall_AC=N'[@AC]'


    But in the formula above all criteria are required, otherwise you need to use case statement which will make your calculated field more complected.

    And one more comment. To sum all your fields correctly (ignoring blank values), you need to use IsNull function. Like this:


  2. Use field name with select, not field parameter. Your calculated field will look like this:

    from RA_Received
    where Date>[@BeginDate] AND Date<[@EndDate] AND PreCall_SupervisorLocation=[@Location] AND PreCall_Supervisor=[@Supervisor] AND PreCall_AC=[@AC]
  3. Seems that I misunderstood the problem. Here is an another solution :) Hope, this one will work for you.


    You can use SQL statements in the calculated field to sum/count values thought all records, but in this case, you will have the same value for calculated field in all records. Here is the formula:

    select (sum(field1) + sum(field2) + sum(field3) + sum(field4))/(count(field1) +  count(field2) + count(field3) + count(field4)) from my_table


    Where field1, field2, ... field4 are fields from your table, and my_table is a table name.

    If your DataPage has any search criteria you should mention them in calculated field after where, here is an example:


    select (sum(field1) + sum(field2) + sum(field3) + sum(field4))/(count(field1) +  count(field2) + count(field3) + count(field4)) from my_table where field1>[@a] AND field2 < [@c]

  4. Hi...


    It can be done with calculated fields. To sum all field, you can use the following calculated field:



    To count not blank value, you can use the following calculated field:

    case when [@field:field1] IS NOT NULL then 1 else 0 end +
    case when [@field:field2] IS NOT NULL then 1 else 0 end +
    case when [@field:field3] IS NOT NULL then 1 else 0 end  + case when [@field:field4] IS NOT NULL then 1 else 0 end


    Now you can just use:



    If you don't want to create 3 calculated fields and just want to display everything in one, you can use the following formula:


    (IsNull([@field:Field1],0)+IsNull([@field:Field2],0)+IsNull([@field:Field3],0)+IsNull([@field:Field4],0))/cast((case when [@field:field1] IS NOT NULL then 1 else 0 end +
    case when [@field:field2] IS NOT NULL then 1 else 0 end +
    case when [@field:field3] IS NOT NULL then 1 else 0 end  + case when [@field:field4] IS NOT NULL then 1 else 0 end) as decimal)


    You need to convert divisor to decimal to get correct results, or you can just multiply devisor by 1.0 instead of using cust. It will also give you correct answer:


    (IsNull([@field:Field1],0)+IsNull([@field:Field2],0)+IsNull([@field:Field3],0)+IsNull([@field:Field4],0))/((case when [@field:field1] IS NOT NULL then 1 else 0 end +
    case when [@field:field2] IS NOT NULL then 1 else 0 end +
    case when [@field:field3] IS NOT NULL then 1 else 0 end  + case when [@field:field4] IS NOT NULL then 1 else 0 end)*1.0)


    Hope this helps.

  5. Hi JRoss!


    I hope, I have a workaround for you. I believe, your DataPage is based on the table JRRESULTSTABLENEW, so field MemberID is from the same table. The workaround is to create a view, based on this table, you can include all fields from the table into the view, you even can name them the same, except of the MemberID, you should rename this field (for instance, name it as Member_ID). After that, change the data source of your DataPage to the just created view, and update your formula to the following:


    select sum(b.CCO_Place_Points)

    join Lookup_Places_2 b on b.CCO_Place_ID = a.CCO_PlaceID
    where a.MemberID = [@field:Member_ID]


    Hope it helps!

  6. In this case you need a JS to implement it. You may have two cascading hidden virtual dropdowns and you may populate another virtual dropdown with values which are present in both cascading dropdowns. With help of JS you can find which dd has less options and go through each each option in a loop and check (in another loop) if the same option is present in the second dropdown. And if yes, you need to add this option to your virtual dropdown. And on submit you may save this virtual dropdown value to the real field, which has to be hidden on the form.

  7. Do I understand correctly, that your multi-select is the standard multi-select listbox on a search page? If yes, than you were right, that passed values will be separated by ||cb||. In this case, to parse the value you can use the following JS:

    var str="[@Virtual_City]";

    while (str.indexOf("||cb||")!=-1) {

    str=str.replace("||cb||", " OR ");


    I'm not sure how do you want to pass it to the next page. Will you be redirected to it after page submission or you want to have a link?

    Any way your third page should be predefined criteria. And you need to set Contains comparison type for the City field and receive passed parameter.

  8. If you need to use the same field for value and for display for your cascading dropdown, then you can use the following approach. Create a virtual field and use cascading dropdown form element for it, use Courses as display and Start Date as value, and select - sort by value option. Use hidden form element for your real course field. And you have to create a JS function, that will update this fields value with selected virtual dropdown option. This function should be called on form submit.

    Hope this helps.

  9. If you want to export view XML, in order to be able to import it then back to Caspio Bridge, you have to go to the view section and click export button in the toolbar:

    Posted Image

    But if you want to export view data, you have to open Export Data wizard (File->Export Data), and choose view you need from the Views section

  10. Quote


    WHEN (IsDate([@field:Root_Cause_Identification_Date])=1)

    THEN Datediff(day,[@field:Trigger_Date],[@field:Root_Cause_Identification_Date])

    ELSE 'Not Complete'


    You are getting this error, because the result of Datediff is integer, and 'Not complete' is a varchar. However, one calculated field cannot have a different data type. So,  you need to convert first statement result to a varchar:

    cast( Datediff(day,[@field:Trigger_Date],[@field:Root_Cause_Identification_Date]) as varchar)

  11. You have to add Total&Aggregation to your DataPage - use Insert dropdown below the DataPage Elements panel to insert Totals & Aggregation element, then choose field you want to sum from available fields in Aggregate options, and choose SUM function for it. See Aggregation section in this article:

    http://howto.caspio.com/release-notes/w ... e-6-9.html

    But, if I am not wrong Lite accounts doesn't have this feature enabled.

  12. To archive it you separated your search and report DataPage into two different pages - submission for search and predefined criteria for report. You have to create a separate table for first DataPage, it will collect information about all searches - search data, search time and Yes/No field, that will be yes if no records found, etc, you need add autonumber to this table as well. Then you may customize your No Records found message - add JS to it:


    You may do it in localization wizard.

    And you need to create Single Record Update DataPage, based on the table you are using for collecting search information, find record thorough unique id (autonumber from the table and id parameter)

    You have to use auto-submit script to submit this form, and you may add Yes/No field from the table, use hidden form element, checked by default.

    And finally you may customize Successful submission message to No Records Found.

    So, when user found no records, you localized message will redirect you to update form (and will pass unique id parameter), your update form will be updated (and yes/no field will be changed to yes) and a user will see message No Records Found. You may also hide submission button on your update form, so user doesn't see it.

    Hope this helps.

  13. Hi zzhwvw01, The random ID number may be added if you are using an Auto Complete as form element. You may use getElementsByName() instead of getElementById(), but this method returns all elements with the specified name. So, to access first element, you need to add [0]. Try updated code:

    <script type="text/javascript"> document.getElementsByName("InsertRecordOrigin_City")[0].onchange = document.getElementsByName("InsertRecordOrigin_City")[0].style.textTransform="capitalize"; function capitalize() { var value = document.getElementsByName('InsertRecordOrigin_City')[0].value; var newVal = ''; val = value.split(' '); for(var c=0; c < val.length; c++) { newVal += val[c].substring(0,1).toUpperCase() + val[c].substring(1,val[c].length) + ' '; } document.getElementsByName('InsertRecordOrigin_City')[0].value = newVal; } document.getElementById('caspioform').onsubmit=capitalize; </script>
  14. You may use the following script instead:

    <script type="text/javascript"> document.getElementById("FieldId").onchange = document.getElementsById("FieldId").style.textTransform="capitalize"; function capitalize() { var value = document.getElementById('FieldId').value; var newVal = ''; val = value.split(' '); for(var c=0; c < val.length; c++) { newVal += val[c].substring(0,1).toUpperCase() + val[c].substring(1,val[c].length) + ' '; } document.getElementById('FieldId').value = newVal; } document.getElementById('caspioform').onsubmit=capitalize; </script>

    It should capitalize letters while you are typing. So you always will have first letter capitalized. And function capitalize() will update value of the field while user submit the form, so value with capitalized first letter will be added to the table. Hope this helps.

  15. I want to have a calculated field where the user either sees a link to click on (which redirects to a document), or sees a message "The document hasn't been uploaded yet".

    I tried to do this by having the following in a calculated field:

    WHEN [@field:File]<>'' THEN [url="[@field:File]"]Click here to read the document[/url]
    ELSE 'The document hasn't been uploaded yet.'

    But this doesn't work. When I want to go the the details page I get Error in formula.

    Do you have any suggestions? I can't upload the file to Caspio, so that is not an option.



    Hi Bjorn,

    You cannot write HTML code in calculated field. The best solution is to add html block with Java Script instead of calculated field:

    if( '[@field:File]' != "")

    document.write('Click here to read the document');


    document.write('The document hasn't been uploaded yet.');

  16. I have a calculation that I want to populate a field in a details report page. The field totalappfield is not populating and I've tried dozens of iterations based on examples provided by Caspio. Can anyone see what's wrong?

    function calculate()


    var applicationfee = parseFloat(document.getElementById("EditRecordapplicationfee").value);

    var numbercoapps = parseFloat(document.getElementById("EditRecordnumbercoapps").value);

    var totalappfee = (applicationfee * numbercoapps);




    Script looks good. Make sure that all your field's Ids are correct. You mentioned totalappfield field, but according to the script you are updating totalappfee field.

    All fields used in calculation have to be Text fields or Hidden, you may not use display only fields in calculations.

  17. 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.

  18. You may archive it using multiple deployment. Deploy two DataPages on your web page:

    1. First one is a report, use predefined criteria, you may use timestamp field in the criteria, so you will be able to see only records submitted today (comparison type today), enable delete record on the Search results options wizard screen, you may also enable editing, if you want.

    2. Bellow it (or above, as you wish) deploy submission form.

    So, after each submission, record will be added to the report, and you will be able delete it (or update).

    Or, you may simply use a report with inline insert enabled.

    Hope this helps.

  • Create New...