POV for relationship

[LaRetta]

I'm struggling with POV for a Sales report.

Request: Ability to find Customer orders which have a LineItem ProductID of P00085. This is a floor display (just the display itself). Be able to specify a from/to on ShipDate (of Order). I need the report (parts) to look like:

Leading Part1:[color:blue]SALES REP

Leading Part2:[color:green]CUSTOMER NAME, ChainName, LastContact

body - ShipDate - InvoiceNo - TextField ( * see below)

body - ShipDate - InvoiceNo - TextField ...

Leading Part2:[color:green]CUSTOMER NAME, ChainName, LastContact

body - ShipDate - InvoiceNo - TextField ( * see below)

body - ShipDate - InvoiceNo - TextField ...

Leading Part1:[color:blue]...next Sales Rep etc...

* sample result of text field (all LineItems ItemType = DIS for that invoice): 36 Stevia 100-pak (15%), 24 Vanilla 2 oz (8%), ...

Field names for text concatenation: Quantity, ShortDesc, Discount

The determination for WHAT is included is if LineItem::ItemType = "DIS". Since I need to combine LineItems, I assume my POV should be Invoices as body with Customer as leading part and LineItems as (gulp) calculation? And I considered GTRR to LineItems, picking up the Invoice Numbers (write to multiline global?) then GTRR again to that set and ... as you can see, my perspective is a bit fuzzy. I prefer NOT to perform a find on my LineItems (it is huge). SalesID and CustomerID also resides both in my LineItems and Invoices. Ideas greatly appreciated on how to structure the relationship and where I should be planted to generate the report?

Oh. They may want a trailing sub-summary on Customer Name on the average discount given on just items = DIS for each Invoice (which is another reason I figured a new table occurrence might be necessary). Discount is a number in LineItems.

LaRetta

[comment]

Do you often have invoices with more than one line item of the same product?

[LaRetta]

Hi Michael,

Yes, we usually have same LineItem but different ItemType (Buyer Review, Display Item, Marked Sample, etc.). Thank for you considering my problem.

Update: The Display is unique ... there is always only one listed on an invoice although the quantity may be several. But on regular products, there may be ProductID P00026 ItemType = DIS, P00026 ItemType=Sample, etc.

L

Edited September 16, 2005 by Guest

[LaRetta]

Okay, I've come up with the best I can and it's quite ugly, but 'ugly' may be the only way I can accomplish what I need.

Overview: Displays (from LineItems) are isolated by selected date range. Global is set with this Value List and GTRR isolates those LineItems. Then from LineItems another global (geez) is set which jumps back to Invoices for Report (I can improve that write to multi-line global there to eliminate the duplicates as it writes, I think). The multiple (only DIS) LineItems are a calculation in Invoices based upon the Invoices for Report TO (only those related to LineItems Displays). I can't get the Contact Name to display even after creating a directly-attached Contacts TO.

With vs. 8, it seems the need to use globals for multi-key will be eliminated, right? Because it uses the found set for the (proper full) GTRR. Well, any fine-tuning (or complete re-write) would be appreciated. I really dislike this kind of fluff but we really need this report view.

It might be easier just to write the darned LineItems multiline directly to a field in Invoices.

LaRetta

DisplayReport.zip

[comment]

I got lost regarding the purpose of this.

Clearly, your report is a report of selected line items. Which line items?

IOW, if you were doing this by finding the relevant line items, what would be the search criteria?

The terms DIS and Floor Display are also not clear not me, nor their relevance to the issue.

[LaRetta]

Hi Michael

We need to find all Invoices which had a LineItem called Floor Display. It is ItemType=DIS (ProductID P00086). This indicates it's part of a 'package' sale (products sold separately). Each stock item used to 'build' the display (end-cap) is ItemType = DIS.

Once I know which Invoices have a Floor Display, I need a report of those Invoices (as Report body). The text line should be a concatenation of ONLY ItemType = DIS.

A search in LineItems for date-range and ItemType = "DIS" takes too long and GTRR was quicker. PLUS, I needed to isolate the DIS LineItems in a multiline (ValueList) to pull them into the same text calculation field for the body of the Invoice.

I'm probably just too muddied in it to see it clearly. Well ... no surprise ... my graph shows that quite clearly.

LaRetta

[comment]

I am not getting this. Date within date-range AND ItemType = "DIS" - I understand. Why is a ProductID necessary then?

> The text line should be a concatenation of ONLY ItemType = DIS.

IMHO, a body of line items would be easier to read - and probably also easier to produce.

[LaRetta]

"Why is a ProductID necessary then?"

We send out DIS products which DON'T contain an actual Floor Display (ProductID P00086). These products replace purchased product (within the display). Because they are end-cap, stores pay a different price for them and they are tracked separately than regular stock. So, many invoices may have a DIS product ... we only want those that also contain a physical floor display.

"The text line should be a concatenation of ONLY ItemType = DIS."

Correct - what products (including the display itself because sometimes they ask for several) makes up that display package. The same product may appear several times on an Invoice - be ItemType = DIS (for insertion into display), Sample (handed out), Buyer Review, regular Stock, etc.

"IMHO, a body of line items would be easier to read - and probably also easier to produce."

I suggested that originally and I'm very close to doing just that. Sales Manager wanted text field as 'note type' and wanted very small report. He wanted it as one field because (unlike my example), many products will fit on one (or two lines) max. But he just may not get what he wishes. :wink2:

Edited September 17, 2005 by Guest

[comment]

:idunno: