How to create a report for "Revenue rec'd by date, per task"?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Each [Job Number] has:

[DW 1 $ Received], [DW 2 $ Received], [PT 1 $ Received], [PT 2 $ Received], [Ext PT $ Received]

[Dw 1 Pymt Date], [DW 2 Pymt Date], and so on....

Pymt Dates are calculated from [Start-Hang Date]

I am looking for a table that looks like this:

Pymt Date 02/14 02/28 03/15 03/30

Total of all payments due on that date: $5000 $3000 $1000 $5000

Photo of Lynne8817

Lynne8817

  • 630 Points 500 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Try a Summary Report where the rows are grouped by Job # and then Customer Name and the columns are grouped by Pymt Date. I believe that you have your Payment dates being set by formula to be the 15th or the last day of the month, so you will only have 2 "columns" per month.
Photo of Lynne8817

Lynne8817

  • 630 Points 500 badge 2x thumb
I think my problem is that I have multiple payment dates...one for each revenue task.
Photo of Lynne8817

Lynne8817

  • 630 Points 500 badge 2x thumb
Pay Date DW 1 DW 2 PT 1   PT 2 Ext PT
15-Feb $ Totals
28-Feb
15-Mar
30-Mar
15-Apr
30-Apr
Are you running the report off the revenue tasks table?
Photo of Lynne8817

Lynne8817

  • 630 Points 500 badge 2x thumb
I have a table set up called Receiveables.  All data is contained in that table
Photo of Lynne8817

Lynne8817

  • 630 Points 500 badge 2x thumb
Revenue Projection
JOB NUMBER

Start Hang
01-17-2015
 Overview Complete/Job Closed
 Drywall Revenue
DW 1 Revenue
$2,551.70
DW 1 Received
02-02-2015
DW 1 Pymt Date
02-14-2015
DW 1 $ Received
$2551.70
DW 2 Revenue
$134.30
DW 2 Received
02-04-2015
DW 2 Pymt Date
02-14-2015
DW 2 $ Received
$134.30
 Paint Revene
PT 1 Revenue
$2,727.48
PT 1 Received
02-23-2015
PT 1 Pymt Date
03-01-2015
PT 1 $ Received
$2727.48
PT 2 Revenue
$481.32
PT 2 Received
03-23-2015
PT 2 Pymt Date
03-31-2015
PT 2 $ Received
$134.30
EXT PT Revenue
$160.00
EXT PT Received
03-02-2015
EXT PT Pymt Date
03-16-2015
EXT PT $ Received
$160.00
 Budget Totals
DW Total Revenue
2,686.00
PT Total Revenue
3368.80
JOB Total Revenue
6054.80
This is getting complicated.  if you have a record with multiple fields to hold payment dates, and then want a summary report grouped by payment date, then that is not so practical.  It would be better to have a child table containing all the payments, tagged perhaps by the type of payment, and then you can easily have a summary report.  i think that you need to rethink the design of your tables to have a payments table with just one field for the amount and another single field for the date.
Photo of Lynne8817

Lynne8817

  • 630 Points 500 badge 2x thumb
Thx.  I found a work-around for this.  However, I can't seem to get something else to work.
Here is the situation:  If a job is completed between the 2nd & 15th of the month, payment will be received on the 5th of the month. If a job is completed between the 16th & 1st of the month, payment will be received on the 20th. Please advise how to "write" this.
Can you give examples with some real dates for the 1st, the 2nd and the 16th of the month?