https://netsuiteprofessionals.com logo
Join Slack
Powered by
I have created a custom record that records daily ...
# suitescript
s
I have created a custom record that records daily the current inventory levels using a nightly scheduled script. I want to be able to send a daily report that shows the latest information. How do I pull only the last record? I want to pull it for each item each night and update the record with both the daily totals and the incremental change from the previous day. My record currently has only the SKU, the datetime of the record creation (when my script runs) and the avail/onhand/onorder/backorder quantity fields.
a
can you not just use a filter on the record creation datetime?
s
I suppose that I could pull yesterday's numbers but if my script that records those every night happens to run twice in a day or get missed for whatever reason on a day, getting a record where the creation date is yesterday will occasionally fail.
In sql server I would use maximum or order by date and pull top 1. but with saved search, I cannot seem to get my query to give me one record with the quantities when using group by and maximum on the date.
a
add another field to your custom record check box type Is Current when you create them you set that checkbox to true. berfore you do the create you find the previous one for that item that has is current = T and you set it to false
yeah summary search won't work
this way you can exclude some items that had no changes that day from your script, so you don't have to keep creating pointless custom records if nothing changed
s
hmmmm. Thanks, I was trying to keep my calls to a minimum as I have to update nearly 600 skus currently. But I may have to go that way. I assume that I can't do generic updates like in SQL? set current = false when date is < current date?
a
we dont have direct write access to the NS DB
you can do that too via suitescript, but then you'd have to create them all on every run, I was trying to add efficiency by only creating the ones that changed
is it really 600 skus everyday? like do they all have changes everyday? cos I was assuming some are more stable and can just be skipped, if you can write the logic in such a way that you can know they didn't change
what do you mean by keeping your calls to a minimum?
s
I did not think about comparing the current = T record to the results pulled for the day and updating only those. that may be the better way to go.
my script currently pulls a list of active items with their quantities and loops through them to add the records to the custom record. Now I will need to read the custom record within that loop, compare it to the current one and, if changed, write it out and then remove the current flag from the previous.
I can change the initial item pull to those that have changed that day. that would help.
a
right, its a little more work to do the compares, but it potentially saves doing the creates at all, which will be a win longterm
s
obviously that would pull any change and not just quantity but those other changes should be minimal.
a
not to get TOO fancy but you might want to support both? have the logic controlled by a script param so you can manually run and select to do ALL items vs. just the regular scheduled run that just checks for changes
you'd also have to add logic for new items, cos if you try to compare and there's no previous you need that to be handled the same as the previous is different
s
yes. my scheduled scripts normally have two deployments. one for schedule and one for manual execution.
the pull of the existing record for that sku can then check record count of return and skip the compare when 0 to add the item if it is new
👍 1
thanks!
a
yeah not complicated, just need to be aware
m
You might have your scheduled script create a new record marked "is Current" as mentioned above, then have a user event script for the summary record run on create which is responsible for unchecking "is current" for each record of the same item (there should only be 1 record for each item that needs to be unchecked). This separates your logic, but you don't have to have a script create 600 then uncheck 600 records. Scheduled script creates 600 records. Each of those records triggers a UE script that updates the previous record for that item.
r
Instead of updating checking the current checkbox and unchecking the previous record. You can just have a small suiteql query in your schedule script that after it creates all the records it sends an email
s
**Mike. yes I have done UE scripts but I always prefer to keep things simple. If I go that way then there are two scripts that could be subject to failure. If I go single script, then if it fails in the middle then those items it got to would be fine and the others still have the previous day.
**raghav...if you mean simply send an email of the records...no. That would satisfy the immediate need of a daily report but I always want to look long-term and would want this available for other uses.
Thank you all, I will probably implement a single script that updates only changed items. It will get the list of changed items, get the current record for that item, or create it if it does not exist. If it does exist, it will clear the flag on the old record. I may use that data and include increment change fields comparing the new and the old and recording the diff. Because even that would be difficult with saved search to pull the current and the previous record when there could be many previous records.
28 Views
Open in Slack
PreviousNext
Powered by