Posted Wed, 07 Nov 2018 13:20:33 GMT by Daryl Foxhoven

So I'm trying to lookup a PO# from an external MSSQL data source.  The issue is, the PO# in the data source is in the following format PO1-08888, however, the PO that we're capturing from the image is just 0888.  So I'm trying to use a like or contains operator to find the PO in the external source to fill in additional information.  I've tried so many different queries and have found none to work.

 

Here's what I'm currently using, and it does not work:

SELECT PO Number FROM [DatabaseName] WHERE [PO Number]  like "GV_PO_Number"

 

My GV_PO_Number is the number captured from the image 08888 and the [PO Number] is the column name in the databse.

 

Any thoughts??

 

Posted Wed, 07 Nov 2018 14:16:47 GMT by Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

You are enclosing the variable in quotes. This will make it search for the litteral string GV_PO_Number, and not the actual value.

Try this WHERE clause: LIKE "%" & GV_PO_Number & "%"

Here we parse the actual value of the variable encapsulated with the wildcard % sign.

 

 

Phil Robson
Senior Director Support Americas

Posted Wed, 07 Nov 2018 14:28:36 GMT by Daryl Foxhoven

Phil,

I tried your suggestion and get the following message (which seems to be the same message I get for almost all my attempts)

 

Error in system activity: [1: Paradigm PO] LDS Connector error! "Database operation SELECT [PO Number] FROM [Paradigm] WHERE ([PO Number] LIKE \"%\" & '08888' & \"%\") failed. Invalid column name '%'.\r\nInvalid column name '%'."

 

Here was my statement

SELECT PO Number FROM [Database Name] WHERE [PO Number] LIKE "%" & GV_PO_Number & "%"

Posted Wed, 07 Nov 2018 14:28:39 GMT by Daryl Foxhoven

Phil,

I tried your suggestion and get the following message (which seems to be the same message I get for almost all my attempts)

 

Error in system activity: [1: Paradigm PO] LDS Connector error! "Database operation SELECT [PO Number] FROM [Paradigm] WHERE ([PO Number] LIKE \"%\" & '08888' & \"%\") failed. Invalid column name '%'.\r\nInvalid column name '%'."

 

Here was my statement

SELECT PO Number FROM [Database Name] WHERE [PO Number] LIKE "%" & GV_PO_Number & "%"

Posted Wed, 07 Nov 2018 14:42:08 GMT by Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

Try this:

LIKE "*" & GV_PO_Number & "*"

 

Phil

Posted Wed, 07 Nov 2018 14:56:13 GMT by Daryl Foxhoven

Nope.  I didn't think it would be this difficult...have spent the last day trying to get this to work.  Here's the error:

 

   

Time: 10:55 AM

Error in system activity: [1: Paradigm PO] LDS Connector error! "Database operation SELECT [PO Number] FROM [Paradigm] WHERE ([PO Number] LIKE \"*\" & '08888' & \"*\") failed. Invalid column name '*'.\r\nInvalid column name '*'."

Posted Wed, 07 Nov 2018 15:38:35 GMT by Gilles Sauvagnat Altexence Président

Hi 

In case you are trying to use such select in V7, as far as know there is a pending bug in the WF using LIKE

Regards

Gilles

Posted Wed, 07 Nov 2018 15:46:48 GMT by Daryl Foxhoven

I am using v.7....so perhaps that's my issue!  Thanks!!

Posted Wed, 07 Nov 2018 15:46:50 GMT by Daryl Foxhoven

I am using v.7....so perhaps that's my issue!  Thanks!!

Posted Wed, 07 Nov 2018 18:29:10 GMT by Jon Weston

What now? I have a couple of DW7 upgrades scheduled shortly and if there's a bug with using LIKE in workflow then I need to know about it.  Where is this info?

Posted Wed, 07 Nov 2018 20:07:00 GMT by Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

Ok. Everyone keep cool. LIKE does work. However, what it does not LIKE to do is to parse a concatenated string.
Therefore this will fail:

SomeFieldFromDatabase LIKE '%' + GV_PO_Number + '%'

This will work:

SomeFieldFromDatabase LIKE '%' + 0881 + '%'

Of course that is useless since our PO number is dynamic not a fixed entry.....

The solution is to work the problem. Since it does not LIKE to concatenate strings in the WHERE clause with a LIKE operator, we simply concatenate the string we intend to use in another variable first.....

Follow me here....

String Variable - GV_PO_Number
String Variable - GV_TempVar
String Variable - GV_Returned_Value

Data Assignment 1:    Assign the PO Number to GV_PO_Number. (I assigned 0881)
Data Assignment 2:    Use an Arithmetic expression to create the value to parse to the database
GV_TempVar =   '%' + GV_PO__Number + '%'

Data Assignment 3:   Execute the query (UpdateValue is simply the field name I used in an external DB)

GV_Returned_Value = UpdateValue LIKE  GV_TempVar

Result = AAAC-00881

Note!! Each data assigment must be a seperate step, do not assign them in one step.

 

Phil Robson
Senior Director Support Americas

https://www.docuware.com/sites/default/files/forums-images/LIKE.png

Posted Thu, 08 Nov 2018 13:31:03 GMT by Gilles Sauvagnat Altexence Président

Dear Phil,

To be precise one should read

"what it does not LIKE to do is to parse a concatenated string since V7 update"

as it worked before

Best regards

Gilles

PS : anyway thanks a lot for the workaround

Posted Thu, 08 Nov 2018 13:42:35 GMT by Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

Gilles,
Fair point. I had a quick look for the bug yesterday and could not find it, so I did not dwell on it. However, there is a fix for the LIKE operator imminent in the Cloud, and presumably in the next On-Premise 7 update. I'll test it once it is released and report back here.

 

Phil

Posted Thu, 08 Nov 2018 18:02:00 GMT by Jon Weston

Hi Phil, are you seeing something similar with parsing dates in WHERE clauses? I'm testing an upgrade to DW7 and noticed that a duplicate invoice check that I'd built was broken and am trying to diagnose it with the help of this thread as well as this one: https://www.docuware.com/forum/english-forums/docuware-help-technical-pr...

I've gradually stripped away the various criteria from the WHERE clause and found that the INVOICE_DATE = GV_InvoiceDate bit was the problem - as soon as I take that part out it works.  As a matter of fact just leaving that part in by itself doesn't work.  I've tried using the Assign Data task using both External Data and File Cabinet as the source but it just refuses to match the date.  I've tried putting single quotes around the GV the way we had to prior to DW7 and that didn't help.

Posted Thu, 08 Nov 2018 18:11:32 GMT by Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

Jon,
I'm not aware of that problem. Can you post the actual expression and the WF History showing the error condition? I'll then have to work through it.

 

Phil

Posted Thu, 08 Nov 2018 19:03:05 GMT by Jon Weston

For sure, Phil.  Three pics uploaded:

  1. the Assign Data at the beginning of the wf where I assign the GVs so you can see I've assigned the GV_InvoiceDate
  2. the WHERE clause.  very simple.  as you can see, this should even get a hit on the document itself, never mind any other docs in the cabinet, because I haven't included the DWDOCID <> GV_DocId filter
  3. the workflow history of the document, showing the blank Invoice Amount that resulted from the WHERE clause that didn't return any matches

I really hope I'm missing something simple here.  I have a hard time believing that I'm the only person that's trying to reference a date-type GV in a WHERE clause - others must be doing it successfully.

https://www.docuware.com/sites/default/files/forums-images/1%20assign%20GVs.png

https://www.docuware.com/sites/default/files/forums-images/2%20where%20clause.png

https://www.docuware.com/sites/default/files/forums-images/3%20no%20result.png

https://www.docuware.com/sites/default/files/forums-images/3%20no%20result_0.png

Posted Thu, 08 Nov 2018 19:30:58 GMT by Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

Jon,

I tested it as follows: GV_InvoiceDate was extracted from the document index field "Date".
My lookup was against the Documents file cabinet, Amount field.
Expression was DW_DATE = GV_InvoiceDate

Amount returned was $667.80 which is correct.
Which version of DocuWare 7 do you have installed?

Mine is: 7 (Build: 7.0.0.8468)

(Look at Web Client > About)

 

 

Phil

Posted Thu, 08 Nov 2018 19:57:17 GMT by Jon Weston

Darn, I thought I'd found the problem: I was missing the "DW_" from the front of the INVOICE_DATE field in my WHERE clause (I was referencing the sql name of the field - check my second pic and you'll see what I mean).  I fixed it but that didn't solve the problem, which surprised me quite a bit, tbh.  I'm on version 7.0.0.8441 (early October's release, I believe) so I'll grab the latest release and see if that helps.

Posted Thu, 08 Nov 2018 20:02:02 GMT by Gilles Sauvagnat Altexence Président

Jon

Try with INVOICE_DATE = 'GV_InvoiceDate'

The single quotes should make the trick.

Regards

Gilles

Posted Thu, 08 Nov 2018 20:07:35 GMT by Phil Robson DocuWare Corporation Senior Director Professional Services, Americas

Jon, Yes grab the update.
Gilles, I did not use the quotes - but you never know till you try it. Right?

 

Phil

You must be signed in to post in this forum.