Close, but....Not Quite There

[forskare]

I've been working on totaling the AMOUNT column on an embeded browse which will show totals of the value chosen in the TYPE, METHOD, PAYEE or PAYOR fields. The fields at the bottom of the columns is a total of the chosen value of the column. It works pretty good except the total of more than one column can change. Example: if you click on a value in the TYPE column, the total is shown for the selected type. The total will also change in one or more of the other columns. While that's not a problem, cosmetically, it's a distraction.

So, my way around this is to create an expression in a calc field that incorporates all four totals. This is what I have so far:

if(DBSUM("ACCTITEMS.DBF","LINEPAYOR",PAYOR,"AMOUNT"),if(DBSUM("ACCTITEMS.DBF","LINEPAYEE",PAYEE,"AMOUNT"),if(DBSUM("ACCTITEMS.DBF","LINETYPE",TYPE,"AMOUNT"),if(DBSUM("ACCTITEMS.DBF","LINEMETHOD",METHOD,"AMOUNT"),0))))

I get the error message: Too Few Parameters.

What am I missing?

Thanks,

kenn

[forskare]

RE: Close, but....Not Quite There

Update. It appears that the columns and the rows are being read at the same time, if that makes sense? If that's the case, my expression could be way off since the only total that should change is the total below the column that has focus.

Thanks,

kenn

[Bill Warner]

RE: Close, but....Not Quite There

The error message is telling you that you don't have enough parameters in the IF statements.

You have:

IF(DBSUM(....),... Remember that DBSUM returns a value. So, you have to equate that value to something so IF can determine whether or not it is true, i.e.:

IF(DBSUM(....)=5,...

Also, with multiple IF's, you have to have a true and false result for each IF, i.e.:

IF(X="A",IF(Y="A",IF(Z="A","XYZ","XY"),"X"),"NADA")

This example might not be doing what you want. If you describe what result you want, maybe we can refine this for you.

[forskare]

RE: Close, but....Not Quite There

Bill,

Two threads were started accidently. This is the latest expression, it gives Incorrect Data Type. I understand there's a mismatch in the data, character vs numeric.

if(DBSUM("ACCTITEMS.DBF","LINEPAYOR","PAYOR","AMOUNT"),Str(Amount),if(DBSUM("ACCTITEMS.DBF","LINEPAYEE","PAYEE","AMOUNT"),Str(Amount),if(DBSUM("ACCTITEMS.DBF","LINETYPE","TYPE","AMOUNT"),Str(Amount),if(DBSUM("ACCTITEMS.DBF","LINEMETHOD","METHOD","AMOUNT"),Str(Amount), Str(Amount)))))


I also tried:

if(DBSUM("ACCTITEMS.DBF","LINEPAYOR","PAYOR","AMOUNT"),Amount,if(DBSUM("ACCTITEMS.DBF","LINEPAYEE","PAYEE","AMOUNT"),Amount,if(DBSUM("ACCTITEMS.DBF","LINETYPE","TYPE","AMOUNT"),Amount,if(DBSUM("ACCTITEMS.DBF","LINEMETHOD","METHOD","AMOUNT"),Amount, Str(Amount)))))

The difference being that I deleted the STR() and I tried it by putting "" around Amount. All three attemps yield the Incorrect data Type.

I also tried changing the indexes by adding the AMOUNT field but that didn't work.

Thanks,

kenn

[Willard]

RE: Close, but....Not Quite There

if(DBSUM("ACCTITEMS.DBF","LINEPAYOR","PAYOR","AMOUNT")>amount,Amount,DBSUM("ACCTITEMS.DBF","LINEPAYOR","PAYOR","AMOUNT"))

the dbsum function returns a number and needs a rational comparision.

[Stephen Williams]

RE: Close, but....Not Quite There

Ken -
I took a look at your form, and I am not sure what the problem is..."The total will also change in one or more of the other columns. While that's not a problem, cosmetically, it's a distraction."

A distraction from what? All you have is a browse with four columns, and the totals at the bottom reflect the totals for content of the current field in that column. Works for me!

[forskare]

RE: Close, but....Not Quite There

Stephen,

Thanks for taking a look at this. Notice what's happening. Clicking on a name in one column, may change the total at the bottom depending on the sequence of the clicks. If you click on the word "Deposit" in the TYPE column, you will get a total of all deposits. Just what should happen.

On the same record, click on any (or all) of the other three columns and watch what happens. Usually there is no change in any of the totals and the user has to go to a different record and then come back. Not the most efficient way of doing it.

If only one calc field is used (for any column), it works great each and every time. As soon as another calc field is added, that's when the annoyance begins. The user can get the right answer but has to click in on otherenames in oter columns first.

For some reason, the calc fields interact with each other when displayed on the form. That's the part I can't figure out; why are they interacting. My thought is that it has something to do with the index. I tried adding the AMOUNT field to it but that dead ends in 00.00.

I also think that unless the annoyance is resolved, the 'if' expression may not work as desired.

Thanks,

kenn

[Stephen Williams]

RE: Close, but....Not Quite There

Ken -
I'm at home and my copies of stuff are at the office. But I know what you mean. I had actually thought this would be a good "puzzle" to see who can make it "flow". I have a lttle set I have been meaning to put up here to show some of the ways databases work (or don't work). I am trying to sift and sort three way links, such as Date, Crew, Job. Best shown with a demo.

Anyway, it has to do with refreshing the data. It seems data is read and values computed in a different sequence than you might expect, so you are always seeing data one step behind the current pointer. Alpha Five allows so much flexibilty it suckers you into some erroneous structures. So you need to be careful in design...

[forskare]

RE: Close, but....Not Quite There

Stephen,

How right you are. A simple program does simple things. A5 is so very flexible that it drove me nuts compared to Access97. I had to force myself to NOT think of how I did it in Access but how is it done in A5.

After a bit, I got to thinking about what the totals actually meant for each record. It got quite complex in a hurry. I think you are right (your comment about the totals) but understanding it is another issue. One really has to think it out.

When I added another account number and accountitems to the scenario, things really became complex as the totals were then showing for both account numbers rather than the one in focus. I created another post, INDEXES & DBSUMS which explains or lays it out in better detail, at least I hope it does.

Yes, it would be a good project as no doubt many could use this in their applications.

In the mean time, I'll keep chipping away at this, trying to get it to work a bit at a time and then put the pieses together.

Thanks,

kenn