Skip to content
+1-888-319-3663

COMMUNITY FORUM

Check if value exists in GP database and skip record

Boris asked 8 months ago
We run an import using folder data source, I want the import to check if one of the fields already exists in the GP database, but need to use a query as I want it to check if the value exists on either an open or posted transaction, not a voided historical transaction. If the value is found in the database I want the records in that group (it’s one of the group by fields) skipped. What’s the best method for handling this?
Answers
Patrick Roth Staff answered 8 months ago
If this were NOT a folder data source the best way would have been to use a multi-data source.
One source would be your existing source and then the second would be a query on the GP database.
You’d use that and join them together to filter out the ones that already exist and thus you would only integrate the ones that do not exist.
But since it is a Folder DS and we can’t do this, Plan B is query the data yourself.
I would create a “before document” task. In it you would connect to SQL and run the query you are looking for.
https://www.eonesolutions.com/tech-tuesday-query-data-in-smartconnect-with-net-scripting/
so you would run whatever query you would like using the connection you made.  And then in the end, you return “true” or “false” back to the method.
So “true” if you want SC to process the data and “false” if not and SC will skip this document.
 
Boris replied 8 months ago

Thanks Patrick, but would a before document task allow me to check each record and skip just the record?

Patrick Roth Staff replied 8 months ago

Yes – that is why I suggested you do this.

you can prove it easily enough by creating the task and set to simply

return false

and you’ll find that SC doesn’t execute for those documents

then set to

return true

and then it will be processed.

The work then is writing the code to run the query and return the results to know whether your code should return true or false.


If you would like to submit an answer or comment, please sign in to the eOne portal.