I am working on a workflow that uses WFA's "Incremental naming" feature for volume names. Even though the volume name the workflow is about to create will be automatically generated, I'd like the UserInput form to behave in certain ways based on what that new volume-name WILL be (when the workflow executes). One approach I found is that I could have a hidden userInput field (in an un-expanded UserInput group). I call this field $displayHighestVolume (example). I make it a type QUERY and have it search the storage.volumes table and return the highest volume-name is use (probably similar to how "WFA Incremental Naming" does it.) So I can tell it (example) to search for volumes with name = "volume%" and it returns "volume78". I can then presume the next volume name "WFA Incremental Naming" picks will be "volume79".
----- UserInput Field: $displayHighestVolume -> Type QUERY: SQL ----
storage.volume.name LIKE "volume%"
storage.volume.name DESC LIMIT 1
So far this idea works great and it lets me do what I need to do in tests with my $displayHighestVolume field in the way that I need (ie; determine if it is even or odd is one thing). The trick is when there is no volume at all yet with the name prefix "volume". So, just like WFA Incremental Naming where you specify a value to use 'if nothing found' ... I need a similar way to return something, like "volume00" in the case where there is no volume yet. A quick google search point me to SQL functions IFNULL() and COALESCE() but I've been unable to crack the nut.
I should add that I also tried COALESCE but I'm not sure it is appropriate and it didn't work as I needed ... and not sure I did it right (I think coalesce is for for "null" values, not a completely null set of rows??)
Hey Tim, Thanks. That works perfect 'conceptually'. It also works perfect in the SQL [Test] button window provided to test SQL query code. However, when I click [OK] twice and then [Save] to save the workflow the clock cursor spins a few seconds (always a bad sign 🙂 then pops a window saying:
java.lang.RuntimeException: An unexpected error has occurred while attempting to format SQL. It may be that the SQL provided is not well formed.
To make sure of what I was seeing I simplified my SQL to the exact cut/pasted example from above. With the UNION in there I get the 'not well formed' error when saving. Without the UNION the workflow saves fine. I also created a new workflow with a single step: 'Create Volume' cmd and the above SQL QUERY. It also [Test]'s Okay but gets the 'not well formed' error when I attempt to save the workflow. take out the UNION and it saves fine. I'm getting impression it 'should' work but doesn't'.
Do others see similar behavior?
I'm doing this in WFA 2.2RC1 and don't have ready access to test 2.1 or other versions.