Trying to do a "like" or "contains" operator in WF Designer-1541600433

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??

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

Phil,

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

Phil,

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

Try this:

LIKE "*" & GV_PO_Number & "*"

Phil

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:

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

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

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

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?

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

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

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

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.

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

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

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

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.

Jon

Try with INVOICE_DATE = 'GV_InvoiceDate'

The single quotes should make the trick.

Regards

Gilles

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

Phil