Please help with a procedure or formula


#1

Hello friends, I need help with a formula or procedure. I have a data base with people registering for a convention. We have groups of people coming with the same address. For instance, ten people with the same address coming from the same church. I would like to select that address (field “address”) and select all the records with that address and then total the amount they paid (field “Total Amt”), I would then like to take that total amount and display it temporarily on the date input form.


#2

I would set the address field to Clairvoyance so you don’t have to type it out each time. Then group by the address field and total the payments. Using outlinelevel to show just the summary records, or possibly set a variable for group total and use the variable to display on a form.


#3

Craig has suggested the group statement, which has been a great tool for calculating all the summaries in an entire database. But if I read the original question correctly, what Gerald wants is to display just one subtotal, not all of them, and he wants to display that total immediately, as data is being entered, not as part of a report process. That was pretty difficult to do in earlier versions of Panorama, but Panorama X has a new function that makes this a snap – the aggregate( function. I think this may be one of the most overlooked great new features in Panorama X.

To display the total on a form, all Gerald needs to do is to put this formula into a Text Display object on the form. This will immediately show the total of all records with the same address on the form.

aggregate({«Total Amt»},"+",{address=««address»»},"",true())

So what does this formula do?

The first parameter, {«Total Amt»}, is a formula that tells Panorama what values you want to total. This formula must be quoted, in this case I used curly brackets ({ and }) around the formula. In this case, the formula is just a single field, the Total Amt field, but you could combine fields or total based on a calculation, for example if you wanted 75% of the Total Amt field.

The second parameter, "+", tells Panorama to add up the values from the first parameter. Adding is the most common option, but you can also calculate counts, averages, minimum and maximums.

The third parameter, {address=««address»»}, tells Panorama which records you want to include in the sum. In this case, only records where the address is the same as the address of the current record. Again, the formula must be quoted. The double chevron notation ««field»» means “from the current record”, see the documentation page for more detail.

The fourth parameter, "", specifies the database. Since this is blank the current database is used, but you can calculate aggregates for any open database.

The final parameter, true(), tells Panorama that you want ALL records to be included in the aggregate, not just ones that are currently selected. I’m assuming that you want this total to include all records even if a subset is currently selected. If that’s not what you want, make this parameter false(), or just leave it off (in that case you could also leave the database parameter off.

Since you will using this in a Text Display object, you’ll probably want the display to update if you go to a different record, or update the address field. Usually the Text Display object looks at the formula you give it and can figure out what fields are involved in the formula, and from that knows automatically when to update the display. But in this case, all of the formulas are quoted, and the Text Display object cannot “see” field names inside a quoted formula, they just look like text. So the Text Display object won’t update when needed. To fix this, add an ignore( function to the formula.

ignore("",address)+aggregate({«Total Amt»},"+",{address=««address»»},"",true())

The ignore( function doesn’t change the value calculated by the formula. What it does do is let the Text Display object “see” that it should update the display when the address field changes. In the ignore( function, the address field isn’t hidden by being inside a quoted formula, so the Text Display object can register that it is contained in the formula and that the display should update if it changed.


I know that the necessity of quoting formulas in a situation like this can be confusing. The reason for this is that these particular formulas are not calculated immediately. Instead, the aggregate( function saves these formulas and then uses the saved formulas over and over as it scans the database. If the formula wasn’t quoted, the result would be calculated immediately instead of being saved for later. Panorama always calculates formulas immediately, but by putting the formula in quotes, the result of that immediate calculation is the text of the formula we really want to use. Panorama can then save that text and use it later when it actually needs to calculate the formula.