Tag: saved search formula

Change date format in a NetSuite saved search result column

In NetSuite, the TO_CHAR() function can be used to convert a date object into a text representation of it. Here are some examples of date/time format values that can be used with TO_CHAR(): ‘YYYY’: 4-digit year‘IY’ or ‘IYYY’: ISO Year‘M’: Month2‘DL’: Long version of the date including day of the week, month, day, and year‘YEAR’:… Continue reading Change date format in a NetSuite saved search result column

Client needs to create saved search to cumulate the sales order amount column.

Scenario: Client needs to have a saved search to cumulate the Sales order amount separately for each location. Solution: List> Search> Saved Searches Select TransactionsEnter Search TitleCriteria:Type: Sales OrderMain line: FalseTax line: FalseCOGS: FalseShipping: False Results:Field: IDField: Location Field: AmountField: Formula(Currency) SUM ({amount}) OVER(PARTITION BY {location} ORDER BY {internalid} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT… Continue reading Client needs to create saved search to cumulate the sales order amount column.

Saved Search Shows Identical Values in Multiple Formula (Numeric) Fields

Issue When using multiple formula fields with numeric values in a saved search, you may encounter an issue where the search results display the same value in all these fields, even if they should be different. Solution: Use Labels with Numeric Formulas To resolve this problem, use different labels for each of your numeric formula… Continue reading Saved Search Shows Identical Values in Multiple Formula (Numeric) Fields

Saved search formula to exclude not only the lines but the entire record.

Requirement – In the saved search, the purchase order records need to be excluded if any of its line items satisfies a condition. When adding the condition, only the item line is excluded from the search, not the record. Solution – The condition should be applied as the summary condition. CASE WHEN item.class =’labor’ THEN… Continue reading Saved search formula to exclude not only the lines but the entire record.

Ranking Items with Multiple Sorting

Sort the items based on the following order: Qty Sold -> Descending Qty Available > 2 -> Descending Qty Available <= 2 and Status = A,B -> Descending Qty Available <= 2 and Status = S -> Descending Brand in the following order: Bosch LG Appliances GE Appliances GE Profile Cafe Samsung Thermador Monogram Other… Continue reading Ranking Items with Multiple Sorting

Unexpected error when using the ‘Age in Days’ function.

Issue – The ‘Age in Days’ function is used in the saved search with Formula(Date) and an unexpected error is shown when running the search. The Formula(Date) is used to calculate the expiry date age here. So, no need to use the Age function here as the difference between today and the expiry date is… Continue reading Unexpected error when using the ‘Age in Days’ function.

Create a saved search to display the total of sales amount form current month to date last year and this year.

Scenario: Saved search to display the total of sales amount form current month to date last year and this year considering sales orders. Solution: List> Search> Saved Searches Select TransactionsEnter Search TitleCriteria:Type: Sales OrderMain line: FalseTax line: FalseCOGS: FalseShipping: FalseResults:Field: Documents number, Summary Type: GroupField: Formula(Currency), Summary Type: (Sum): CASE WHEN TO_CHAR({trandate}, ‘YYYY’) = TO_CHAR({today},… Continue reading Create a saved search to display the total of sales amount form current month to date last year and this year.