display calculated summary field in related table

[tarheel]

I have a main table and an accounts table. They are related by "account#".

I can get totals and sub totals of the fields in the main table but if I place the calculated summary field from the accounts table in the main table it won't display

only get a blank field. How can I display a calculated summary field in a report?

I have checked this forum for days and cannot find an answer.

thanks for any help

Bob...

[transpower]

You've got it reversed. The summary field should be defined in the main table. Then it can be placed on the layout for accounts table.

[tarheel]

Thanks for the reply. But I am trying to create a report using the main table layout and include the total from the accounts table. Maybe I don't understand how reports really work?

Bob...

[transpower]

I assume that each record in the accounts table is for one account. I assume that any number of records in the main table can share the same account. Therefore, you want the summary to be in main table and displayed in the accounts table (which will give the total for that account). Or maybe that's not how you have your database set up ...

[tarheel]

My main table is my transaction table where all entries are made for all accounts.

The accounts table has 23 different accounts each with a unique appropriated amount. I need to calculate the summary total of all the amounts, which works OK in the accounts table layout, but I can't get that total to display in the main table with a calculation or get summary. I can't do a summary from the main table of a field in the accounts table because those fields are not visible for the calculation.

[tarheel]

Is it correct to say that calculations only take place when a layout is selected?

meaning I need to have a script to select the layout so the calculation takes place then move the calculated field to the report I want?

[transpower]

No, calculations occur whenever any of the arguments (fields) for that calculation change.

[tarheel]

Well then why can't I see the calculation field from my accounts table in a layout for my main table? I just don't get it.

[-Queue-]

Do you have a relationship between the tables? If so, is the key for the accounts side not an unstored calc or a global?

[T-Square]

tarheel--

I had similar problems with summaries when I first got going.

Transpower's advice might seem backwards, but it's not. Assuming that your relationship is called "AcctRel", create a calculated field in the *main* table with a calculation, thus:

Sum(AcctRel::TheFieldIWantTotalled)

What this does is takes all the Accounts records that are linked to the entry in Main, and sums them up.

If you create a calc field in the remote table, Filemaker doesn't know how to evaluate the calculation. You see, in Filemaker lingo, the Accounts table doesn't have a context when you are on the Main display. Since it doesn't know which context to apply, it doesn't do one at all. Think of it this way: the summary is determined by the entry in the Main table; therefore, the summary field must be created and evaluated from this (Main) table.

Does that help--or have I completely missed the boat here?

[tarheel]

Queue, Yes the tables are related by the account# fields. I have a sumary type field in the accounts table that produces the correct total of all the accounts appropriated amounts. But I can't display that summary field in the main input table using a get summary field type.

T-Square - I tried your suggestion and it doesn't display the total.

"The summary must be created and evaluated in the main table." That's what I'm trying to do. I created a field type number with a Auto-enter calc which is

Sum ( Accounts2::Appropriated amount) and that didn't work.

Bob...

Thanks for the input.

[-Queue-]

Is Accounts2 the relationship between Main and Accounts that uses the account# as a key field? If so, this will only display the sum of the related account. Create a Cartesian relationship using the X operator, to relate each record in Main to all records in Accounts, if you want a grand total of Account records.

If you want to see the sum for each account, you'll need a relationship for each account number, using either a calculation or global field in Main to hold each number as the parent key.

[tarheel]

Whoa. "Create a Cartesian relationship using the X operator, to relate each record in Main to all records in Accounts" Is this definition right? "A Cartesian relationship coordinate system in which data are organized on a grid and points on the grid are referenced by their X,Y coordinates." Could you possibly give me an example to follow? I think you hit on the real problem now if only I knew how to correct it.

Thanks very much for spending the time to diagnose the problem.

Bob...

Bob...

[-Queue-]

Yes, but that is a completely different field of study. Here, a Cartesian relationship is merely another name for what a constant-to-constant relationship would have been in previous versions. Instead of creating two constant fields (usually equal to 1) and relating them, FM 7 allows you to relate any two regular fields with the X operator to produce the same result, that being each record in Table A is related to all records in Table B, when Table A and Table B are joined via the X operator instead of = or one of the inequality operators.

[tarheel]

Ok then. I made the relation of all fields in Accounts joined to Main via the x operator and sure enough I got the total of all amounts in the Accounts table to show up in the report by using a summary field in Accounts of the appropriated amounts just the way I wanted. Now of course all total appropriated amounts for each account are lost and I need to create a sum for each account in Main and I'll be good to go. Back to it.

Thanks so much for straighting me out of this problem.

Bob...

[tarheel]

Well I can't figure out how to "If you want to see the sum for each account, you'll need a relationship for each account number, using either a calculation or global field in Main to hold each number as the parent key."

Can you help me here?

So close yet so far away...

Bob...

[-Queue-]

First, you should use a Sum( ) calculation field, not a related summary field which is based on the related table's found set and not only the relationship. Summary fields are designed to be local, and while you may achieve the desired results once using them in a related table, doing so will more than likely cause you problems the next time or some time afterward.

Create a calculation field equal to your first account number and a relationship from this field to the account number field in Accounts. Then create a calculation of Sum(newrelationship::fieldtobeTotaled). Repeat for each desired account. You could use globals instead of calculations, and either set the value manually or in a global calculation--the weapon of choice is up to you.

You may see the need to script the updating of the totals fields, as calculating the values each time the layout is refreshed may take quite a while. If this is the case, you can change your Sum( ) calculation fields to normal number fields and create a script to

Set Field [sumAccount1; Sum(relationship1::fieldtobeTotaled)]

Set Field [sumAccount2; Sum(relationship2::fieldtobeTotaled)]

etc.

and call it after Account records are modified.

You may be able to use a technique similar to the one I proposed here, but I haven't attempted to expand it to related tables yet.

[tarheel]

Thank you for the input. I will start working on it and hope I learn to understand the process so I can repeat it.

Bob...

[T-Square]

tarheel--

Reeling back to your response to my reply...

Assuming that in fact what you wanted was a total by account, I think your problem is in how you defined the field. You said you "created a field type number with a Auto-enter calc". Instead, try creating a **Calc** type field with the unstored calc in it. That would give you a total for each account.

Queue: Now why would you want to create a Cartesian relationship from one table to the other and then make all those scripts and calculations? I don't see where you'd do that. If you want a complete total of transactions, then build the relationship the other way (from the transactions to the accounts), and total within the transactions table. Ditto if you want to display totals for every account. Create the layout in the transactions table with subsummaries (leading and trailing if you wish) by the transaction account id, sort your list by account id, and the result will total each account and then all (if you put in a trailing grand summary and put the sum field at the end. Note that in this case, your sum fields have to be in the transactions table. And if all you want is the totals, delete the body of the layout.

[T-Square]

tarheel--

Reeling back to your response to my reply...

Assuming that in fact what you wanted was a total by account, I think your problem is in how you defined the field. You said you "created a field type number with a Auto-enter calc". Instead, try creating a **Calc** type field with the unstored calc in it. That would give you a total for each account.

Queue: Now why would you want to create a Cartesian relationship from one table to the other and then make all those scripts and calculations? I don't see where you'd do that. If you want a complete total of transactions, then build the relationship the other way (from the transactions to the accounts), and total within the transactions table. Ditto if you want to display totals for every account. Create the layout in the transactions table with subsummaries (leading and trailing if you wish) by the transaction account id, sort your list by account id, and the result will total each account and then all (if you put in a trailing grand summary and put the sum field at the end. Note that in this case, your sum fields have to be in the transactions table. And if all you want is the totals, delete the body of the layout.

[T-Square]

tarheel--

Reeling back to your response to my reply...

Assuming that in fact what you wanted was a total by account, I think your problem is in how you defined the field. You said you "created a field type number with a Auto-enter calc". Instead, try creating a **Calc** type field with the unstored calc in it. That would give you a total for each account.

Queue: Now why would you want to create a Cartesian relationship from one table to the other and then make all those scripts and calculations? I don't see where you'd do that. If you want a complete total of transactions, then build the relationship the other way (from the transactions to the accounts), and total within the transactions table. Ditto if you want to display totals for every account. Create the layout in the transactions table with subsummaries (leading and trailing if you wish) by the transaction account id, sort your list by account id, and the result will total each account and then all (if you put in a trailing grand summary and put the sum field at the end. Note that in this case, your sum fields have to be in the transactions table. And if all you want is the totals, delete the body of the layout.