Jump to content

Search the Community

Showing results for tags 'calculated field'.

More search options

  • 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






Found 30 results

  1. I can color the background on input fields, but not having luck on calculated fields. Also want the amount in bold.
  2. 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'?
  3. 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 hidden Virtual Field and trigger the SQL with the following code: <script> document.getElementById('caspioNote').onmousemove = function() {myFunction();}; function myFunction() { var i = document.getElementsByName('InsertRecordField1')[0].value; document.getElementsByName('cbParamVirtual2')[0].value = i; } </script> ------ SQL for VirtualField1: SELECT Field_Name FROM Table WHERE [@cbParamVirtual2] = Field_ID The JavaScript works fine but the SQL does not seem to notice the changes in VirtualField2 This happens to other cascading fields too when I use VirtualField2 as the Parent Field. How could I archive this? Thanks in advance
  4. 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 submitted. Any help would be appreciated!
  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 error near "(", which I take to meant hat the STRING_AGG function does not exist. Is there another function I could use to accomplish this?
  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.getElementById('section1').style.display = "none"; document.getElementById('section2').style.display = "block"; } </script> I've used this code before with a regular table field: how is the calculated field referenced where EditRecord is (EditRecordcalcfield18 obviously isn't right)? I don't know the syntax. Any help would be great- thanks in advance.
  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-2014 10:00:00 | LogoutDT 8-17-2014 10:40:00 SessionID = 3 | LoginDT 8-17-2014 11:00:00 | LogoutDT 8-17-2014 13:30:00 Desired Result: SessionID = 1 | Logged Hours 0.25 SessionID = 2 | Logged Hours 0.67 SessionID = 3 | Logged Hours 2.50 Datediff(hour,[@field:LoginDT],[@field:LogoutDT]) SessionID = 1 | Logged Hours 0.00 SessionID = 2 | Logged Hours 0.00 SessionID = 3 | Logged Hours 2.00 Datediff(minute,[@field:LoginDT],[@field:LogoutDT]) SessionID = 1 | Logged Hours 15.00 SessionID = 2 | Logged Hours 40.00 SessionID = 3 | Logged Hours 150.00 Datediff(minute,[@field:LoginDT],[@field:LogoutDT])/60 SessionID = 1 | Logged Hours 0.00 SessionID = 2 | Logged Hours 0.00 SessionID = 3 | Logged Hours 2.00 (Datediff(minute,[@field:LoginDT],[@field:LogoutDT]))/60 SessionID = 1 | Logged Hours 0.00 SessionID = 2 | Logged Hours 0.00 SessionID = 3 | Logged Hours 2.00 Round(Datediff(minute,[@field:LoginDT],[@field:LogoutDT])/60,2) SessionID = 1 | Logged Hours 0.00 SessionID = 2 | Logged Hours 0.00 SessionID = 3 | Logged Hours 2.00 Round((Datediff(minute,[@field:LoginDT],[@field:LogoutDT]))/60,2) SessionID = 1 | Logged Hours 0.00 SessionID = 2 | Logged Hours 0.00 SessionID = 3 | Logged Hours 2.00 Any ideas? Thanks!
  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 updated SELECT COUNT (DISTINCT [@field:Card_ID]) FROM Card_comments_and_updates WHERE Week=target.[@field:Week] Total number of updates SELECT COUNT([@field:Card_ID]) FROM Card_comments_and_updates WHERE Week=target.[@field:Week]
  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_and_updates WHERE IsNull([@field:QA_Tester_RatingX],0) >0 AND User_ID=[@field:Analyst_IDX])),0) I want it to display zero if it can't calculate anything! Basically my datapage displays all my users along with an average of all the star ratings they have. The calculated field looks in a different table for every instance where the user ID in this table is listed in the Analyst column. Then it checks the 2 different fields where ratings are given (QA Tester Rating and Optimiser rating) for values more than 0. Adds them together and then divides them by the count, thereby giving the average. For the most part it works fine ... EXCEPT where there are fields where either the QA Tester Rating or Optimiser Rating are zero. Help! Many thanks Nikki
  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 for the field and/or calculated field that should normally so none. Thanks for any help.
  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 = "Suite 33" Calculated Field 4 = "Atlanta, GA 30308" There will always be a <br /> to break up the lines so I was thinking that might be used in the formula to delineate which lines are which. I have no idea of what the formulas would look like, though. Does anyone know how to do this? Many thanks!
  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' and 'RateAmount'. For example: Table Name: Invoicing (Columns): RecIDL RateAmount (Vaules): L1 $100.00 L1 $100.00 L2 $150.00 L3 $175.00 I can aggregate totals and group by the RecIDL using the aggregate caspio field; however I need this information to pass to the actual invoice so a calculated field is required. I'd like to select the 'RecID column' and sum the 'RateAmount' and then group by the 'RecID.' I've tried the following select statements. SELECT RecIDL, SUM(IsNull(RateAmount,0)) FROM Invoicing_Loads GROUP BY RecIDL (*Expression Error Msg: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. ) SELECT RecIDL, SUM (RateAmount) FROM Invoicing GROUP BY RecIDL; (*Valid Formula per Caspio verify: When html page is opened "error in formula" message received; unable to view webpage) SELECT SUM(RateAmount) FROM Invoicing_Loads WHERE RecIDL = target.[@field:RecIDL] Group by RecIDL (*Statement is grouping the RateAmount for each RecIDL. The amount is appearing numerous times on the report; the total is correct.) This is what is showing: (The total should be $200.00 for L1) (Columns): RecIDL RateAmount (Vaules): L1 $200.00 L1 $200.00 L2 $150.00 L3 $175.00 This is what I need to see: Total Aggregations for each RecIDL; preferably at the bottom of each RecIDL group. (Columns): RecIDL RateAmount (Total L1): L1 $200.00 (Total L2) L2 $150.00 (Total L3) L3 $175.00 I'm not a strong sql user so I'm not certain what the "expression" error is speaking to. Based on the knowledge that I do have I believe it should work. I have searched the forums but could not locate any information specific to my need. I'd be grateful for any assistance provided. As always, Thank you! Bre
  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 match with the other users of the database. On the report I search by "red" OR "blue" OR "yellow" OR "black" at the same time Table of best matches for Bob (this would be the report) Name "Hits on search" Elen 2 John 1 I hope to be able to do this with a calculated field on my report ("hits on search", on the example) and sort by it. Somebody suggested to use COUNT, but that counts vertically across users, if I understood correctly: (times "red" showed up under the color1 column, for example) Any idea would be appreciated. Thanks V.
  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 WHEN [@field:Per]='lamp' THEN [@field:Per] * 2 WHEN [@field:Per]='fixture' THEN [@field:Cost] * EXTERNALPARAMETER ELSE 0 END
  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])) + 2,Charindex(' ======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) - 2) This code is a substring in example two that works until I add it to the code above Charindex(' ======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) - 2 Any help will be appreciated
  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 for calculated field 3. I've tried a lot of syntax but cant' get it to work (again, I can't use the standard aggregation). Does anyone know how to do this?
  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 does not works. It seems that the parameter of the search page is not working with the proper date format , somebody ideas CASE WHEN [@field:begin_date_item]<[@beginyear] THEN [@beginyear] ELSE [@field:begin_date_item] END
  • Create New...