Jodie

Hi,
I am not able to see errors on that triggered action but the screenshot is not showing it all. Is the triggered action not triggering at all or it is showing a run error? Normally when that happens I just check the tables that the trigger is referencing to see what I have changed recently or I try to disable blocks of the triggering action until I find out which block is causing the problem. 

I'm afraid that the screenshot you have provided would be insufficient to check this issue. As it is, I don't see any issues with it.

For now, I would only suggest that you check into the BadgePassport table as well. Does it also have a trigger? If yes, disable it and see if this trigger will now work properly.

Keep an eye on the formula fields too.

Hello @roattw,
The main idea I tried to cover is the #inserted table on Update.
I know that it is confusing, so I wanted to highlight this topic. 
The trick is that when the record is updated, the #inserted table includes all the fields of the updated record and not only those that are actually updated. 
In my example, I have 4 fields: ID, Notes, Address, and Phone:



Even when I update just one field (let`s say, the 'Notes'  field is updated), the  #inserted table still has 4 fields. 
As a result, the #inserted table looks like this:

 
So, as I mentioned, the WHERE clause is skipped. 

However, this Trigger is fired when the Update action occurs in Table_1.
The Join helps us to identify the record that was updated. For that, it is really crucial to join tables on the unique field. 
Since in the SET block, all the fields are mapped with the fields of the #inserted table, if the value was not updated, it will be "reassigned".
In this example, Address and Phone will be set in Table_2 by the Trigger, but the values are the same, so the result of this reassigning is invisible because we still can see the old values.

To summarize, by this Trigger we replace these values

with these values from the #inserted table:


Hope this makes sense. 

Ok, I solved it for myself.  It isn't working within the datapage footer but when I put the script on the footer of the webpage that I've embedded into the datapage it seems to work just fine

Hi @cheonsa and @Tubby,
I'm genuinely grateful for your responses. I eventually turned to Caspio for help and they very kindly came up with a solution very close to your response Tubby. Here's the code:-
@media (max-width: 1048px) {
div[class*="cbFormNestedTableContainer"]{
display:block !important;
}
}
The code need to be placed in the HEADER.
Kind regards
Ron
 

Hello @Jodie,
The 'target' keyword doesn`t work with Virtual fields, as far as I understand.
Please test this formula, it should work.
CASE WHEN [@field:tbl_opportunities_sign2]='manager' THEN (SELECT Email FROM tbl_users WHERE UserGUID='[@cbParamVirtual7]') ELSE '[@cbParamVirtual9]' END

Thank you @CoopperBackpack - this worked perfectly. Only changed I made was to CAST [@field:rcount] as a Float to get the decimal

Hello @Jodie,
1) There is no need to cast the Yes/No field as bit.
2) The result is blank because when the checkbox is not checked this returns NULL and the whole result is NULL (blank). To fix this you need to add the ELSE part too.
3) I don`t know what the 'rcount' field's data type (Integer or Number) is and what value is stored there.
Let`s say it is an Integer (for example, 10). In this case, you need to consider that an Integer divided by an Integer produces an Integer. For example: 5/10 = 0.5 but the Integer part is 0. So the Formula result is 0.
If the 'rcount' field has a Number data type the formula should be:
 
(CASE WHEN [@field:preq1] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq2] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq3] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq4] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq5] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq6] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq7] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq8] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq9] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq10] = 1 THEN 1 ELSE 0 END ) / [@field:rcount] If the 'rcount' field has an Integer data type the formula should be (added  multiplication by 1.0): 
(CASE WHEN [@field:preq1] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq2] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq3] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq4] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq5] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq6] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq7] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq8] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq9] = 1 THEN 1 ELSE 0 END + CASE WHEN [@field:preq10] = 1 THEN 1 ELSE 0 END ) * 1.0 / [@field:rcount] Perhaps, you need to multiply by 100 the whole result if you need to produce percentages. 

Hi @Jodie

From what I can see here, there could be multiple reasons why this might not work:

1. In a one CASE WHEN statement you can only return one DataType. I believe [@field:variable] is a number/integer type of data, so after THEN you return a string and after else you return a number, which is forbidden.
Also, to check if a number cell is empty use IS NULL. Here is an SQL statement that should work:

CASE
WHEN [@field:variable] IS NULL THEN
'Missing'
ELSE
CAST([@field:variable] as NVARCHAR(max)) 
END

2. In document.getElementByName("cbParamV1")[0].value plural 's' is missing, so it should be getElementsByName. Also, the name of the first virtual field I believe should be cbParamVirtual1, so this code part will look like document.getElementByNames("cbParamVirtual1")[0].value

3. In "if/else" statement you put the same document.getElementById("tbl_tech8").style.display="none"; for if and else conditions.

4. IDs of table elements usually do not have "tbl_tech8" naming, it could be something  like "tbl_tech8_146f65b245c8c2" and changes dynamically on page load, so a different selector could be required.

But without seeing the actual details DataPage and its setup, it would be hard to tell for sure what to do to make it work. You could share a link to that DataPage for furhter checking.

Yep, that is exactly the miss on this one.

If you are working with triggers, it's very likely that the virtual table #inserted (for insert and update) and #delete are involved. Those virtual tables serve as a placeholder for the values that just got inserted, updated, or deleted depending on the workflow. 

If you simply refer to the parent table name instead of the virtual table, you are actually referring to the values of the table before the specific action has occurred, that is why you noticed that your trigger is somewhat behind the process. 

Another thing I found that helps is using JavaScript for any calculations that you can... If you have some more simple calculations, you may want to try running those via a script and then using that produced value in the more complex calculations. The script runs much quicker than calculated fields in most cases. 

Hi
So this had me baffled yesterday and it took IT support to help me out. Once they responded and explained the problem I was able to find posts in the Caspio Community with SQL calculations returning 0 instead of decimal value, but up till then I just thought I was writing the maths wrong.
I'm just adding the issue and solution again for formula tables so maybe its easier for the next person to find (or myself when I forget ) 
When using all integers, Caspio will return an integer unless a field includes decimal value. In my example, I was trying to calculate the percentage of a plan the user had completed. Completed items had the value of 1 so my formula summed all them and divided by 7. Instead of returning a decimal every value was 0. All I had to do was change 7 to 7.00.  Other options to get a decimal easy could be to multiply a value by 1.00.  I could have also changed my 1s to 1.00 but I didn't realise that maths on integers didn't quite work the same as Excel! 
 

Hi @Jodie

Here is the JS code snippet you can add to the footer on "Configure Results Page Fields" on the tabular report
<script> const dataFieldColumnNumberArr = [2] const keywordDropDownName = 'Virtual1' const addKeywordsToArr = () => { document.KeyWordsArr = [] document.querySelectorAll(`#cbParam${keywordDropDownName} option`).forEach(option=>{ document.KeyWordsArr.push(option.getAttribute('value').trim().toLowerCase()) }) } const highlightCellKeywords = (HTMLTargetCell) => { let initHTML = HTMLTargetCell.outerHTML document.KeyWordsArr.forEach( keyword => { let regex = new RegExp(keyword, "ig") initHTML = initHTML.replace(regex, `<span class="target-keyword" data="Change this word">${keyword}</span>`) }) HTMLTargetCell.outerHTML = initHTML } const highlightAllKeywords = () => { for (let i=0;i<dataFieldColumnNumberArr.length;i++) { document.querySelectorAll(`.cbResultSetTable tr td:nth-child(${dataFieldColumnNumberArr[i]})`).forEach(cell=>{ highlightCellKeywords(cell) }) } } const hideVirtual = () => { document.querySelector(`#cbParam${keywordDropDownName}`).style.display = 'none' } const main = () => { hideVirtual () if(document.KeyWordsArr == undefined) { addKeywordsToArr() highlightAllKeywords() } } document.addEventListener('DataPageReady', main) </script> Where dataFieldColumnNumberArr variable contains the comma-separated column number(s) of the data columns on the tabular report page where keywords must be highlighted. 
In the example above, keywords will be highlighted in the second column.
If you need to highlight keywords, say in the 2nd and 3rd column, the code snippet will look as follows:
const dataFieldColumnNumberArr = [2, 3]
keywordDropDownName variable must contain the name of the virtual field that is drop-down based on keywords lookup table.

The following CSS code snippet should be added to the header of the search form (if you have a search form in your tabular report) or to the header of the "Configure Results Page Fields" screen if you do not have a search form.
 
<style> .target-keyword { background-color: red; } .target-keyword:hover { cursor: pointer; } .target-keyword:hover::before { content: attr(data); display: block; position: absolute; top: 100%; left: -7px; right: -7px; padding: 15px; background-color: #000; } </style>

 

Hi there.

I needed to be able to execute an application task by clicking on a button on a DataPage.
Since application tasks manually can be executed only on the back end of the application, here is a workaround I came up with using triggered action:

1. Create an auxiliary table with one Text 255 field:



2. Add one record to this table:


3. Create a triggered action on the auxiliary table that will run  on update and will be executing the required task logic (in this example, it will be sending emails to users stored in the main table)




4. Now you can create a Details DataPage using the auxiliary table as a source with the following configurations:
4.1 Choose "Filter data based on your pre-defined criteria."
4.2 Select no filtering fields
4.3 On "Search and Report Wizard - Select Details Page Fields" choose Text 255 field from the table
4.4 Add header with the following CSS code snippet:
 
<style> section div:not(.cbBackButtonContainer) { display: none; } </style>

4.5 Add the following JavaScript code snippet to the footer of your DataPage:
<script> const alertMessage = 'Task was executed' document.addEventListener('click', (e)=>{ if(e.target.getAttribute('type')=='submit') { alert(alertMessage) } }, true) </script>

4.6 "Destination after record update" is set to the "Same form."

With the aforementioned setup, you will get a DataPage with a single button that will trigger required actions upon click, which simulates application task behavior. 

 

Hi Rene
Do you need a new and unique table for each site? Or do you need:
a user-site table that records all the sites a user has added matched with the uniqueid, and  A singular site table where each row is a new site and the columns then replicate the common table structure  You could either structure the user site form to open a second page inside it that creates the new site record or you could pass the parameter, or use triggered actions after the event. 
 

Hi @Connonymous

You can set up filters on your report DataPage to filter out records based on external parameters:
https://howto.caspio.com/parameters/tech-tip-custom-filter-elements/

So you will have Criteria 1 and Criteria 2 filters that accept Criteria_1 and Criteria_2 external parameters.
Then, using iframe deployment, you can deploy the same DataPage 9 times and each time pass different values to Criteria 1 and Criteria 2 filters via query string in the iframe source. For example, 

<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=A"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=B"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=C"></iframe>

And so on. 

If you need to add additional search filters on top of these Criteria 1 and Criteria 2 so that they accept dynamic values based on search input, additional JavaScript will be required.
The flow can be summarized as follows:

1. You create a submission form DataPage that will act as a search form. This submission form should have only virtual fields, and the destination after submission should be the same page if you want search results to be below the search form.
2. JavaScript is needed to listen to "FormSubmitted" event. JavaScript will collect values from search input and add additional query string parameters with these values to source URLs of iframes. For example, if you need to filter out records based on department after search submission, the iframes will look something like this:

<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=A&Department=ValueFromSearchInput"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=B&Department=ValueFromSearchInput"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=C&Department=ValueFromSearchInput"></iframe>

Hello @Connonymous,
As for the Triggers, you are correct, you need to take into account the #inserted table.
It is better to use the following approach: 
1) If you already have data in the tables, you may use a Task to populate the additional table with the current data.
For example, I have this table as a main table:



And this one as an additional table to use to create a Chart:



This Task will populate the Count field:

 
2) After that, you may use Triggers to re-calculate the Count field.
Trigger example that works on Insert:


Trigger example on Delete (work for Inline delete, requires a Loop for bulk delete)

 
Trigger example on Update (work for Inline edit, requires a Loop for bulk edit)

First, if you can do this with Rules, it is much easier to set up and to deal with later. A lot of times you can combine rules so that you can use the same field for multiple Actions, if you've already used that field in a Rule. If Refrigerant = No, Hide Refrigerant_Gas (or the section that field is in). I think you should still be able to use Refrigerant in multiple Rule Criterias if it's already being used. If Refrigerant_Gas isn't being used in a Rule Action, I'm not sure why this wouldn't be possible.
If you want to stay the JS route for whatever reason, try this below. I think we were having multiple problems - first not selecting the dropdown values correctly, and second not listening to changes in the Refrigerant dropdown. This should hide the Refrigerant_Gas dropdown if Refrigerant = No, and reappear if it is changed to Yes.
document.addEventListener('DataPageReady', function() { hideDropdown(); }); var yesNoField = document.querySelector('select[id^="EditRecordRefrigerant"]'); yesNoField.addEventListener('change', function() { hideDropdown(); }); function hideDropdown() { var refrigerantLabel = document.querySelector('label[for="EditRecordRefrigerant_Gas"]'); refrigerantLabel.style.display = (yesNoField.value === "No") ? "none" : "block"; var refrigerant = document.querySelector('select[id^="EditRecordRefrigerant_Gas"]'); refrigerant.style.display = (yesNoField.value === "No") ? "none" : "block"; }  

Hi @RonAnderson,
kpcollier provided the correct solution. To hide the field we need to listen for the value change in the Cascading Text field (eventListener is needed).
I just wanted to add that if the Refrigerant field is a Cascading dropdown it is an input tag but not the select, so we need to reference it in a slightly different way.
The code should work on the Submission form if you select elements with the prefix 'InsertRecord':
<script> document.addEventListener('DataPageReady', hideDropdownHandler); let yesNoField = document.querySelector('input[id^="InsertRecordRefrigerant"]'); function hideDropdownHandler(){ yesNoField.addEventListener('change', hideField); document.removeEventListener('DataPageReady', hideDropdownHandler); } function hideField() { const refrigerantLabel = document.querySelector('label[for="InsertRecordRefrigerant_Gas"]'); refrigerantLabel.style.display = (yesNoField.value === "No") ? "none" : "block"; const refrigerant = document.querySelector('select[id^="InsertRecordRefrigerant_Gas"]'); refrigerant.style.display = (yesNoField.value === "No") ? "none" : "block"; } </script> And on the Details page if you select elements with the prefix 'EditRecord':
 
<script> document.addEventListener('DataPageReady', hideDropdownHandler); let yesNoField = document.querySelector('input[id^="EditRecordRefrigerant"]'); function hideDropdownHandler(){ yesNoField.addEventListener('change', hideField); document.removeEventListener('DataPageReady', hideDropdownHandler); } function hideField() { const refrigerantLabel = document.querySelector('label[for="EditRecordRefrigerant_Gas"]'); refrigerantLabel.style.display = (yesNoField.value === "No") ? "none" : "block"; const refrigerant = document.querySelector('select[id^="EditRecordRefrigerant_Gas"]'); refrigerant.style.display = (yesNoField.value === "No") ? "none" : "block"; } </script>  

Hello @Jodie

statusValue.checked must be wrapped in parenthesis: 

 if (statusValue.checked) 

This should eliminate this error.

This might seem super obvious to others, but using a virtual field in a calculated value requires using a virtual field thats set to calculated value. A calculated field won't let you use a virtual field.  Hopefully, I'll finally remember this myself, and won't keep trying to  use a calculated field!

Check out this trick I stumbled upon.
 
Put Caspio Variables into your Classes so you can use CSS to dynamically style your pages.
(js not required)
 
 
Div to dynamically format 
<div class="action[@field:action]"></div> css
.actionCall { background-image: URL("../img/call.png"); } .actionEmail { background-image: URL("../img/email.png"); } .actionText { background-image: URL("../img/text.png"); } Dynamic Elements - load all options, and hide all but needed
<a class="btnFile fileName[@field:FileName]" href="../some/dir/[@field:FileName]">Download</a> <a class="btnLink fileLink[@field:FileLink]" href="[@field:FileLink]">View File</a> css
/*-- when [@field:***] is null, hide the div --*/ .fileName, .fileLink { display: none; } Hope this helps
 
Think Easy
CHAD
-I know the Title of this Forum is "Ask....", but IDK where else to share stuff like this.

This sounds like an interesting feature and I was curious so I asked chatGPT.  I didn't continue asking for the code because its not something I need right now, but thought I'd paste the high level guidance. If you do code this and get the functionality working, I'd love to see what you did.
 
To add the capability for a user to tag a comment in a notes field in Caspio and then send an email to the tagged user with the comment, you can follow these steps:
Set up the Notes Field:
In Caspio, create a table with a notes field where users can enter their comments. Add an additional field to store the tagged user's email address. Modify the Notes Field to Include Tagging:
Use a Rich Text Editor or a Text Area with HTML support for the notes field. Implement a tagging system where users can mention other users by typing "@" followed by their username or email address. This can be achieved by using JavaScript and HTML/CSS. Capture the Tagged User's Email:
Implement JavaScript functionality to detect when a user is mentioned/tagged in the notes field. Extract the tagged user's email address from the database or from a user list, based on the username provided. Store the email address in the additional field you created in step 1. Submit the Form:
When the form is submitted, store the comments and the tagged user's email address in the Caspio table. Send Email Notifications:
Utilize Caspio's Email Notifications feature or an external email service (e.g., SMTP, API) to send email notifications. Configure the email notification to be triggered whenever a new entry is submitted. Customize the email content to include the comment and other relevant details. Use the tagged user's email address stored in the table to send the notification specifically to them.

Hi @WatashiwaJin
I've been messing with this to try to get it to work. 
document.getElementsByName('Bulk').onclick = function(){myFunction()};
function myFunction() {document.getElementById('InsertQuantity').value = "100";
};
This removed the onclick error. But it still doesn't fill Quantity to 100.
*Edit
I might've done too much. Putting [0] after 'Bulk' creates the onclick error. Taking it out prevents the error but still does not work. I tried to log a message through the console in the function and didn't get any response... so my function does not work lol.
*Edit #2
https://howto.caspio.com/datapages/ajax-loading/
Found this in the online help article listed above.
onload(), onsubmit() and other loading events are no longer supported. You must use the Caspio built-in event handlers, as described below on this page. Working on trying to use a Caspio event handler to substitute the onclick function.

Funny enough, I've been using ChatGPT A LOT for debugging and finding mistakes in my code. It's really good at finding tiny mistakes, things that don't make sense logic wise, and better, more compact code that accomplishes the same task as what I developed. I use this almost every day now.
If you want something more traditional, I'd use Visual Studio Code and look up some videos on how to use their debugging tool. It's got a lot of features that could potentially help.