Calculate using summary from related file

[jeh910]

I wrote a bit about this before under the heading of Sums but the thread disappeared whenI uploaded a pdf of the relationship page. I obviously gave the server something it could not chew so it spat it out.

I had a pay application form that was filled in using information from other files. With the conversion to 7, I seemed to lost the ability to use summaries from other files in my calculations. I have been reading the books and posts on here trying to find my way through this but to no avail. So, I politely knock at your door again.

In the daytime, I work for a construction company and we are paid through a system of percentage of work done using activity numbers and descriptions. (I developed all this work in FM 3.) This percentage activity is set up in a file named "Pay_Core". One of the layouts is an AIA form for those of you who know construction. This file appears to have come through the conversion unscathed although minor touchups were required.

The next file is a pay application form (Pay_App)but you could call it a summary form because it summarizes current work minus previous work to arrive at a pay this month total. In part of the form there is a place to list then summarize change orders. This part is not giving me a problem because each change order has a relationship to the Pay_Core and the amount for each change order shows up. In fact I have 70 CO relationships because we had close to 60 change orders on a previous job.

Now to my problem, I need to derive the total pay from the Pay_Core file for a calculation in my Pay_app file. Previous to 7, I went to the CO70 (Change order 70) relationship and used the summary of total pay. The reason I went to CO70 is because it was always the last record in the Pay_Core file and only asking for the sum from the last record would give a correct sum.

I have been working way beyond my knowledge with FM in Macs and PCs for years now and have developed fairly complex routines to acheive needed results but this one stumps me and I feel like a rank amateur again. I guess the main question is: how do I bring summaries from one file to another? TIA

Johnny

[Søren Dyhr]

You're scating on thin ice since, because any attempt to beat Luca di Paciolis double sided ledger from the 1490'ies have failed to the best of my knowledge (Kenny Lay from Enron tried eagerly...)

http://accfinweb.account.strath.ac.uk/df/a1.html

The ledger priciple takes only care of the line above instead of being a summary of all records in a found set, and a way to establish such a system expoits Filemakers Lookup Next Lower function, but it demands a system for the record ID's.

--sd

[jeh910]

I did not know that my explanation would expose that side of me but I was an accountant and I am going to Italy at the end of the month and double entry journal ledgers are still used as part of trial balance accounting methods. So how do I entice Ken Lay not swear to the stars above that the oil on the water clouded his vision so he could not grasp the futiility of his inexplicable ways. I would prefer restitution over retribution.

I went back to another group of files and found that running totals placed in a regular footer did change according to the record you were in, whereas running totals in Trailing Grand Summary provided the correct grand total not matter the record. That could indicate a reason for the assumption of evil asperations on my part.

So how do I get summaries from one file to use in calculation in another file?

[comment]

Summary fields depend on the current found set.

Normally, to summarise records in another file, you would use a calculation field with one of the aggregate functions, and summarise the related records. This does not depend on the found set in the related file (which can vary from user to user, or even between two windows into the same table).

An undocumented feature in version 7, which I am only now discovering: a summary field placed on the layout of another table will return the summary of the related records - same as the above-mention calc field.

[Søren Dyhr]

So how do I get summaries from one file to use in calculation in another file?

By and large is the point in relational designs to manipulate indexes, and let the data remain in one place. Data is used by referencing the contents of a record.

If you use the Lookup Next Higher/Lower is it a regular field, and can therefore be seen along the path of your relationships as they are designed...(somtimes called tunneling)

http://previews.filemakermagazine.com/videos/541/GraphRules_full.mov

--sd