cheonsa

Hi all,
Just wanted to share with you the three formulas to use when ranking records. These are working in Calculated Fields in the DataPage.
RANK() 
- RANK() SQL Rank function is used to specify the rank for each row in the result set.
- Treats ties as equal but ranks will be skipped with each change in value.
RANK() OVER (PARTITION BY Request_No ORDER BY Date DESC)
ROW_NUMBER() 
- ROW_Number() SQL RANK function is used to get a unique sequential number for each row in the specified data. It gives the rank one for the first row and then increments the value by one for each row. We get different ranks for the row having similar values as well.
- Does not consider ties.
ROW_NUMBER() OVER (PARTITION BY Request_No ORDER BY Date DESC)  
DENSE_RANK() 
- DENSE_RANK() function is used to specify a unique rank number within the partition as per the specified column value
- Treats ties as equal but ranks are not skipped with each change in value
DENSE_RANK() OVER (PARTITION BY Request_No ORDER BY Date DESC)
Please note to change the Request_No and Date fields. 
 
 

Hi @roattw,
For more details regarding Triggered Actions, you may refer to this article: https://howto.caspio.com/triggered-actions/triggered-actions-2/
You can also check this video for some popular examples of Triggered Actions:
 

Hi All,
Just wanted to share with you this good news from Caspio. They recently released a new feature called Custom Domain. Using a custom domain can help you resolve an issue with third-party cookies in browsers.
For more details, you may refer to these articles:
https://howto.caspio.com/managing-your-account/account-settings/custom-domain/
https://howto.caspio.com/release-notes/caspio-44-0/#:~:text=Release Notes-,Custom Domains,-We are excited

Hi @Catra - to achieve this, it will require you to have a calculated field, JS and CSS.
First, create a Calculated field that will compare the two fields or set the criteria. For example this:
CASE WHEN [@field:Name] ='' AND [@field:Comments] = 'Candidate' THEN 'Show' ELSE 'No show' END Next, add Header and Footer. Make sure to disable it first on the Advanced Tab. 
In the Footer, copy and paste this code:
<script type="text/javascript"> document.addEventListener("change", function(event){ event.stopImmediatePropagation(); //check if input that is being changed is an Inline Edit Field if(event.target.getAttribute("name").includes("InlineEditFIELDNAME")){ //first calc field occurrence value var calc = event.target.parentNode.parentNode.parentNode.querySelectorAll('td[class*="cbResultSetCalculatedField"]')[0].innerHTML; //check if Calculated field is Show - means show the alert message if(calc =='Show'){ alert("Please change"); document.querySelector('input[name="InlineEditFIELDNAME"]').checked=false; } } }); </script> this code will check the behavior of the checkbox and then make it unchecked and show the message when the user ticks the checkbox. 
Result:


If you want it to be equal to only one field in the InlineEdit, you can use this:
 
<script type="text/javascript"> document.addEventListener("change", function(event){ event.stopImmediatePropagation(); //check if input that is being changed is an Inline Edit Field if(event.target.getAttribute("name")=="InlineEditFIELDNAME"){ //first calc field occurrence value var calc = event.target.parentNode.parentNode.parentNode.querySelectorAll('td[class*="cbResultSetCalculatedField"]')[0].innerHTML; //check if Calculated field is Show - means show the alert message if(calc =='Show'){ alert("Please change"); document.querySelector('input[name="InlineEditFIELDNAME"]').checked=false; } } }); </script> If you want to HIDE the calculated field, you can insert this CSS in the Header:
<style> form[action*='[@cbAppKey]'] tr.cbResultSetDataRow td:nth-child(4), form[action*='[@cbAppKey]'] tr.cbResultSetTableHeader th:nth-child(4) /* replace 4 with the number position of column you want to hide */ { display:none !important; } </style>

Hi @DRAhmed, 
Kindly enclose the CSS code in the style tag.
 
<style> [class*=cbFormBlock173], [class*=cbFormBlock174] { display: none !important;; } </style>  

Hi @Mitch,
You may try the code below:
<style> /*adjust the header of the grid edit*/ div[class="HeadCtnr"] th:nth-child(1) { width: 300px !important; } div[class="HeadCtnr"] th:nth-child(2) { width: 300px !important; } div[class="HeadCtnr"] th:nth-child(3) { width: 300px !important; } /*adjust the content of the grid edit*/ div[class="BodyCtnr"] th:nth-child(1) { width: 300px !important; } div[class="BodyCtnr"] th:nth-child(2) { width: 300px !important; } div[class="BodyCtnr"] th:nth-child(3) { width: 300px !important; } </style> The code will adjust the width of the first three columns. You can change the number inside the th:nth-child(1) to the corresponding column number that you would like to modify. You can also change the 300px width size. 
 
Hope this helps!

Hi @KG360,
I found this howto article that might be the answer to your question:
https://howto.caspio.com/tech-tips-and-articles/disabling-inline-edit-option-in-reports-based-on-a-condition/
 
You can also refer to this forum post:
 
Hope this helps!

Hi @KG360,
I tried to replicate the issue on my end, however, I experienced a different problem. I am getting an Invalid Formula error if I don't convert both equations to integers. 
May you please try this one:
CONVERT(int,(SELECT COUNT (DISTINCT Perspective) FROM tbl32_OrgTargets WHERE Corp_FK=target.[@field:Corp_FK])) + CONVERT(int,[@cbParamVirtual62])
The value being saved in the table is correct.
 
Hope this helps.

Hi @roattw,
The reason why you are getting a Mismatched Datatype error is that your destination field is a Text(255) field only.  Though they are both texts, the number of characters in your source field might exceed the number of allowed characters in the destination field.
You can use this block under the Text tab to make it work.


 
Hope this helps  
 

Hi @biota,
You can also open a ticket for the support team to take a look at it further. 
https://tickets.caspio.com/
They are great with Triggers!  

It is looking like I need a different solution.
I am going to make a new post asking for suggestions.
Thanks for your help @cheonsa!

Hi @biota,
Based on what I understand, you would like to update the Report_ID of the inserted records. May you please try to use #inserted instead of recordsLfwInd_tbl_1?
For example:

 
Hope this helps!

To add to my previous comment, The #inserted table represents data that is being inserted or updated in the table.
You may check this video for more Trigger samples:
 

Hello @biota,
Sorry for the confusion. I thought that recordsLfwInd_tbl_1 is the same Table where you have the Triggered Actions. By the way, you are correct that #inserted relates to the original table into which data was inserted to trigger the action. 
 
In this Inner join, can you confirm that there matching records between the two tables? You are using the Report_ID field to join the Tables, and this is also the field that you are updating. 

 

Hi @Jodie,
You can check this forum post from Caspio. It provides a guideline for referencing Caspio form elements using JavaScript.
 

Hi @cheonsa,
Thanks for posting this--that's really great. Also, the CSS that ended up working for this particular datapage is: 
#target table:nth-of-type(1) td:nth-of-type(n+23):nth-child(-n+49) {display: none;}
#target table:nth-of-type(1) th:nth-of-type(n+23):nth-child(-n+49) {display: none;}
#target table:nth-of-type(2) th:nth-of-type(n+23):nth-child(-n+49) {display: none;}
Between these two methods it should be a clean, more efficient datapage. 

Hi @roattw,
I also tried your formula and it is working as expected. The LEFT command is working  fine as well. As futurist mentioned, you can use ISNULL function for each fields.
For more details, you can check this link: https://howto.caspio.com/function-reference/

Thanks cheonsa
That worked perfectly.
Best wishes
Steve

Thank you so much @cheonsa, this script you've left here worked perfectly and I figured out where I made a mistake with the code I was trying to implement.

Hi @Aleksandra,
I found this forum post, and I think it is similar to your question:
I have changed a few things and it looks like this:
<script> document.addEventListener('DataPageReady', colorHandler); function colorHandler() { const BudgetRatio = document.querySelectorAll('td:nth-child(4)'); // 4 is the order number of the Budget Ratio field BudgetRatio.forEach(element => { const currentElement = parseFloat(element.innerHTML); if (currentElement <= 0.20) { element.previousSibling.style.backgroundColor = 'red'; // field previous to the Budget Ratio field. This should be you Budget Remaining field } else if (currentElement > 0.20 && currentElement <= 0.40) { element.previousSibling.style.backgroundColor = 'orange'; } }); document.removeEventListener('DataPageReady', colorHandler); }; </script>

Hi @roattw,
You can also create a custom Back button. Hide the original one, and create a new button with a function that will go back to the initial form.
Add Header/ Footer and disable HTML editor for both elements.
In the Header, you can paste this style to hide the Back Button container:
<style> .cbBackButtonContainer{ display: none !important; } </style> In the Footer, use this code to create a custom button:
<button class="cbBackButton" onclick="window.location.href='DATAPAGE_REPORT_URL';">Back</button>  
Hope this helps!  

Thank you @TheClaretand @vidierrefor your interest in the Directories feature. Currently, Groups can be used in App connections. Product team is looking into adding Groups support for Authentications in future releases. 
Please note that you can use directory as a data source or a View, so you can assign directory users to Authentication based on filtering criteria in a view. If you have more questions, please contact support. Check out the available video tutorials.

Hello @scavjas,
Are you planning to search the values for one field only? Like for example, you would like to search for records in Column A with values Q, W, E, R, T and Y. Is that correct?
If that is correct, your format in Search field should be Q OR W OR E OR R OR T OR Y. Each value should be separated by OR.
To add 'OR' automatically, you need to use two DataPages. One is Submission Form that will act as the Search Form, and a Reports DataPage.
You can refer to this article for more details:
https://howto.caspio.com/tech-tips-and-articles/tech-parameters/how-to-separate-search-page-and-results-page-into-two-different-webpages/
Basically, you will have virtual fields in the Submission Form. One Virtual Field is for replacing spaces in the values to OR. 

In the Advanced Tab, you can hide this Calculated Value, and pass the parameter to the Reports DataPage.

 
For the configuration of the Reports DataPage, you can follow the steps provided in the article above.
 
Hope this helps!

Hello!
You can check this video tutorial. The sections are closed by default.
 

Hello @Schbal,
The result seems to be expected for me. When searched for a record, then modify it, the updated record will not meet the applied criteria anymore. If you try to edit it again on the same grid, it will throw you an error since in the result, it should not be included.
As a workaround, after you modify the record, then if will not meet the criteria, it's better to exit the grid edit option first, then enable it again.
You can also open a support ticket so Caspio's support team can confirm.