Summary calc based on 3 elements

[chlowden]

I am not sure if this is the right use of a calculation or not, but it would be handy if it is. I have a table which is an expenses form, that has 3 key fields, an amount, a value list that includes a list of types of expenses & a project ID that is associated to the expense. I also have a layout that lists by field a sum for each type in the value list (ie. the value list has food, so the summary has a field called food)

In short, I am trying to make a summary page that adds up the expenses associated to a type of expense for a project.

The calc would be held in the summary layout field and would do the following (in laymans english):

Add up all the amounts where the field called value list contains the word e.g. "food" and the project ID selected in the summary layout match.

I hope I am clear. Thank you

[bcooney]

There are two approaches for this.

1-

The first is a sub-summary report that would have two sub-summary parts: ProjectID and ExpenseType. In the body part would be your list of expense items.

Create a summary field that is the Total of Amount. Place it in the ExpenseType sub-summary part.

Sort by ProjectID and ExpenseType and in FM9, preview to see the results.

If you first find for a specific Project, you'll only include that project's expenses in the report.

2-

Create calcs in the Project table that sum expense records. Use a relationship for each calc that has two predicates; ProjectID and ExpenseType. So, TotalFood = sum (projects_expenses~food::amount) would be the calc. You'll need as many relationships and calcs as you have expense types.

[chlowden]

Thank you for the reply. You have made me discover the sub summary report. Very interesting.

Having played with around with what you kindly wrote, I am unable to get a subsummary report to look like the final report layout that I need.

Also, I presume that a sub-summary report cannot be used to create a summary report. I have to calculate totals of certain groups of value list elements. (I wonder sometimes what I am talking about).

Therefore, I have tried to apply your calc but with out success. What does the ~ mean? Is this a way of just finding food in the project expenses field?

I have attached my work so far in the hope that you can push me in the right direction. The fields are in French, so to sum up, I am looking to populate the 2 Sommaire layouts with the info that I enter in the Compta layout. My subsummary effort is there too. Maybe you can tell me how I can replicate the Sommaire layout as a subsummary report. I figure this cannot be too hard but I am really blocking of how to do it.

Many thanks

LAFRONDE_COMPTA_copy_Copy.fp7.zip

[bcooney]

Paging someone who speaks French!

I tried to look at your file, and failed miserably. It seems that you don't have the correct data model. For example, if Projet is an entity, then why do you also have a Projet field in the Compta table and in the Sommaire table?

You look at if you want to "Excel" this (which is my #2 suggestion). Please study sub-summary reports, I believe that's all you need and base it on the Compta table (by Projet, by Type). You'll need two sub-summary parts, the first by Projet, the second by Type. Put the summary field in each part.

[chlowden]

I am not french but I have lived in Paris for many years. There is FM here but it is in French and my french is not good enough for me to get advise here.

Compta = Amount Entry

Sommaire Projet = Summary by Project

Sommaire Lafronde = Summary of all the entries

You mention correct data model. You have hit the nail on the head. The layout Sommaire is an official document for a budgeting an amateur theatre company. It has to be in this form. Therefore, even if I use the subsummary, I can't see how I can make it in this form with also percentage calcs, total calcs etc.

In my head, populating fields with 2 criteria (amount & type of expense) in a summary seemed quite straight forward, except that it means sorting the amount & type of expense info automatically. I was thinking that I would have to create a table for each expense type, but that seemed very laborious.

So, I have a real problem getting my head around the data model to get a result that looks like the Sommaire layout. You mention Excel. Originally, this was an excel spreadsheet, but I wanted to port it to FM because I can't both see expenses by project and have a global overview of all the project expenses.

If this is a 'one to one' relationship, one entry to one project, I presume this is a sort situation. Therefore is it possible to sort to a layout?

[bcooney]

No, it's not a sort issue.

You can achieve an Excel-like spreadsheet in FM. You wouldn't need a "table" for each expense type, but rather a calc field in a parent table that looks to the Compta table and sums only its respective expense type.

However, look at this technique when you can: M. Edoshin's Cross-tab Technique

[comment]

Perhaps you could start with something simple and adapt it to your needs:

SimpleBudget.fp7.zip

[chlowden]

Bcooney,

Thank you for the link. It has given me loads of ideas. Many thanks and enjoy House.

[chlowden]

Thank you. Thank you. Now I have something that works, I can understand how it works, probably break it trying to adapt it and then finally, get it working again. Sadly, this is the only way I know how to learn. Thank you again.