COUNTBLANK The COUNTBLANK function returns a count of empty cells in a range. Cells that contain text, numbers, errors, spaces, etc. are not counted. Formulas that return empty text are counted. COUNTBLANK takes just one argument, range, which must be a cell range. SUMIF The Excel SUMIF function returns the sum of cells that meet… Continue reading Excel Functions
Author: Anila George
Excel: Transpose Rows into Columns
We can convert the values in each row of the same column to different columns of the same row.We can transform the items in one of those rows into columns (or vice versa). It would take a lot of time to copy and paste each individual header — but the transpose feature allows to do… Continue reading Excel: Transpose Rows into Columns
Calculate Date Difference in Saved Search if one of the Date is in Date/Time format
In saved searches, to calculate date difference we just need to use the Formula(Numeric) with formula {datefield ID 1} – {datefield ID 2). But if one of the date fields is in Date/time format, this formula will not work. If date difference can not be calculated by this formula, use the following formulaFormula(Numeric) : ABS(TO_DATE({datefield… Continue reading Calculate Date Difference in Saved Search if one of the Date is in Date/Time format
Setting Recipient for Scheduled Saved Search Emails
The recipients can be set to a scheduled saved search in two ways – Specific Recipients and Recipients from Results Setting Recipients: Specific Recipients :In the Email subtab of the saved search setup, the “Specific Recipients” tab will allow you to give details of specific individuals to who you would like to send the email,… Continue reading Setting Recipient for Scheduled Saved Search Emails
Multiple File CSV Import
Some record types include sublist fields, especially most of the transaction records. For example, Sales order and Purchase Order records include Item/Expense sublist data. We can import sublist data for some record types including them in a single file with the body level data or as multiple files. The import assistant provides the “Multiple Files… Continue reading Multiple File CSV Import
Item Saved Search to find Items with Negative Inventory in any Location
Create an item saved search Criteria :Type – Inventory ItemLocation on Hand – less than zero Results:Name Display NameLocation On HandInventory Location fields (Join field) – Name Note: The “On Hand” and “available” result fields will show the sum of the Quantity from all locations. To get the quantity of each location, we should select… Continue reading Item Saved Search to find Items with Negative Inventory in any Location
Prevent dashboard tiles from appearing in all custom portlets
Issue Faced: whenever creating new dashboard tiles, it is appearing in the custom portlet that sources “Dashboard tiles” even if that dashboard tile was not intended to appear in that custom portlet. For example, refer to the screenshot below. It is a custom portlet located in a custom center tab. The “Unfulfilled transfer Order” dashboard… Continue reading Prevent dashboard tiles from appearing in all custom portlets
Duplicate Detection of Records When Created Through CSV Import
To block the import of records that appear to be duplicated, we can use the “Prevent Duplicate Records” advance option in the import assistant. To identify duplicates, the Import Assistant uses the same criteria that you specify on the Setup > Company > Company Management > Duplicate Detection page. When the Prevent Duplicate Records option is available,… Continue reading Duplicate Detection of Records When Created Through CSV Import
Custom field in Inbound shipment to display item line total
Requirement: Need a custom body field in the inbound shipment record to display the total amount of all the item line amounts. Solution: Create a saved search that results the item line total and source it to a custom field.Create a transaction saved search of type Inbound Shipment. Add a formula(Numeric) field with formula –… Continue reading Custom field in Inbound shipment to display item line total
Add subsidiaries to Customer/Vendor record in bulk
To add subsidiaries to customer/vendor records in bulk through CSV import. The subsidiary sublist is not available in the customer record option in import assistant. For that, we can select “Relationships” from Import Type drop-down and select “Customer-Subsidiary Relationship” from the Record Type drop-down. Map Customer name, subsidiary, and an optional External ID(for the customer-subsidiary… Continue reading Add subsidiaries to Customer/Vendor record in bulk