Jump to content


Caspio Guru
  • Posts

  • Joined

  • Last visited

  • Days Won


Everything posted by CoopperBackpack

  1. Hello @Alessandro, Please test the formula using the SELECT statement. For example: (SELECT SUM(Field_1) FROM Table_Name) /(SELECT COUNT (DISTINCT Field_2) FROM Table_Name) Please replace field names and table name with your local names. And please note that if you use a View and not the table, you need to use _v_ prefix before the View name. You may want to check this article https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/ Hope this helps
  2. Hello @Alessandro, Could you specify the formula you need to apply and the DataPage type where you want to apply this formula? Basically, if you need to get data from 2 different tables, you may use a SELECT statement For example: SELECT Count(Country) FROM Customers WHERE Country=’Canada’ You may want to check this article https://howto.caspio.com/function-reference/ If you use different tables to build these 2 DataPages, you may join them in the View and use this View to create a DataPage https://howto.caspio.com/tables-and-views/what-are-views/creating-a-view-to-join-tables/ I need to have more details to provide you with a formula.
  3. Hello @kpcollier, As far as I know, the records are counted as affected records even if Task/Trigger "scan" them. Let`s say, you have 10 000 records as Max Task/Trigger records in your plan. And, for example, in Table A there are 15 000 records, and the Task should move only 100 records to Table B. You will receive this error: 2083 - The number of affected records by this action exceeds the number of records Tasks can process at once. Please reduce the number of records affected and try again. So, a usual workaround is to add an additional condition in the Where clause to limit the number of records as described in this post:
  4. Hello @ronbrumbarger, If my understanding is correct, then the workflow is the following: 1) For example, I am logged in as a user with UserID = 1. 2) In the first record we can see that I sent $100 to the user with UserID = 2 3) In the second record we can see that the user with UserID = 3 sent $250 to me. If this description is correct and you need to display the value as Total, then you may add Totals&Aggregations in a Report DataPage, set it to 'Formula' to use your custom formula: The idea is to sum all the Amount values from the table where CreditID = logged in UserID and subtract the sum of all the Amount values from the table where DebitID = logged in UserID. Please use your fields/table names. You may also apply formatting to the result. IsNull((SELECT SUM(Amount) FROM Bank_balance WHERE CreditID = '[@authfield:ID]'),0) - IsNull((SELECT SUM(Amount) FROM Bank_balance WHERE DebitID = '[@authfield:ID]'),0) Hope this is the expected result.
  5. Hello @Guasho, First of all, please note, that in Triggers you can use the fields from the tables. Also, you may use Functions foк different data types: Text, Number, Date. You may check available functions under each section. For example: You may want to check this article https://howto.caspio.com/tables-and-views/triggered-actions/ Maybe you can use the Formula in the table instead https://howto.caspio.com/tables-and-views/data-types/formula-fields/ Since everything depends on the expected result, you may clarify what exactly you try to achieve.
  6. The second solution is to to use the momentjs library that is available via the link https://momentjs.com/ For example, you may open moment.min.js. , copy the code: And paste it to the Value section of the App parameter: The code in the Footer section: <script> [@app:moment_min_js] // app parameter document.addEventListener('DataPageReady', function (event) { //Select the Criteria input fields const dateFrom = document.querySelector('#Value1_1'); const dateTo = document.querySelector('#Value1_2'); //Set dates const today = moment().format('L'); const threeMonthsPrior = moment().subtract(3, 'months').calendar(); //Assign values dateTo.value = today; dateFrom.value = threeMonthsPrior; }); </script> ! The output with this solution is different. With the same example, when Today is May 31, 2022, the date that is calculated as 3 months before is February 28, 2022.
  7. Hello @innov2e, I as I know it is only possible to set the Date/Time field to Timestamp: So, it seems we need to apply JavaScript code. I can suggest 2 options: 1) To use only JavaScript, 2) To use an external library. The first solution is to add the following code to the Footer section on the Configure Search Fields page. Please don`t forget to disable the HTML editor before pasting: <script> document.addEventListener('DataPageReady', function (event) { //Select the Criteria input fields const dateFrom = document.querySelector("#Value1_1"); //need to change the ID depending on the order number of the field const dateTo = document.querySelector("#Value1_2"); //need to change the ID depending on the order number of the field //Today const date = new Date(); const todayDay = `${date.getDate()}`.padStart(2, 0); const todayMonth = `${date.getMonth() + 1}`.padStart(2, 0); const todayYear = date.getFullYear(); const today = `${todayMonth}/${todayDay}/${todayYear}`; //Three months prior const datePrior = new Date(date.setMonth(date.getMonth() - 3)); const priorDay = `${datePrior.getDate()}`.padStart(2, 0); const priorMonth = `${datePrior.getMonth() + 1}`.padStart(2, 0); const priorYear = datePrior.getFullYear(); const datePriorFinal = `${priorMonth}/${priorDay}/${priorYear}`; // Assigning values dateTo.value = today; dateFrom.value = datePriorFinal; }); </script> The only change is to select the correct IDs. If the Date field is the first one on the Search form, then IDs are #Value1_1 and #Value1_2 as in the example above. If the Date field is the second one, then IDs are #Value2_1 and #Value2_2, etc. However, it`s better to inspect the page. ! With this solution, for example, if Today is May 31, 2022, then the date that is calculated as 3 months before is March 3, 2022. To test the output you may pass the date like this: const date = new Date('May 31, 2022');
  8. Hello @Sergioleno1, As I understand from your request, you are referring to the Chart DataPage. Since there is no STDEV() function in the list of the Aggregate functions on the Chart DataPages, you may try to create Combined Chart and Report DataPage. 1) Let`s say there is a table with the following design: 2) In the DataPage, in the 'Configure Results Page Fields' add the Calculated field with the SELECT statement: SELECT STDEV(Value_field) FROM Chart_STDEV WHERE DatePart(wk, Date) = DatePart(wk, target.[@field:Date]) Replace field names and table name. 3) The 'Configure Chart Options' page should have the following settings * Data Source Settings: * Category (X Axis) * Values (Y Axis) 4) The result: 5) It`s possible to hide the results (if needed) by using CSS in the Header section on the 'Configure Results Page Fields' page: <style> table[id*='cbTable'] { display: none; } </style> Do not forget to disable the HTML editor on the Advanced tab before pasting.
  9. Hello @JenKri, Another possible option is to use the Calculated Value in the Submission form if you add new records to the table through the Submission form. For example, you need to have the Chapter_Number field and the Year field in the form to populate them with values. Then the Project_Number field can be set to the Calculated value: Replace the field names with your local field names. CASE WHEN (SELECT MAX(Project_Number) FROM Projects_ WHERE Year = target.[@field:Year] AND Chapter_Number = target.[@field:Chapter_Number]) IS NULL THEN 1 ELSE (SELECT MAX(Project_Number) FROM Projects_ WHERE Year = target.[@field:Year] AND Chapter_Number = target.[@field:Chapter_Number]) + 1 END The Formula in the table to calculate the final project number is still relevant (please see my post above).
  10. Hello @roattw, As for me, the solution with the Pivot table and the Year field as a Row looks fine. Maybe I missed something in your description, but it seems that there is no need to hard code the Year value in the formula (for example, Year = '2021'). You may use a parameter instead. SELECT COUNT(EM_FA) FROM Publications WHERE EM_FA = 'YES' AND Year = target.[@field:Year] The output:
  11. Hello @wimtracking2, The easiest way is to restrict submitting the form when more than 8 values in the List-String field were selected. As far as I understand, you are referring to the Single Record Update form. I tested this code and it works for me: <script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script> <script> document.addEventListener("BeforeFormSubmit", function (event) { const listOfvalues = document.querySelector('input[name="EditRecordlist"]').value; // replace the 'list' part with your field name const numberOfValues = listOfvalues.split(",").length; if (numberOfValues > 8) { event.preventDefault(); Swal.fire({ icon: "warning", title: "Select less than 8 values", }); } }); </script> You may paste the code to the Footer section and disable the HTML editor before pasting. I am using so-called SweetAlert (https://sweetalert2.github.io/) to display a warning and the first line of code and Swal.fire() refers to it.
  12. And if you want the Year field to be auto-populated with the current year(last 2 digits of the year), you may test this Trigger design:
  13. Hello @JenKri, I think this Forum post suits better for your workflow https://forums.caspio.com/topic/19337-multiple-customers-unique-ticket-number-starting-at-000/ You may check the steps I described in that Forum post. And if to modify the Trigger and Formula for your case: 1) For example, I have the table like this: 2) We are going to insert the Chapter_Number and Year. The Project_Number will be populated with the following Trigger: 3) Formula (the ProjectNumberFinal field) is the following: CASE WHEN [@field:Project_Number] < 10 THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + '00' + CAST([@field:Project_Number] as nvarchar) WHEN [@field:Project_Number] >= 10 AND [@field:Project_Number] < 100 THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + '0' + CAST([@field:Project_Number] as nvarchar) WHEN [@field:Project_Number] >= 100 AND [@field:Project_Number] < 999 THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + CAST([@field:Project_Number] as nvarchar) END 4) The result:
  14. @MeralomaFC, As an option, if you want to evaluate the Triggered Action feature, you may create a Trial account under your Profile (it is free , has 14-days duration, and has features that are included in the Grow paid plan). Then export your app from the Free account and import it to the Trial.
  15. Hello @peewee, You may check the list of supported functions in this article https://howto.caspio.com/function-reference/ Let me add more details about the FOR XML PATH clause for the future reference. For example, there is the table: 1) To display a comma-separated list of names: STUFF((SELECT ', ' + (EmployeeName) FROM EmployeeTable WHERE id > 1 ORDER BY EmployeeName FOR XML PATH ('')), 1, 1, '') The output: Ann, John, Tom 2) To display a list of names with line breaks: STUFF((SELECT CHAR(10) + (EmployeeName) FROM EmployeeTable WHERE id > 1 ORDER BY EmployeeName FOR XML PATH ('')), 1, 1, '') The output: Ann John Tom Note: WHERE clause, ORDER BY keyword are optional. Replace the field name and table name.
  16. Hello @MeralomaFC, I have one idea and it requires using only Caspio standard features. Maybe someone can share another idea. I don`t think that we can display the Prefixed Autonumber(Ticket_No ) in the popup right after clicking the Submit button since this is an auto-generated value. It appears in the table only after submitting the new record. So, you may try these steps: 1) Add the Number_Of_Tickets field to your table (data type is Integer) 2) Create a second table with the same design, just change the DataType for the Ticket_No field to Text(255) 3) In the first table you may create a Triggered Action(if this option is included in your plan). The Trigger works on data insert and inserts the number of records that is equal to the Number_Of_Tickets into the second table. For example: And these are the records that are inserted by the Trigger into the second table: 4) To display this data to the user we need 2 DataPages: * For example, Details DataPage that is based on the second table, on the 'Search and Report Wizard - Search Type' you need to select the 'Filter data based on your pre-defined criteria' option and receive the Ticket_No value as an External parameter. Since this is Prefixed Autonumber you need to add manually the 'InsertRecord' before the field name: * Submission Form that you mentioned based on the first table. On the 'Web Form Wizard - Select Fields' you need to check this checkbox to pass the Ticket_No value as a parameter: This article regarding the Passing Parameters can be helpful https://howto.caspio.com/parameters/passing-parameters/ And on the 'Web Form Wizard - Destination and Messaging' you need to select the second DataPage as the destination As a result, once the Submission form is submitted, the user is redirected to the Details DataPage where all the records are displayed. It can be a Tabular Report DataPage instead of the Details, for example: Hope this helps.
  17. Hello @Amirf, As far as I understood from your description, you have a field in the table with the List-String data type. And on the Submission form it is set to the Multiselect Listbox: If so, List data types have a list of restrictions and it is better to avoid using them in case you need more than just submitting and displaying the values. This article can be helpful https://howto.caspio.com/tables-and-views/data-types/list/ 1) You may use Text(255) data type instead and apply this solution to allow multiselection https://forums.caspio.com/topic/3148-js-select-multiple-values-from-a-listbox-in-a-webform/ 2) Since selected values are added to the table as comma-separated values, it is possible to count them in the Formula field: CASE WHEN LEN([@field:Text_field]) = 0 THEN 0 ELSE LEN([@field:Text_field]) - LEN(REPLACE([@field:Text_field], ',', '')) + 1 END Replace the field name with your local field name. Here is the result: I am hot sure that this is the case for you but hope this helps.
  18. Hello @EvSam, I modified the code from this forum post: https://forums.caspio.com/topic/12061-how-to-get-the-file-size/ This should work for the Submission form. If you have a Submission form, paste this code to the Footer section. Do not forget to the disable the HTML editor before pasting the code. <script> document.addEventListener('DataPageReady', fileInputEvent) function fileInputEvent() { document.querySelector("#InsertRecordFile_2"),addEventListener('change', fileInputHandler); // use the InsertRecord[your file field name] instead document.removeEventListener('DataPageReady', fileInputEvent) } function fileInputHandler(event) { const fileNameInput = document.querySelector('#cbParamVirtual1'); // replace the field name if needed fileNameInput.value = event.target.files[0].name; } </script>
  19. In case you store all data in one table, for example: Then you may use this Task design: Maybe the Task can be optimized, however these Tasks work from my side. Hope this helps.
  20. Hello @kcastagnaro, If I got your question correctly, you may test this solution. 1) Let`s say we have a table with users` info 2) The second table stores some data and each record stores User_ID as a secondary key. So, these 2 tables have a relationship based on User_ID. 3) This is a Task example to send individual emails: The user with ID=1 receives the following email: And the user with ID=2 receives the following email: Task design explanation: The first table variable is "Variable_For_Message". Here we select the fields from the Assigned_Records table that we want to use in the email body. The key point here is to set any false condition. In this example this is Where 1 = 0. This is needed to set up a table variable structure, but to leave the table variable empty. It is empty thanks to the condition is never met. In the second table variable (that`s "List_Of_Emails") we need to select email addresses from the users_emails table. Also, we need to select the field that is used in relationships between our tables, to join the tables on this field, and group by selected fields. In the For...Each loop we are going to populate the "Variable_For_Message" table variable -> To send email -> The clear the table variable using the 'Delete From'. As a result in each iteration the table variable is populated with correct data that is related to the specific user. In the 'Insert Into' block we need to select from the Assigned_Records table and set the condition that compares User_ID in the table with the #record.user_ID(so the User_ID of each iteration). Then we also need to match all the fields:
  21. @MarkMayhrai, It is easy to apply this approach to text values or numeric values. But in your example it is a formatted number, and since the value has the $ sign it is a string. So, everything depends on the condition you want to apply. If this is just 'equal to', then you may use the same code: <script > document.addEventListener('DataPageReady', function(event) { const currencyFormatted = document.querySelectorAll('td:nth-child(2)'); currencyFormatted.forEach(element => { if (element.innerHTML === '$0.00') { element.style.cssText = 'background: #52cc96; font-weight: bold'; } else if (element.innerHTML === '$20.00') { element.style.cssText = 'background: #008ee4; font-weight: bold'; } }); }); </script> The output: However, if you need to apply 'greater than', 'less than', etc. , then we need to remove the $ sign and to convert a string to a number. <script > document.addEventListener('DataPageReady', function(event) { const currencyFormatted = document.querySelectorAll('td:nth-child(2)'); //select the 2nd field, replace '2' to your field order number currencyFormatted.forEach(element => { const currentElement = parseFloat((element.innerHTML).substring(1)); //remove first character ($) and convert the value to the floating point number if (currentElement < 10) { //first condition (if the value is less than 10) element.style.cssText = 'background: #3a9c9c; font-weight: bold'; } else if (currentElement >= 10 && currentElement < 15) { //second condition (if the value is greater than or equal 10 AND greater than 15) element.style.cssText = 'background: #ca808f; font-weight: bold'; } }); }); </script> The output: I will test other approaches and add them here if I have any.
  22. Hello @MarkMayhrai, For such cases I use the following code in the DataPage Footer: <script > document.addEventListener('DataPageReady', function(event) { const statusField= document.querySelectorAll('td:nth-child(5)'); // '5' is field number for which styles have to be applied statusField.forEach(element => { if (element.innerHTML === 'In Transit') { element.style.cssText = 'background: #ff5a4b; font-weight: bold'; } else if (element.innerHTML === 'Delivered') { element.style.cssText = 'background: #376f4d; font-weight: bold; color: #ffffff'; } }); }); </script> Here is the result: 1) So, the idea is to select the required field, in my example its order number is 5. 2) Then depending on the value we can apply inline styles. Since you want to apply multiply styles, like background color, font-color, etc., we can use the cssText property. Hope this helps.
  23. Hello @jboder2, I can see redundant open parenthesis before [@cbParamVirtual1]. Also, please wrap text values in single quotes. You may test this: CASE WHEN [@cbParamVirtual1] = 1 THEN 'Approved' WHEN [@cbParamVirtual1] = 2 THEN 'Denied' ELSE 'Pending' END Hope this helps.
  24. Hello @MatthewK, It looks like the values in the Group-level aggregation are absent because in the Header we apply the styles to hide the elements with the attribute: data-cb-aggregation="Aggregate2" (for example). And Totals and Group-level aggregations have this attribute. Therefore we hide all of them, but we moved values to Aggregate1 only for Totals, but not for the Group-level aggregation. I hope this makes sense and this is the case. I tested another example with SubTotals. 1) Let`s say there is a Report with 2 Totals&Aggregations fields (the first one is used to calculate the Qty Sum, the second one is used to calculate the Price Min) 2) I added this code to the Footer to move the values (of course, everyone needs to specify their field number in the td:nth-child) <script> document.addEventListener("DataPageReady", function (event) { let totalsSecond = document.querySelectorAll('tr[data-cb-aggregation="Aggregate2"] td:nth-child(2)'); //select all Totals and SubTotals of Aggregate2 let totalsFirst = document.querySelectorAll('tr[data-cb-aggregation="Aggregate1"] td:nth-child(3)'); //select all Totals and SubTotals of Aggregate1 totalsSecond.forEach((element, index) => { totalsFirst[index].innerHTML = element.innerHTML; }); //move all Totals and SubTotals of Aggregate2 to Aggregate1 }); </script> 3) After that we can hide all elements related to Aggregate2 and align values if needed. This code is added to the Header: <style> tr[data-cb-aggregation="Aggregate2"]{ display: none; } tr[data-cb-aggregation="Aggregate1"] td:nth-child(3){ text-align: right !important; } </style> Also, I deleted the Label for Aggregate1, and here is the result:
  25. Hello @skwaler, My first thought is to use the Cascading Dropdown (https://howto.caspio.com/datapages/datapage-components/cascading-elements/), however, you need to test this. If your sr_tbl_study_sites table includes all studies and sites that are linked to them, then you may use this table as a Lookup table for Dropdown to select the Study and Cascading Dropdown to select the Site.
  • Create New...