How to sum all records regardless of found set

[DPS]

How do I create a global field which displays the sum of one specific field in all records, REGARDLESS of the found sets.

For instance, I am creating a purchase order database related to an inventory database. Each PO would substract the correct amount of items from the Inventory and therefore ALL POs must be counted at all times, not just the found set.

[transpower]

GetSummary(summaryField;summaryField)

[Ender]

In the Inventory table, use a calc: Qty on Hand= Sum(LineItem::Qty), where LineItem is a relationship to your PO LineItems based on InventoryID. Note that this will begin to perform poorly as you get into thousands of records. It may be better to use scripts to update the Qty on Hand on a daily basis, or as each transaction is processed.

(GetSummary() only shows the summary of the records in the current sort break in the current found set.)

[Søren Dyhr]

It may be better to use scripts to update the Qty on Hand on a daily basis, or as each transaction is processed.

Strictly speaking is an invoice an official type of document, you not are allowed to delete ever again when issued ...but multible users invoicing at the same time might bring in timing issues when one user suddently regrets a line in a invoice and deletes it again before printout. But if such matters can be handled satisfying might Lookup Last be exploited ...because such a batch handling on a daily basis certainly not avoids the situation "...who the h*** have (today) sold the last of X without telling the rest of us"

You see Jack Rodgers actually hit the nail right on the head - as to why we develope databases in the first place.

Here's the secret of database design: you study human activities and what they want to acheive and then you design a database that will at the click of a button do all of that work and deliver information in a neat and tidy form that they helped designed and that would have taken them several weeks to manualy produce...and then misplace and spend hours looking for it. One small part of the solution I am rewriting, for instance, authorizes 100,000 credit cards a year and could easily do 15 times more. The approvals are posted to rental accounts and accounts that are late are mailed letters. All with one button click. My guess is that this saves about 20,000 hours of labor if the work were done manually. That's just a guess. Considering all of the work that the database does, I would guess that 30-50,000 hours of labor are saved per year. Not to mention all of the information that is available instantly to anyone in the store who needs it.

From: http://www.jackrodgers.com/fmp7/index.html

So convenience to the developer doesn't count here - to have such data reliable must deletion of lines in an invoice cause an extra line to be created to counter the original line is made in storage. Out ******* duty to the users is make this as intuitive and seemless as posible.

--sd

[Ender]

True, record locking is an issue in a multi-user environment that you need to address no matter what the process.

When I said "It may be better to use scripts to update the Qty on Hand...as each transaction is processed," I was thinking this might happen when an order is completed. You'd loop through the Line Items, updating the Qty on Hand with a Set Field. If there was a record lock error, you might have the script wait a couple seconds and try again. If a couple attempts fail, you then mark the problem line item and continue (or stop) and inform the user of an error.

There are some complexities to work out with transaction updates like this, but if speed issues are a concern, then it's a good approach.

S

[Ugo DI LUCA]

Hi,

Or LookupNext ( ) in adpatation of what was suggested by Bruce Robertson on another post as an alternative to Max ( ) or Count ( ) on a Cartesian Join.

It's just a matter of what field you are targetting although you would need the true related calculation as the targetted field.

[DPS]

Thanks to all of you for the comments. This was my first post and it helped me out.

I have resolved it for my current situation and from what I understand, it is possible to have the sum of all record in one table listed in a related table but if you use the "sum" function in the same table it will only give the the sum of the found set and can never find the sum of all fields regardless of found set.

Not an issue since I have resolved my problem but I do remember running into this for another application I was working on a few months ago.

[Ender]

The function Sum() is used for totaling repeating field values in the same record, totaling a series of values (like Sum(x,y,z)) in the same record, or totaling the values of a related field (like I showed above.)

There is another field type Summary, that counts or totals the values of a field in the found set.

If you needed to total the values of records in the same table, regardless of the found set, this would be done with the Sum() function on records through a self-join relationship. For example, if you wanted to see a total value of all closed invoices in the Invoice table, you might set it up like this:

gClosed (global, text) = "Closed"

Status (text)

InvoiceSelfJoin=

Invoice::gClosed = Invoice::Status

Now the Total calc:

TotalofAllInvoices (calculation, number result) = Sum(InvoiceSelfJoin::InvoiceTotal)

So Sum() and Summary are used for different things, and there are different ways to use them. Hopefully this will help you sort them out.