If you're pulling Est. GP and Cost on an invoice s...
# suiteanalyticsj
J-Mack
08/27/2021, 1:22 PMIf you're pulling Est. GP and Cost on an invoice search, won't that pull the actual GP at that point since the numbers are finalized? Team is hung up on it saying "estimated". Seems weird to have to do formulas to get the "real" GP numbers. Never questioned it before, but being asked about it made me wonder. It seemed good to me, but maybe I'm off the beam. Funny how having someone ask makes you wonder.
k
KevinJ of Kansas
08/27/2021, 1:51 PMNo
KevinJ of Kansas
08/27/2021, 1:51 PMEst GP is established at time of SO - and flows through to the invoice, but your actual cost is incurred on the fulfillment, or on a vendor bill (depending on if you are doing drop ship or not)
KevinJ of Kansas
08/27/2021, 1:52 PMNow generally, it's gonna be in the ballpark if you are using the same costing method
KevinJ of Kansas
08/27/2021, 1:52 PMhowever, FIFO/LIFO aren't supported for estimated cost.
j
J-Mack
08/27/2021, 2:02 PMOkay, so we do have to do formula work to get "real" GP. Looks like we'll have to source from several different records?
k
KevinJ of Kansas
08/27/2021, 2:03 PMWell, first question - are you always selling from inventory? or always dropship? or mix of the two?
j
J-Mack
08/27/2021, 2:03 PMMix.
k
KevinJ of Kansas
08/27/2021, 2:03 PMDrop ship makes getting real GP in a saved search stupid hard.
KevinJ of Kansas
08/27/2021, 2:03 PMYou might need a customization to push values back to a custom field on your invoices
j
J-Mack
08/27/2021, 2:03 PMWe're probably 80% drop ship.
J-Mack
08/27/2021, 2:04 PMAnd because we do customized items, some of our items do not have set cost and price. It's set at the time of sales order creation.
k
KevinJ of Kansas
08/27/2021, 2:04 PMalternatively - you might be able to fudge with the values to keep the SO # in a static field on the PO/Invoice/Vendor Bill
KevinJ of Kansas
08/27/2021, 2:05 PMand then you do a search and decode it to get it grouped by Sales Order so that you can display the info
KevinJ of Kansas
08/27/2021, 2:05 PMI'd probably expect to dump 4-5 hours into getting all the pieces working for it.
j
J-Mack
08/27/2021, 2:05 PMOkay, well, we do need it. It's one of the top requests.
J-Mack
08/27/2021, 2:07 PMWill the captured field on the invoice cover our flux price/cost items?
k
KevinJ of Kansas
08/27/2021, 2:07 PMYeah.
j
J-Mack
08/27/2021, 2:08 PMSounds good. I think that's what we want, since this will primarily be used for commissions and such.
k
KevinJ of Kansas
08/27/2021, 2:08 PMWe're pretty close to the limit of what I'd want to do for free, but give me a sec and I'll give you high level approach
👍🏼 1
KevinJ of Kansas
08/27/2021, 2:09 PMI'd add a custom field call it "Analysis" or something. Set that field value via workflow/script on records. On Invoice it's created from. On PO it's Created from Created from on VB It should inherit from PO.
KevinJ of Kansas
08/27/2021, 2:10 PMWhat that should result in is a consistent field value like "SO #1234
KevinJ of Kansas
08/27/2021, 2:11 PMThen you do a couple decode statements based on transaction type field in order to get values like maximum of "invoice number" (and # of invoices in case an SO gets billed more than once)
KevinJ of Kansas
08/27/2021, 2:11 PMthen your search contains your types of Item Fulfillment/invoices/vendor bills
KevinJ of Kansas
08/27/2021, 2:11 PMfrom there you'll have to do decode statements to calculate the amount fields based on the transaction type
KevinJ of Kansas
08/27/2021, 2:11 PMand your search gets grouped by the analysis field I suggested earlier
KevinJ of Kansas
08/27/2021, 2:12 PMDefinitely something you do in chunks in order to get the right values individually, and then work the way to it being done. 4-5 hours assumes you are good at decode/understand the base structure. probably takes longer if you're unfamiliar with it.
j
J-Mack
08/27/2021, 2:12 PMI've never done decode at all. 😞
k
KevinJ of Kansas
08/27/2021, 2:12 PMand if you have to script the field value as oppsoed to workflow, probably adds another layer
KevinJ of Kansas
08/27/2021, 2:13 PMyou can use case statements in the same manner as a decode
KevinJ of Kansas
08/27/2021, 2:13 PMso if you're familiar with that, it should work. In theory - case statements are slower, but you shouldn't be running into any character limits with this one due to only having to deal with a few transaction types
KevinJ of Kansas
08/27/2021, 2:14 PMdecodes work like
decode ( {field I want to check}, value 1, what I want if it's equal to value 1, value 2, what I want if it's equal to value 2,..etc., if it's not equal to any of my values)j
J-Mack
08/27/2021, 2:14 PMI'll look into it. Thanks for your help!
k
KevinJ of Kansas
08/27/2021, 2:14 PMShould be enough to get you started down the path.
j
Josh_SatoriReporting
08/27/2021, 5:49 PMWe've solved for that issue. Our reporting product has unlimited joins across all the netsuite data to answer questions like "actual" GP on an invoice, SO, PO. Let me know if you are interested in a demo. www.satorireporting.com
Josh_SatoriReporting
08/27/2021, 5:59 PMHere's an old video...but shows the basic concept (high level): https://www.satorireporting.com/post/netsuite-sales-order-profitability/
2 Views