YAQBQ - Yet another query builder question - Lifecycle History

[avillanueva]

I am looking to report out lifecycle history event timestamps against Part Requests. I've done this with other objects but in this case, Part Request is iterated so those events are occurring against difference object iterations. 

The QML from CS420621 is a method I've used before where you linked to the object history and filter by state. To get them into columns, I believe I needed to create multiple calls to lifecycle history:

Now, in some cases the Part Request might not have an phase for that state, so I would expect nulls and having to use outer joins. Despite my best efforts, Query Builder seems to opt to just filter those out (only showing ones that had all states).

To deal with the multiple iterations, I added criteria to link on the master id:

What should be hundreds of rows, drops to a few dozen. So easy to handle this in SQL. Any obvious mistakes or other things to try? Goal is to list events in the same row, one row for each request. If there was no event for that state, it should report nothing, null.

[joe_morton]

---

If there was no event for that state, it should report nothing, null.

---

From my experience with Query Builder, anytime you say IF / THEN, it's going to really hard to build in Query Builder, if not impossible. 

• Best/easiest method from the query standpoint is to report every state change as its own distinct row. You would need a separate dashboard to manipulate the data to show all state changes in 1 row.
• Another thing you could try, (although I still wouldn't recommend it) is to change the links to outer joins as well. Right click the arrow, modify join. The arrows will be green. That should help reduce the number of items getting omitted. It's still really hard to get the intended results.