Hi all -
I've created a custom record for the purposes of logging, daily, the on-hand quantities of all of our SKUs in each of 3 locations. The record is very simple, called Item Inventory History, and has the following fields:
[LIST]
[*]Date
[*]Item (list/record, is parent)
[*]Location (list/record)
[*]On Hand Count
[/LIST] I wrote a scheduled script to create the log records nightly based off of a simple saved search; of course, I can only use so much governance, so the scheduled script needs to be able to pick up from where it left off and run until it's done. Normally, I would just add a custom checkbox or date field to, say, the item record and have the saved search ignore the records that were already processed on that same day.
The problem is, I don't want to update a date (or any) field on each item record nightly - that really causes the script to choke, and it would take hours to process the list. I solved this by using the parent:child relationship and adding a summary criteria to my saved search:
Maximum of Item Inventory History : Date is not on or after Today
This works to remove the items from the search after the Item Inventory History record is created by my script; HOWEVER, if the script gets interrupted and only creates a record for one or two of the locations (and not all three), the Max(Item Inventory History.Date) function returns the date for ALL rows, regardless of whether a record is there or not. I suspect this is due to the fact that only the Item is the parent record of the custom record, whereas I really would want a combination of the Item & Location to be the parent. As far as I know, this is not possible in NetSuite.
Here is a visual representation of the problem in my search:
[ATTACH=CONFIG]n414354[/ATTACH]
Here is the custom record that is created:
[ATTACH=CONFIG]n414355[/ATTACH]
Ultimately I want to use the data from this custom record in an external BI software application, to identify patterns in out-of-stocks, trends, etc. I'm aware that it's a lot of data to store and I'm also aware of the report in NS that will give you some of this data on demand, but I need to be able to query and analyze it externally.
[LIST]
[*]Is it possible to accomplish what I'm after using custom records this way?
[*]Does anyone have any other out of the box ideas for something like this?
[/LIST] Thanks in advance for your time.
Alex