Get Total help

[Kicker3]

Thanks as always for your help.

I need to get a total for a field. Here goes my best to try to tell you what I need. If (field) "Date Signed" is NOT empty then I want to get total for (field) "cTotal" I'm drawing a blanc on how to write this calculation. I know it is real simple, but.........

[Lee Smith]

I guessing that your fields are Date Signed and cTotal.

I prefer using Case, this should do what you have described.

Your new Field

[Kicker3]

Yes but most times there are more than 1 proposals signed. In other words I need all "Date Signed" & all "cTotal" added togeather.(well I need all cTotal added togeather on every signed proposal) I set up the Case statement but it only looks at the last Date Signed, cTotal in my portal & there are usally more than 1

[Lee Smith]

This is quite a bit different than your original request.

Since there are multiple choices, perhaps you had better Attach a sample of your files, and a brief explanation of what you are wanting to do again.

Lee

[Kicker3]

I want to add all "cTotal" field that has a "signed Date" field that is NOT empty.these fields are in a portal. Any suggestions?

[Kicker3]

Did everyone give up on helping me?

[Lee Smith]

and a sample of your file is where.

[LaRetta]

Hi Kicker3,

Lee is right on. You haven't given enough information. And when you posted again, you simply repeated your exact request. We read it the first time and it helps if you at least use different words so possibly we could figure out more specifically what you need.

However, I think you simply want a conditional relationship (sample attached). Just create a NEW table occurrence of your related (portal) table as per attached demo. And then only place the Grand Total below your existing portal.

I have placed the original relationship portal (so you can see records without a DateSigned also). The first grand total shows the total of all related (even those without DateSigned); the second grand total sums only those related records with a date signed.

I believe the logic is very clear and easy to follow. Note the new field in your related file. cSigned number field with calc of Sign(DateSigned) simply produces a 1 which is used in conjunction with the MainID to generate the new relationship.

LaRetta

Totals.zip

[Kicker3]

Thanks I didn't post a sample of my file because I'm not sure how to post a part of it and it has alot of people's names & addresses in it, So I tried to explain the best I could. I will study your attachment to see if I can make it work for me. Again thank you.

[Lee Smith]

Sorry, sometimes the things that come second nature to us, can be confusing to a member who hasn't gone through process yet.

First of all, we are not the least bit interested in your data. So, save a copy of your files using the Clone [color:"blue"] "NO RECORDS" of your file or files [color:"blue"] (be sure to include enough of the files so that we can see what you are talking about) , put the files in a folder, and zip them.

Next create a new Reply, say something like see the enclosed attachment, and click on the [color:"blue"] Continue button. and be sure that the [color:"red"] I want to preview my post and/or attach a file is checked. The next view of your messages has a button for I want to enclose a file, click on that, locate the zip folder and select it.

That should do it.

If for some reason if fails, you can edit your post and try again.

HTH

Lee

[Kicker3]

I'll try to explain it better: Here goes:

I have a portal with Field A which is a date field

also Field B which is a number field.

I want to total all numbers in Field B when there are dates in Field A (not all Feild A have dates)

I want to total all the numbers that have dates.

I hope this makes sense...

[LaRetta]

Kicker3, if you have an existing portal then you have an existing relationship. I assume it is a one-to-many. Your description of your fields (and you needs) seems to fit the file I attached pretty accurately. You need to use Sum() to define what RELATED RECORDS should be totalled.

And because you don't want ALL normally related records to relate, ie, you only want those with a DateSigned, I created a new relationship. The field I totalled in this new relationship is number (as you've specified).

The only thing I'm unclear on is what the cTotal represents. I hope it's a simple Quantity * Price (number). That's how I structured the example. I hope we are not attempting to Sum a calculation which references OTHER calculations. And a bit of information about your relationship wouldn't hurt, ie, Invoices with LineItems portal; or Customers with Employees portal or whatever (gives us a picture of the purpose which is why Lee asked for the file).

A filtered relationship is pretty standard stuff. Some might disagree with my use of a constant in Main; others might disagree with Sign(DateSigned) calc. I could have used any boolean test to produce the 1 (just because I felt in the mood to use Sign()). So others may suggest better/different join fields but pulling the grand total of your related cTotal is usually handled as I've indicated.

Let us know if you have further questions or need to tweak the relationship or calculations at all.

LaRetta

[comment]

Try creating a third field =

Case ( not IsEmpty ( DateSigned ) ; cTotal )

Then summarize the new field instead of cTotal.