Applied Invoices Report

[tbcomputerguy]

I now have my solution running pretty smoothly. But I am having a propblem getting a report to work. I have a Jobs Table, Invoices table and customer table. All I am trying to do is make a report that shows all the invoices made from the job. I have tried in my opinion everything I can think of. I have supplied a pic of my relationships. the group on the left is where the invoices are created from the jobs. the group on the right is where I am trying to get the data to generate the report. On my report every thing shows up except the invoice numbers and the amount of the invoice. Perhaps I am missing what context this report should be based on. I hope the pic helps.

D

[LaRetta]

Hi D,

The report layout should be based upon Invoices since that is the many side to one job and you want the details (invoice number and amount). You would perform a find for that job first in Invoices to isolate the correct report records or you can GTRR (go to related record) from Jobs directly to isolate the job's invoices.

Your reports layout does not have to be based upon that second table occurrence group (TOG) at all unless the relational-filter criteria is different and it isn't; it's same as your primary relationship. You can GTRR from Job_Orders5 to Invoices_From_Job2 but then specify to use your report based upon Invoices_From_Jobs (the original one).

So whenever you want the details of a record (the body) then the context (table the layout is based upon) should be the 'many' side. :^)

[tbcomputerguy]

Thanks, Just a little push in the right direction and i got it figured out. I couldn't get my head around which context to use. Also one of the formulas I was using was not being calculated from the correct context as well.

D

[tbcomputerguy]

Oops spoke to soon. I have tried different ways to get the sum of all the job orders as shown on my pics attached. I don't understand why it is picking up the last job order only? I have attached my relationship grid they layout in browse mode and in design mode.

Again, thanks in adavance.

D

[LaRetta]

Thank you for providing the screen shots - it sure helps!

I'm not sure why that field is showing only the last record, D. What is that JobOrderNumber_Calc? Why aren't your summary parts based upon the Invoices id_jobOrder? You are showing data on those fields and it usually helps to view them showing the field names but I will assume you are sure that the field in the Trailing Grand is the same summary field in the trailing sub-summary part above it? They are summary fields from the Invoices table?

[tbcomputerguy]

The JobOrderCalc field is a field that is calculated when the job order is created. there are 4 types of jobs roof, floor, wall, stair. Each division if you like has its own numbering system. These are derived from seperate jobnumber creation tables. See at first i related the fields based on the jobordercalc as it is created at the time the record is crated and it is via a script put into the joblineitems when the invoice is created to link if will the line items to that joborder even though it has a joborderId (pk). As for the fields, i have tried the summary field from the joborders table and nothing, but i did try summarizing by the id_Joborder, but to no avail. Here is a screener of with the fieldnames.

D

[LaRetta]

Hi Dave,

Jobs_To_Invoices::sum_of_order_total looks like a summary field from Jobs and that won't work. Summaries are based upon existing table and found set. You need to use a calculation in Invoices and then create a summary field for this calculation or possibly GetSummary(). I would need to see the file or maybe others can spot the issue.

[tbcomputerguy]

Using some calculations in the jobs table and and the invoices table, with the help of laretta, we identified that we just needed a count of the invoices in the jobs table based on the fk_invoiceid. Then sum up the orders from the job table and divide it by the count of invoices in the jobs table.

The for the total of all orders, we used a summary of joborders as a running total when sorted by the jobordernumber. See attached.

Thanks LaRetta