Triggered action to check if the record is already existing

[Kuroshi]

Is there a way to create a triggered action that checks on another table? I have 2 tables. Once I enter a record on the first table, it will check if it is already existing in the second table. If it does not exist on the second table, I need the record to be submitted on the second table and if it already exists then it needs to do nothing. Would you be able to provide me a sample configuration for this?

 

[Barbie]

Hi @Batchini,

You can checkout this trigger configuration which might help you achieve your workflow: 



What this trigger does is once you insert a record on the main table and it is not existing on the second table, it will save the records on the second table. Otherwise, it will not save since it is already existing in the said table.

I hope this helps.

- Barbie

[astroboy]

I hope this is not too late but here are some links that you may check for reference if you are interested to know more about Triggered Actions:

• https://howto.caspio.com/tables-and-views/triggered-actions/

Cheers!

[NailDyanC]

Hi, just to add to the previous comment above. This can also be done in the DataPage level. If you would like to do it in this way. 

You may use a calculated field that will determine if that value is already existing in your table and just add a JavaScript that will prevent the form to be submitted if the value is existing:

CASE WHEN 
(SELECT COUNT(field_name) FROM Table_name WHERE fieldname = [@field:fieldname] THEN
'Existing' 
ELSE
'Not Existing'
END

Then, you may add this JavaScript in the footer of your DataPage:

<script>

 document.addEventListener('BeforeFormSubmit', clickHandler);

function clickHandler(event) { 

var calc = document.querySelector("[id*=fieldID]").innerText;

 

if (calc  == "Not Existing"){

event.preventDefault();

window.alert("Submission BLOCKED!");

}

else {
document.location = "http://www.mysite.com/menu.html";
}

}

</script>

 

Just change the field names and table names depending on the field that you are using. 

[NailDyanC]

Also, here's the link that you may consider checking for Calculated fields:
https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

[NiceDuck]

You may also want to use the block mentioned here, just change the activation from update to insert
 

 

[kpcollier]

I'm trying to use this logic to add records from Table A to Table B that have not yet been added to Table B. Table A gets it's data directly from imports, so I cannot use insert trigger.

Does anyone know if the 'querying' or 'scanning' of records to see if there is a matching ID counts as 'number of affected records' in a task? I am trying to avoid the Maximum rule for a single task. The table being scanned is pretty small right now, but will probably be over 50,000 records in a year, so I assume I will need to scan through the 50,000 records to see if the ID matches any of them or not. 

Or, if anyone has a better way to add imported records from Table A to Table B... it would be great to hear it.

[CoopperBackpack]

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:

 

[Hiperf]

On 11/7/2020 at 2:22 PM, NailDyanC said:

Hi, just to add to the previous comment above. This can also be done in the DataPage level. If you would like to do it in this way. 

You may use a calculated field that will determine if that value is already existing in your table and just add a JavaScript that will prevent the form to be submitted if the value is existing:

CASE WHEN 
(SELECT COUNT(field_name) FROM Table_name WHERE fieldname = [@field:fieldname] THEN
'Existing' 
ELSE
'Not Existing'
END

Then, you may add this JavaScript in the footer of your DataPage:

<script>

 document.addEventListener('BeforeFormSubmit', clickHandler);

function clickHandler(event) { 

var calc = document.querySelector("[id*=fieldID]").innerText;

 

if (calc  == "Not Existing"){

event.preventDefault();

window.alert("Submission BLOCKED!");

}

else {
document.location = "http://www.mysite.com/menu.html";
}

}

</script>

 

Just change the field names and table names depending on the field that you are using. 

I am getting an error 

Incorrect syntax near (.

when using this calc.

CASE WHEN
(SELECT COUNT([@field:SMNUnique]) FROM tblSubmission WHERE [@field:SMNUniqueCalc] = [@field:SMNUnique]) 
THEN
'Exists'
ELSE
''
END

[PotatoMato]

Hi,  @Hiperf. You're missing a condition on your select count query.  It should be:

CASE WHEN
(SELECT COUNT([@field:SMNUnique]) FROM tblSubmission WHERE [@field:SMNUniqueCalc] = [@field:SMNUnique]) >= 1

You may change '>= 1' as needed.

 

-Potato

[RuisiHansamu]

 Hi there, there are a couple of posts that are similar to this workflow. it helped me a lot in this. You can view it here: