Report for status stagnancy

Tom_Cat · 01-13-2020 12:11 PM

Hi,
I have an app that tracks times for order fulfillment. There are various steps in the order process from “order received” to “order shipped”. I wanted to know if I can generate a daily or weekly report that shows me all the active orders and how long they’ve been at their current status. So for example it will show:
Order 1234 - Status: Processing - Last update 3 days ago, or something like that, so I know that this order has been forgotten for 3 days.

Suvrutt_Gurjar

Hi @Tom_Cat,

I believe what you are looking at is extension of the discussion we had in earlier post.

I believe it is possible to have the status by days. You may wish to use a report template with expressions something like below

Please replace column names and change expressions suitably. [ID] is key column, [Receiving], [Assembling], [Testing], [Final QA], [Shipped] are date type columns with dates of respective statuses.
The report looks something like below

Hope this helps

Edit: If you wish to have ageing as of date ( as of today), you may wish to have a template expressions something like below

Tom_Cat

Hi Suvrutt,
Thanks for the reply, I’m trying to wrap my head around this.
So from what I’ve undertood about reports, should I put these tables you made in a google-docs template and use it as the email template to be emailed?
What I am having a hard time understanding is how the reports operate “for each row on this table”
does that mean that it will send 10 emails if there are 10 updates?
if I just make a table with 1 row as you have made, will it automatically populate multiple rows with all different orders and their respective status?
Also, should I add a virtual column that keeps track of last-updated ??
like a virtual column with a value of =TODAY(), that I would assume would get re-set or over-written every time a row is edited. that way I can keep track of “time since last update” by doing a =NOW() - [timesincelastupdate] ??

Suvrutt_Gurjar

Hi @Tom_Cat,

Here are my responses.

In general, yes, the report expressions template in Google docs will look similar to the samples shared by me. I shared the report templates tested examples with you. However the exact expressions will defer based on table name, column names and conditions ( Like say records only for last week) you wish to have in your reports.

Yes for “each row in table” will generate one email for each row. Depending on what kind of report you wish to generate (per record or group of records) you need to select options between “for each row on this table” or “ForEntireTable” .

Did you get a chance to take a look at the following article where this setting as well asoverall report template creation is very well described.

Yes, if the template has proper SELECT() statements- (enclosed within <<Start: and <> parameters) - that select multiple rows the report template, even though the template shows just one row, it will repeat itself to add more rows in the final report that satisfy the logical conditions in the SELECT() statement.

Again the above referred reports article has excellent examples of various report template types.

You may wish to elaborate this more, as what conditions you wish to include in report. In general, yes, reports can be very much configured through SELECT() statements or through slices to include only those rows that meet your reporting need. So if you have say [Date] column for each record, you may write conditions to include records only within certain dates, for example.

You may also wish to take a look at teh following sample app, just in case you have not. The app has some good report samples

https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=245...

Tom_Cat

Hi Suvrutt,
Thanks for that I’ve been looking at that and I was able to make a report that shows all new oders placed in the last week.
However, I am having an issue finding a way to report the orders that have not been updated in a week. I am not sure how to “FILTER” these out, each row consists of the following columns:
Order ID, Order Type, Status, Date Ordered, Date Assembled, Date Packaged, Date Shipped.
As each status is updated it fills in the date of when it was updated, however I don tknow how to make a filter that says "look for the most recent date and check if that has been more than 5 days"

The app you linked does not let me look at the report templates so I cant see how they filtered their views

Steve

Try this:

(
  ANY(
    SORT(
      (
        LIST(
          [Date Ordered],
          [Date Assembled],
          [Date Packaged],
          [Date Shipped]
        )
        - LIST("")
      ),
      TRUE
    )
  )
  < (TODAY() - 5)
)

LIST([Date Ordered], ...) produces a list of all possible dates from the row you want to consider.
(... - LIST("")) removes any blank dates from the list from (1).
SORT(..., TRUE) sorts the list from (2) in descending (newest to oldest) order (per TRUE).
ANY(...) extracts the first item from the list from (3), which should be the most recent of the dates.
(... < (TODAY() - 5)) answers the question, is the date from (4) earlier than five days ago?