Skip to content
+1-888-319-3663

COMMUNITY FORUM

GP SOP Node – Difference Between Update Existing

Chris asked 6 years ago
I’m working with GP SOP10100, and saw a KB article written by Kevin back in Dec 2011 advising to not use Update if Exists.
What is the difference between the ‘Update Existing’ checkbox on the top of the Mapping window and the Destination Column ‘Update if exists’ with true/false values?
I’m trying to move SOP orders from one batch to another (or changing the batch ID column), so every document exists.  However, I am getting different fail errors depending on how I have these set.
Answers
Patrick Roth Staff answered 6 years ago
Chris,
I’m assuming you mean this article
http://www.eonesolutions.com/kba-01116-w3s5m8/
And I don’t see that he advises “not to use it”.  He had noted that you can only use “update if exists = TRUE” if the document DOES exist.  And to use “update if exists = FALSE” if it does not.  Otherwise you get validation errors.
For GP, that checkbox is a quick way to set the “update if exists” flag that exists on most eConnect nodes.  It should flip that flag from TRUE/FALSE depending if you have the box marked or not.  But I notice that on my ‘create sales order’ it doesn’t seem to work.  I  think that might be because in the SOP document the underlying field is “updateexisting” instead of “updateifexists”.
But in either case, look for the “Update if exists” on the create sales transaction and either set it to List Option TRUE/FALSE or set to local constant of 1/0 depending on what you need.
So in your case, your documents exists so you’d want to make sure this was set to TRUE/1 so that eConnect can find it and update the record.
Boris G replied 1 year ago

Hi, I know this is an old thread but had a question. If I have an import that includes some new sales orders, and some that exist but with updates, whether quantity of an itemnumber on the sales order, or an itemnumber that was previously imported is no longer there or an new itemnumber is on the order now, can I use this flag to have SC essentially overwrite all the lines on the order to whatever is in the file? Do I have to set the update if exists on the line level rather than the create sales order, or do both need to happen?

Patrick Roth Staff replied 1 year ago

You would have to set the Update If Exists flag on both the header & lines.

Yes you can determine this value at runtime vs hard coding the setting.

Couple of different ways you could do this:
1. use a multi data source map

https://www.eonesolutions.com/tech-tuesday-using-multiple-data-sources-to-bring-in-the-correct-1099-amount-on-payables-transactions/

Here you would query the SOP10100 table and then join it (or just a couple fields) to the source data. So lets say you just return the SOP10100.CUSTNAME field only (because we don’t care about it, just need to see if it is empty or not)
Then in the calculation you create, you could then look at the field. If it is empty, then the SOP Number doesn’t exist so you want to return 0. If it has a value, then that means there was a SOP Number and the record exists so you return 0

CALC_IF_EXISTS
if _CUSTNAME.Trim() = String.Empty then
return 0
else
return 1
end if

2. use a calculation (since you were going to create one anyway)

https://www.eonesolutions.com/tech-tuesday-automatically-set-the-1099-amount-on-a-payables-transaction/
Here, we make a connection to SQL and run a query directly. In the above, they get the 1099TYPE but you would just change the query.

So you could query the SOP10100 directly and use if exists() in the query to return 1 or 0.

Either will work fine, the multi ds map is a bit easier to understand and create but the query is probably the better route.

Boris replied 4 months ago

Hey Patrick, thanks for your response on this, I didn’t see an email noting a response and this was put on the back burner, but is now coming up again. I tried setting the update if exists flag using mssql based on customer po number since that will be unique in our case, where it returns 1 (using sopstatus field) if the order exists, or 0 as the default value when it doesn’t. When I run a file with an existing order I get an error 2608 document does not exist in the work file and notice that it is creating a new document number because I have the document number as a rolling GP field. Do I need to make that a calculated field where I insert the document number based on a query that’s successful, else use the gp rolling column if the query doesn’t return results?

Patrick Roth Staff replied 4 months ago

“Do I need to make that a calculated field where I insert the document number based on a query that’s successful, else use the gp rolling column if the query doesn’t return results?”

Your best bet here is to run the query and if you need to get the “next number” then YOU call the E1_SC_GetNextNumber stored procedure yourself by making a .net connection and then calling the proc. You would have to review the proc itself to see what is being passed by the MSSQL Lookup.

Lorren talks about calling that proc here:
https://www.eonesolutions.com/discussion/conditional-gp-rolling-column/

And is a good starting point.


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