Running Summary Sort

[LaRetta]

Hi Everyone,

I have an Inventory portal displayed on a Products layout. They are (of course) joined on ProductID. We view this portal descending by MoveDate.

I have a summary field in Inventory set to Running Total. However, this running total only displays in ascending order (by record creation) it appears.

My Users only view the top entries of this portal (latest entry). The current inventory balance listed next to the latest date does not match. I know I could place the field outside the portal to display the current balance; but the running total (back through time) is also important to view.

I've tried sorting via Relationship, portal, Reorder based on summary field and even physically sorting the Inventory but can't make the running summary reverse. Any ideas?

LaRetta

[comment]

Interesting. I've made a small test and it seems that:

1. Sorting the portal or the relationship reverses the RECORDS ORDER - not the VIEWING ORDER. IOW, a portal will always show the FIRST RELATED record in the topmost row;

2. A running summary looks at records in RECORDS ORDER - therefore the "history" will ALWAYS run from top to bottom.

What you would like is to for the RECORDS to remain in their proper (date ascending/record creation) order, but to VIEW them from bottom to top.

Ideas? If you could do without scrolling (fat chance), you could rearrange the portal, row-by-row, from bottom to top. Or, you could do the running total yourself in the child table by looking up the previous balance. If it were me, I would place the current state outside the portal, and put a nice bold label saying 'History" above the portal.

[LaRetta]

Hi Michael,

Peculiar it is ... if I sort the portal descending, the running total doesn't run. Unsorted displays correctly as:

- MoveDate - Activity - In/Out - Balance

06/15/05.....Purch........200.......200

06/16/05.....Sale.........-15.......185

But if I sort descending, it then displays:

- MoveDate - Activity - In/Out - Balance

06/16/05.....Sale.........-15.......-15

06/15/05.....Purch........200.......185

I even created a calculation in Inventory using the summary field and placed that in my portal. A calculation is supposed to attach at the record level, thus displaying the running summary matching that record (in it's natural unsorted created state in the table??), but it displays as above also (correct if ascending only). So running summaries are one-directional and tied to a table more than (even) a record. I suppose they are aggregate so that fits, but it's perplexing anyway.

I had hoped to view a date and see what the balance was on that date but, as you say, theory won't hold. I like your idea and I'll use a History button opening a small Inventory window instead. It works great that way. Thank you!

LaRetta

[comment]

Actually, it seems quite sensible to me. It has a logic to it - even if the logic sometimes doesn't fit the facts as we humans see them. So:

- MoveDate - Activity - In/Out - Balance

06/16/05.....Sale.........-15.......-15

06/15/05.....Purch........200.......185

means that you have started by selling (short) 15 pcs, leaving you with a deficit of 15. Then, on the next day (and it IS the next day according to the defined sort order), you have purchased 200 pcs, thus covering your deficit and leaving you with a balance of 185. Perfectly logical.

Reminds me of the story about three scientists observing a house. Over the time they see 2 people enter the house, and 3 people leaving. ... and then the mathematician said: "Now if one more person enters, the house will be empty again."

[LaRetta]

"(and it IS the next day according to the defined sort order),"

So sort order doesn't know the 16th comes after the 15th? FM sees the 15th as coming after the 16th because it's now on row 2? MoveDates were entered 15th then 16th (they are the creation date).

We started 6/15 with 200 purchased - balance 200. Then on 16th, sold 15, balance 185. And if I look at 6/16, it says the balance is -15 when it's 185. I think I understand what you're saying, ie, that because I changed the MoveDate sort, summaries starts calculating from there regardless, right? I liked your anology. Maybe I'm confused (like the mathematician in your example) because I'm a closet-mathematician*.

I want Users to view the last 20 related records only. If they want more history, they can click History button. I can eliminate scroll and leave it unsorted to display proper balances (on each specific date) with final at the bottom. Is it possible to restrict the relationship to the last 20 related records (or any group of record numbers)? Joining on ProductID and something else?

I considered using serial (number) or MoveDate(number) but there may be holes so simple multiline (or range key) won't work, will it? Would I need to script it?

* Closet-mathematician: One who uses their fingers to count and gets confused converting fraction to decimal; but who spends empassioned hour-upon-hour wallowing in calculation beauty.

LaRetta

[comment]

So sort order doesn't know the 16th comes after the 15th?

That doesn't make much sense, I am afraid. 16th comes after 15th only if the sort order is ascending. That's what "order" means: what comes before/after what. When the order is descending, 16th comes before 15th. And a running total runs according to the specified order.

[LaRetta]

"So sort order doesn't know the 16th comes after the 15th?"

Poor wording. What I meant is that summary does not attach itself to the record (based upon unsorted creation order (as I thought it should) but rather begins its summarising based upon the record order specified (sort) and the found set, ie, it doesn't reverse itself. We are in agreement.

I considered posting my question (about restricting the relationship) in a new thread ... but I thought others who face this logical illogic may want ways to address it as well when they read this thread with the same problem.

But I realize that won't solve anything ... because the summary will still start calculating only with the matched relationship records so it still won't be accurate (final stock count on a specific date). Back to the History button like you suggested originally.

I can't specify a separate portal for each row and reverse (stack) them because I don't know how many related records I will have - and I would need to specify that (portal row number) I think. Ah well ... I'll keep playing with it.

LaRetta

[comment]

summary does not attach itself to the record (based upon unsorted creation order (as I thought it should)

Yes, but if you don't mind, I will add two quibbles to that:

1. In many applications, records can be back-dated. For example, a bank can credit/debit your account retroactively. The account statement, however, is sorted by date, and the running balance must follow that. So I don't think summary should follow creation order. Summary is a group attribute, not a record attribute. It should follow the definition of the group - and that includes the group's sort order.

2. There's no such thing as "unsorted creation order". In fact, that is an oxymoron. The term "unsorted" is IMHO an extremely poor choice of FMI to describe a state that is in fact very much sorted - by record creation. Even randomly ordered records are ordered. Once you can see them, you can point at any record and say 'this record comes before that one'. That is as sorted as it ever gets.

[LaRetta]

I will add two quibbles to that

I like quibbles. Nothing gets me sparking like quibbles. They provide alternate (and deeper) perspectives ... keep 'em coming.

Management wanted running balance in portal. So I unsorted (smile) the portal so they display in ascending order by MoveDate/creation date (smile) and the running balance is correct (as we knew it would be).

Then I just scriipted a Go To Portal [ Last ] when switching to that page (and also to the record movement arrows. Row highlights but is unaccessible. It looks like I'm drawing their attention to the stock balance. I like things that look planned and clever but are in truth a guise.

I suppose I can also place a button next to the portal to jump to the 'latest and greatest' figures. Maybe they'll agree to an Inventory window instead. Thanks again sir!