cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need help navigating or using the PTC Community? Contact the community team. X

Query Builder Help - How to fill a column when condition is true

mariaSutton
7-Bedrock

Query Builder Help - How to fill a column when condition is true

I am creating a report listing task details.

One of the columns in this report is for the "Completed Date" of a task.


I need help implementing a condition for the "Completed Date" column. The condition is:

          If "Work Item.Status" equals "Completed"

               then populate the "Completed Date" with "Wf Assignment. Last Modified",

          else, leave "Completed Date" null/blank.


How can I go about accomplishing this?
Should I create two reports? If so, how do I merge the reports to see both completed and uncompleted tasks?
Is this something that is done under the "Select or Constrain" tab or under the "Criteria" tab? 

TIA!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi again,

I was able to figure out what was missing. When I would run the report an error was getting thrown about an invalid number.
The result that "Work Item.Status" returns is not a string or a number, its a enumerator. 
I was able to add a Database function of "To Character" then under that "Work Item.Status". 
After doing this the first value under Database "Decode" can be compared to the second arg which is a string value of "COMPLETED".

MR_10589254_0-1697555750304.png

 

View solution in original post

7 REPLIES 7

Use the "DECODE" database function.

 

Name the column "Completed Date"

First argument to be "Work Item.Status" (what you're evaluating)

Second argument to be "Completed" (the value you are looking for)

Third argument to be "Wf Assignment.Last Modified" (the value you want to display if the value you're evaluating matches the one you're looking for, i.e. arg2 = arg1.value

Fourth argument to be blank text string (the value to return if the value in arg2 not found as value of arg1

 

Link to WC Help database function: Database Functions

 

Hope that helps!

Hi @aaronjlarson,

Thanks for your reply. I am trying out what you described but cannot get a result. Can you let me know if this looks right?

These are the steps I did:

  1. Under Select or Contrain tab, selected Database Function
    1. In Add Database Function modul/popup form
      • Display Name = Completed Date
      • Database Function = Decode
      • Operator = equals
      • Value = Constant value
      • 1.PNG
    2. Under Decode dropdown
      1. Add Reportable Item "Work Item. Status"
      2. Add Constant String "Completed"
      3. Add Reportable Item "Wf Assignment.Last Modified"
      4. Add Constant String "" (blank)
      5. 2.PNG

Hi again,

I was able to figure out what was missing. When I would run the report an error was getting thrown about an invalid number.
The result that "Work Item.Status" returns is not a string or a number, its a enumerator. 
I was able to add a Database function of "To Character" then under that "Work Item.Status". 
After doing this the first value under Database "Decode" can be compared to the second arg which is a string value of "COMPLETED".

MR_10589254_0-1697555750304.png

 

Yes - was just testing that and was going to post same thing as well.  I didn't check that before my first response and since it's an enum you must specify in the internal value, case-sensitive, when specifying as string criteria in a database functino.  I see it works too in my example. Not sure what you're going for, but in my simple test I thought to just use the work item's last modified date as the "Completed Date" if the status is COMPLETED - because if the item is complete wouldn't the completion date be the "last modified"?  Maybe not in your case... anyhow glad you got it working.

aaronjlarson_0-1697556219427.png

 

Hi @aaronjlarson 

I guess that the issue is if the state is completed, then the Last modified can be still changed in future so it can be irrelevant information when it was really completed 

PetrH

Like I said - in my case this would work because that condition could never happen.  Depends on your environment/use-case.  If you really wanted to dig into when the assigned user clicks the "Complete" button on the task you would have to pull in the Wf Voting Event Audit table to the query and use the "Created" date from it as the "voting event" is created when the task is completed.

@aaronjlarson, what table would you join the Wf Voting Event Audit to if you were to try to get the Wf Voting Event Audit "Created" date? 

Top Tags