Single job#, multiple invoices, duplicated expenses

[CR4Reid]

I have a report that details each invoice and cost for each job. The problem is that 1 job may have more than 1 invoice or credit memo.

No problem, I group by JobID, then list all invoices for that job. The problem I'm having is when there are multiple invoices or credit memo for 1 job. It reports the costing as many as there are invoices or credit memos.

Eg. Job ABC, Invoice 12345 - $200, expense $50
Job ABC, Invoice 33345 - $150, expense $50 (should be $0)

The total revenue for Job ABC should be $350 with $50 of expenses netting a profit of $300.

1 other thing: this report must be sorted by the invoice date. Some jobs may start 3 months back but is not invoiced until today. To match the financials, this report must be listed by invoice date range, not job date range.

 

[lbass]

If there is always only one expense value per job, then instead of summing, use a maximum of the expense field in the calculation.

-LB

 

[CR4Reid]

Thanks lbass. I thought of that, but when an invoice is credited, then corrected with additional expenses, that's when it gets trickier.

The hang-up is using the same job#. I'm trying to use a subreport to total all expenses for all invoices and then showing that on the main report. Lots of formulas for 1 field, but....

If anyone can offer more tips, I'd love to hear it.

 

[lbass]

You can handle this without a subreport. You must have some sort of field that would distinguish an additional expense from the original expense,e.g., a transaction ID or a date field. You can use a running total for the expenses that is evaluated on change of that field, and reset on change of invoice.

If you need more help, please provide some sample data that illustrates this, along with any other fields that are unique to the expense field.

-LB

 

[CR4Reid]

Thanks LB - I never thought of using rt's but will experiment and advise of results. I can't work on it until later in the week.

Thanks again
CR4

 

[CR4Reid]

Ok - I tried the rt route, but it didn't work. I neglected to mention my reports structure. The invoice data is on one table and the expense data is on a separate table.

On the detail section, I list all invoices with its total. Then I sum all totals grouped by JobId.

On GF1, the invoice total amount by JobId, then Invoice# are shown. As well, on GF1, I add the "summed" amount of labour and material for that invoice.

Here's a partial sample of how my report looks like. There are 3 transactions for 1 job. The first is invoice a completed job with its associated costs.

2nd transaction reverses the invoice completely. However, the related cost is not reversed because those expenses were incurred and cannot be recovered.

3rd transaction corrects the amount, again with no change to costings.

DETAIL SECTION
--------------------
Hidden

GROUP FOOTER 1
---------------------
Job# Inv# InvoiceTotal Labour$ Material$
5554 1234 $500.00 300.00 20.00
5554 1235 ($500.00) CR 0 0
5554 1236 $675.00 0 0

 

[lbass]

What are you expecting to see? I can't tell what the remaining problem is.

It looks like the sample data is really from the detail section, not the group footer--unless you have a group on inv#.

-LB

 

[CR4Reid]

Right! Ideally, that's what I'd like to see. However, right now, it's showing:

Job# Inv# InvoiceTotal Labour$ Material$
5554 1234 $500.00 300.00 20.00
5554 1235 ($500.00) CR 0 0
5554 1236 $675.00 300.00 20.00
TOTAL $675.00 600.00 40.00

The total should be $675 sales, less $300 labour & $20 materials, profiting $355 for that job.

And to clarify, the sample data is from Group Footer 1. I hid the Detail Section, which contains all transactions for each invoice (eg. line by line item of units multiply by cost of each line item) grouped by Job#.

Many thanks LB.

 

[lbass]

Please clarify--this looks like the data is grouped on invoice, NOT job #. You would not see repeated Job #'s if it were grouped on job #.

You should be grouping on job # and then on Invoice #. The rt should be set up to evaluate labor and material on change of job #--as long as credit is never the first row in a group--and placed in the group #1 (job) footer.

I'm not sure about the Group Footer #2 display--are the repeating values appearing on each actual invoice? Or is that the display of the running total (which doesn't need to be placed in GF#2)?

-LB

 

[CR4Reid]

Thanks LB for your time and expertise.

The report is grouped by job#; on my sample data, I wanted to show that job 5554 have 2 invoices & 1 CR note. It's really difficult to illustrate without screen shots.

I'm going to experiment using queries to sum the invoice and expense data. Will advise if it works.

Once again, thanks for your help LB.

CR4