Hello! Looking to see what others have done to provide reporting of promotions at the item level. The native process creates the discount lines based on the item - but does not provide any tie back to the item itself. I would like to be able to create/pull a report, by item, that shows the amount of promo $$'s applied to that item during a specific period. Or even conversely - which items were discounted utilizing a specific promo code. Ideally I would like promotions to NOT create a separate line when they are applied to a single item but show the discounted amount (i.e. Standard Rate, Promo Code, Discounted Rate x Qty = Amount), or is that asking too much?? Would love to hear how others have handled this challenge!

Are you posting your promotions to a separate promotions GL account(s) or are you really just setting a different item price per customer GL-wise?

The discounted amount does report to a separate GL account

Hmm...that makes it a lot trickier to do an in-line discount. If you weren't doing that I'd be thinking along the lines of separate fields at the line item level for the gross/non-promotional price and discounted amount then, with the line level amount reflecting the net of discounts. But if you're posting to a separate account you kind of need something on a separate line, even if it's something that doesn't ultimately display on the printed customer invoice.

That would work.... probably needs to be scripted?

You could add fields to track this and have those manually filled in, and in theory that could maaaybe be done without scripting, but personally I'd be looking to create something a little more slick/less error prone that yes, would involve some scripting.

Thanks, Jimbo - appreciate your help!

Sure sure, no problem. It's been a few years since I've been deep in inventory hell. So I'm definitely not the end-all-be-all, but always happy to chat.

I've done a little in SuiteQL matching my sales discount lines with the item above it using linesequencenumber...

I'm going to check into that - THANK YOU!

I've done the line sequence numbers and the GL account options, both work depending on your requirements.

Do you happen to have a screenshot of your saved search?

No, it's been a few years, but I can walk you through it

That would be terrific

What are you trying to get to show up?

Exactly - I would like to be able to generate a report of items that related to Promos during a specific period

On invoices?

Yes - I would want to know that they shipped

I don't think you can get the shipped at the line level from the sales orders and quotes

Yeah - Ignore the invoices, working from order that are not closed or cancelled

the fulfillment is connected directly to the invoice

Ignore that requirement

hah ok

You've been so kind - let me give you the big picture

one moment I'll come up with a mockup formula. I don't have discounts in my current environment so yours will be a little different

When we run promos we split the cost with our vendor. If we offer $5 off, the vendor has to pay us back $2.50. The vendor wants to see what product was sold as part of the promo. That is the primary reason for needing to tie them

ah ok

It's based on customer sales, not our purchases. We like to get our $$ back asap so we would like to able to pull the info from quotes and open invoices

got it

Not a commission thing - just need to bill the vendor

ah ok

Example:

oh hmm I really thought this would be doable with a formula but it's just not exposed in a saved search. You have two options: 1. script it 2. pull all relevant columns and use a vlookup in a separate tool like Excel or PowerBI

No, we don't care if it's shipped, only ordered

ok

I can't figure out how to look it up even in Excel. There just aren't any common fields at all

Sales orders and quotes?

Yes. The promo lines do not populate anything except rate & amount, brand & location. That's it.

This is from a LCS training sandbox so things are named differently, where it says Order it's a sales order

That seems too simple.... LOL I will try it first thing in the morning. Huh

Hey I've been doing this for 8 years, plus another 20 years in database administration / software engineering before that

You're amazing! I kind of don't want to go home now... anxious to watch this done! I love your scripting to hold the line. I will add that to our project list. We have too many scripts running now and we need to do some cleanup and consolidation before we do anything else. But THANK YOU!!!

Sure thing! Also if you ever need a consultant, I just finished a large project and am looking for new clients to fill the gap. 🙂

I will definitely keep you in mind!