https://netsuiteprofessionals.com logo
Join Slack
Powered by
there's no way to leverage 'when ordered by' in a ...
# suiteanalytics
a
there's no way to leverage 'when ordered by' in a criteria filter, is there
n
What are you trying to achieve?
a
something very strange
n
we live on strange. can you get your requirement through the Criteria - Summary?
a
well that's what i'm hoping to do, but without being able to incorporate when ordered by as part of the summary criteria like you can in the results tab, i don't think it will work
i have a custom field on item records that stores the item rate of the item on the most recent purchase order (max of document number)
and i want to build a saved search that shows me when these two values are out of sync
so the criteria currently is something like 
Transactions : Type = Purchase Order
and in the results tab I have 
Name - Grouped
, 
Transaction : Document Number - Maximum
and 
Transaction : Item Rate - Maximum, When Ordered By Transaction : Document Number
as well as my custom field - 
Most Recent Purchase Price - Grouped
so this just returns ALL items, as long as they have at least one related PO
i'd like to return only those where 
Most Recent Purchase Price - Grouped != Transaction : Item Rate - Maximum, When Ordered By Transaction : Document Number
this is basically all an exercise to try and update the 
Most Recent Purchase Price
field on item records without using a script
so i currently have another saved search driving a non-stored value field, and a scheduled workflow to update the items stored value field with the non-stored value field's value, nightly
but i'd like it to not go through EVERY item each night - only the ones where the value needs to be updated
i've used this same pattern with success before, but not when the summarized value involved dependent sorting like this (using when ordered by)
i suppose i could make this multi-step process and build in other ns-s field value pairs somehow
perhaps with storing the most recent PO in a custom field
then only updating the most recent purchase price if most recent PO (stored) != most recent PO (calculated)
n
I have a 'generic' scheduled script that I use that takes a 3 column SS input with Record ID, New Val, Old Val. In this case it would be Item Internal ID, you Order by field, your custom Item field. The script takes all and compares them and only writs the update when they don't match.
PS: also consider you might only need to update Items with POs from today as they are the only ones with possible changed values.
a
that's a very good and obvious point - i've got it set to only look at recent POs anyway but last modified or date created of today could lighten the load
i actually really like that generic script approach - that's an easy thing to package and just set up multiple deployments with a SS param - plus you get the added benefit of the script deployment repeat schedule options which for whatever reason are far better than workflow ones
n
I’ll DM you the script.
a
thanks!
Open in Slack
PreviousNext
Powered by