@scottvonduhn To get the most recent date instead of all the dates

If you are looking for the latest Purchase Order date for ALL inventory items, then you could just use the MAX summary function in your createColumn call for trandate. If you wanetd the latest Purchase Order date for EACH inventory item, then just add a GROUP function on internalid and itemid. The best way to do this is to build the search in the UI and use the Search Export extension in Chrome to give you the code for the search.

Thanks Scott, @scottvonduhn that was my first attempt but I found it would write every PO rate to the field I created, this was my code before. When I look in the system notes on the items, each time the script would run there would be multiple writes to the custom field.

right. you have no summary functions in your columns, so you are not getting aggregated results. Aggregating in the filter is just filtering out any inventory item that does not have a purchase order after 4/5/2019. It's not giving you only the latest trandate in the results. that requires putting the summary function on the column instead

in my second example I have summary functions, unless I'm doing it wrong

yes, that one looks better. i was referring to the first one.

That was my first attempt but it would write multiple times to the field

Though, there should probably only be one MAX function. I think you want a GROUP function on internal id. Maybe not, though.

I tried both, I stuck with MAX because in the UI the internal id would not have a link on it with MAX to drill into results. I'm not getting more than one result in ui search

So, the UI gives you exactly one result, but you are getting more than one in the script, for the same search?

Haven't tried that yet, that is a good thought

Also, based on the criteria, I'd expect more than one result, unless you only have one inventory item.

I am getting multiple results in the search, I wonder if I have to sort by name instead of date

Sorting shouldn't matter in the code, unless order of updates matters

it didn't matter

to be clear on the results your script is seeing, try adding a logging statement right before the call to `record.submitFields`:

log.debug({ title: 'Item Internal Id: ' + item, details: 'Last PO Rate: ' + lastPORate });

The internal id is there more than once. For now I've just sorted by item and then by date so the most recent date gets written last. I don't like it but it works for now

ah, i think i see the problem

Ok, I can't max it because then I'll get the highest one, not the most recent, right?

right, that won't work as expected

thank you for your help

You might be able to translate this into a Map/Reduce script that combined all the results together by internal id in the Map phase, and then in the Reduce phase select only the latest rate.

what is available?

The REST Web Services (Beta) feature

In terms of getting minimal/maximal values from summarized search results, you probably need to be using When Ordered By in your search. Here's the chapter straight from my Transaction Search cookbook

Thanks Eric, our Admin came up with a formula that seems to be working but your example is probably cleaner. MAX({transaction.rate}) KEEP(dense_rank last order by {transaction.trandate}, {transaction.datecreated})