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


Forums

  • 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

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


MSN


Website URL


ICQ


Yahoo


Skype


Location


Interests

  1. I want a calculated field formula that can return the date in MM/DD/YYYY format. How can I get this?
  2. I have a calculated field in my datapage to find the biggest "Clean_Serial_Number" under a particular Job_ID. There are many serial numbers under each job so I want it to find the max serial number under JUST that job. Right now, this field is just displaying the max "Clean_Serial_Number" from all the data, not just from the Job_ID. SELECT MAX(Clean_Serial_Number) FROM tbl_ggl_job_lines WHERE Job_ID = [@field:Job_ID] What am I doing wrong?
  3. I'd like to add some HTML to my Datapage to conditionally change the color of a report's background and text color based on the value of a calculated field. This field finds the number of days left until a project is due. I'd like to change the text color of this field to red and the background to yellow if it is past due (if the days left are negative). I know there are a couple of other pre-existing threads on this topic but for some reason I am having no luck. I pasted these in the source of my header but they had no effect. Let me know if you see anything wrong with my formulas: //for the background <a id="visi[@calcfield:1#]"> <script> var isi = document.getElementById("visi[@calcfield:1#]"); if(isi < 0){ isi.parentNode.parentNode.style.backgroundColor = 'red'; } else{ isi.parentNode.parentNode.style.backgroundColor = '#NoColor'; } </script></a> //for the text <div id="mydiv[@calcfield:1#]"> </div> <SCRIPT LANGUAGE="JavaScript"> if ("[@calcfield:1#]" < "0"){ document.getElementById("mydiv[@calcfield:1#]").innerHTML ="<span style='color:red;'> [@calcfield:1#] </span>"; } </SCRIPT>
  4. BLUF: does anyone have any suggestions on how to make a submission form's rules engine read the values from a virtual calculated field? i am using virtual calculated fields in a submission form to query five boolean variables in another table which specifies which of the five fields in the form should be required (this is customizable by the user through a separate form). the queries successfully execute and i am getting expected results (i.e. 'Yes' and 'No' for each of the five variables), which are based on the user's selection of a profile in a dropdown in the same form (e.g. profile a, profile b, profile c, which, again, they configure through another form). the queries are dynamic...they correctly recalculate as the user changes their 'profile' selection. i've attached a screenshot of the interface. i need to use these virtual calculated fields ('Yes' and 'No') values in the form's rules so that i can specify which of the fields are mandatory based on the selected profile. though the rules engine seems to be able to distinguish between 'is blank' and 'is not blank' on the virtual fields, it seems incapable of reading the values. that is, i have tried "virutal field 1 is equal to Yes" (or 'yes' or 'true' or 1)....and nothing works. does anyone have any suggestions on how to make the rules engine read the values from a virtual calculated field? p.s. for the form submission, i am using a view which joins the profile table with the test entries table. however, since only the test entries are editable in the view, i don't have access to the profile values (even though they are duly joined through the view) and therefore can't use them directly in the rule. this is why i have had to resort to using virtual calculated fields with sql queries.
  5. I can color the background on input fields, but not having luck on calculated fields. Also want the amount in bold.
  6. 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'?
  7. 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
  8. 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!
  9. 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
  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, 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.
  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 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!
  15. 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.
  16. 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
  17. 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.
  18. 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?
  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. 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!
  21. 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
  22. 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
  23. 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?
  24. 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.
  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...