Spliting values with dates

[Ralph Schwegler]

Dear mates

Sometimes, I feel like I have to learn FM again, and I am happy to (try to) do that.

I have invoices, with the gross amount and date (amongst other values). I have the invoices linked to the addresses. I can show the sum af all invoices. Now I want to split that up on the last five years.

What would you recommend to use?

I know how to do it "old style" with a script which feeds five different cells. Is that still the way to go?

Cheers

[Vaughan]

A summary report by year?

[Ralph Schwegler]

can I show it on screen while browsing the addresses?

[comment]

I want to split that up on the last five years.

What exactly do you mean?

I know how to do it "old style" with a script which feeds five different cells. Is that still the way to go?

That was never the way to go.

[Ralph Schwegler]

I stand corrected. I didn't like that way, but that's what I did.

Say we have 5000 EUR in 2010, 9856 in 2009, 1900 in 2008, 12000 in 2007, 0 in 2006 and 2005. I want to see that on screen, taken from the amounts in the File/Table Invoices.

[comment]

In version 10 and higher, you can view sub-summary parts in Browse mode.

I don't see how the addresses figure in this.

Edited July 13, 2010 by Guest

[Ralph Schwegler]

say customer #x has the numbers I stated in my previous reply. I want to be on x's record and see it's figures.

Any specific help? I cannot get it out (yet)...

[comment]

There are several ways to do this - I'll outline one that is simple, if somewhat laborious:

Define a new table Slots, with two fields:

• CustomerID

• Year

Populate the table with all possible combinations of customer and year (this could be automated).

Define a relationship between Customers and Slots, matching on CustomerID, and a relationship between Slots and Invoices, matching on year (assuming you have a calculated year in Invoices) and CustomerID.

Place a portal to Slots on a layout of Customers. You need two fields in the portal: the Year field from Slots, and a summary field (Total of Amount) from Invoices.

---

This can be further smartened up by changing the CustomerID in Slots to a global field, and populating it by a script triggered on record load.

Edited July 13, 2010 by Guest

[Søren Dyhr]

This can be further smartened up by changing the CustomerID in Slots to a global field, and populating it by a script triggered on record load.

Huh "smartened up" ... while indeed doing it myself, is it to me almost cheating!

--sd

[Ralph Schwegler]

Thank you. I did it that way, which works:

•join Table, with years, customer id, calculated amount and a field called year.custid

•relation between customers and join, with customerid as key

•relation between join and invoices, with year.custid as key

•second relation allows me to calculate the yearly amount

•first relation is used to create a portal with year and calculated amount from the join

cheers and thank you :

[comment]

The year.custid field is not necessary: you can define a relationship matching on two fields.

(I have edited my previous post, since this part was missing.)

[Ralph Schwegler]

that's right. do you think there is a huge performance difference?

I have to work on the field customerID (I divide it with 10, as there is much data which should stay untouched – it is still unique, and usable as a key)

[comment]

do you think there is a huge performance difference?

I don't think so - but I have never tested one against the other.