Thursday, June 30, 2022

Salesforce: Query Org ID and Sandbox

You can get your salesforce Org ID and other info from Company Information in the Setup menu. But, if you need to get the data from SOQL, here is the sample query

SELECT Id, Name, InstanceName, DefaultLocaleSidKey, TimeZoneSidKey FROM Organization

While for Sandbox info, you can go to the Sandboxes in the setup menu in Prod. The same, you can use SOQL to get the information, but you need to use Tooling API. 
SELECT Id, SandboxName, Description, LicenseType, HistoryDays FROM SandboxInfo

The Id returned here is SandboxInfo ID, not the Org ID and unfortunately, we can't get the Org ID.

You will get the same result if perform query with Rest Explorer from Workbench
GET /services/data/v53.0/tooling/query/?q=+Select+Id,SandboxName,LicenseType,Description+from+SandboxInfo


Wednesday, June 8, 2022

Tips for Salesforce Data Cleaning

Data is arguably one of the most important aspects of a business. Good data helps drive decisions that improve products, services, and customer experiences. If your organization has bad Salesforce data, you will start to notice inconsistencies down the line that can challenge marketing campaigns, sales initiatives, reporting, and analysis.

Data naturally decays over time, so without a proper cleanse that fixes recurring errors, bad Salesforce data will continue to worsen and hinder your business. In this blog, I’m going to identify common types of bad data, why you should complete Salesforce data cleaning, and give you tips for improving these processes.

What is bad Salesforce data?

If you use Salesforce, you may already notice data-related problems due to input error, poor data collection methods, or natural data decay. Bad data, like the types listed below, has been known to cost organizations an average of $13 million in revenue each year. 

These are the most common types of bad Salesforce data that you may encounter:
  • Duplicates: Multiple different records that have similar or the exact same data.  
  • Inaccuracy: Typos, data entry errors, or fields that are filled in incorrectly.
  • Irrelevancy: Records or contact information that is outdated or no longer relevant.
  • Inconsistency: No standardization with formatting fields within records.
  • Incomplete: Missing data in key fields, such as company size, industry, or title.

The reasons for Salesforce data cleaning

Good data improves productivity and insights, which helps organizations reach business goals and stay ahead of the competition. Day to day, good data can help your team make smarter decisions that drive growth and revenue as well as increase overall efficiency. It can also help your team with audience targeting that increases the effectiveness of campaigns. 

Clean data is good for your customers as well. It can help build better customer relationships when you know exactly what they need, so your team can offer more appealing services or products.

And finally, good quality data open your organization to greater and more targeted opportunities that increase your competitive advantage and profitability. 

Five tips for a Salesforce data cleanse

Salesforce data cleaning is an ongoing process that helps your business improve data quality over time. Follow these four tips to increase the effectiveness of your Salesforce data cleanse.

Identify root causes

The first step in cleaning Salesforce data is to identify where the majority of errors occur. Is your team inputting data manually and making mistakes? Or are you relying on a data collection system that doesn’t have the right integrations with your business systems? 

These inconsistencies or errors within your current data collection methods may be the cause of bad data. Catching bad data at the source will help you determine what needs to be changed or improved. This initial data audit provides a clear path toward quality data, whether it be through more team training, better software, improved data governance, or something else. 

Choose the right tools

Whether your organization deals with hundreds or thousands (or even millions) of datasets, having the right tools to effectively collect and manage this data is critical to maintaining it. Manual data cleaning for datasets this large is also generally too big of a task for a single data scientist or administrator. 

Having the right data cleaning tools in your systems accelerates cleaning processes and minimizes manual tasks. These tools can automate cleaning tasks, with specific functions that target and correct data quality issues, such as duplicates or missing values.

In addition to adding data cleaning software, implementing an integrated web form and data collection platform like FormAssembly helps ensure that data entering your Salesforce Org is clean and high-quality from the start. 

Standardize processes

The phrase “garbage in, garbage out” is just as relevant to data collection as any other process. Manual processes that begin with a customer filling out a paper form and end with an employee inputting data by hand into Salesforce are prone to numerous errors. 

These processes make it virtually impossible to have consistent validation or standardization of data collection methods. Adopting online forms for streamlined collection, training employees on validation standards, and using data cleaning software help eliminate many of these data errors. 

Setting standards for data ensures that all data entered into Salesforce through a web form follows the same formatting (email, industry type, address, etc.). Standardization minimizes the discrepancies that lead to duplicate records and helps maintain data quality over time.

Enforce rules

Without rules that are set up correctly, data will never be cleaned or completed. Users are joining and leaving the company, which also means the new users may not be aware of existing rules. Manual intervention wastes a lot of time for small teams and is nearly impossible for larger teams.

Setting mandatory fields, dependency fields, and validation rules are easy and can be implemented by Salesforce admin within minutes.

Complete routine audits

Once you have completed a comprehensive Salesforce data cleanse, the process does not stop there. Without proper maintenance, data errors and data decay will inevitably continue to occur. Conducting routine audits means that you will discover and resolve issues faster, while providing your organization with an in-depth look at how data is being processed and stored.

A data quality audit should always include error identification, error resolution, and improved standardization protocols. These audits, when completed regularly, keep your data clean and give your team the information they need to improve processes, analyze accurately, and maintain productivity. 

Clean data starts at the source

Getting quality data into your Salesforce Org efficiently and accurately is the next step in keeping data clean long-term. A data collection platform with advanced Salesforce integration like FormAssembly helps ensure clean, high-quality data from the source—online forms. 

Using FormAssembly’s web form builder, you can easily create Salesforce-connected forms that create or update data for standard and custom objects, prefill forms, and more. In addition, FormAssembly’s newest solution, Workflow, makes it possible to build entire data collection workflows to further reduce errors, accelerate processes, and increase productivity for your team. 

Learn more

With quality data available to leverage, your team has more time, knowledge, and resources to spend driving initiatives that help increase revenue. To learn more about how FormAssembly helps with clean Salesforce data collection, visit their website or find them on AppExchange.

Tuesday, June 7, 2022

CRM Analytics: Dashboard URL

CRM Analytics Dashboard URL Id starts with prefix 0FK, which is shared between dashboard and lens. We can share the dashboard with users in Analytics Studio or Analytics tab, although ideally, Analytics Studio is intended for the dashboard builder. The Analytics tab will let users stay in the Lightning app.

When working on a dashboard in Analytics Studio, we will see the URL, for example:

If we share the above URL with a user, the dashboard will be opened in Analytics Studio.

If we only put the Id after such as
this will be translated to
which will open the dashboard in the Analytics tab

while a lens will be translated similarly, with the difference in the assetType parameter, such as
this will open the lens in the Analytics too

There is another option to open the dashboard in the Analytics tab
this will be translated as

Clicking the Share button in the dashboard will give us 2 options, and the difference is only at the last parameter which is "analyticsContext"

Analytics Tab

Analytics Studio

Friday, May 27, 2022

Salesforce: Retrieve Custom Field Created Date

If you notice in Salesforce: Retrieve all fields from an Object, all options to retrieve all fields will not include Created Date, but only Last Modified Date, so this blog will share how to get the custom field creation date.

You need to query the CustomField object and select Use Tooling API.

Sample query: SELECT DeveloperName, CreatedDate FROM CustomField WHERE TableEnumOrId = 'Account'

use Salesforce Inspector

use Developer Console


  • DeveloperName field in the Tooling API does not include the suffix __c, so you need to manually add __c to get the API Name, e.g. DeveloperName = Region --> API Name = Region__c
  • TableEnumOrId field, for the standard object, it would be the object name e.g. Account, for custom objects, it would be the object Id, starting with 01Ixxxxxx, you can easily get the ID from the URL of the object from Object Manager.


Tuesday, May 17, 2022

Salesforce: Retrieve List View columns and filters

Five years ago, I blog retrieving List View Sharing to get the list of users, groups, and roles that can access list views. This blog is to continue to get the columns and filters.

Open Workbench and from Rest Explorer, example: /services/data/v53.0/sobjects/Case/listviews/00B0o00000Fq3xQEAR/describe

Make sure to update the object name in red and List View id in orange.

To get the list of columns, click the "Show Raw Response" link. 

Copy the result of the column and use JSON to CSV tool to convert it into CSV or Excel file. Some of the columns here are added by the system and do not appear in the list view, such as Id, Record Type Id, etc.

To get the filters; looks for the query underneath, this will also show the fields, filter, and order in SOQL format:

SELECT CaseNumber, Contact.Name, Subject, toLabel(Status), toLabel(Sub_Status__c), toLabel(Priority), CreatedDate, Owner.NameOrAlias, RecordType.Name, Id, RecordTypeId, CurrencyIsoCode, LastModifiedDate, SystemModstamp, Contact.Id, ContactId, Owner.Id, OwnerId FROM Case WHERE IsClosed = false AND (Contact.Name != null OR Status = 'Escalated') ORDER BY CaseNumber DESC NULLS LAST, Id DESC NULLS LAST

** Use toLabel(fields) to translate SOQL query results into the user’s language.

Here is the original list view:

Sunday, May 15, 2022

Salesforce: Case Team Report

We shared Account Team Report and Opportunity Team Report a few years back, in this blog, we will look into the Case Team Report.

Similar to the Account and Opportunity report, you can filter Case based on the owner too, here are the options:

Let us go through each of them:

My cases: ONLY cases that are assigned to you.

All cases: ALL cases that you have access to, no matter who is the case owner.

User owned cases: ALL cases that you have access to, and are owned by a user, not a queue.

Queue owned cases: ALL cases that you have access to, and are owned by a queue, not a user.

My case team's cases: These are cases where you are added as a Case Team member.

My role-based team's cases: These are cases owned by you and your subordinates in the role hierarchy, but not owned by a queue, although you or your subordinate is a member of the queue.


Friday, May 13, 2022

Salesforce: File and Content Report Type / Query All Files

File and Content report type produce a report of Salesforce CRM Content and Files in Salesforce. When you create a new report using the File and Content report type, a lot of fields will be added by default.

When you run the report with standard fields, you may see no result, this is because the data, instead of showing as blank, this report type will just remove the rows altogether, such as Downloaded By, if the files have never been downloaded, the files will not appear as a row in the report. The same behavior for fields such as Link Name, if the link for the file has not been created.

On the other hand, if the file has been uploaded in multiple versions, the same file name will appear in a few rows based on the number of file versions uploaded, if you add the File Version field to the report.

This report will show files that you owned and shared with you, even if you are a system admin or user with View All Data permission, and selects Show Me All Files in the filter.

Query All Files

There is permission called Query All Files, this permission is not enabled by default for the System admin profile, it allows users with View All Data permission can query ContentDocument and ContentVersion and retrieve all files in the org, including files in non-member libraries and files in the unlisted groups, and not just files that are owned or shared to the user. 

However, this permission does not impact to result of the report using the File and Content report type, even if the user has Query All Files permission.


Monday, April 25, 2022

Salesforce: Send List Email (2)

This is to continue Salesforce: Send List Email blog written 19 months ago.

As per the EmailMessage blog, every email sent out will also create Task, the same for emails send from Send List Email in list view, it will create a Task too, but a bit different. Let us query the Tasks created.

From the above screenshot:
1st row is manual sending email from the Contact record
2nd row is using send list email from the Contact list view

1st row - Subject start with Email:
2nd row - Subject start with List Email:

Related To (or WhatId in API)
1st row - WhatId is blank
2nd row - WhatId starts with 0XB

Comments (or Description in API)
1st row - contain: To, CC, BCC, Subject, and Body of email
2nd row - blank

If you do not see the Send List Email button in the Lead or Contact list view, check this:

1. Make sure Send List Email is selected in the List View Button Layout in Contact or Lead object setup, it used to be called Search layouts for Salesforce Classic prior Summer '22 release. 

2. Make sure the users have Allow sending of List Emails permission. However, if you remove Mass Email permission, this permission will be auto removed too, and re-enable back the Mass Email permission will not auto-enable Allow sending of List Emails permission.

Thursday, April 21, 2022

Salesforce: Case Lifecycle report type

Case Lifecycle report is a standard Salesforce report type provided, but probably the one that is not so famous, not many people use, a bit confusing and very less official article about this.

What is the Case Lifecycle report?
Run case lifecycle reports to view the results of the Range field, which indicates the length of time since the case last changed status or owner. Each time the status or owner changes, the counter begins again at zero --- copy from Using Custom Report Types to Report on Support Activity.

Here are a few in the trailblazer community throughout the years:

Ideas related to this in IdeaExchange:
From all the above documents and conversations that can be found using Google search, nothing really explained how Case Lifecycle report works?

What is the Case Lifecycle report?
It is a standard report type that tracks the duration of a case when the case owner or status is changed.

There are 5 fields available in the Case Lifecycle report:
  • Case History Owner
  • Case History Status
  • Case History Last Modified By
  • Case History Last Modified Date
  • Duration

Let us walk through this report type with samples:

1. New Case creation
Remember, every time the case owner or status is changed, a new entry will be created for the case lifecycle report, so creating a new case will generate a new entry for the lifecycle report.

The below report is using minutes unit.

After 2 minutes, let us refresh the report, both the duration and age increase by 2 minutes

2. Change the Case owner
As mentioned earlier, changing the case owner will create a new entry to the lifecycle report.

- 1st row: duration at 7.9 minutes, the case owned by 'Johan Dev501' for 7.9 minutes, before hand over to the new owner.
- 2nd row: duration at 1.083 minutes, the latest duration since it was assigned to a new owner and it has been held for 1.083 minutes  

Let us refresh the report without changing anything on the case

- 1st row: no change, this is completed, the duration will no longer be changed
- 2nd row: now the duration change to 5.1 seconds

3. Change the Case owner
Changing the case owner will create a new entry to the lifecycle report.

- 2nd row: this is completed, the duration will no longer be changed
- 3rd row: the duration start for the new owner

4. Change Status
Changing the case status will create a new entry to the lifecycle report.

- Case History Last Modified By, show who change the status or owner
- Case History Last Modified Date, show when does the change happen 
- Case History Owner, show the owner when the entry is created 
- Case History Status, show the status when the entry is created
- Notice that Age keeps counting from the beginning, this is because the case is still open

5. Closing the case
Changing the case status will create a new entry to the lifecycle report.

Let us refresh the report without changing anything on the case

- Notice that the age stops at 26 minutes because the case has been closed
- But, the duration on the last entry keep counting, no matter the case status  

6. Re-open the case
Changing the case status will create a new entry to the lifecycle report.

- Age recalculate again from the beginning
- Duration of the initial Closed entry stopped at 4.033 minutes

7. Re-closing the case

After a few minutes or hours or days, refreshing the report will not change anything, except the duration for the last items, which will keep counting.

Can this report type be customized? 
Such as using a custom field instead of standard status and owner? unfortunately not, see the above list of idea exchanges. Can we only track the owner only or status only? unfortunately also not.

Can we get the data with SOQL? 
The closest we can get is by query to the CaseHistory object, but without duration and age. 
SELECT Id,CaseId,CreatedBy.Name,CreatedDate,Field,OldValue,NewValue FROM CaseHistory WHERE CaseId = '5000I00002FBKqF' AND Field IN ('Owner','Status') ORDER BY CreatedDate

Note: owner will show double, as Id and Name, so you just need to get one as needed.

If you have anything to add for the Case Lifecycle report type, please comment on this blog.

Tuesday, April 19, 2022

Salesforce: Getting Started with Salesforce Flow

Dreamforce 21 announced that Flow is the future of Salesforce, it will replace Workflow and Process Builder and is also friendly for admin without coding background. The flow itself is not something new but it has been added with many features recently, at least can do all workflow and process builder can do.

Here is the business use case: ABC Inc. would like to have a simple "quick create" form that allows users to easily give feedback or idea to enhance the system, from anywhere in Salesforce Lightning, does anyone still in Classic ⚡

Solution: Screen Flow

1. Create a custom object to capture the data

  • Create all the fields needed
  • Add a custom tab if needed
  • Enable the object for reporting, allow activities, track field changes, and allow search
  • Set the object permission by profiles, and fields level security 

2. Create screen flow

Add 3 elements in the flow: screen for form, create records, screen for thank you 

Main screen element, nothing fancy here, just add the input type and set it as required, for this example, type values are derived from a picklist field and set as Picklist Choice Set. You can add emojis to make it fun 😊

Picklist Choice Set gets values from the Type__c custom field.

Create Records element, this is pretty simple, just map each screen component as a value to a field of the object, and remember to select "Use separate resources, and literal values".

** You can add a fault path, in case the system is unable to save the record, such as because of validation rules or etc.

Thank You screen element, the purpose of this screen is simply to tell the user that the data has been captured. In this sample, I will hide the button at the footer.

You can try to debug or run the flow to make sure it will run correctly.

3. Add Flow to Utility Bar

Few items to note here:
  • The flow must be activated
  • Only Screen Flow will be available 
  • Users must have "Flow User" enabled in the user detail, otherwise, the user will not see the flow in the utility item

There is really nothing to be configured here, it is just standard items such as label, icon, panel width, panel height, and of course, the most important is to select the correct flow.

Done and the form looks pretty nice and fits into the utility bar.

Wednesday, April 13, 2022

Salesforce: Subscribe Report

Almost 5 years ago, I wrote about Subscribe report in Classic and Lightning, but now in 2022, everyone has migrated to Classic (or at least 90%). 

Here are a few items related to Subscribe report:

1. As of Spring 22, the max report to subscribe is 7

Check out this idea exchange, but if someone adds you as a recipient for a report subscription, it does not count for the limit.

2. Add other users to receive the report

The report must be not stored in the Private Reports folder, and the recipient must have access to the report folder.

3. Find all reports that you subscribed to

All reports that you subscribed to will have a checkmark in the Subscribed column (Pro tip: you can sort by this column too). But, if you are added by someone, there will be no checkmark on that column. 

You will see in the email user who adds you subscribe to the report.

4. You can set the Run Report As someone else or as yourself

If there are many people added as recipients, the report will run as a user, whether the one subscribes to the report or a recipient. But what does this mean? 

  • Everyone will receive the same report result
  • No matter each user's access to the report, it will be based on the visibility of the user set as the run report
  • If the report is filtered with date, the data will be based on the time zone of the running user set in SFDC, e.g. created date = 1-Jan-2022 and running user timezone is Singapore, the user sees 200 records when running the report, the same number of records will be delivered in email for all users, but other users where timezone set to EST run the same report, that user may see 201 or 199 records, this is because based on that user timezone. SOQL sample: SELECT Id, Name, CreatedDate FROM Opportunity WHERE CreatedDate >= 2022-04-12T00:00:00+08:00 AND CreatedDate < 2022-04-13T00:00:00+08:00 ORDER BY CreatedDate

Bonus: Standard fields with type = date/time, such as Created Date and Last Modified Date will only show the date portion without time information, but if you sort the report based on this field (ascending or descending), the sort order will include time information, even all date is the same.

Thursday, March 31, 2022

Remove Personal Information when sharing Office documents

Microsoft Office by default documents stores your personal information when creating or editing an office document. You can remove that information like comments, version history, and metadata containing personal information like document author, and date of creation, before sharing the file with other people externally. The Document Inspector feature in Word, Excel, PowerPoint, or Visio can help you find and remove hidden data and personal information in documents that you plan to share.

For Windows

  1. In an open Word document, click File then Info.
  2. Select Check for Issues and choose Inspect Document to control what type of content you want to be flagged.
  3. To start your search, click Inspect and then Remove all the information you want to be edited from your content.

Repeat the process by clicking the Reinspect and Remove all buttons until your document is clean. Click the Close button to save your changes

Wednesday, March 30, 2022

Streamlined Salesforce Data Collection

Salesforce out of the box offers web-to-lead and web-to-case, where we can create a simple form and publish it to our website, our customers or prospects can submit their questions or issues, and the data will be stored in Salesforce. However, if you need a custom form for a specific purpose, such as order, request, etc. that links to Salesforce, you need to get developers to build sites, which is not simple, takes time, and also takes a big effort to support and maintain.

In this blog, I would like to introduce FormAssembly, a platform that supports large and complex data collection workflows that require advanced Salesforce integration. You'll benefit from FormAssembly’s powerful, all-in-one data collection platform and visual workflow builder that doesn’t need a team of developers to build and maintain the form as your business changes. Simply use FormAssembly builder (with point and click, instead of code) to make the changes and publish the form to your website.

FormAssembly provides a secure, versatile, and scalable solution for solving data collection challenges for organizations across industries. With a user-friendly platform and intuitive builders, non-technical users can easily create web forms and data collection workflows for any use case no matter the complexity or security requirements. With a simple connector and integration setup, data flows seamlessly from people to web forms, then directly and cleanly into Salesforce.

A FormAssembly Enterprise or Compliance Cloud plan gives users access to several key features and benefits, including: 
  • Advanced Salesforce integration: Create or update data for standard and custom objects in Salesforce, prefill forms, use Dynamic Picklists, and more.
  • High security and compliance: Protect data with Section 508 compliance, spam protection, PCI DSS Level 1 Certification, encryption at rest, and TLS 1.2 secure forms.
  • Respondent authentication: Allow only authenticated users of LDAP, SAML, CAS, or Salesforce Communities (now Salesforce Experience Cloud).
  • No-code web form builder and workflow builder: Easily create custom web forms and build out complex data collection workflows without code or technical support.
  • 24/5 support by live chat, email, or phone: Receive friendly, helpful support from a dedicated and knowledgeable team (phone is for Priority Support customers only).

The Form Builder
FormAssembly’s web form builder provides a user-friendly, drag-and-drop interface that lets users easily create custom Salesforce-connected forms, edit themes, add images, and more.

1. Once logged into your FormAssembly account, you can start building a web form by clicking on “+ New Form” and choosing a blank form, template, Salesforce import, or clone of another FormAssembly form.

2. All forms are customizable, from text (drop-downs, radio buttons, text area, etc.) to images and themes. You’ll have the ability to add your organization’s logo and brand colors. 

3. Setting a form to active or archived status, setting a processing quota, and creating custom messages can all be achieved in the form processing options section.

4. You can also customize a “thank you” message for a web form submission as well as set a URL for a page redirect.

Salesforce Integration
When it comes to Salesforce, FormAssembly makes this integration seamless and streamlined, while providing several powerful connectors. This powerful Salesforce integration lets users update and create records for standard or custom objects, prefill data, leverage multiple objects, and more. 

FormAssembly’s level of form customization and advanced integration is in most cases even better than Salesforce’s native options. This is why this external data collection platform is the go-to solution for building web-to-anything forms that send data seamlessly to your connected Salesforce Org.

Salesforce Integration Benefits
Leveraging FormAssembly’s platform and integrations offers several advantages that help solve common challenges of Salesforce data collection:
  • Eliminate manual work: Create or update records with Salesforce forms that work with any standard or custom object and even objects from AppExchange apps.
  • Enable form prefilling: Data pre-loads from Salesforce into a FormAssembly form, making it easy for customers to update information, which syncs back to your CRM.
  • Seamless file upload: Customers can easily attach files that upload to a specific Salesforce record when a form is submitted.
  • Complete live lookups: FormAssembly’s Salesforce Dynamic Picklist tool lets users complete live lookups to Salesforce from specific fields in a form.
  • Streamline processes: Embed and easily load FormAssembly forms in Salesforce Communities using the FormAssembly Lightning component.
  • Build forms in minutes: Easily create FormAssembly forms with your own Salesforce objects using FormAssembly’s Salesforce Import too.

Connecting FormAssembly and Salesforce
1. Within the connector section, you have the option to choose between form opened, form submitted, and after form submitted connectors. 

2. Once you choose the Salesforce Connector, click the “Configure button” to connect to Salesforce. Enter Salesforce credentials and allow access to FormAssembly and click the save button.

Learn More
To learn more about FormAssembly for Salesforce, visit their website or find them on AppExchange.

Monday, February 28, 2022

Salesforce: Rename Related List for Custom Field

When you add a lookup field in an object to another object, the lookup object will become the parent object, this is applied for both Standard and Custom objects.

In this example: I have a custom object called Region with a lookup field to Account.

And if we go to the Account page layout, we can add Region as a related list.

How to change the "Regions" label to something else? Let's say it should be called "Supported Regions".

Let's go back to the Regions object, open that field and change the "Related List Label"

Refresh the account page and now should show "Supported Region"

Bonus requirement: the New button should not be in the related list.

  • Go to Account page layout
  • Click Related Lists on the left panel, scroll down to the related list object
  • Click properties icon, click Buttons, then untick New checkbox

Monday, February 21, 2022

Salesforce: File Privacy on Records

By default, when you upload files from a record Related List, the file will be visible to all users who have access to the record. But, this can be changed by adding a field called File Privacy on Records in the Content Version page layout.

Once the file owner selects that checkbox, the file will not visible to other users, unless manually shared, but users with View All Data permission will be able to see those files.

Step to update the checkbox:
1. Upload the files from Related List, you can upload multiple files in one go

2. Open each file and click the arrow next to View File Details, then select Edit File Details

3. Change File Privacy on Records from Visible to Anyone with Record Access to Private on Records

  • The file visibility does not roll up to users in the higher role hierarchy, so when the file owner sets this as Private on Records, the manager will not see the file, unless shared manually.
  • The file visibility does not depend on the related record owner but depends on the file owner.
  • If you query on ContentDocument object (prefix: 069), this is stored in SharingPrivacy field, P for Private on Records, and N for the default visible to anyone

However, if you click the Edit File Details link from the Files dropdown in a related list, you will not see that checkbox, as mentioned in this article. I have created an idea exchange to vote.


Salesforce: Opportunity Split Query

This is the continuation of the previous blog Forecast and Opportunity Split, in this blog, we will share more on Opportunity Split records.

SELECT Id, OpportunityId, SplitOwnerId, SplitOwner.Name, CurrencyIsoCode, SplitAmount, Opportunity.CloseDate FROM OpportunitySplit WHERE SplitAmount <> 0 AND Opportunity.ForecastCategory = 'Closed' AND SplitTypeId = '1492v0000008opLAAQ' AND Opportunity.CloseDate > 2019-06-30 AND Opportunity.CloseDate < 2019-10-01 ORDER BY Opportunity.CloseDate DESC 

  • The number of rows here is based on the number of split records that fit the criteria, so there is no summarized calculation as in ForecastingItem
  • The currency can be different based on the currency defined for the opportunity
  • The Split Amount is not roll-up to the manager.

Wednesday, February 16, 2022

Salesforce: Retrieve all fields from an Object

Config Workbook

This tool is not free, but you can do a trial for a few analyses, and need to install it from AppExchange.The details provided are quite comprehensive, but missing a few important pieces of information.

Click CWB Tool tab >> Reports tab >> Full Object and Fields Details Only.

This will produce an Excel file with the following columns:
  • Field Name
  • API Name
  • Data Type, missing information if the field is a Formula field
  • Picklist Value, for Picklist field only, and not for Multi-Select Picklist  
  • Length, for Text and Long Text Area
  • Visible Lines, for Long Text Area
  • Default Value
  • Mandatory
  • External ID
  • Unique
  • Case Sensitive
  • Description
  • Help Text
  • Relationship, for Lookup: Related To (object name) and Child Relationship Name
  • Number, with Precision and Scale, for the length of Currency, Number, Percent field
  • Track Feed History
  • Track History

It also missing Controlling Field, Modified By, Modified Date/Time information.

ColumnCopy extension for Chrome browser

This needs to be performed in Classic (not Lightning) with Chrome (or Edge or Opera) browser, you just need to add ColumnCopy to your browser. Once the extension is added in your Chrome browser, go to the object's field page, right-click on the field table >> ColumnCopy >> Copy entire table >> paste into Excel.

You will get all information presented in the fields table:

  • Field Label
  • API Name
  • Installed Package
  • Data Type
  • Indexed
  • Controlling Field
  • Modified By (including Modified Date/Time)
  • Track History

* Indexed and Track History will not copy by ColumnCopy because they are checkboxes.

This is good enough if you just need to get basic field information quickly.

Salesforce Schema Lister

This tool uses the Salesforce Metadata API to build a list of objects, fields, and field attributes from within your Salesforce Org, this tool is part of the Toolkit for Salesforce, and please note this is a 3rd party tool, not by Salesforce.

The tool will scan all objects, instead of asking with objects that you would like to work with. This will work well if you are just starting a new org, but when working in a big org. (with hundreds of custom objects), it may take a few minutes or longer.

Once completed, you have the option to export the data into an Excel file, as a single tab or multi-tab (one object per tab).

What do you get in the Excel file?

  • Field Label
  • API Name
  • Type. including length (all fields), picklist values (including multi-select picklist)
  • Help Text
  • Formula, this is the raw formula for the field


Query from FieldDefinition object.

Sample: SELECT DurableId, QualifiedApiName, Label, DataType, ValueTypeId, LastModifiedDate, PublisherId, Length, Precision, Scale, EntityDefinitionId, RelationshipName, ReferenceTo, Description FROM FieldDefinition WHERE EntityDefinition.QualifiedApiName = 'Account' 

You must have EntityDefinitionId or DurableId added as a filter, EntityDefinitionId refers to an object and DurableId for a field.

A few interesting fields from the query result:
  • DurableId, return ObjectName.QualifiedApiName for standard object and ObjectName.00Nxxx for custom fields
  • ValueTypeId, return id, string, boolean, integer, double, etc.
  • PublisherId, return System for standard objects, or Package Prefix name for installed packages

Full documentation here

Note: Because this is a query based on the user permission, the query result will be based on field (and object for lookup field) visibility for the login user. 
You don't option to retrieve Help Text here.

Custom Report Type

Create a custom report type with primary object = Entity Definitions with or without records from Field Definitions.

You can filter Durable ID (of Entity Definitions) to an object name to pull just fields of that object. Sample report:

Note Same as SOQL, the report runs based on the user permission, so the result will be based on field (and object for lookup field) visibility by the report running user.
You don't option to get Help Text using this method.

Salesforce DevTools

This is another Google Chrome browser extension that is mainly focused on Salesforce admin/dev productivity. You can get it and include more information from this site.

From the Salesforce DevTools panel, select the object >> Export >> Object Fields Definition 

You will get an Excel file with the following columns:
  • Field Label
  • API Name
  • Data Type
  • Length
  • Field Type, this is standard or custom field information
  • Required
  • Picklist Values
  • Formula
  • Extend ID
  • Help Text
Note Same as the previous method, the Excel file result will be based on field (and object for lookup field) visibility by the report running user.

So, which methods work best for you? I would say it depends on what you want to achieve, do you need extra information to retrieve, can you install an app into the org., can you switch back to Classic, how complex is the org., do you have visibility to all fields, etc.

Page-level ad