Jump to content

REPORT from info in portal & main database


This topic is 7357 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello and please help..I am so frustrated, I am in tears...really!

I cant figure this out and I have been working on it for a week and I have tried everything I know..please help. I am really at my wits end..

HELP??

INVOICES is a database that uses LINE ITEMS database to store all the information ordered on each individual serialized Invoice.

The LINE ITEMS database stores the styles ordered in the Portal In INVOICES, related record by related record.

In the INVOICES database, the totals for each invoice are summarized, such as tax totals and shipping totals.

I have to make a REPORT Database that summarizes the line items ordered by each invoice along with the totals for each invoice.

The report should

look like this and be sorted by invoice number and report on a group of invoices by date or by week or month.

INVOICE NUMBER

STYLE NUMBER DESCRIPTION SIZE COLOR QUANTITY RETAIL LINE TOTAL

TOTAL OF LINE TOTAL

TOTAL OF DISCOUNT

TOTAL OF SUBTOTAL

TOTAL OF TAX TOTAL

GRAND DAY TOTAL

The problem starts when I am trying to make this report in the LINE ITEMS database, and the information will only calculate correctly for the LINE ITEM TOTAL, which makes sense, because the calculation to add up the line items occurs in the LINE ITEMS database.

Basically, I cannot get the information to summarize correctly. I now after really going in some serious circles, that I cannot get it to summarize by each line item and then go to the Invoices database and add up all the information that is specific to each invoice.

I tried to make this Report in the Invoices database and it summarizes the totals correctly by the group of invoices but it will only show the first line of the portal in the body of the report.

I tried to make another third database that calculates the totals for each invoice and then another report that would call upon the information in both of the databases and then report on each, invoice by invoice, but It would not work.

It seems to be the problem that the calculations for the taxes and discounts and such are INVOICE based and so cannot seem to attach themselves correctly to the information stored for also each invoice.

I know I sound confused,...I am! Please help me? confused.gifconfused.gif

Link to comment
Share on other sites

Thank so much for your help.

I start to understand this..that the calculation has to be item by item in the LINE ITEMS database, but what happens when i want the Sumary report to also add up the totals for a group of invoices adding up the discount and shipping and tax. Right now, the totals are at the bottom of the invoice and i want the report to show the itemized line items order along with the totals of tax and shipping and discounts for the entire group of invoices?? That is where it starts to get tricky if the information for one report also is pulling from the main database and not just out of the line items.

Link to comment
Share on other sites

I attach images of the database, Invoice and Invoice 2 being the portal and totals examples in the INVOICE database, and REPORT being what the way that the report should look. Unfortunately right now, the totals will not come up right, because the TOTAL OF TAX and such fields are just copied from the Invoice database and summarize ALL of the totals entered into the invoice database. I hope you can help.Ami

Link to comment
Share on other sites

Make a calc field in Line Items, DateCalc, equal to Invoice Date.

Create a relation back to Invoices, DateCalc = InvoiceDate

Create calc fields

DaySubTotal = sum(InvoiceByDay::Subtotal)

DayTaxTotal = sum(InvoiceByDay::Tax)

etc.

Your report in line items should have a subsummary by DateCalc part and these fields will go on that part. I just tested this, it works correcty.

Link to comment
Share on other sites

Thanks for your help---i think we are close. Your solution sounds brilliant.

I am just confused with this:

In what database do i place the DaySubTotal=sum(InvoiceByDay::Subtotal) and the other calc field? In the invoices or the LIne items database.

And is it the reason that in the calculation you use InvoiceByDay is because it is the name of the relationship??

Thanks again,

ami

Link to comment
Share on other sites

Wait, wait...Wow...Oh, really, I just pulled out a bottle.!!!IT WORKS!!! Bruce R...you really rock...if you were in my home town, i would buy you a drink! I am so so so relieved.

Its great. ok, ok.

Next problem.

SO, it actually worked when these day subtotals were in a trailing grand summary section, then they totaled all of the invoces that i had chosen.

So, the problem is now when I want to chooose a group of invoices that will not necessarily have the same date..for example:

I want to make the exact same idea of an invoice, but now actully for a weekly summary and a monthly summary report as well...

what kind of calculation would match a chosen group of invoices when i choose a specific month or a range of dates.

I thank you immensely for your time, and massive respect!!

Ami

Link to comment
Share on other sites

This topic is 7357 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.