Start a new topic
Answered

Filtering out quotes that DO NOT have a certain part

Hi folks,


Hopefully someone can help me make better sense of this report.


Our company has what we consider standard products (we'll call it part number 5) and custom products (we'll call it part number 10).


Sometimes we will quote/sell just part number 5 and other times we will quote/sell part number 5 AND part number 10 (same quote/order).


I can easily filter out and report on all quotes that include part number 10 to see all the quotes I have done that have custom product on it, however I also want to be able to run a report that only shows me quotes that do not have part number 10.


I notice when I do a filter on a part number and select either not equal to or does not contain part number 10, the quotes with part number 10 will still show up in the report and I believe it it because the report goes line by line and see part number 5 on the same quote and then adds it to the report.


Is there a means getting a report to filter out the quotes that do not have part 10, regardless of them having other parts on it?


The only thing I can think of now, in hindsight, is to use a custom field in the document headers that designate a "custom" vs "standard" product quote, but that would now require me to go through several thousand quotes to add data to a custom filterable field.


Thanks for any insight you may have. I hope I was able to explain my dilemma clearly.


Barry


Best Answer

Hi Barry,


This is achievable, but a little complex to explain.


Basically, we ask the report to check every line as to whether it is "Part 10".


If it is, we give this line a value of "1", if not, we give it a value of "0"

.if.documentitems->manufacturerpartnumber="Part10".then.1.else.0


We then total up all those values (per document) and then only show the quote number, date, company, etc. if that value is above 0. (indicating one, or more, lines on that document is Part 10).


This requires no changes to historical data and, with a bit of work on the report, will give you the information you want (or don't want)


Answer

Hi Barry,


This is achievable, but a little complex to explain.


Basically, we ask the report to check every line as to whether it is "Part 10".


If it is, we give this line a value of "1", if not, we give it a value of "0"

.if.documentitems->manufacturerpartnumber="Part10".then.1.else.0


We then total up all those values (per document) and then only show the quote number, date, company, etc. if that value is above 0. (indicating one, or more, lines on that document is Part 10).


This requires no changes to historical data and, with a bit of work on the report, will give you the information you want (or don't want)

Getting the QuoteWerks Report engine to filter as you're describing is probably possible using advanced techniques in the Layout Designer such as counting "Part Number 10" Line Items and then Filtering on Sections.


The much, much easier approach would be to go with the idea of designating a "custom" vs "standard" product quote at Document Header level.


Hope this helps!


1 person likes this

Thank you both for the insight. I am glad (in a way) that it wasn't something super simple and I couldn't wrap my head around it after having spent a bit of time trying to accomplish it.


You do give me an idea on what to tinker with initially (counting the custom and displaying those with 1 or 0 instances of the custom part). I'll at least give this an attempt as I do understand what you're saying.


Otherwise I will end up going the other approach.


Thank you both again for taking time to respond.


Barry

Feel free to post back or reach out if you need further help.

Login or Signup to post a comment