Jump to content

Search the Community

Showing results for tags 'SQL'.

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



Found 21 results

  1. 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 to a reviewer if they have or have not reviewed (child table) a proposal (parent table). Unique identifies are: [@field:NTAID] in the parent table, [@field:NTAID] and [@Managing Area] in the child table. A view with the parent and child does not work as either inner join (all 800 proposals need to display) or left join because duplicates occur as soon as a proposal has more than 1 review, which is appropriate and expected. Solution is a dp of the parent table only and use a calculated field to verify if a review exists in the child table. I searched high and low on the web and the Caspio forums for similar syntax without luck. Suggestions for a more appropriate approach are welcomed! thank you Forum folks for your time!
  2. 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.
  3. 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: points += 8; break; case 3: points += 6; break; case 4: points += 4; break; case -2: points += -2; break; default: points += 2; } document.getElementById("EditRecordCurrentPoints").innerHTML = points; } document.getElementById("mypage").onsubmit=calculate(); </script> There is already a <div id="mypage"> in the header and a </div> in the footer. Am I just being very stupid?
  4. 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?
  5. 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.[@field:Name] group by Name Tested the following statement which is not grabbing the date information selected from the caspio form thus returns the grand total w/o date criteria again: SELECT sum(PaymentDue) FROM Test_Table WHERE Start_Time >= [@field:Start_Time] AND End_Time <= [@field:End_Time] Is there anyway to have a details page pull the 'paymentdue' from the table based on the date range searched on the Caspio search form (I configured the details page to allow users to select data using the search form? I essentially need an aggregation function on the details page itself. I understand that this information cannot be saved...I'd just like for the grand total data to dynamically display on the invoice based on the end-user's selection. As always, I greatly appreciate any feedback that can be provided. Thanks!
  6. 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
  7. 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
  8. 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.
  9. Clint

    SQL with an HTML Output

    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!
  10. 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?
  11. JulienMoulis

    SQL on View

    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
  12. 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?
  13. 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 123 = $97,900, ID 456 = $83,820 ID 789 = $59,730 Table name: market_data ID 123 456 789 PayscaleAverage $89,000 $76,200 $54,300 ERI_Median $97,900 $83,820 $55,321 Miliman_Avg $91,670.00 $78,486.00 $59,730.0 Here is the SQL Query: I am using UNPIVOT function. SELECT MAX(highestdata) AS maxnumber FROM market_data UNPIVOT (highestdata FOR Val IN (PayscaleAverage, ERI_Median, Miliman_Avg )) AS Number WHERE ID = [@field:ID_Lookup] I appreciate any help. Thank you in advance. P.S. Please, if you could, please do not recommend conditionals, such as, "WHEN Col1 < Col2 then..." because this table has over 200 columns.
  14. DataCobalt

    Summary Report For Many Yes/no Fields

    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 only no values per field, but I haven't found a good way to make this calculation apply to only dates in a given date roll up month (I do really like the date roll up grouping) without having to manually apply date restrictions and even then it would only work if I wanted to pull one month at a time. I am hoping others have run into similar issues with Caspio's aggregation and charting and have found workarounds. If anyone is creative it is this community! Any an all help would be much appreciated. Thank you!
  15. 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 Tabular report. Is this possible? Additional Information: For several reasons, of which I don't want to waste your time or over-complicate the question, using views to combine the tables in a manner to accomplish is not desired. Nor would a solution dependent on Sorting or Grouped results be sufficient. I really would like to make these results entirely invisible/inaccessible. Thanks in advance for all of your time and assistance. Cooper
  16. 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 error that the syntax of the OVER function is incorrect. Any ideas? Alternatively, if you have another way to easily display the records in one row I would be very appreciative. Thanks in advance!
  17. 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' AND [@field:ReturnMaster_ACCEPTED] <= '2/15/2015' AND [@field:ReturnMaster_Sent] = ''THEN [@field:InfusionsoftCustomers_a2014_State_EF_Fee] ELSE 0 END) FROM _v_BillingView where ReturnMaster_EFIN = target.[@field:ReturnMaster_EFIN] I'm not super familiar with SQL but I was thinking it might be possible. I was trying something like this: INSERT INTO BillingView (InfusionsoftCustomers_AmountBilled2) VALUES (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' AND [@field:ReturnMaster_ACCEPTED] <= '2/15/2015' AND [@field:ReturnMaster_Sent] = ''THEN [@field:InfusionsoftCustomers_a2014_State_EF_Fee] ELSE 0 END) FROM _v_BillingView where ReturnMaster_EFIN = target.[@field:ReturnMaster_EFIN]) Of course this comes back as Invalid. Since I'm not familiar with SQL I wasn't sure if my syntax was wrong or if it just ins't possible within Caspio. Any insight would be greatly appreciated.
  18. 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 command that only pulls the 12 DISTINCT names from the District column in the table. I am not sure if I am missing something in caspio to make this work... here is what I have: SELECT DISTINCT [@field:District] FROM tbl_Charter_Base when I do this in caspio it pulls every record in the 'District' column to total 1202 records instead of pulling only the DISTINCT records (12) any ideas? Not sure what I am missing... THANKS for YOUR HELP!
  19. I Have tried to get support but they are not responding to my ticket and I have been struggling with this very simple task for a report. I need to create a calculated field that counts the number of fields with data in a column by district - do not count NULL fields. In normal SQL, you can use a count function which does not include NULL fields but this counts everything for some reason. Not sure what I am doing wrong but here is the statement which should pull the count of fields with data but instead counts all the fields by distrcit.. ANY help would be appreciated... select count(Dropping_Unit) FROM CH_tbl_Base WHERE District=[@field:DistrictName] THANKS
  20. Hello all. I am looking to create a view that pulls from two tables, however my caveat is that I am looking to pull random records from the second table. I would like to know if it is possible to create more robust views by directly manipulating the SQL behind it. Thanks, Mack
  21. I am trying to make a SQL calculation in a HTML data page, to display the sum of points attained by the logged-in group member. My SQL statement, in a calculated field, is: select sum(Points) from Activity_Table where Activity_Table.Member_ID = [@field:Member_ID] If I manually set the member_ID field in the formula (e.g. Activity_Table.Member_ID = 103), the formula works correctly, but when I try it as shown above my result is the sum of ALL points regardless of member. I am assuming that there is some problem passing the field value correctly, but I can't figure it out. Anyone have any thoughts? Thanks!!