Sales Report

[Mandu]

The CD sales database contains the following tables:

Products [various CDs by various Artists]

Artists

Invoices [sales of CDs to various Customers]

LineItems

I need to produce a report summarizing sales for a period of time, as follows:

Artist _______ Product ________ # Sold _______ $ Total

I *think* I need to attach my layout to the LineItems TO. From there I can get

LineItems::Quantity

LineItems::ExtendedPrice

LineItems::InvoiceID > Invoices::Date

LineItems::ProductID > Products::CDTitle

LineItems::ProductID > Products::ArtistID > Artists::Name

By breaking at each ProductID, and summarizing on ExtendedPrice, I should be able to fulfill the requirements. The layout "wizard", however, has me confused.

Have I identified all the essential pieces of this task? I think I need a summary field for summing LineItems::ExtendedPrice, but I don't know what table it belongs in!

Thanks for all help,

Chap

[mr_vodka]

You summary field will belong in your lineitems table as well.

[Mandu]

Yup. Thanks.

Now here's the next kicker. I gotta ask user for date range, and use the two dates in a Find against the LineItems to provide the found set against which to report. I don't know what happened to my mind, I just can't seem to remember how to work this thing.

I'm scripting this (did i mention that?). I throw up a small new window asking for From and To dates, which are plugged into global date fields in LineItems. Pause, Resume. Now perform a Find against LineItems for LineItems whose parent Invoice's date (Invoice::Date) was in range LineItem::g_Report_Date_From to LineItem::g_Report_Date_To. Does Invoice::Date have to be on the stupid layout for this to work? I couldn't remember, so I put it there, figuring I can always make it invisible somehow. Still, the Perform Find gets a 401 -- no records found. What could the problem be?

[mr_vodka]

If you have entered in the dates to global fields, you do not even have to do a find. Create a relationship from the global fields to the Invoice Table. gStartDate ≤ InvoiceDate and gEndDate ≥ InvoiceDate. Then use go to related record. Make sure you have the option checked to display only related records. Once you do that, use another GTRR to get to the lineitems table child records. Make sure you have the option on to display only related records as well. However, there is yet another option that states 'Match all records in found set'. Check that.

Now run the script to generate your summary report.

[Mandu]

Very nice - works very neatly. I did discover the source of my woes: Invoices::Date was defined as a text field.

Thanks for the help.

Chap