Author Topic: Total cost of items waived?  (Read 9775 times)

Sherri

  • Global Moderator
  • Full Member
  • *****
  • Posts: 33
Total cost of items waived?
« on: February 18, 2010, 03:04:54 AM »
I want to see how much we've had to pay for items ordered from Awana out of our budget (as opposed to items getting reimbursed by parents).  In other words, I want the total of items waived, not including dues.  I thought that I should easily be able to see this total by looking at the Ledger's Fees grid.  I filtered the grid with "(Waived=True) and (Dues?=False)".

At first glance, this seemed to be exactly what I wanted, but then I noticed that there were a few items in the list that I didn't remember waiving.  Turns out that they were items that had less than a dollar owed on them, so I waived the small amount.  (People miscalculated, or rounded down a few cents & I didn't think it was worth bothering them about it)

So, I tried the Payments grid, but it included dues that have been waived & I don't see a way to filter them out.

- Is there a different/better way to get at the info that I'm trying to find? (without using a sql query)

- Should the Ledger's Fees grid maybe have an "Amount Waived" column instead of just a "Waived" checkbox?
Sherri Meadows
Monument, CO

Rick Leffler

  • Guest
Re: Total cost of items waived?
« Reply #1 on: February 18, 2010, 08:56:58 AM »
Sherri, thank you for the details.  After looking into this I think the Fee.Waived field should be dropped because it was part of the original table structure from a very early version, before we were able to track payments separately (there used to be a Fee.Paid_Amt field).   Now that partial amounts of a Fee balance may be waived, that field isn't serving us very well.

I think an SQL statement may be the only way to get the info you want. Please give this one a try. Let me know if it works.  If it does, we could put this under our SQL topic to share with others.  :)

select Sum(FP.Amount) as Total from Payment P, Fee F, Fee_Payment FP
where P.Type = 'Waived' and F.For_Dues = False and P.Payment_ID = FP.Payment_ID and FP.Fee_ID = F.Fee_ID and F.Club_Year = '2009-2010'

« Last Edit: February 18, 2010, 05:28:01 PM by Rick Leffler »

Sherri

  • Global Moderator
  • Full Member
  • *****
  • Posts: 33
Re: Total cost of items waived?
« Reply #2 on: February 18, 2010, 01:10:48 PM »
Close.  I just had to change SUM(P.Amount) to SUM(FP.Amount).  Thanks!

But it still seems like there should be a way to get that info without having to run a query.  Oh well...
Sherri Meadows
Monument, CO

Rick Leffler

  • Guest
Re: Total cost of items waived?
« Reply #3 on: February 18, 2010, 05:35:07 PM »
Ah, yes of course, it should be "FP.Amount". Thank you.  I edited the statement above so it'll be right in case someone else wants to use it.

It's great to have some really sharp folks like yourself participating in this AWdb project. The software is much better for everyone thanks to the involvement of so many users sharing their talents, experiences and ideas.