When using the standard Microsoft ER configurations or when building a custom ER report, you sometimes find there is missing data. If are in this situation you can sometimes find the required data using the standard data relationships. However sometimes you will need to create your own relationships. I like to use calculated fields to relate two tables that have no native relationship. In this post I will demonstrate how you can use a calculated field to link tables and use the data.
In this example I will use data from the supplier invoice (VendInvoiceInfoTable) and join the Workflow Tracking Status Table (WorkflowTrackingStatusTable). The workflow tracking status table ContextRecId column matches the RecId column from the VendInvoceInfoTable. Our output must represent a list of the pending invoice and the respected active workflow instance ID.
To build this report I have setup a data model and have retrieved the VendInvoiceInfoTable and the WorkingTrackingStatusTable. Add the calculated field underneath the Pending invoice section because this table is the base table for our report. Enter the following formula in the calculated field:
WHERE ( WorkflowTrackingStatusTable,
WorkflowTrackingStatusTable.ContextRecId = PendingInvoce.RecId )
Note that you can also use FILTER. This function differs from the WHERE function, because the specified condition is applied to any Electronic reporting (ER) data source of the Table records type at the database level.
Once the join has been made, you can use the standard table relationships to expose more data to your desired destination. For example, work item information can be retrieved from the WorkflowTrackingStatusTable data relationships.
When linking these fields to the data model its very important to understand the data structure. For example, a supplier invoice and have more than one related instances. This needs to be reflected in the data model structure.
The above data model structure has been setup with two “record lists” sections. One for the pending invoices and another one the workflow instances. That’s because you want to retrieve more than one pending invoice each time you run the report and each pending invoice can have more than one workflow instance. If you decide not to work with record list but instead link the instance underneath the “invoice record” then the system will only retrieve the first workflow instance record. Therefore I recommend you investigate the data before you modify or create a new data model structure.
When I defined the report conditions and stated that the report must retrieve the pending invoice and ACTIVE workflow instance. By understanding the data we know that there only be a single active instance assigned to a pending invoice record. Therefore it would be ok to bring the “instance” field underneath the “invoice record” section if the calculated field takes ACTIVE condition into account. So let examine how this can be achieved.
We need to extend the current calculated field to include a second condition. The second condition can be added in the same field so we don’t have to create a second calculated field. The second condition can be added using the AND function:
WHERE (
WorkflowTrackingStatusTable,
AND(
WorkflowTrackingStatusTable.ContextRecId = PendingInvoce.RecId,
WorkflowTrackingStatusTable.TrackingStatus = Enums.WorkflowTrackingStatus.Pending )
)
The second condition looks at the workflow tracking status. This column is of type enum, therefore I had to add the relevant enum as data source. Once the enum is added as a data source you can use each enum value as filter.
The following output was extracted:
The second record didn’t present a workflow instance that’s because the invoice hadn’t been submitted to workflow.
The AND and OR functions can be used to extend calculated fields as shown above.
Comments