Jump to content

Search the Community

Showing results for tags 'sql'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Caspio Bridge
    • Caspio Apps for Ukraine
    • 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. 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 workaround for this? From research it doesn't look like there are any ODBC connectors that could be used by some sort of SQL query tool to query the tables with a cross join and then load the data back into Caspio. It would probably be possible to use the API to pull data out then use another tool to perform the cross join and load the data back in, but this seems a bit clunky and inefficient. Don't know if I've missed anything obvious but any ideas, suggestions, or feedback would be great! Thank you!
  2. Hello caspio universe! I came across an issue and wanted to see if someone else had figured this out. I'd like to embed SQL select statements within a case statement so that a calculated field can execute a different SQL select statement based on the case conditions. In psuedo code: Case When [@Virtualfield1] = 'Option1' then Select count(Column1) from tbl1 Else Select count(Column2) from tbl1 End Anyone else find a way to embed a calculation (or SQL statement) within a case statement? My objective here is to run different SQL queries based on the case statement. Thanks for your advice!
  3. I have a formula field which calculates a number value perfectly but I'd like to add a letter to the front of the number so I assume that means I need to cast my result to a string. Here is my formula right now: SELECT MAX(Clean_Job) FROM tbl_ggl_job WHERE Job_Letter = 'CC') + 1 Which gets the max job number that is currently in use and adds 1 to that. So if the max job is CC100 this would return 101. I'd like to concatenate this with the Job Letter CC so that the result would look like CC101. How can I do this?
  4. 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 the Aggregation tools to achieve what I need but I can’t find out how to actually view the SQL statement behind the scenes. Can anyone assist? James
  5. 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!
  6. 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?
  7. 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'')
  8. Comments on your script or code can be helpful specially when it gets complicated and larger. This helps you to mark different parts of your code which in turn will help you debug them. I compiled a list of comment tags that can be used on every part of your DataPage whether it's for a calculated field, formula data type, JavaScript, CSS, or HTML. HTML comment tag: <!-- this is 1 line of comment --> or <!-- these are multiple line of comments--> CSS comment tag: /* This is 1 line of comment */ or /* These are multiple line of comments */ JavaScript // single line of comment or /* This is 1 line of comment */ or /* These are multiple line of comments */ SQL -- Singe line of comment or /* This is 1 line of comment */ or /* These are multiple line of comments */
  9. Hello Caspio community! I have a little idea but i can't make it work, is something like this: CASE WHEN EXISTS(SELECT name FROM myTable WHERE name = target.[@field:name]) THEN 1ELSE 0END My problem i get the "Not records found" of Caspio instead of 0 and i don't know where i'm mistaking. In the future I pretend to count how much tickets a client have and show it, if that client never create a ticket is not going to be in myTicketsTable yet for obvious reasons
  10. 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!
  11. 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!
  12. I'd like to make the label on my datapage display the current month. Is there a way I can do that?
  13. 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?
  14. 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
  15. I'm trying to find a function that will take a string and output only the numbers within that string. My string field values look something like this: C-123R C-1 Ladders R-32L where the beginning of the string is always 2 characters before the numbers start. But, the numbers can be either 1-3 digits long, and the chars after the numbers can vary greatly. I've tried a few different things like this and this but wasn't able to get it going. Any help is appreciated.
  16. 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.
  17. 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
  18. 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 correct but it is not changing when the user checks/unchecks records in GridEdit mode. Total Selected field - this is case statement to show if each record has "Selected" checked or not. The value is correct but when I display it in my header (via field selector) then it only shows the value for one record. Aggregate field - this sum Total Selected and is set to display above the data but it is not appearing. I'm not sure if that is because I am using a GridEdit form or due to another reason. I have attached two documents to show what is currently happening. Document 1 - this shows the datapage when it is loaded. The ChkSelected total is correct and the value for Total Selected is correct for each record. The Aggregate field is not showing Document 2 - this is after checking a record (and navigating to another record to force the update in GridEdit). The ChkSelected in the table is correct for the updated row but the total in the Header has not changed. The Total Selected value for the updated record is correct. I would like the ChkSelected total shown in the header to be recalculated & updated when the user checks/unchecks the "Selected" checkbox. I suspect I will need JavaScript fired when the checkbox state changes but I don't know how to "force" the SQL field to recalculate. This would then allow me to have additional checks/formatting based on the total of ChkSelected as I don't believe that "aggregated" fields can be used for this purpose as they do not have IDs (when you inspect the page). I would appreciate any suggestions on how to resolve the issue. TIA, Craig
  19. 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 like to show them, which field(s) is/are causing this field to go over the threshold of 100%. The rules section is not conducive to this goal, as rules section is limited. Triggered action section forces me to either email or text the users if their total exceeds 100%. I don't want to do this, since the whole point is for users to correct errors immediately. Is there anyone out there who can impart me a script or two to help me? SQL or javascript that would work with Caspio. Thank you so much. Emme
  20. 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?
  21. 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 Log Summary Table: Summary ID (Primary Key) Accession ID(Foreign Key from Orders) Modified by User Modified Date Change Type (I, U, D) Audit Log Details Table: Audit Log Detail ID (Primary Key) Summary ID (Foreign Key from Summary Table) Column Name( Field that changed in orders table) Old Value New Value I know how to create the trigger to insert Accession ID, Modified by User, and Modified Date into Summary table upon Insert, Update, or Delete. I'm not certain how to stamp the change type field - based on Insert, Update or Delete. What I'm really struggling with is next step. For each event, how do I identify Fields that changed, and insert changes into details table? An would I be nesting this into original trigger in Orders table, or creating a new trigger in Summary Table? Pasting 1st part of trigger for reference. Any help or guidance greatly appreciated...
  22. 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?
  23. 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!
  24. 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.
  25. 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
×
×
  • Create New...