# casyana

Caspio Ninja

39

2

2. ## Duplicate Data

You are right, there is no such feature in Caspio Bridge, you cannot search for duplicates in the DataPage, but you can create a simple tabular report and group by address field.
3. ## Use Totals & Aggregations Values In Calculated Fields And Graphs

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: (sum(IsNull(Listening_Listen),0)+sum(IsNull(Listening_Concerns),0)+sum(IsNull(Listening_Probe),0)+sum(IsNull(Listening_Paraphrase),0)
4. ## Use Totals & Aggregations Values In Calculated Fields And Graphs

Use field name with select, not field parameter. Your calculated field will look like this: select(sum(Listening_Listen)+sum(Listening_Concerns)+sum(Listening_Probe)+sum(Listening_Paraphrase))/(count(Listening_Listen)+count(Listening_Concerns)+count(Listening_Probe)+count(Listening_Paraphrase)) from RA_Received where Date>[@BeginDate] AND Date<[@EndDate] AND PreCall_SupervisorLocation=[@Location] AND PreCall_Supervisor=[@Supervisor] AND PreCall_AC=[@AC]
5. ## Use Totals & Aggregations Values In Calculated Fields And Graphs

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]
6. ## Use Totals & Aggregations Values In Calculated Fields And Graphs

Hi... It can be done with calculated fields. To sum all field, you can use the following calculated field: (IsNull([@field:Field1],0)+IsNull([@field:Field2],0)+IsNull([@field:Field3],0)+IsNull([@field:Field4],0)) 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: [@calcfield:1]/[@calcfield:2] 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.
7. ## Using Sql Select In A Datapage

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) from JRRESULTSTABLENEW a join Lookup_Places_2 b on b.CCO_Place_ID = a.CCO_PlaceID where a.MemberID = [@field:Member_ID] Hope it helps!
8. ## Let a field automatically look up a table

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.
9. ## Passing Parameters from Multi-Select Listbox

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.
10. ## JS - How to display HTML links based on data in table

Hi Maverick I'm glad, that it helped. I don't think you will experience any issue with document.write, it is supported in all major browsers: http://www.w3schools.com/jsref/met_doc_write.asp Regards, Casyana.
11. ## JS - How to display HTML links based on data in table

I would propose you to use the following JS in the HTML block: if ( '[@field:Membership]' == 'Basic') document.write(' Link 3 ');
12. ## Display a check mark if the field value equal a specific val

You can use the following JS in the HTML block: Your link if('[@field:field_you_need_to_compare]'=='value') document.write('');
13. ## Let a field automatically look up a table

Could you, please, elaborate? It sounds like a cascading form element, isn't it what do you need? http://howto.caspio.com/datapages/form- ... boxes.html
14. ## Passing value to a variable in a query dynamically(Web API)

Hi Supra01, Could you, please, elaborate a little bit more: which language do you use to call WS API methods, and from where the dynamic criteria should be taken? For now you may try to create a variable, assign the dynamic value to this variable and use it in the method call.
15. ## custom sort cascading dropdown

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.
16. ## Import views

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: 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
17. ## Calculated field to compare dates or return text

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)
18. ## How do I sum results?

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.
19. ## How to record zero results?

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: location.href="?id=[@InsertRecord]"; 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.
20. ## Javascript For Capitalizing First Letters In Field

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>
21. ## Javascript For Capitalizing First Letters In Field

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.
22. ## How to have a url in a calculated field

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'); else document.write('The document hasn't been uploaded yet.');
23. ## Inserting a calculated field

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.
24. ## Search Across Fields

Yes, you are right. But, looks like there is no such possibility to combine AND and OR in the search in Caspio.
25. ## Javascript for adding and removing items to be reported

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