Wacked Out Total Balance

[Daniel Shanahan]

I am perplexed!

I have a video database for which I'm trying to concoct an invoice. And for the most part it works.

I have a Catalogue DB and reservations are kept in a Reservations DB (also related to a NameAddress DB, etc).

My problem lies in the Reservation DB. I have the following fields: Fees (number), Late Fee (Calc), Misc. Fee (Number).

Then I have an Item Total (Fees + Late Fee + Misc. Fee).

Another field is Amount Paid (Number).

Finally, I have an Item Balance (Item Total - Amount Paid).

And all that works fine. It is in the Body part of the Invoice Layout.

Next, I have a Sub-Summary field sorted by date. Here I have a Sub-Total Field (Summary = Total of Item Balance) and a :Shipping/Handling Field (from a related DB. This allows me to assign one S/H Fee for more than one item).

My bane come in my final field Total Balance. In my mind, all I need to do is have this Calculated field = Sub-Total + Shipping/Handling. But it doesn't work. Instead of a number that should be, for example, $10.43, I get $48.35. I tried another Calculated field: Total Balance = Sum(Item Balance) + Shipping and Handling. However, this does not give all the Item Balance fees. For example, if there are 3 videos out and each has a late charge of .25

[Kurt Knippel]

quote:

---

Originally posted by Shanahan:

My bane come in my final field Total Balance. In my mind, all I need to do is have this Calculated field = Sub-Total + Shipping/Handling. But it doesn't work. Instead of a number that should be, for example, $10.43, I get $48.35. I tried another Calculated field: Total Balance = Sum(Item Balance) + Shipping and Handling. However, this does not give all the Item Balance fees. For example, if there are 3 videos out and each has a late charge of .25

[Daniel Shanahan]

Thanks for the reply Kurt. I'm not sure I was too clear before. Here's what I have -

There are three fields in a sub-summary field:

1. Subtotal (a summary field. =Total Item Balance). The Item Balance field is in the body of the report.

2. ShipHan.DB::Shipping and Handling Fee. This is a number field (currency) from a related database. It allows me to charge one S/H fee for more than one item borrowed.

3. Total Balance. This is a calculation field (numeric result). = ShipHan.DB::Shipping Handling Fee + Sub Total.

The first two work fine. But the Total Balance doesn't. Yet, it is in the Sub-Summary field.

[Kurt Knippel]

quote:

---

Originally posted by Shanahan:

The first two work fine. But the Total Balance doesn't. Yet, it is in the Sub-Summary field.

---

Total Balance is a normal calculation and it matters not one bit where it is. Basically Summary fields work special in a Summary report part and not anywhere else, normal calculations work the same no matter where they are.

What are the parts in your report? What fields are in each part? How is the report sorted?

This information is needed to correctly define the GetSummary function, which is what it looks like you need here.

[Daniel Shanahan]

There are several DB at work here: 1). Catalogue DB 2). Client DB 3). Employer DB 4). ClientEmployerJoin DB 5). Reservation DB 6). Shipping Handling DB

For a variety of reasons I need a seperate Client and Employer DB. They come together in a joinDb called ClientEmployerJoinDB.

The problem I'm having is on an invoice in the Reservation DB.

The layout is this:

Part: Sub-Summary when sorted by ClientEmployerJoinID

This groups all the Clients onto one invoice.

Part: Sub-Summary when sorted by Date Out

This organizes all the client's items by date.

Part: Body. Fields in the Body are Title, Due Date, Date In, Late Fee, Item Balance.

Part: Sub-Summary when sorted by Date Out. Fields here are SubTotal, :ShippingHandling, Total Balance

Those are the major components. I'm not sure all of that is clear - I hope so.

Thanks.

Dan