khultquist

Bulk Receiving of Multiple Receipts against Multiple Purchase Orders is in the 2012.1 release!

in Bulk Receive POs or Receive multiple POs at once?? Comment by khultquist Feb 21, 2012 4:23PM

I just tested the function MONTHS_BETWEEN and it worked with a datetime field MONTHS_BETWEEN({datecreated}, {today}) So give this a try MONTHS_BETWEEN({custrecord_plp_project_id.lastmodified}, {custentity_nv_projorpricinglastupdated})

in A less disgusting formula to compare two DateTime fields? Comment by khultquist Feb 4, 2019 2:55PM

wahendrick-cws since you are using Chrome, look into Screen Scraper extensions. I don't use any of them so I can't vouch, but some even claim to scrape 'next page' automatically. https://chrome.google.com/webstore/search/screen%20scraping?hl=en-US

in Copy Search Results for Easy Paste into Spread Sheet Application or Email? Comment by khultquist Feb 1, 2019 12:58PM

That's how I would do it too, JCirocco Alternatively, you can check the "Use Expressions" checkbox in Criteria, and use parentheses with AND / OR for two formulas.

in OR / AND operators in saved searches? How to check to see when, for example, {internalid} is betwee Comment by khultquist Jan 2, 2019 10:29AM

Could it be that you have some results without an Activity, and that's causing the error?

in Basic CASE when query? Comment by khultquist Nov 1, 2018 10:40AM

A couple ideas... 1. what is sysdate, and shouldn't it be in {}? I typically use {today} 2. try running a simplified version like (CASE WHEN {accounttype} = 'Income' AND (Trunc(sysdate, 'IW') - Trunc({trandate}, 'IW'))/7 = 1 THEN {quantity} ELSE 0 END)/ ((SUM(CASE WHEN {accounttype} = 'Income' AND (Trunc({today}, 'IW') -…

in Divide by Zero Errors, even with NULLIF or NVL Comment by khultquist Sep 20, 2018 2:40PM

I know with email generated from saved search, you can find the internalid of the saved search in the email header. See this thread: https://usergroup.netsuite.com/users/forum/platform-areas/reporting/407204-keeping-track-of-saved-search-alerts There may be a similar field for scripted email, not sure...

in Unknown Source of Automated Emails Comment by khultquist Sep 4, 2018 11:00AM

There's no way (in off-the-shelf NetSuite) to copy/paste a list of search criteria or filters. That would take a custom Suitelet. One way to accomplish the same result would be to use a custom body field, like a checkbox, and populate it on your pre-determined list of invoices using CSV import. The add the checkbox to your…

in How to Search Multiple Invoice Numbers in Saved Search Comment by khultquist Feb 6, 2018 2:10PM

It's always tricky to get NetSuite to return search results for records that don't exist. The key is to return results in one column but not in another. For example [LIST] [*]Create a placeholder transaction everyday, such as an invoice for $0 to "Misc Company". You can create these through script, csv, etc. [*]Modify your…

in Defaulting to 0 when no result Comment by khultquist Jan 16, 2018 9:38AM

I don't know if you can 'filter' in the strict sense of the word -- as in 'available filters' on a saved search. But you can use 'summary criteria' in a saved search. Standard Criteria Main Line = F Summary Criteria Count of Line ID = 1

in Filter transaction search by the number of line items? Comment by khultquist Jan 9, 2018 12:40PM

Use summary search criteria of MAX of invoice date is less than 12/31/2015

in Search to find old stock Comment by khultquist Oct 17, 2017 11:52AM

It's not that straightforward. You will probably have to use COUNT of all service items >0, and MAX/MIN to of service items to be filter out the item you don't want. Try the other way first, using 'Items Ordered'.

in Need saved search to return when an entity doesn't have a service in a Member Service Agreement fiel Comment by khultquist Sep 11, 2017 4:53PM

I thought there was a thread about this, and did a quick check of the user group but couldn't find it. This should be possible to create using MOD, CASE, TO_CHAR and TO_NUMBER.

in Saved search formula to calculate difference between dates excluding weekends Comment by khultquist Oct 9, 2017 12:46PM

Sometimes it's difficult/impossible to use Saved Searches to find records or joins that don't exist, but here are a few suggestions: [LIST] [*]If you are searching for Customers, and the services are Items, try using the search criteria of 'Items Ordered' and then choose 'none of' Service C [*]Use a Summary Saved Search,…

in Need saved search to return when an entity doesn't have a service in a Member Service Agreement fiel Comment by khultquist Sep 8, 2017 3:56PM

You can export the list of Custom Transaction Forms to CSV but I don't see a way to search for them.

in Saved Search on transaction forms Comment by khultquist Sep 5, 2017 12:26PM

Sorry I misread that... I created something like this once, and I believe I joined for transactions as pcutler says, but then limited it to invoices, receipts, inventory adjustments, etc. (basically any transaction that can have an effect on inventory), then set a date criteria for <12/31/2015. The logic here is that in…

in Search to find old stock Comment by khultquist Oct 17, 2017 2:23PM

This should work and is a little bit cleaner Least ( (case when NVL({custrecord307},0) = 0 then 9999999 else {custrecord307} end), (case when NVL({custrecord13},0) = 0 then 9999999 else {custrecord13} end), (case when NVL({custrecord15},0) = 0 then 9999999 else {custrecord15} end), (case when NVL({custrecord17},0) = 0 then…

in Search Minimum Value across multiple custom fields Comment by khultquist May 25, 2017 9:41AM

How do you find out about these undocumented fields I use Michoel's Chrome extension Netsuite Field Explorer it's awesome and worth the price!

in SQL Case When statement Comment by khultquist Jun 19, 2018 12:04PM

Using joined records in searches can be tricky, and sometimes the results columns can act like criteria. That said, I can see one immediate problem: You have two criteria that are mutually exclusive [LIST] [*]Contract Item: Contract Item End Date - is on or before yesterday [*]Contract Item: Contract Item End Date - is on…

in Saved Search Help Comment by khultquist May 2, 2017 4:35PM

Try this CASE WHEN {custrecord104.custrecord127} = '1' THEN CASE WHEN {custrecordf} = 'No' THEN 'Requires Attention' WHEN {custrecord136} = 'No' THEN 'Requires Attention' WHEN {custrecord136} IS NULL THEN 'Requires Attention' WHEN {custrecord75} = 'No' THEN 'Requires Attention' ELSE CASE WHEN {custrecord104.custrecord127}…

in Nested Case Statements Comment by khultquist Feb 1, 2017 9:16AM

I suspect that the data types are different or there are nulls, and this is throwing off the logic. I see that you are using sum() in your statements, that might be converting currency type to float. If you do end up using a custom field, you can add that that to the calculation. And add some handling for null. Something…

in Saved Search using Ranges / Bands Comment by khultquist Jan 27, 2017 1:22PM

The key to simplifying this is to rewrite your commissions with cumulative percentages, as follows: (1% of the total) + (1% of the total over The key to simplifying this is to rewrite your commissions with cumulative percentages, as follows: (1% of the total) + (1% of the total over $1,000) This will give the same results,…

in Saved Search using Ranges / Bands Comment by khultquist Jan 27, 2017 11:36AM

It can be tricky to discern the correct fields, there are a lot of Inventory and Location choices... in your case you want to use 'Inventory Location' and 'Location On Hand'

in Looking up inventory through saved searches Comment by khultquist Mar 1, 2017 5:44PM

Can you post screenshots of your search criteria tab and results tab?

in Saved Searches - incorrect results Comment by khultquist Feb 7, 2017 2:47PM

With multiple locations, you can probably accomplish what you are trying by making it a Summary Search and using Summary Criteria, and some SQL formulas.

in Saved Search Results Based On Two Inventory Locations Comment by khultquist Jan 11, 2017 2:00PM

If you only have two locations, you can do this with 3 criteria lines, making use of 'Inventory Location' and 'Location On Hand' [IMG2=JSON]{"data-align":"none","data-size":"full","src":"http:\/\/i65.tinypic.com\/r26mg1.jpg"}[/IMG2]

in Saved Search Results Based On Two Inventory Locations Comment by khultquist Jan 10, 2017 2:06PM

try this (looks like your first two lines are both at 5%) 0.05 * ({netamountnotax}-{shippingamount}) + 0.00 * (case when {netamountnotax}-{shippingamount} > 6000 then {netamountnotax}-{shippingamount} - 6000 else 0 end) + 0.01 * (case when {netamountnotax}-{shippingamount} > 12000 then…

in Saved Search using Ranges / Bands Comment by khultquist Jan 27, 2017 12:31PM

You have two criteria, which gives you four permutations: Warehouse = 0, Showroom = 0 Warehouse > 0, Showroom = 0 Warehouse = 0, Showroom > 0 Warehouse > 0, Showroom > 0 But I have to say I'm a little confused about which one(s) you're looking for, since these are conflicting statements 0 inventory in the showroom but have…

in Saved Search Results Based On Two Inventory Locations Comment by khultquist Jan 10, 2017 11:14AM

Add yourself or another email account you control to all automated emails. It makes tracking down problems a lot easier.

in Automatic Email within Saved Search - Shows Sent, User did not receive Comment by khultquist Jan 6, 2017 9:43AM

Does the script use the actual search record (built in the UI) or did you recreate the search in the code?

in Saved Search works in browser but fails in script? Comment by khultquist Sep 22, 2016 4:23PM

MFA for Oracle Community

khultquist Red Ribbon

Comments