Summarizing Data - CONFUSED

[panchristo]

Hi all!

I' m having a small problem trying to summarize data correctly.

Here is the case:

Table: People

pk Person ID

Table: Payment Categories

pk Payment Category ID

Category Name

Table: Payments

pk Payment ID

fk Person payed

fk Person(s) affected ID

fk Payment Category ID

Payment amount

CALC Cost per person (=amount/ no. of person IDs)

The tables relate to each other as follows:

Every payment is assigned to a person through the "who paid" field, but also through the "who is it for" field, accepting values of person IDs from Table People.

Also, Payment Categories Table provides the value list for Payments table.

What I am trying to achieve is not only to have through the People table/layout the ability to see in portals the payments from each person, but also:

Summarize categories of payments from a single person with their corresponding Totals.

So far I have been able to bring the payment categories used by each person by connecting the Payment Categories TO to the People Table through an in-between Payments TO connecting to People with Person ID and to Payment Categories through the Payment Category ID field. What I can't do though is get totals for every Payment category each person has used.

After that I believe I can sort out how I can calculate according to who has paid and who is the payment to be billed to (multiple persons possibe), therefore calculate remaining amounts and make balances for an overall month.

Can someone help please?

[panchristo]

I thought I may be on the worng thinking path from the beginning, so I'll try to explain what I need to do in total in case it is more clear that way.

Im' trying to set up an application which will monitoring expenses.

1)It will involve a number of persons (not predefined and easy to change)

2)It will track expenses under the principle of who paid for each expense and who this expense concerns (of the people involved in the app).

3)Each expense is characterized by a Category list (also customizable) and a short description.

4)Each expense may be a combination of person(s) paid + Person(s) billed to, as well as corresponding amount values.

For example:

Expense 1

Category type: Personal expenses

Description: Hair styling

Expense cost: $25

Paid by: John $15| Mary $10

Billed to: Mary

Expense 2

Category type: Food Supplies

Description: Super Market

Expense cost: $60

Paid by: John $30| Mary $30

Billed to: John | Mary

The application will need to:

1) display per person the expenses he is supposed to have paid for (Total expense amount, participation amount)

2)Calculate who-owes-what-to-who

3) Have reports for each month summarizing per person and category of expense.

Can anyone suggest an effective schema?

So far, I have been able to do some of the things but when it comes to calculating who-has-paid-what and whho-owes-what-to-who I'm a little stuck.

I've used a middle table of expenses which creates child records in a "Payments Table" and a "Billings Table" in order to be able to assign multiple people to each payment but also to who it concerns. One problem I am facing is that at sometime I need to have a calculation field in "Expenses" which will say which people are involved (either as having paid or billed to) and in turn use this as a match field for the people table, but I can't get it to work...

So can anyone suggest something effective as a whole?

P.S. I hope I 'm not asking too much from friendly volunteers who may think I'am exploiting them...

[comment]

It sounds to me like you already have a correct structure. I didn't get the problem you are describing.

The one question that I do see here has to do with summarizing by person: if Billings and Expenses are in separate tables, it will be difficult to get the balance (if that's how it works - I don't see that the billings have amounts??).

Edited May 15, 2010 by Guest

[panchristo]

Ha! At least one answer...

Well, I have found a way to calculate balances:

First of all, the amount to be billed to each person is calculated by counting the number of people that participate in the expense entered through the corresponding portal (and dividing the expense amount by that).

If for example for an Expense ID there is one Payments related record and 2 Billings, I would perform an automatic calculation (with several CASEs/IFs - I admit) in Billings comparing the Person IDs and find that one of them has he same Person ID as the one that has made the payment. In that case, the the Billings record multiplies the portion of that person by +1 and the other's by -1 (i.e. in the other record). So when all these are filtered through a portal to the People Table, a sum is calculated for all the Expenses alltogether, calculating the total debt of each person (can't find a way to calculate to who this debt will be though).

The problem starts when you try to incorporate this in portals in order to be able to view the info for each person separately. There, calculations that are 2 tables away don't work and can't be used as match fields.

As one becomes thirsty (me), I tried to incorporate things that a user would like as summaries by categories of expenses fin each person's record(People table portal), date filtering etc...

[comment]

I would perform an automatic calculation (with several CASEs/IFs - I admit) in Billings comparing the Person IDs and find that one of them has he same Person ID as the one that has made the payment. In that case, the the Billings record multiplies the portion of that person by +1 and the other's by -1 (i.e. in the other record).

I am afraid you lost me there. I don't see why - in theory at least - a person cannot bill themselves (and pay to themselves as well). I can bill myself $50.- and owe that amount to myself until I make a payment of $50.- to myself (assuming I don't charge myself interest...).

I also don't see why you cannot sum the calculated amounts over a relationship - after all, these amounts are not matchfields (and should not be matchfields).

For a more detailed summary, I'd suggest you use a report from a join table, instead of trying to do everything with portals. However, having two join tables you can only report one type at a time.

[panchristo]

[bruceR]

That's one of the many applications of the virtual list technique.

[comment]

given the multiple data origins that need to be gathered.

But that's precisely the point: do you really NEED separate tables for Billings and Expenses? I am having trouble understanding what this is really about, in real life. Can you explain this in more detail?

At one point you mentioned calculating who owes what to whom. But the way you have it, no one owes to anyone else: all the debts are owed to an Expense, and paid to an Expense. That's the aspect I find the most confusing here: someone (we don't know who) bills Mary $25 for a haircut. Later, John ($15) and Mary ($10) pay this bill. What happened to the money paid?

Just rename the file's ext.

Please don't do that.

[panchristo]

Just rename the file's ext.

Please don't do that.

Didn't know that's bad

[panchristo]

Not quite sure what you mean, can you give an example?

[panchristo]

But that's precisely the point: do you really NEED separate tables for Billings and Expenses? I am having trouble understanding what this is really about, in real life. Can you explain this in more detail?

At one point you mentioned calculating who owes what to whom. But the way you have it, no one owes to anyone else: all the debts are owed to an Expense, and paid to an Expense. That's the aspect I find the most confusing here: someone (we don't know who) bills Mary $25 for a haircut. Later, John ($15) and Mary ($10) pay this bill. What happened to the money paid?

What I'am trying to achieve is trace whether for something concerning one person this person has paid or not, if for something concerning two people both of them have paid, or someone owes money to the other etc. Is it more clear now?

The whole thing is a tracing of expenses between people, not to be used for invoicing. As for the example where they contribute by unequal amounts to the expense (as they're always supposed to undertake expenses equally), I'd like to calculate that Mary should owe John $2,50 (Therefore from Total=$25 / 2 persons billed --> amount pp=$12,50, Mary owes $2,50)- Got it?

[comment]

I'd say put Billings and Payments in the same table, with negative amounts for the charges. Then you can produce a variety of reports from this table, e.g.

Sorted by Expense and Type:

HAIRCUT

Billed:

• Mary:   -12.50

• John:   -12.50

----------------

          -25.00

Payed:

• Mary:    10.00

• John:    15.00

----------------

           25.00



BALANCE:    0.00

Sorted by Expense and Person:

HAIRCUT

Mary:

• Billed: -12.50

• Payed:   10.50

----------------

           -2.50



John:

• Billed: -12.50

• Payed:   15.00

----------------

            2.50

Sorted by Person and Expense (no body part in this example):

JOHN

Haircut:    2.50

Grocer:   -20.00

----------------

          -17.50



MARY:

Haircut:   -2.50

Grocer:    10.00

----------------

            7.50

[panchristo]

Speaking from the interface point of view, I can't understand how this would be setup. First of all, I want to be able to have from as many as one person per expense to whatever number. That's why I used a portal to produce the payments related records and one similar for the billings. If I use one table for both, how will I be able to distinguish between each?

It may also be that I'm stuck on my perception of functionality/structure but I'm willing to discuss.

I am definately going to try though, keep on explaining, THANKS!

[comment]

Perhaps this can serve as a kind of a starting point:

Expenses.zip

[Vaughan]

Wow comment, think how much easier that relationship graph would have been to work with if you'd used the anchor-buoy method.

[comment]

Would it? Why don't you post an example with the same functionality (i.e. two side-by-side portals on both sides of the join) and we'll compare.

[panchristo]

Before continuing, a big THANKS!

Vaughn, wait till you see mine..

Well, Comment, it seems that I can come to a point as that, but when I try to put some more features in, everything collapses...

For example I spent half my day today working on a way to calculate unequal contributions to an expense by different people and calculating their balance, just to find out that my calculations would work only when viewed through a specific table (similar to Transactions in your example) and not being able to view anything that makes sense through the People table.

Arghhhh....

I'm really getting fed up with this exercise!

I really can't understand how a simple home-project otherwise implemented in excel with 1-2 pivot tables has been so dificult to do in filemaker (I am a dedicated fan of FM).

[comment]

A few things ARE easier in Excel - a pivot table being a prime example.

[Vaughan]

Comment: the smiley must have dropped off. ;(

I took a look at your relationship graph int he demo file and was impressed with the symmetry (yes, such work is appreciated).

It was a dig AGAINST anchor-buoy...

Sorry if I offended.

[panchristo]

Is it only me or someone else also thinks that a pivot table (incorporated as a feature in 2 last generations of Access) should be included as a feature in FM?

It's a shame in my opinion that such a useful and simple feature is not included in an otherwise extremmely versatile tool...

[comment]

No offense taken. And I am glad to see you on my side of the fence - we could really use that Q-36 of yours...

[comment]

Is it only me or someone else also thinks that a pivot table (incorporated as a feature in 2 last generations of Access) should be included as a feature in FM?

It's not as simple as it may seem. How exactly would the result be displayed? In a text field? A pivot table has columns and rows, which implies a table - but the number of columns is not known in advance.

[Vaughan]

A pivot table is just one export-to-excel away. IMHO there are a lot more features and bug fixes that should be given priority.

[panchristo]

... but the number of columns is not known in advance.

This is exactly the weak point in FM. The ability to produce dynamic Pivot Table Reports

[comment]

There's no argument about it being a weak point. However, it is a direct consequence of the fundamentals upon which Filemaker is built. The same fundamentals that give you the strong points of Filemaker.

[panchristo]

I suppose that the same building concept is the reason for which you can't put a tab in a portal or a portal inside a portal, huh?

Anyway, I think I have managed to solve the problem that initiated this topic. A single table with payments where a Yes/No checkbox defines if the person who has paid should be "affected" by the debt and produce a billing amount (with a - in front) as well as a summary field to produce balances. So far so good...