Jump to content


Caspio Guru
  • Posts

  • Joined

  • Last visited

  • Days Won


Community Answers

  1. Hastur's post in Display aggregate of total time as HH:MM was marked as the answer   
    The calculated field should look like this in:
    CAST((( DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) - (DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) ) / 60) AS nvarchar) + ':' + CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar) Also you may find other examples here - 
  2. Hastur's post in Preventing a table from being updated based on logic criteria was marked as the answer   
    You can implement such a trigger.
    Here you can find the example of the trigger and table design.
    As the trigger has a big size? i have divided print screen in two parts


  3. Hastur's post in XIRR function was marked as the answer   
    Hello @Vitalikssssss!
    It is possible to implement such a formula within Tabular report using additional JS.

    Please follow these steps:
    1. You need to create additional APP parameter to use the "moment.js" library.
    Please check this article to get familiar with the APP Parameters in Caspio - https://howto.caspio.com/apps/app-parameters/

    2. Create the Tabular Report and add the Header/Footer. Please insert this code into the Header:
    <div style='display:flex; justify-content:flex-start;padding:30px;'> <input placeholder="Guess Rate, %" id='rate'></input> <button id='click' class='cbResultSetAddButton' style='margin-left:10px;'>Calculate XIRR</button> <div id='result' style='margin-left:10px;'></div> </div> <script src="[@app:URL_1]"></script> <script> "use strict"; document.addEventListener('DataPageReady', function () { document.querySelector('#click').addEventListener('click', function (event) { var formatDate = 'DD/MM/YYYY'; var columnDateNumber = 2; //Change based on your column position on a result set var columnValuesNumber = 3; //Change based on your column position on a result set //Not changeable part var datesSelector = "tr>td:nth-child(".concat(columnDateNumber, ")[class^=\"cbResultSetData\"]"); var valuesSelector = "tr>td:nth-child(".concat(columnValuesNumber, ")[class^=\"cbResultSetData\"]"); var dates = []; var values = []; document.querySelectorAll(datesSelector).forEach(function (item, index) { if (!item.hasAttribute('style')) { return dates.push(item.innerText); } }); document.querySelectorAll(valuesSelector).forEach(function (item, index) { if (!item.hasAttribute('style')) { return values.push(+item.innerText); } }); var guess = +document.querySelector('#rate').value; var some; if (guess != 0) { some = guess/100; } document.querySelector('#result').innerHTML = "Result:<strong>".concat(XIRR(values, dates, some, formatDate)*100, "%</strong>"); }); }); function XIRR(values, dates, guess, local) { // Credits: algorithm inspired by Apache OpenOffice // Calculates the resulting amount var irrResult = function irrResult(values, dates, rate) { var r = rate + 1; var result = values[0]; for (var i = 1; i < values.length; i++) { result += values[i] / Math.pow(r, moment(dates[i], local).diff(moment(dates[0], local), "days") / 365); } return result; }; // Calculates the first derivation var irrResultDeriv = function irrResultDeriv(values, dates, rate) { var r = rate + 1; var result = 0; for (var i = 1; i < values.length; i++) { var frac = moment(dates[i], local).diff(moment(dates[0], local), "days") / 365; result -= frac * values[i] / Math.pow(r, frac + 1); } return result; }; // Check that values contains at least one positive value and one negative value var positive = false; var negative = false; for (var i = 0; i < values.length; i++) { if (values[i] > 0) positive = true; if (values[i] < 0) negative = true; } // Return error if values does not contain at least one positive value and one negative value if (!positive || !negative) return "#NUM!"; // Initialize guess and resultRate var guess = typeof guess === "undefined" ? 0.1 : guess; var resultRate = guess; // Set maximum epsilon for end of iteration var epsMax = 1e-10; // Set maximum number of iterations var iterMax = 50; // Implement Newton's method var newRate, epsRate, resultValue; var iteration = 0; var contLoop = true; do { resultValue = irrResult(values, dates, resultRate); newRate = resultRate - resultValue / irrResultDeriv(values, dates, resultRate); epsRate = Math.abs(newRate - resultRate); resultRate = newRate; contLoop = epsRate > epsMax && Math.abs(resultValue) > epsMax; } while (contLoop && ++iteration < iterMax); if (contLoop) return "#NUM!"; // Return internal rate of return return resultRate; } </script> You should change the selectors of your Date and Value fields, if they are placed in the different position.
    Selectors can be changed here: 
    var columnDateNumber = 2; //Change based on your column position on a result set var columnValuesNumber = 3; //Change based on your column position on a result set Please find the print screen attached.

    Do not forget to Disable the HTML editor.

    Also, find the dummy application with this customization attached - XIRR_Function_1_0_2019-Aug-22_1503.zip
    Please let me know if you need any assistance.
  4. Hastur's post in How to Allow Only One (1) Record in a Particular Status at a Time? was marked as the answer   
    @Becca37 Hello!

    It is possible to implement such a workflow using Calculated field and additional JS code.
    The idea is to count entries with needed statuses within the table using Calculated Field.
    If there are more then 0 entries - we need to hide submission form and show the message.

    I created an APP for you. Please feel free to import it - JS_Dynamic_Disable_Submission_1_0_2019-Apr-09_1223.zip
    Let me know if you have any questions.

    Client credentials are:
    Login - Password
    client1 - 1
    client2 - 2
    client3 - 3
  • Create New...