Calculation VERTICALLY ? (like Excel and Numbers for Mac)


#1

Is there a way for Panorama to perform a calculation VERTICALLY (like Excel and Numbers for Mac) rather than HORIZONTALLY?
Here’s what I’m trying to do:
I have about 200 customers with 200 different accounts, that is 200 different records/rows. I caclulate fees for these customers. Some customers have more than one record (that is, they may have several accounts). Let’s say I SELECT John Doe who has 5 accounts (that is, 5 records/rows). I want to total (SUM) the fee field (column) for the 5 accounts. Then copy and paste that sum to the fee field for only ONE of his records (because I’ll take ALL fees charged to him from only one of his accounts). Then I want to delete the 4 records that do not have the sum total of his fees. I’d like to record a procedure (Macro in Excel jargon) that does all that.

After selecting records, I see that you can right click > column stats > total to get the total I need (except it’s rounded off and I need dollars and cents). However, I don’t know of a way of pasting that total. Must be a better way. Thanks


#2

Create an extra field for that calculation and use the Menu Records > Morph > Fill with formula.

It looks that can be done without a calculation in an extra field. You could use the Analyze… command in the Records menu. That would create a summary of your Client field with a Total in the Fee field. — No need to delete one of your records. No need of manually copying values.


#3

You can select the text in that dialog and copy it.

The Column Stats dialog uses the aggregate( function to calculate the sum. You can use that function yourself in a program to do exactly what you want. I’m assuming you have a text field named Account, and a numeric field named Fee. If your field names are different, you’ll have to modify this code. I’ve added comments for each line of the code to explain what is going on. This code doesn’t require you to select the Account you want – just click on a record in that account and the code will do the rest – calculate the total, “paste” the total into the field you clicked on, and then delete all of the other records associated with that account.

// save the current account name in a variable
let accountName = Account
// calculate the total fees for this account -- notice that we don't have to select, the aggregate( function does this
let totalFee = aggregate("Fee","sum",{accountName = Account},"",true())
// save the total in the Fee field in the current record
Fee = totalFee
// unselect all of the other records in this account
select Account<>accountName or Fee = totalFee
// remove the other records associated with this account
removeunselected
// go back to the original record we started with
find accountName = Account

Now that I’ve told you how to do it, I must tell you that I think this is a horrible idea. You are destroying data! It would be much better to come up with a non-destructive solution, but since I don’t know what your ultimate goal is, that’s a bit tough for me. Let me throw out a couple of potential solutions.

One idea would be to group the database by account, and calculate ALL of the fee totals in the summary records.

field Account
groupup
field Fee
total
outlinelevel 1

See Summary Records to learn more about this technique.

Another great option would be to use the new Summary Wizard. Again, this could easily calculate the fees for ALL accounts at once. You can even export that to a new database if that is what you need.

I hope one of these solutions will appeal to you.


#4

Thanks much. I neglected to say that the the 5 accounts for John Doe all have DIFFERENT account numbers. I want to sum ALL John Doe’s fees with the 5 different account numbers, then paste the sum of the fees to just ONE of his account and then delete the other 4 records. I could record a procedure which selects by account number rather than name, but name (in my case) seems easier. The procedure will need to know where to paste the sum to the fee field/column (that is, which one of his accounts) which (I guess) would have to be identified by the record with the target account number.

Thanks also for the destructive warning, but in my particular case I need to destroy/delete the other 4 records so Doe isn’t charged too much. I have to submit a csv file as the final end result. Thanks again


#5

I have some customers that want to pay the fees for ALL their accounts from ONE account. I’m trying to write a procedure that selects a customer, totals his or her fees and “pastes” (if that’s the right word) that total of fees for ALL the customer accounts into just ONE of the customer’s accounts, then deletes the other accounts. These customers have different account numbers for each account. The Fee field is a formula based on TOTAL VALUE (TOTAL VALUE *.00375). The customer (in the following example John Doe) could be selected by “John Doe” or selected by his account numbers.

Here are the fields for example

ACCOUNT NUMBER ACCOUNT NAME TOTAL VALUE FEE

ACCOUNT NUMBER ACCOUNT NAME TOTAL VALUE FEE
1001 John Doe 3600 13.5
1002 John Doe 1535 5.75625
5214 John Doe 1425 5.34375
2123 John Doe 878 3.2925
(total fee) 27.8925

I’d like to get the total of fees o $27.89 pasted into the Fee

field to the right of ACCOUNT NUMBER 1001 and delete the other

accounts. I know this is normally a “horrible idea” but I have

to save this as a CSV file and send it with the un-needed records/rows

deleted, be sure only ACCOUNT NUMBER 1001 is charged and not charge

the other accounts.

Sorry but I’ve not been able to get the last reply to work at all

I assume it is a procedure. Thanks


#6

Well, you seem to be dealing with some screwed-up records. What I would probably do in this case is change the account numbers so that they are all the same. If you have the fields ordered by Account Number, you loop down the list, select by Account Name, fill the Account Number with the value of the first Account Number, reorder by Account Number, and loop to the next higher Account Number.

Once you have done that, it is pretty straightforward: Group by Account Number, totaling what needs to be totaled, propagating what needs to be propagated, and then getting rid of the original data, leaving the summary with the total.


#7

thanks Bruce but their account numbers can’t be changed


#8

But that is what you said you wanted to do! If you are consolidating account numbers, you are changing all the later ones to the first one. Then you add them up and they all go into one summary record. If you delete the original data, all you will have is one record with the first account number, the account name, whatever you want to do with the total value, and the fee.

You do not need to change the account numbers, if you do not want to, though. Just set up a new field with for new account numbers.


#9

Thanks again for the 2nd reply Bruce. What I need is just ONE record, the one with Account 1001, but with the total of $27.89 in that record’s Fee field. Thus, I do want just the one record. You could call it a consolidation. I
view it as a deletion of the un-needed records after putting the total in the fee field of Account 1001 record. I won’t argue the semantics. I must keep the same account number (that is, account 1001). I can’t add new fields (unless I later delete them) as this is a “template” that must be sent as a csv file to another party after I do the computations. That is, the final result must have only those 4 fields. I can easily select John Doe and total his fees, manually paste it to the fee field for record for Account 1001 and delete the now un-needed records. However, since I have to do this for some 12 customers (out of the 200 that don’t need this totaling done) I thought it shouldn’t be hard to record or write a procedure. When you select John Doe and total (Command +T), you get a summary record which gives the total I need. I’ve not figured out how to Command +K (delete summary record) as part of a procedure because when I record Command + K, it doesn’t record that in the procedure.


#10

You will get only one record per name.

What this is doing is totaling all the records that have the same account name. That results in a summary level that has the totals, so if there are more than one account number for an account name, they will be consolidated into one record, with the sum of the fees. The only tricky part is choosing the account number, which I assumed would be the lowest one.

You really have to understand how summaries work. I suggest you try it, using a duplicate of the original data, of course.


#11

So here is a simple way to do what you want. Work on a duplicate:

Go to field Account Number. Sort down.

These commands are in Field>Morph or Field>Analyse

Go to the field Fee. Field>Analyse>Total (for some reason, this is not totaling, but it gives a summary record.)
Go to the field Account Name> Field>Morph>Propagate.
Go to the field Account Number. Field>Morph>Propagate.
Go to the field Fee. Field>Analyse>Total
Field>Analyse>Remove Detail

Voila!

I do not know what you want to do with the Total Value field, but you can do something with that, total it or whatever.


#12

In the olden days I used to just add a footer to the form and put a summary there. You could add a simple formula to fill in a text box. If you are just using the standard data view that obviously will not work and I am sorry for bothering you.


#13

Here is the code I used to produce your required results:

startdatabasechange "allrecords","Consolidated Fees"
local theName
GetText "Enter Account Name…", theName
If theName=""
    stop
Endif
select «Account Name»=theName
if info("empty")
    message "No Account Name found!"
    stop
endif
field «Account Number»
SortDown
field "Fee"
runningtotal
lastrecord
loop
    uprecord
    stoploopif info("stopped")
    deleterecord
while forever

This will result with only one record remaining for the selected Account Name with the total fees in the Fee field. Here is what is happening in this code…

  1. Activate Undo for this action in case things go wrong
  2. Get an account name entered for the selection (stop if no entry)
  3. Select the entered account name records (trap for no such name)
  4. Go to the Account Number field and sortdown so the lowest number is at the bottom
  5. Go to the Fee field and use runningtotal to get the total in the last record
  6. Go to the last record with the Fee total and delete all records above it

That seems to work fine in my experiments but you should do this on a duplicate file just in case something goes sideways the Undo won’t resolve it. So, anyway, here it is in action:

ConsolidatedFees


#14

Thanks very much Gary. Unfortunately, I failed to mention that the “target” account, that is, the one that the total of fees goes to, is not necessarily the lowest (or highest) Account Number. In the example it happens to be 1001 which is lowest in this example. There are some 12 different customers I have to do this procedure for and the “target” Account Number (and it’s record/row) could be a higher or lower number than the other selected account numbers. I think I’ll have to insert some “select” or “find” instruction and put in the specific Account Number so it puts the total on the right record/row. Sigh. Thanks again


#15

I think I may have figured it out. Thanks to all.


#16

This topic took up a lot of energy because you did not define the problem properly. You are going to face a lot of frustration unless you define what you want to do accurately.

There is a classic book which discusses the methods which you will need to be successful working with Panorama, or in fact, any method of solving problems: How to Solve It, by G. Polya. He discusses the importance of posing questions completely and properly. There are synopses of the methods that he espouses available on the internet which may be sufficient for some people, but the entire book is still in print. I recommend it to anyone who is having trouble learning to use Panorama. Its methods will help you keep your head straight when you are faced with a problem.


#17

I think the easiest way to accomplish your task is to create an extra field for the account number that shall be charged.
You have complete control which fields shall be exported to a CSV file — so no fear about adding an extra field to the database!
You can do your complete task by grouping and summarizing — but without losing any detail information about any purchase on any account. Those details can remain hidden when you export the summary.


#18

In that case I would change the code slightly to assume that the desired final record is currently selected. We now get the current value of the Account Number as well as the Account Name to Select all records with that name and then Find the record with that number. At this point we move that record to the bottom of the list, use runningtotal on the Fee field and then remove all the records above this record.

startdatabasechange "allrecords","Consolidated Fees"
local theName,theNumber
theName=«Account Name»
theNumber=«Account Number»
select «Account Name»=theName
find «Account Number»=theNumber
loop
    moverecorddown
    stoploopif info("eof")
while forever
field "Fee"
runningtotal
lastrecord
loop
    uprecord
    stoploopif info("stopped")
    deleterecord
while forever