Question

Photo of Eddie Holeman

0

Run SQL action in Workflow not returning value

2

I created a Workflow trigger off the addition of a resource to a Room Management reservation.  The trigger is successfully initiating my workflow. Using the Attribute Set from Entity Action in the Workflow, I am successfully putting the Guid of the resource into a text attribute.  I am then attempting to run a Run SQL action to get the ApprovalState value from the [_com_centralaz_RoomManagement_ReservationResource] table so that I can make decisions in the workflow depending on the value.  The action is successful in the Log, but the Integer attribute doesn't get the value.  Here is the RunSQL action I am using:

RunSQL.PNG

If I place a known guid value in place of the Lava, the return is correct and the Approval State attribute contains the value.  Any thoughts on what I am doing wrong? I am assuming that I am doing something wrong with Lava, but can't see it.  Thanks.

  • Eddie Holeman

    Eddie Holeman

    Daniel, thanks for the idea. I should have put my screenshot of my lava with that format. I have tried that that lava format as well as the not declaring the SQL variable. Neither have been successful. I also tried a SanitizeSQL lava filter just to see if that would clean up anything. My assumption is that the RunSQL action is actually successful, but something about the Where clause is causing it to return no results.

    • Eddie Holeman

      Eddie Holeman

      Daniel, I failed to mention that I have an Attribute Set Value action in the workflow and I am using {{ Workflow | Attribute:'ResourceGuid' }} in the Text Value field. It brings in the value of the guid with no problem, so feeling confident that the problem somehow is in the the SQL statement returning nothing. I might try the SELECT '{{ Workflow | Attribute:'ResourceGuid' }}', but will need to change the attribute type that I am pushing this into. Right now it is an Integer attribute type.

      • Photo of Daniel Hazelbaker

        0

        Hey Eddie, I don't know if this is the exact problem, but your Lava syntax is using legacy syntax which may or may not be disabled on your system. It should be `{{ Workflow | Attribute:'ResourceGuid' }}`

        You can also just do:
        ...
        WHERE ccRRR.[Guid] = '{{ Workflow | Attribute:'ResourceGuid' }}`

        without having to declare a SQL variable first.

      • Photo of Daniel Hazelbaker

        0

        You might try just this as the entire SQL query:

        SELECT '{{ Workflow | Attribute:'ResourceGuid' }}'

        The idea being, it should return the value in the attribute. It seems one of two things is going on. Either the ResourceGuid attribute does not have a Guid value (blank), or it has a Guid value that does not match any item in the reservation resource table.