Unable to generate report based on authorised cases

[wfmike]

Hi support, 

I'm currently trying to create a nightly export to a CSV based on Requests and h_bpm_email_approvals 

In the report builder, I LEFT JOIN the h_bpm_email_approvals to Requests and filter table column outcome = accept 

However, on Data Preview the filter doesn't seem to be working (see attached) 

Am I using the right method to filter cases based on approvals?

Thanks

Headset Approved_1591.csv

[Victor]

@wfmike you posted this in About the Forum -> Problems and Questions section which is intended for issues and queries about the forum itself. Your query has now been moved to the product section where it will have appropriate visibility. Please be mindful of where you raise a query so it can get to the right audience...  

[Victor]

@wfmike 

On 4/17/2020 at 6:19 PM, wfmike said:

CSV based on Requests and h_bpm_email_approvals

As the name implies, h_bpm_email_approvals, this table is designed to specifically store approval actions via email. Are you looking for results specifically on approvals made via email? If not, then this is not the table you need. Also, if you don't have email approvals in place this table will be empty.

On 4/17/2020 at 6:19 PM, wfmike said:

I LEFT JOIN the h_bpm_email_approvals to Requests and filter table column outcome = accept 

Not quite. Based on the screenshot the tables are not joined. When joining tables there needs to be a condition involving a field from each table. What you have there is a filter which should be in the filter section, not in the join condition section. However, let's confirm first if this the table you actually need.

On 4/17/2020 at 6:19 PM, wfmike said:

Am I using the right method to filter cases based on approvals?

By the looks of it, no. Let's start defining these "approvals" in your business and workflow context. Can you give me some examples of what an approval is in you instance and how this approval takes place?

[wfmike]

Hi @Victor - thanks for getting back to me on this. 

Regarding the h_bpm_email_approvals - I'm using the Authorisation node and have set an authoriser in it. 

1. An email is then sent to the authoriser 

2. Authoriser either Approve, Tentative or Rejects. 

Is the above not the table that would manage this?

Just so you know, I've implemented a workaround by using a custom field to take the outcome, which I've then used as a filter in the report. 

[Victor]

@wfmike apologies, I wasn't very specific in my previous comment. h_bpm_email_approvals is used by the "External Authoriser" workflow node which is a (very) new functionality still under experimental flag (iirc). I would be surprised if you have it in use since is in very early stages and we still only putting together documentation for it. Most likely that table is actually empty in your instance and iirc is empty in any live instance.

Assuming you are using the Authorisation tasks (https://wiki.hornbill.com/index.php/Business_Process_Designer) either the auto or normal one, then the authorisation outcome is stored in the BPM Authorised (h_bpm_authorised) field in the requests table. This field can have the values on 1 for an approved outcome or 2 for a rejected outcome. So if using the above mentioned node then is not necessary to store the outcome in a custom field and have the report filter on the BPM Authorised field based on the field values mentioned above.

[wfmike]

Thank you @Victor - that's cleared a few things up for me.