Jump to content

Search the Community

Showing results for tags 'sql'.

  • 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. Hi, I need to perform a cross join between two tables and I'm looking for any possible way or workaround. Context: I have one user table and one opportunity table, I want to do a cross join between the two tables so I can get one table that contains every user and opportunity pair, then create a calculated column to define a "compatibility score" for each pair. I know this will be a large table and cross joins are generally ill-advised, but I believe this is a valid use case for one. Brainstorming: Cross joins are not an option when creating a view, but not sure if there is some work
  3. Hi all, I need a total, based on the number of records which have the "Selected" checkbox set to true/checked. The calculated value will be displayed in the header of the form which is only shown to the user in GridEdit mode. At the moment the total is static so when the user checks/unchecks the value in the "Selected" column then the total is not changing. I have created three calculated fields to try and solve the issue but have a number of problems as follows: ChkSelected field - this is a SQL query to count the number of records which have "Selected" checked. Total value is
  4. Hello, I'm new to Caspio. I have an Update Form with a calculated field called "Total_Percentage", that takes the values from other fields. Code below: Isnull([@field:PointFactor_Duties_PercentDuty1],0)+Isnull([@field:PointFactor_Duties_PercentDuty2],0)+Isnull([@field:PointFactor_Duties_PercentDuty3],0)+Isnull([@field:PointFactor_Duties_PercentDuty4],0)+Isnull([@field:PointFactor_Duties_PercentDuty5],0)+Isnull([@field:PointFactor_Duties_PercentDuty6],0) What I would like to do is to prevent users from going forward when their total exceeds 100%. I also, would
  5. I’m from a MS Access background. I’ve created an SQL statement to count the number of records that match certain criteria. The statement is reasonably complex but I have made it work in Access. Now I want to find a way of re-creating the same result in Caspio. I’ve searched and searched but I can’t even find where to begin. I can find examples of discussions about use of SQL statements on DataPages so it appears to be possible but I can’t find where to begin. As a really simple example: let’s say I want a DataPage to show the number of records in a given table. I can use th
  6. So I'm trying to get the value that is already in the current points field, do a calculation as depicted in the switch statement and then fill the field in again with the new number before submitting instantly. This is all supposed to be done as a way of replacing an SQL update statement. For some reason my EditRecord or document.getElementById doesn't seem to be working at all. <script LANGUAGE="Javascript"> function calculate(){ var pos = [@pos]; var points = [@field:CurrentPoints#]; switch(pos) { case 1: points += 10; break; case 2:
  7. Hi, I'm trying to to created a triggered action that populates and Audit Log Summary Table (1 record per event - ) and an Audit Log Details Table ( 1 record per changed Field/Column). Workflow is upon Insert or Update to Orders Table want to Log Changes. Trying to keep Log tables as simple as possible. Assume following Table Structures: Orders Table: Accession ID (Primary Key) Editable fields 1 Editable fields 2 Editable fields 3 Editable fields 4 Editable fields 5 Last Modified - Timestamp Last Modified by - User Stamp Audit
  8. Hello, I often use the following SQL code to create a calculated field which concatenates elements of a list with a character separating them (here is it a vertical bar "|"). For instance, here I am creating a list of all the common names for a given plantID (CPCNumber) in the table tblPlantCommonNames. Is there a way to create this code but remove the character from the end of the list? SELECT CAST( CommonName AS nvarchar) + N' | ' FROM tblPlantCommonName WHERE CPCNum = [@field:CPCNumer] for xml path(N'')
  9. 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
  10. Hi, I want to create a ticketing system like caspio support. I have two table T_Ticket: --> ID_TICKET --> Ticket Subject --> Ticket Commentary and T_Detail_Ticket --> ID_DETAIL_TICKET --> ID_TICKET (Foreign key) --> Commentary_DETAIL_TICKET What I need is, when a ticket is created, create with an SQL insert the first detail_ticket commentary with the commentary_ticket Is that possible?
  11. I am seeking assistance for the correct syntax to test that a record exists in a child table (table name is Evaluation) and display a "Yes" in a calculated field (Link Test calculated field in image) on a dp for the parent table. I have very little SQL knowledge. Current SQL syntax that works but is probably incorrect: SELECT 'Yes' FROM Evaluation WHERE EXISTS (SELECT ManagingArea FROM Evaluation WHERE ManagingArea = '[@authfield:Evaluators_ManagingArea]' and NTAID = [@field:NTABase_NTAID]) Use case: 800 proposals, 30+ reviewers, I am creating a dashboard that indicates t
  12. Hi, I am trying to convert my Access database to Caspio database, and am having trouble accomplishing a task that I used VBA to do. I wanted to know if there is a way to populate a Caspio table from javascript inside HTML code in Form page. I am trying to get Opt 1 and Opt2 input, then generate all the variations, for example: Opt1: Red, Blue Opt2: Small, Medium, Large The Variations that I want to populate the table will be: Opt1 Opt2 Red Small Red Medium Red Large Blue Small Blue Medium Blue Large Please advise.
  13. Good Morning, I'm need the sum of a 'paymentdue' field based on the date range (criteria) selected by the end-user. I'm currently using a sql select statement which is providing the grand total on the details page (formatted for use as the invoice). The sql statment is providing the grandtotal without any date criteria. I need the grand total to change (increase amount or lessen amount) based on the end-user's date range selections. Current Formula used in calculated field on details page: select sum(PaymentDue) from Test_Table where Name=target
  14. Caspio minds I have a Tabular report and want to add a button that once the user clicks it will pass information from the tabular report and insert in another table. I saw an example where someone put an insert sql statement on a tabular report that would add values as parameters to another table. But I can't find this example. Any help will be appeciated
  15. 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'
  16. 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
  17. I have a working conditional statement using SQL. However, the output (following the "then" statement outputs plain text. Does anyone know how to force it to be formatted as a clickable link? Here's the full statement: CASE WHEN [@field:connect_PIF_ER_eval_complete]='' then '<a href="participant_evaluation.html?ER_ID=[@field:ER_Events_1_ER_ID]&Ev_Type=[@field:ER_Events_1_Event_Type]">Evaluation</a>' ELSE 'Evaluation Completed' END I'm sure it's obvious, I'm just not sure what needs to surround the href tag. Thanks!
  18. I have a yes/no column (A), another column that is integer data type (B). I'd like populate a third column (C) with yes/no when certain criteria is met. For example, if column A is Yes and column B is greater than X, then column C equals "Yes", else "No". Is this sort of thing possible via a formula field for column C, for example?
  19. Hi everyone, I'm using a detail datapage on which I would like to show some average numbers. I'm using a view for this datapage. Is it possible to use SQL statement on views? Here is my Statement: SELECT SUM(FIDELIS_INTERVIEW) FROM view_fidelis_emp WHERE ID_EMP= [@authfield:ID_EMP] Thanks
  20. Hello, I am going to try to make sense and comprehensible in explaining. Essentially, I am trying to pick the maximum value (per record ID) out of the entire data table with multiple columns, and put this value in each corresponding row for each ID. For some reason, "SELECT ID, and GROUP BY ID" doesn't seem to be working in Caspio. My information is below: The problem with this code is that, it is finding the max alright, but it is putting the same value into each record. How can I correct this so that each record ID will have the right maximum value? For example: ID
  21. Good morning all, I am running in to a bit of difficulty with the reporting and charting features of Caspio right now. I currently have a table that has 21 Yes/No fields that are based on a review process. We would like to have a summary that shows either a count or % of No's in a given month (going off the date created field. Now while an aggregation SHOULD work, it will only allow count, and not even of only No values and it will only allow 5 at a given time and we have 21 fields. Charting has run into the same issue. I can make a calculated field that pulls a count of on
  22. Is it possible to hide entire rows of data in a tabular results page based on a Calculated field Value using JS? Essentially, I have a search and report to return records based on predefined criteria. However, one criteria that I would like to filter by is not stored directly in the table (Table A). It is stored in another table (table B ). So I have a calculated field holding a SQL Query to return a number based on the records from the other table (table B ). I would like to then see if there is a way to hide all records returned that are returned with a Value of 0 from the Table A Tabula
  23. Good morning all, I am currently trying to build a tabular report that will display a certain field from multiple entries next to one another. Now I know how to select the top record and sorting by a certain field to be able to get one. But I am trying to select from a specific entry so that the table would display each appointment time, in order, in one row. Here is what I have: Select EstimatedAppDateTime OVER (Order By EstimatedAppDateTime desc ROWS between 1 and 1 ) Where Job_RID= target.[@field:MM_Job_Record_ID] AND Location_Not_Used_DateTime is null This returns the
  24. I was wondering if it is possible to insert the result of a calculated field back into the data table I'm using as a data source with the SQL INSERT INTO function? I'm using SQL to create a SUM within the calculated field already. This is what I have and it works: SELECT SUM (CASE WHEN [@field:ReturnMaster_File_Type] = 'Federal' AND [@field:ReturnMaster_ACCEPTED] <= '2/15/2015' AND [@field:ReturnMaster_Sent] = '' THEN [@field:InfusionsoftCustomers_a2014_Fed_EF_Fee] WHEN [@field:ReturnMaster_File_Type] = 'State
  25. Hello. I am trying to create a summary report with calculated fields however I am having issues with the report. In the big picture I want to do SQL COUNTS to see if data is in a particular field however I want to group those counts by districts. There are 1202 records and 12 districts. I can not make a summary report that calculates totals for the 12 districts. I am learning as I go but I am trying something new... In one of the calculated fields (at this point I tried a new report with only this calculated field to see if I could get 12 DISTINCT records) I am trying to do a SQL DISTINCT comm
  • Create New...