Jump to content

Search the Community

Showing results for tags 'calculated field'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Caspio Bridge
    • General Questions
    • Caspio JavaScript Solutions
    • Tables, Views and Relationships
    • Import/Export and DataHub
    • DataPages
    • Deployment
    • Security, Authentications, Roles, SAML
    • Styles and Localizations
    • Parameters
    • API and Integration
    • Calculations and aggregations
    • User JavaScript and CSS Discussions

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start




Website URL






  1. I'm trying to trigger a calculated field contingent on a select2 field or a hidden calculated field and I can't make it work. This is what I have: Field1(Cascading Dropdown Field for value: Field_ID) (converted into a select2 element using $('select[name="InsertRecordField1"]').select2();) VirtualField1 (Calculated Field) (Using the following sql SELECT Field_Name FROM Table WHERE [@field:Field1] = Field_ID) The SQL doesn't notice the changes made to Field1 nor its initial value on page load. I tried using JavaScript and SQL to give Field1's value to a another hidde
  2. Hello, Is there a way to refresh a calculated field when a button is clicked? I have a field set to 'Calculated Field' in a submission form that references a couple other fields that themselves are updated with a button's js function. When you don't manually (type in) update a field then the calculated field won't show any changes. So when my button's function runs and Field1 gets a timestamp, the calculated field that uses Field1 doesn't refresh. It does refresh if I type in the time/date to Field1. I'm need to show the refreshed value in the calculated field before it is submitte
  3. I can color the background on input fields, but not having luck on calculated fields. Also want the amount in bold.
  4. Does anyone know how to use 'contains' in a calculated field to see if the field contains a certain value, instead of using = to equal it. I have a tale with records that have a field with values like 524_789B, 524_788B, 524_794B, 524_791B, 524_1047B. And an authenticated field value might be 524_794B. I want to count the number of records that contain 524_794B. So in a calculated field I need something like below: SELECT COUNT (ItemID) FROM _V_Items Where Assigned_To CONTAINS '[@authfield:Users_CID_StaffID]' What is the syntax for 'contains'?
  5. Hello All, I would like to COUNT the values between a certain time frame regardless of date... for this example lets say 10AM and 4PM or 10:00 and 16:00. Please consider: Item1: 01/07/15 11:00:00 Item2: 01/04/15 13:00:00 Item3: 01/03/15 07:00:00 Item4: 01/06/15 17:00:00 Item5: 01/03/15 09:00:00 Item6: 01/07/15 15:00:00 Desired results: 3 I can pull the hour from the date, but I can't figure out how to filter by hour or count values in a range. Any ideas? Thanks
  6. Hi everyone, I would like to implement XIRR function from Excel in Caspio. Can somebody give me a direction on how to achieve this? Thanks, vitalikssssss
  7. Is there a way to create a search filter for a calculated field?
  8. Hello, I would like to create a calculated field in tabular report datapage that displays a column in related table as a concatenated string. For Example, many of my apps use the tblPlants as a data source. This is a table of plant species that has a one-to-many relationship with many other tables. I would like to present a field in the report that lists the the botanical gardens for each plant is found in from the tblBG as a string. SELECT STRING_AGG(tblBG.Garden,",") FROM tblBG WHERE tblBG.PlantID = [@field:PlantID]; I get an error that say there is a syntax erro
  9. I need to reference a calculated field's value for use in an If/Then function in js. The calculated field uses a SELECT to calculate a View's total records, which is either null or with some records in the result. If the calculated field has a null value I need something to happen vs if it's not null. Here's the code: <script> var v_itemsid = parseFloat(document.getElementById("EditRecordcalcfield18").value); if(!isNaN(v_itemsid)) { document.getElementById('section1').style.display = "block"; document.getElementById('section2').style.display = "none"; } else { document.get
  10. I am trying to get the count from a table and applying record level security, but in the where clause, when I am comparing it with my auth field, it is saying "Invalid Query" For instance, my query: ISNULL((SELECT COUNT(DISTINCT [@field:PropertyKeyId]) FROM UI_FactTrxn_1 WHERE [@field:InvestorUserId] = [@authfield:Users_UserId]),0) I am getting Invalid error for this. But when I am trying to pass the string in place of authfield, it is a valid one! Can anyone please help me on this?
  11. Hello all; I am trying to log the time users are logged in, and I am unable to get any precision. I would like hours to be displayed with two decimal places of accurate precision, but Datediff rounds to the nearest unit of time. This (IMO) should be fairly easy to overcome by using a smaller unit of time, and dividing that large number by 60 or 3600 (for minute or second), but no luck. Using the following sessions let's look at what I want, what I've tried, and what I'm getting. SessionID = 1 | LoginDT 8-17-2014 09:00:00 | LogoutDT 8-17-2014 09:15:00 SessionID = 2 | LoginDT 8-17-20
  12. Hi there, I have a tabular report datapage, that I am using to display calculations from my table. I want to show week number, and what has happened during that week. So far I have 2 columns other than week number, number of cards updated, and total number of updates. The calculations I am using are working fine, but it's showing each number as a separate row. So if the calculation for week 6 is 7 updates, it shows 7 rows. See attached screenshot. Below is my code. Anyone know how I only show each line once (so there's only 1 row for each week number) Number of cards update
  13. Hi there, I have a formula that is working fine in most instances, but it falls down every once in a while. I think I've got it figured out and then it falls down again! This is my code IsNull((SELECT sum(IsNull([@field:Optimiser_RatingX],0)+IsNull([@field:QA_Tester_RatingX],0)) FROM Card_comments_and_updates WHERE User_ID=[@field:Analyst_IDX])/((SELECT COUNT(IsNull([@field:Optimiser_RatingX],0)) FROM Card_comments_and_updates WHERE IsNull([@field:Optimiser_RatingX],0) >0 AND User_ID=[@field:Analyst_IDX])+(SELECT COUNT(IsNull([@field:QA_Tester_RatingX],0)) FROM Card_comments
  14. Hello, I've been trying to find a solution to this for awhile. Does anyone know how to limit/round the decimal places on a field and calculated field, over-riding whatever the setting is in the Localization. I need to do this because in some places I need the localization to show 3 decimal points for percentages and sometimes 0 decimal places (rounding whatever the value is). This is because I have multiple fields on the same datapage that use percentages for display and one needs 3 decimal places and the other needs 0. It makes it sloppy looking to always have .000 after the values
  15. Hello, I have a Text field (64000) that is used to hold addresses with html markup. I need to use calculated fields to get each line's value for use in importing to another app that requires the address to be broken down to individual fields. For example, the value in the text field might be: Acme Building <br /> 22 Main Street <br /> Suite 33 <br /> Atlanta, GA 30308 I need it to take each line and put it in a calculated field: Calculated Field 1 = "Acme Building" Calculated Field 2 = "22 Main Street" Calculated Field 3 = "S
  16. Following syntax worked as script for a calculated field in a previous report: label: Time Period (Start Date) [@field:EA_StartDate] + CASE WHEN LEN([@field:EA_EndDate]) > 1 THEN ' - ' + [@field:EA_EndDate] END Currently [@field:EA_StartDate] + ' - ' already generates "Invalid Formula" when verifying the script.
  17. Hi everyone, Does anyone know how to render a calculated field as HTML? I tried outputting HTML tags inside calculated fields, but they display as plain text. Thanks in advance. -dspolyglot
  18. Hello, I saw this How-to article on changing the background of a row based on the value of a field: http://howto.caspio.com/tech-tips-and-articles/advanced-customizations/how-to-dynamically-change-the-background-of-a-results-page/ However, I need to delete the row instead of changing its background color. Also, I need to delete the row when a calculation returns a blank value. Is this possible? I don't know how to write JavaScript code.
  19. Hello, I'm using a tabular search and report for an invoicing application where I need to show aggregations of the RateAmount grouping by the RecIDL at the bottom of the RateAmount column. I want to pass the aggregated calculated field total to the invoice using an href link to my details page. Currently the link to the details page is showing an invoice for each RecIDL. I need the RecIDL to group on the tabular search and report with the aggregated total for each RecIDL. Here are the specifics: The Table name is 'Invoicing'. I have columns for 'RecIDL'
  20. Hi Guys, I hope somebody has an idea for this. I'm stuck creating the report, step 3 below. 1) user Bob insert its color preferences on a search web form: Name color1 color2 color3 color4 Bob red blue yellow black 2) it is compared against a table I created with information from other users that also like colors Name color1 color2 color3 color4 John red white orange brown mary purple magenta green grey Elen white green red blue 3) I need to give Bob the best ma
  21. Hey everyone, I need some help. I tried to reference to record index in calculated field on results page, however it seems that this parameters is available in html block and header footer only. Is there any workaround? I need to multiply record index and rate field. Thanks!
  22. I have a report that has a calculated field in it that is based on a conditional statement. For one of the options, I would like it to multiply a field by the value of an input box. I can't figure out how to do it though. I've tried passing the value as an external parameter in the URL but I can't figure out how to reference that in the calculated field formula. Right now the input field is on the web page that the Caspio iframe is embedded on, but I can also move the input to the header of the form if that would help. Is there a way to do this? I'm using the code below... CASE WHE
  23. Hi, I'm stumped. This code works Substring([@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) + 2,Charindex(' ======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) + 2) This code breaks with only a minor adjustment: changing + to - at the very end Substring([@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))
  24. I'm trying to use a calculated field for a Sum in another calculated field on a tabular datapage (the aggregation won't work for this particular sum, as the sums are for group headers). This is a complicated series of totals so hopefully the process can be followed through. a) I have 2 calculated fields (that are SELECT) to determine some totals and then a 3rd calculated field that 'decides' which one to use for it's own value: Calculated Field:3 CASE WHEN myfield=1 THEN [@calcfield:1] ELSE [@calcfield:2] END I need to now do a Sum
  25. I have a tabular report with search page . In the search page, I send a parameter with a start date on which I seek ( [ @beginyear ] ) to the resulting page . In the Calculated Field in the resultpage i like to have: search date ( [ @ beginning year ] = 1-1-2016 Record: Start date item item [ @field : begin_date_item ] = 5-5-2016 result: Calculated Field = 5-5-2016 [@field: begin_date_item ] Or Record: Start date item item [@field: begin_date_item ] = 12-4-2015 result: Calculted Field = 1-1-2016 ( [ @ beginning year ] Unfortunately, this do
  • Create New...