Instead of zero, the saved search formula results in blank column values. This occurs because there may be null/blank values for any field used in the formula. The null/blank field values, when used in a formula result in null. This can be prevented by using the NVL function to replace null values with a quantity… Continue reading Blank Columns in saved search formula results
Author: Anila George
Not Showing some Journal Entry lines on applying Customer filter in Income Statement
When applying a customer filter to the income statement, some journal lines are not showing in the income statement. Also, some accounts are showing 0 expenses even if there are entries for that account in the journal entry. This is because, in the journal entry lines, we can enter not only customers but vendors as… Continue reading Not Showing some Journal Entry lines on applying Customer filter in Income Statement
Allow per line Department is Enabled but the Updates on the Header level Reflected in Line Level
The “Allow per line department” preference is enabled in the account. But the department value selected at the header level is also reflected in the transaction line level even if no department value is selected at the line level. This is because, at the transaction line level, the Department field is not exposed in the… Continue reading Allow per line Department is Enabled but the Updates on the Header level Reflected in Line Level
Module for Landed Cost Template
To implement the landed cost template, need the supply chain management bundle. And then the feature(Landed cost) should be enabled in “Enable features”. Note: Only the landed cost templates that are assigned to an item can be selected at the transaction line level for that item.
Concatenate the Date field in Excel
When the date field is concatenated with any text field in excel, in the result field the date formate may change. The date value may also be converted to some other format. To avoid this convert the date field to the text field while concatenating. Ex: COCATENATE(TEXT(C2,”dd/mm/yyyy”),E2) In this formula, the date value in C2… Continue reading Concatenate the Date field in Excel
Saved search to get the Revenue and COGS from Item Fulfillment and Invoices Created from Sales Orders
Saved search steps, Navigate to Reports > Saved Searches > All Saved Searches > New Choose Transaction Modify the Search title On the Criteria tab > Standard subtab > set the following Filters: -Account Type is any of Cost of Goods Sold, Income -Posting is True -Created From fields…Date = set the date -Created From… Continue reading Saved search to get the Revenue and COGS from Item Fulfillment and Invoices Created from Sales Orders
Show line level Location field in Transaction saved search
In the transaction saved searches, the “Location” field displays the header level location value. There is no standard field available for the line-level location field. So to get the header level and line location, follow the below formula. For the Main Location value, Formula(Text) -> Summary function Maximum -> formula = case when {mainline} =… Continue reading Show line level Location field in Transaction saved search
How to get the “Next Approver” field in transaction search System Note fields
On Saved Transaction Searches, the System Notes Field Next Approver is not yet available on the list of Fields Available for Use as a Search Criteria. As an alternate solution, a Formula (Text) with Description = contains Next Approver; and Formula = {systemnotes.field} may be used. However, this is an enhancement – 417924.
Extract Time From Date field
Use the below formulas to extract time from a Date-Time field in a saved search Formula(Text) – TO_CHAR({datecreated}, ‘HH:MI:SS’) Formula(Text) – TO_CHAR({datecreated},’MM/DD/YYYY HH:MI:SS AM’) Note: – Always make sure formula type matches the value you are returning. For example, Formate (Date) and Formula (Datetime) is not the same. – When converting to Date, value will… Continue reading Extract Time From Date field
Extract Month and Year from Date field
The formulas for extracting Months and Year from a date field in saved searches TO_CHAR({datecreated},’MONTH’) – displays month name (Character) as result. Ex: Aprilinstead of ‘Month’ we can specify the format in which the month should be displayed.MON – Jan, Feb, etcMM – a numeric month from 1 to 12 TO_CHAR({datecreated},’YYYY’) – displays year in… Continue reading Extract Month and Year from Date field