Summary Reports

[andrewlaw]

I have a table called "articles" and a child-table called "article_status" which tracks the status of an article through various stages. I'm unable how to figure out how to create a report which shows the article and it's most recent article_status.status filtered by the article_status.status field and show both the article.create_date and last article_status.status

[a.gneady]

The Summary Reports plugin won't work for this case as it creates reports of aggregate data (like sum of order items in an order, ... etc).

For this case, you can use calculated fields. In the main table, articles, create a read-only field, naming it something like last_status and configure it as a calculated field with the following query:

Code: Select all

SELECT `status` FROM `article_status` WHERE `article_id` = '%ID%' ORDER BY `id` DESC LIMIT 1

I'm assuming the name of the status field is status, the name of the lookup field is article_id, the name of the primary key of the article_status table is id, and that it's an auto-increment field so that the most recent status would have the largest id value.