Jump to content
  • 0

Triggered action to check if the record is already existing


Kuroshi
 Share

Question

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?

 

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Hi @Batchini,

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

Screenshot_22_B42940E216.thumb.png.24b4ff2cc79e649dd3bf07cef1de3f8a.png

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

Link to comment
Share on other sites

  • 0

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. 

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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:

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...