Summarizing fields of related table

[skfbelgium]

Hi,

I'm clueless in finding a solution for the following common issue:

I have one table with all sort of financial transactions (e.g. incoming, outgoing, from subcontractors, ...) with different statuses (draft, in progress, done).

I have a second table with projects in it.

I would like to include in the projects table the summary of the related financial transactions:

e.g. for project X,

- show the sum of all incoming transactions with status done;

- show the sum of all incoming transactions with status in progress;

- ...

Do I need to create a seperate relationship for each sum I need or do I need to use a script for this?

I appreciate your help!

[Brent Durland]

This is fairly straightforward.

1. Identify each transaction type by creating a calculation field for each type.

2. Create a new relationship, from projects to transactions, for each transaction type.

3. In the Project table, create a new calculation field for each transaction type, using the Sum function to total the amounts of all the related records.

Make sense? Let me know if you want a specific example. Good luck!

[skfbelgium]

You're absolutely right, it is straight forward. It is awkward how one can miss these things.

Thanks!!

[skfbelgium]

Hold on, I have 12 transaction types (3x4).

So I need to create 12 fields in the project table each with a constant value in so I can define the 12 relationships?

Each relationship links one of the 12 fields with a calculated field in the transaction table.

And I can use a sum on the project table to calculate the sum.

I had a problem with the fact that I needed to create 12 fields in my project table to define these relationships. So all records in my project table end up having the same 12 fields.

I thought I missed something so I could avoid this.

[mr_vodka]

What is the purpose here? If this is to report figures, you are better off using a subsummary report grouped by ProjectID and then status.

[comment]

The most straightforward way to do this is to produce a report from the transactions table, sub-summarized by project and by transaction type.

If you must see the summaries "live" in Browse mode, consider adding a table of Types (with 12 records). Add a global gProjectID to this table, and link to Transactions matching on ProjectID and Type.

---

Sorry, John - had a glitch while you were posting.

Edited August 12, 2009 by Guest

[skfbelgium]

Hello,

Yes, I need to have them in a browse layout.

Can you elaborate on the "in between" table? I'm not sure if I understand it correctly.

The new table Types is able to carry over all the ProjectIDs to the Transactions table?

It sounds cool if this works, I'll give it a try, thanks!

[comment]

The new table Types is able to carry over all the ProjectIDs to the Transactions table?

I am not sure what you mean by that. The method allows you to view only one project at a time. You must select the project to view by placing its ProjectID in the global gProjectID field in the Types table.

[skfbelgium]

I'm almost there, but not completely. How do I need to link the Projects table with the Types table? Only with the ProjectsID or also with TypeID?

If not, how can a layout in Projects know which transactiontype I want to summarize?

[comment]

How do I need to link the Projects table with the Types table?

Use any pair of fields, and connect them using the x relational operator (so that all records are related).

how can a layout in Projects know which transactiontype I want to summarize?

Each record in Types summarizes its own transactions, so that's not an issue. You only need to make sure that the global field in types matches the current project. This is easy to do in version 10 with script triggers - in earlier versions you need to be a bit more creative with your user interface.