Jump to content


Caspio Guru
  • Posts

  • Joined

  • Last visited

  • Days Won


Everything posted by CoopperBackpack

  1. Upon further investigation it turned out that is it possible not to specify the field name in thge formula if the Aggregation function is the same for those fields. So, I used this formula and it works correctly for each field: However, if it is needed to use different Aggregation function (Sum, Avg, Count, etc.) and display the result in 1 row (1 Aggregation), then the solution that I described before can be helpful.
  2. Hello @VincenzoCocciolo, I would like to update this thread since I wanted to achieve the same result as in your request. I need to create a Report like this: So, I added 3 Totals&Aggregations fields. I cannot find the option to have only 1 Totals&Aggregations field, since I used custom Formulas (I need to display the sum of values for Sales, Costs, Profit without the Central Region). My formulas have the following syntax: SELECT SUM(Sales) FROM Sales_By_Region WHERE Region_Name !='Central' SELECT SUM(Costs) FROM Sales_By_Region WHERE Region_Name !='Central' SELECT SUM(Profit) FROM Sales_By_Region WHERE Region_Name !='Central' And I tried the following steps: 1) In the Header section I added the CSS code to hide the second and third Totals&Aggregations fields. Also, there is the CSS property to align the values: <style> tr[data-cb-aggregation="Aggregate2"], tr[data-cb-aggregation="Aggregate3"] { display: none; } td.cbResultSetTotalsData{ text-align: right !important; font-weight: bold !important; } </style> 2) In the Footer section I added this JS code: <script> document.addEventListener('DataPageReady', function (event) { let totalsSecond = document.querySelector('tr[data-cb-aggregation="Aggregate2"] td:nth-child(2)').innerHTML; //select the value from the Aggregate2 let totalsThird= document.querySelector('tr[data-cb-aggregation="Aggregate3"] td:nth-child(2)').innerHTML; //select the value from the Aggregate3 document.querySelector('tr[data-cb-aggregation="Aggregate1"] td:nth-child(3)').innerHTML = totalsSecond; //assign the value of the Aggregate2 to the Aggregate1 row document.querySelector('tr[data-cb-aggregation="Aggregate1"] td:nth-child(4)').innerHTML = totalsThird; //assign the value of the Aggregate3 to the Aggregate1 row }); </script> Maybe you will need to change the element order number in selectors like td:nth-child(2), etc. 3) Here is the result: Also, I want to add that I need a Report without the Search option, so this solution works fine. However, there can be some nuances with the Search to display the correct result. Hope this helps. And maybe someone has more ideas.
  3. Hello @lamarh, Since you are using 'EditRecordField_name', I understand that this is the Details or Single Record Update DataPage. The code looks fine except for the quotes in the attached file. For example: This code works on my side: <script> document.addEventListener('BeforeFormSubmit', function(event) { const docentID = document.getElementById('cbParamVirtual1').value; const leadbox = document.getElementById('cbParamVirtual3').checked; const assistbox = document.getElementById('cbParamVirtual4').checked; if (leadbox) { document.getElementById('EditRecordLead_Docent').value = docentID; alert("you are signed up to lead"); } else if (assistbox) { document.getElementById('EditRecordAssist_Docent').value = docentID; alert("you are signed up to assist"); } }); </script> Did you check errors in console?
  4. Hello @wimtracking2, I apologize for not responding earlier, unfortunately, had no time to look into the inquiry. I agree with TellMeWhy's suggestions. Please test the suggestions and update this thread in case you have any questions.
  5. Hi @Joemac, I am sorry for the late reply. As far as I know, custom formulas are not applicable for Subtotals (applicable only for Totals ) This is why you see the same result as Total and as Subtotals. This is the expected behavior at the moment. As an option, you may suggest new features here http://ideabox.caspio.com/
  6. Hello @Joemac, 1) You may test this formula: CAST( CAST((SELECT SUM(field_name) FROM Table_name) AS int) / 60 AS nvarchar) + ':' + right('00' + CAST(CAST((SELECT SUM(field_name) FROM Table_name) AS int) % 60 AS nvarchar(2)),2) + ' Hrs' The output is, for example: 0:20 Hrs 1:25 Hrs Please replace field_name and Table_name with your local names. 2) Another option is: CASE WHEN (SELECT SUM(field_name) FROM Table_name) < 60 THEN CAST((SELECT SUM(field_name) FROM Table_name) as nvarchar) + ' minutes' WHEN (SELECT SUM(field_name) FROM Table_name) >= 60 AND (SELECT SUM(field_name) FROM Table_name) <1440 THEN CAST( CAST((SELECT SUM(field_name) FROM Table_name) AS int) / 60 AS nvarchar) + ' hours ' + right('00' + CAST(CAST((SELECT SUM(field_name) FROM Table_name) AS int) % 60 AS nvarchar(2)),2) + ' minutes' END The output is, for example: 50 minutes 3 hours 05 minutes Hope this helps.
  7. Hello @wimtracking2, I am afraid that I didn`t get the idea. So, on the initial load you want the records to be displayed that have values "Bozeman", "Belgrade", "Livingston" for the City, and there is another Search field that also should have preselected values. Could you provide more details, please?
  8. Hello @Benades, I agree that it is easier to prevent submission of the duplicated values than to delete them. Since this post is about deleting or avoiding duplicates, I would like to share the solution that I used in my account to delete duplicated records. Maybe it can be helpful for someone. The main point here is to determine what is a duplicate. Lets` say this is the initial table: 1) For example, we need to remove all record with the duplicated names. I used a Task with the following design: The table after the Task run: 2) For example, we need to remove all record with the duplicated names and emails. In this case, the Task is the following: The table after the Task run: So, the idea is to have a some unique field in the table (in my example it is the ID field). Then in the GROUP BY Statement we need to specify all fields where you want to check the duplicates. If the ID field is an Autonumber then using the MAX function we leave the record with the maximum ID value. It is also possible to use the MIN function to leave the record with the minimum ID value.
  9. Hello @Peterson, Please try the following: 1) Add the span or div element with the ID to the DataPage Header. For example: <span id='edit_link'></span> 2) Add this code to the DataPage Footer <script> document.addEventListener('DataPageReady', function(event){ const linkElement = document.getElementById('edit_link'); //save the element form the Header to the variable if("[@authfield:Editor^]" === "Yes"){ linkElement.innerHTML = '<a href="https://domain.com/edit_page?id=[@id]">EDIT</a>'; //change the innerHTML property for the element } else{ linkElement.innerHTML = '<span>Only authorized employees are allowed to edit their company information.</span>'; } }); </script> Feel free to update this thread if you have any further questions.
  10. Hello @Maria, Since SQL syntax is supported in the Formula field, please use the SQL CASE statement instead. The syntax should be like: CASE WHEN Len([@field:Text]) < 2 THEN '0' + [@field:user_email] WHEN Len([@field:Text]) > 2 THEN '1' + [@field:user_email] ELSE [@field:user_email] END The ESLE part is optional. The important note: each THEN/ELSE should return the value of the same data type. You may refer to these articles: https://howto.caspio.com/function-reference/ https://howto.caspio.com/tables-and-views/data-types/formula-fields/ Hope this helps.
  11. Hello @Joemac, As I got your inquiry you want to timestamp the record only when a specific field with Yes/No data type is checked. It is possible with Triggered Actions. 1) If you need to set Timestamp when the record is inserted, then the Trigger design can be like this. The sample table: 2) If you need to set Timestamp when the record is updated, then the Trigger design can be like this. Feel free to update this thread in the case you have further questions.
  12. Hello again @ChrisSayers, Since the Free plan doesn`t include the Task feature, while you are using the Free plan, you may create a View that filters the records based on the condition: Then you may create an editable Report using this View as a data source and check the records that are displayed on the Report every date/edit them.
  13. Hello @ChrisSayers, Yes, as PotatoMato replied it should be possible with the Tasks. You may create a Task and schedule it to run every day. In the case of the following Task design you will receive as many emails as there are records that meet condition in your table: To receive 1 email per day with the list of records, you may use Table Variable: Feel free to update this thread in case you have further questions.
  14. Hello @kpcollier, I tested the same DataPage design in my account and you are correct, in your example we need to hide the 2 last cells in the row with the Totals. In DevTools the layout has the following view: Basically, as I checked, the cbResultSetTotalsDataCell CSS class is used only for the Totals. However, we can use a more complex selector to refer to the required cells. tr[data-cb-name='grand_total'] td:nth-of-type(3), tr[data-cb-name='grand_total'] td:nth-of-type(4) {display: none;}
  15. Hello @kpcollier, I suggested a solution for the Report that was provided in the screenshot. The JS solution should work if the Totals are for the field that is located after the hidden fields. Lets` say, you have 10 fields on the Result page, need to hide fields 5 and 6, and Totals are for field 9. In this case, Totals will not be aligned correctly and you may use the JS code to change the colspan value. May I know which fields are hidden and whether the Totals are for the last field in the Report as I can see from your screenshot?
  16. Hello @BGrambo, As I understand, you receive this error in the table, in the Formula field. Please note that SELECT statements are not allowed in the Formula field. Basically, Formula works within one record. You may want to check more information about the Formula field https://howto.caspio.com/tables-and-views/data-types/formula-fields/ In case you need to get and store the values from the other tables, you may use Tasks or Triggered Actions. https://howto.caspio.com/tables-and-views/triggered-actions/ https://howto.caspio.com/tasks/
  17. Hello @Sam23, It looks like you need to enable the "Enable cross-app login option" that is available in the Advanced setting of the Authentication. Please check this article https://howto.caspio.com/authentications-and-connections/authentication/authentication/ Enable cross-app login – With this option you can enable cross app login to users who already have an active session with another one of your apps that uses the same users table for its authentication. For example if your ticketing system and your sales system are based on the same Employees table, but different authentications object, authorized users who are signed into one app can access the second app without having to login again.
  18. Hello @Hayleyy, Since you need to store the answers, you may try the following: 1) Use a real field from the table to submit answers. For example: 2) Add the Virtual field, set it to the Calculated Value and use the CASE statement there. When the field is set to the Calculated Value it is possible to hide it by checking the "Hide Field" checkbox on the Advanced tab. You may learn more about the CASE statement in this external article if needed https://www.w3schools.com/sql/sql_case.asp 3) And again set the "Destination after record submit" as a Virtual field: Feel free to update this thread if you have further questions.
  19. Hello @Hayleyy, 1) If the video files are stored in the Files section of your Caspio account, you may use the <video> tag inside the HTML block. For example: <video width="320" height="240" controls> <source src="[@field:Video_file/]" type="video/mp4"> Your browser does not support the video tag. </video> Also, you may firstly host a video in the hosting site, for example, YouTube, copy the video embed code and paste it to the HTML block. For both options, there is an issue with iOS devices that cannot render the video correctly and the issue with Safari browser. 2) It is possible to redirect the user depending on the selected answer. The solution is described in this article https://howto.caspio.com/tech-tips-and-articles/common-customizations/dynamic-redirects-after-form-submission/ This should work for Radio Buttons, Dropdown, Listbox Form Elements. The idea is to display the answers (in your example) and use a URL to redirect as a Value: Then use this field as a Destination after record submit:
  20. Hello @innov2e, This should work with document.getElementsByName. Please note that it returns not a single value, but the NodeList Collection of elements, so to get the value you need to use [0] index: document.getElementsByName('cbParamVirtual1')[0].value So, you may try this code: <script> document.addEventListener('BeforeFormSubmit', function(event) { let virtualField = document.getElementsByName('cbParamVirtual1'); document.querySelector('#EditRecordFieldAA').value = virtualField[0].value; }); </script>
  21. Also, I wanted to add, that if you set the field that has Date/Time data type to the Calculated Value, you may use the Formatting options. You may select the format from Localization or set the Custom format: These articles can be helpful: https://howto.caspio.com/localizations/formatting-options/ https://howto.caspio.com/datapages/datapage-components/field-formatting-options/
  22. Hello @JKSGT, It looks like the issue is related to the data type you use in the CONVERT() function. Please try to convert to the datetime, so the formula have the following syntax: CASE WHEN DateDiff(day, SysUTCDateTime(), [@field:Date_1]) > [@cbParamVirtual4] THEN DateAdd(day, [@cbParamVirtual4], CONVERT(datetime, '[@cbParamVirtual1]', 103)) ELSE [@field:Date_1] END
  23. Hello again @innov2e, This should work for the Details page as well, and you may refer to the editable fields as "EditRecordFIELDNAME" (more details can be found in this post https://forums.caspio.com/topic/4377-js-guide-caspio-form-elements/) Single Record Update form and Details page display 1 record per time and you may set the fields as editable on both of them. If you pass some value as a parameter, I believe you need to create separate Results and Details https://howto.caspio.com/tech-tips-and-articles/tech-parameters/results-and-details-on-two-separate-web-pages/ You may provide with more details about the Details page if needed, so I can test it from my side.
  24. Hello @innov2e, Please try to use this code in the Footer section of the DataPage. Do not forget to disable the HTML editor before pasting the code. <script> document.addEventListener('BeforeFormSubmit', function(event) { let virtualField = document.querySelector("#cbParamVirtual1"); document.querySelector('#EditRecordFieldAA').value = virtualField.value; }); </script> Please update this thread in case you have further questions.
  25. Hello @captnjames, As I see, you have a list of checkboxes, and if you need to divide them into several columns, there is an option for this within the section. You may select the number of columns and fill order: This article can be helpful https://howto.caspio.com/datapages/datapage-components/multi-column-and-sections/ Hope this information helps to receive the expected result.
  • Create New...