#Get createdDate of fields

[sfdcfanboy]

A Tip A Day #39 - Easily Find Newly Created Fields in Salesforce

A Tip A Day #39 – Easily Find Newly Created Fields in Salesforce

This post is a part of the daily blog series  A Tip A Day, daily dosage of learning! Day #39 – Easily Find Newly Created Fields Open your Developer Console, click on the Query Editor tab, click the “Use Tooling API” checkbox, then query away! SELECT DeveloperName, TableEnumOrId FROM CustomField WHERE CreatedDate = TODAY And the result is here. DeveloperName is the field name. TableEnumOrId is…

[tak4hir0]

This is the second post in a two-part series presenting how to work with Developer Sandboxes and the Salesforce CLI. Over the course of this series, we’ll cover: Part 1: Sandbox Management Manage sandboxes with the Salesforce CLI Clone sandboxes instead of creating new ones Initialize sandboxes with a custom Apex class (Beta) Secure your production Org with sandbox-only users Import sample data Refresh outdated sandboxes Delete unused sandboxes Part 2: Metadata Management Prefer the source format over the metadata format Retrieve metadata with the Org Browser Retrieve metadata that do not support wildcards in package.xml (reports, dashboards…) Remove metadata that’s no longer needed Leverage Custom Metadata to pass configuration from Production to your Developer Sandbox Test metadata deployments In this final part, we’ll focus on metadata management for Developer sandboxes, but these best practices can be expanded to any non-source tracked org such as Developer Edition orgs or production orgs. We’ll cover various tools and techniques that you can use to enhance your development workflows. Prefer the source format over the metadata format At a high level, Salesforce orgs are made of three things: The Platform, metadata and data: The Platform is the base infrastructure, tools and services that are provided by Salesforce. Metadata is everything that lets you customize the Platform for your business needs (layouts, source code, permissions, custom objects, fields and so on). Data are the records that are stored in the org. Metadata is what Salesforce developers work on. There are two project formats that developers can work with: Metadata format or source format. The metadata format is the legacy format that was introduced to represent metadata on a filesystem. This format uses large XML files that are designed to be machine readable. This format is used by the Metadata API. With the introduction of Salesforce DX, we introduced the source format. This format splits the metadata into smaller files and intuitive subdirectories. This makes it easier to read and manage in version control. With source format, you can easily collaborate on a project with multiple developers, merge changes with fewer conflicts and review the history of your project. Here’s an example of the same metadata represented in both formats:   As a general rule, the source format is preferable over the metadata format but there are a few common misconceptions around it so let’s clarify a few things before moving on: Source format works with any orgs, it’s not limited to Scratch orgs. What is specific to source-tracked orgs are certain CLI commands such as force:source:pull or force:source:push. There are equivalent commands for metadata deployment to non source-tracked orgs like sandboxes or production orgs ( force:source:retrieve and force:source:deploy for example).   Projects aren’t locked into a specific format: the metadata and source formats can be converted from one to the other. There are two CLI commands that let you convert between the two, respectively force:source:convert and force:mdapi:convert. All CLI commands starting with force:source work with source format and all force:mdapi commands work in metadata format. Always prefer the source format for your projects as it facilitates collaboration and versioning. Retrieve metadata with the Org Browser If you use VSCode, you can use the Org Browser (cloud shaped icon on the left toolbar) to explore the org’s metadata and download selected artifacts in source format with just a few clicks. This feature is amazingly simple and convenient.   Retrieve metadata that doesn’t support wildcards in package.xml When retrieving artifacts with the CLI, you can use a package.xml file to specify the resources that are retrieved. This is generally pretty easy with the use of wildcards (the * symbol). However, some artifacts like reports, dashboards, documents and email templates can’t be retrieved with wildcards because they’re located in folders. You can either retrieve those artifacts by using the Org Browser (see above) or a combination of CLI commands. Here’s an example of how you can retrieve reports in source format using the CLI: List the metadata folder artifacts (ReportFolder type for Report in this example): sfdx force:mdapi:listmetadata -m ReportFolder [ { createdById: '00558000000yFyDAAU', createdByName: 'Philippe Ozil', createdDate: '2020-02-26T12:39:18.000Z', fileName: 'reports/SomeFolder', fullName: 'SomeFolder', id: '00l4H000000eerVQAQ', lastModifiedById: '00558000000yFyDAAU', lastModifiedByName: 'Philippe Ozil', lastModifiedDate: '2020-02-26T12:40:51.000Z', manageableState: 'unmanaged', type: 'ReportFolder' }, ... ] List artifacts for a given folder (in the output of this example we see that there’s a My_User_Report report in the SomeFolder folder): sfdx force:mdapi:listmetadata -m Report --folder SomeFolder { createdById: '00558000000yFyDAAU', createdByName: 'Philippe Ozil', createdDate: '2020-02-26T12:40:08.000Z', fileName: 'reports/SomeFolder/My_User_Report.report', fullName: 'SomeFolder/My_User_Report', id: '00O4H000004U7glUAC', lastModifiedById: '00558000000yFyDAAU', lastModifiedByName: 'Philippe Ozil', lastModifiedDate: '2020-02-26T12:40:08.000Z', manageableState: 'unmanaged', type: 'Report' } Add the artifacts to your package.xml file. You’ll have to specify the artifact fullName (a combination of folder name and artifact name) that you retrieved in the previous command: Retrieve the artifacts in source format with the CLI: sfdx force:source:retrieve -x package.xml This is a rather manual process when doing an initial import, but note that these operations can be chained and automated with custom CLI plugins or scripts. Remove metadata that’s no longer needed Metadata or source deployment only adds new artifacts by default. It’s your responsibility as a developer to do some cleanup and remove unneeded metadata to avoid cluttering your org. If you use VS Code, you can simply right click on a file in the Explorer and select SFDX: Delete from Project and Org.     You can also delete metadata with CLI commands: # Remove a DeleteMe Apex class and a Hello Aura component sfdx force:source:delete -m ApexClass:DeleteMe,AuraDefinitionBundle:Hello # Remove all metadata (in source format) from the 'deleteMeFolder' directory sfdx force:source:delete -p deleteMeFolder Leverage custom metadata types to pass configuration from production to your Developer sandbox While Developer sandboxes are created with no initial data, you can leverage custom metadata types to pass configuration from your production org. Because custom metadata records are no ordinary records, they aren’t considered “data”. This means that they get copied over from the production org to the sandbox. With that special rule in mind, you can set up the following process: Admin configures custom metadata in production Admin creates a new sandbox Metadata and custom metadata records are copied over to the sandbox Apex sandbox initialization class executes automatically Class reads the custom metadata records Class configures the sandbox Here’s a sample project that illustrates how custom metadata lets the production admin configure new sandbox users. This project contains a sandbox initialization Apex class that relies on a custom metadata type to dynamically create sandbox users with specific names and emails. Test metadata deployments You can setup CI to test metadata deployments without modifying the target org. This type of test saves you precious time and eliminates the risk of errors because you do not need to cleanup your sandbox between jobs. Deployment tests are made possible thanks to the checkOnly flag in the metadata deploy command. This option is available in the Salesforce CLI force:source:deploy command with the -c or --checkonly flag. Here’s an example of how you can test the deployment of your local source (force-app folder) and run tests on your sandbox with the CLI: sfdx force:source:deploy -p force-app -c -l RunLocalTests Closing words This concludes our Salesforce CLI Best Practices for Developer Sandboxes series. In this final post we’ve covered CLI best practices for managing metadata in non-source tracked orgs such as Sandboxes, Developer Edition orgs or production orgs. We covered the benefits of the source format, how to retrieve or remove metadata, how to pass configuration from a production org to a Developer sandbox and how to test metadata deployments. We’ll leave you with a CLI command cheatsheet that summarizes the commands we’ve covered in the series:   Try them on your orgs and keep an eye out for the Sandbox source-tracking Beta in Summer ’20. Resources Sandbox management best practices for administrators Salesforce DX Developer Guide Sandboxes Develop Against Any Org Modern Tooling for the Sandbox Development Workflow About the author Philippe Ozil is a Principal Developer Evangelist at Salesforce where he focuses on the Salesforce Platform. He writes technical content and speaks frequently at conferences. He is a full stack developer and enjoys working on robotics and VR projects. Follow him on Twitter @PhilippeOzil or check his GitHub projects @pozil.

[sagar-jaybhay]

Constraints In SQL By Sagar Jaybhay

New Post has been published on https://is.gd/zFLU3k

Constraints In SQL By Sagar Jaybhay

Constraints In SQL

Constraints are the rules which are enforced on columns of the table in database.  They specifically used to limit or restrict that data goes into a column. Constraints ensure the reliability and accuracy of data.

We can apply constraints on column level or table level. In this column level constraints are applied on one column at a time but when you use table-level constraints these are applied on all columns of that table.

Commonly used Column constraints are below

Not Null

Default

Unique

Primary Key

Foreign key

Check

Index

Commonly used table-level constraints

Primary key

Foreign key

Unique

Check

In case if you insert any data that violets the constraints then the operation is aborted.

Constraints can be applied at the time of table creation by using Create Table syntax and another is with Alter table statement.

Default Constraint

This constraint is used to set or specify a default value for that column if any value doesn’t provide. This means it is used to insert a default value into a column. The default value is set for all records if any value doesn’t provide including Null.

To alter an existing table or add default constraint to a column using Alter table syntax.

Alter table table_name Add constraint constraint_name Default default_value for Column_name

To add a new column to an existing table with a default value

Alter table table_name Add column_name data_type (Null | Not Null) Constraint constraint_name default default_value

Drop Constraint                

Alter table table_name Drop constraint constraint_name

alter table Person add constraint df_value default 3 for [genederID]

Check Constraint

The check constraint is used to limit the range of the values which are entered for a specific column. In our case, a person’s table is already created. So we can add a new column age with check constraint by using alter table syntax.

alter table person add age int constraint chk_age check(age>0 and age<200)

Now if I going to insert negative value in that person column by using below query

insert into Person values(7,'rr1','rr1@rr1.com',Null,-9)

it will throw below error Msg 547, Level 16, State 0, Line 50 The INSERT statement conflicted with the CHECK constraint “chk_age”. The conflict occurred in database “temp”, table “dbo.Person”, column ‘age’. The statement has been terminated. The only flaw of this if you pass the null value it will be inserted any way and not throw any error.

insert into Person values(9,'rr2','rr2@rr2.com',Null,NULL)

this query works perfectly.

How Check constraint works?

When we add check constraint we add some condition in parenthesis. It is actually Boolean Expression when we pass value it will first pass to that expression and the expression returns the value.

If it returns true value then check constraint allows the value otherwise it doesn’t allow that value. So what happens when we pass the null value? In this person’s age case when we pass NULL value it passes to expression and expression is evaluated this as Unknown so for that reason it allows null value.

Drop a Check Constraint Alter table person Drop constraint chek_constraint_name

Unique Key Constraint

This unique key constraint is used to enforce the uniqueness of a column i.e column shouldn’t allow any duplicate value. You can add unique key constraints by using a designer or by using a query.

 Below is the syntax for add unique constraint by using alter table syntax.

Alter table table_name Add constraint constraint_name unique(column_name);

If you see both primary key and unique keys are used to enforce the uniqueness of column so the question in your mind when to use what?

One table has only one primary key and if you want to add uniqueness for more than one column you can use unique key constraints.

What is the difference between the Unique key and Primary Key?

A table has only one primary key but a table can have more than one unique key

The primary key doesn’t allow null values where a unique key allows only one null value.

alter table person add constraint unique_name_key Unique([email]);

What if you enter the same value again for a unique constraint?

insert into Person values(10,'rr2','rr2@rr2.com',Null,NULL)

I use the above query to insert value I only change a primary key value and all record is present previously in a table at 9’Th location but when I ran this query I get the following result

Msg 2627, Level 14, State 1, Line 87 Violation of UNIQUE KEY constraint ‘unique_name_key’. Cannot insert duplicate key in object ‘dbo.Person’. The duplicate key value is (rr2@rr2.com). The statement has been terminated.

How to drop unique key constraint?

Alter table table_name Drop constraint constraint_name;

alter table person drop constraint unique_name_key

Not Null Constraint

By default, a column can contain null values but you want to restrict the column that not to allow NULL values then this constraint is used.

This not null constraint enforces a rule on a column that always contains a value.

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );

Not Null constraint by using Alter table syntax

ALTER TABLE Persons MODIFY Age int NOT NULL;

Index Constraint

It is used to access the data very fast means for faster retrieval of data index is created. An index can be created on a single column or multiple columns. When you create an index it will create or assign rowed for each row.

Indexes have a good performance on large databases when it comes to retrieval of data but performance is low when insertion.

create table Person(ID int not null Primary key,name varchar(100),email varchar(100),genederID int)

CREATE INDEX index_name ON table_name ( column1, column2.....);

CREATE INDEX person_tabel_index ON person (id,name);

How to drop an Index?

ALTER TABLE table_name DROP INDEX index_name;

But you will get the following error

Msg 10785, Level 16, State 2, Line 97 The operation ‘ALTER TABLE DROP INDEX’ is supported only with memory-optimized tables. Msg 1750, Level 16, State 0, Line 97 Could not create constraint or index. See previous errors.

To avoid this error your table needs to memory-optimized and for that when you create a table use this syntax to create table memory optimize. But for that, your database needs to memory-optimized so use below command for that in below query temp is my database name.

ALTER DATABASE temp ADD FILEGROUP [TestDBSampleDB_mod_fg] CONTAINS MEMORY_OPTIMIZED_DATA; After that use below command ALTER DATABASE temp ADD FILE (NAME='temp_mod_dir', FILENAME='D:\timepass\TestDB_mod_dir') TO FILEGROUP [TestDBSampleDB_mod_fg]; --Then you use this create table command to create memory optimized table. CREATE TABLE userSession ( SessionId int not null, UserId int not null, CreatedDate datetime2 not null, ShoppingCartId int index ix_UserId nonclustered hash (UserId) with (bucket_count=400000) ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) ;

Primary key Constraint

A primary key is a field in the table which uniquely identifies the row in a table. The primary key contains unique values. The primary key doesn’t have null values.

A primary key is one per table but it can contain more than one column and this called a Composite key. Create primary key at the time of table creation

CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );

Create a primary key using the alter table

ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

When you use alter table syntax you need to ensure that the column which you provide is Not Null.

Foreign Key Constraint

The foreign key constraint is used to join 2 tables together.

It is a key which might have a combination of one or more column or fields in one table that refers to Primary Key in another table.

The table which contains foreign key is called the child table and the table containing the candidate key is called the referenced or parent table.

The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); --Foreign key using alter table ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); --Drop a Foreign Key constraint ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;