Datatype is very important for designing the application and storing correct data in the database.A data type, in programming, is a classification that specifies which type of value a variable has and what type of mathematical, relational or logical operations can be applied to it without causing an error.Each data type will have different format we can use these format based on our business logic.
Single Line of Text –
- Single line of text is a string attribute.The length for string can be defined from 1 to 4000 characters.This datatype has different format option.
Format:
- Email – The text provides a mailto link to open the user’s email application.
- Text – This option simply displays text.
- Text Area – This format option can be used to display multiple lines of text. But with a limit of 4000 characters, the Multiple Lines of Text field is a better choice if large amounts of text are expected.
- URL – The text provides a hyperlink to open the page specified. Any text that does not begin with a valid protocol will have “http://” prepended to it.
Only HTTP, HTTPS, FTP , FTPS, ONENOTE and TEL protocols are allowed in this field. - Ticker Symbol – For most languages, the text will be enabled as a link to open the MSN Money website to show details about the stock price represented by the ticker symbol.
- Phone – In the web application, fields will be click-enabled to initiate calls using either Skype or Lync if a client for either is installed on your computer. The telephony provider choice is at the bottom of the General tab of System Settings.
Option Set –
This data type is drop down or pick list field.A user is allowed to select only one option from given values.A blank values is also acceptable and default value can also be defined.Each options has value and label.
There are two types of option set :
- Global – Created in solution and can be refered to any local optionSet.
- Local – Specify to entity only.
Two Options –
Two option is boolean value.Yes/No is default option, can be renamed as needs and can set default value to the field.This datatype will have O(No) and 1(Yes).Fields can be displayed as pick list, radio buttons or check box this can be customized after field is placed in the form.
Image –
This datatype stores image of the entity.Each entity that supports images can have one image field. When an entity has an image field, it can be configured to display the image for the record in the application.There are 24 system entities where an image field is
available.
Supported Format:
- BMP, GIF, JPG, JPEG,PNG, TIFF, TIF
Whole Number –
This datatype stores only integer value from -2,147,483,648 to 2,147,483,647,the range can be set according to the business logic.This datatype has different format option.
Formats:
- None: This is the default integer format.
- Duration: This option provides a drop-down field with value options for minutes, hours, and days; this option should be only used when we want to represent time: durationTime zone.
- Language: This option can be used to select language options based on the language packs installed.
- Time Zone: This option displays a select list of time zones such as (GMT-12:00) International Date Line West and (GMT-08:00) Pacific Time (US & Canada). Each of these zones is stored as a number.
Floating Point Number –
This datatype is used store fraction value from -100000000000 to +100000000000, range can be changed with decimal points upto 5.Floating point numbers store an extremely close approximation of the value.Use floating point numbers when you store data that represents fractions or values that you will typically query comparing to another value using greater than or less than operators.
Decimal Number –
This datatype is used store decimal numbers from -100000000000 to +100000000000, range can be changed with decimal points upto 10.Decimal numbers are stored in the database exactly as specified.Use decimals when you need to provide reports that require very accurate calculations, or if you typically use queries that look for values that are equal or not equal to another value.
Currency –
This datatype is used to store money value from -922,337,203,685,477 to 922,337,203,685,477 with decimal points upto 4. Based on your currency settings, the correct currency symbol is also displayed such as the dollar sign or euro symbol.
Multiple Lines of Text –
Multiple Lines of Text is a string attribute. It is similar to Single Line of Text but can store more characters upto 1,048,576.You can set a maximum length to less than this. When you add this field to the form you can specify the size of the field.
Date and Time –
This datatype is used to store date and time data.You can choose to have both the Date and Time displayed or only the Date portion.Data values automatically converted to the date and time format selected in the user’s personal settings.Value are stored as Coordinated Universal Time (UTC).This datatype has different format option.
Format:
- Date Only
- Date Time
Lookup –
This datatype represents a link to another entity.When you create a new lookup field you are creating a new Many-to-One (N:1) entity relationship between the entity you’re working with and the Target Record Type defined for the lookup. But all custom lookups can only allow for a reference to a single record for a single target record type.
Not every lookup behaves this way. There are several different types of system lookups
- Simple – Allows for a single reference to a specific entity. All custom lookups are this type.
- Customer – Allows for a single reference to either an account or a contact record. These lookups are available for the Opportunity, Case, Quote, Order, and Invoice entities. These entities also have separate Account and Contact lookups that you can use if your customers are always one type. Or you can include both instead of using the Customer lookup.
- Owner – Allows for a single reference to either a team or a user record. All team or user-owned entities have one of these.
- PartyList – Allows for multiple references to multiple entities. These lookups are found on the Email entity To and Cc fields. They’re also used in the Phone and Appointment entities.
- Regarding – Allows for a single reference to multiple entities. These lookups are found in the regarding field used in activities.
Customer –
A lookup datatype that you can use to specify a customer, which can be an account or contact.
This feature was introduced in Dynamics CRM 2016 Service Pack 1 On-Premises and Dynamics CRM Online 2016 Update 1.
Multi-Select Option Sets –
Multi-select option set is similar to option set datatype but in this we can select more than one value.This was introduced in dynamics 365 from July update. Older version will not be able to see this data type.
Features of the new Multi-Select option set include:
- New AttributeType: MultiSelect Option Set
- The ability to add them to forms, views, and quick create forms, and read-only and editable grids
- Can add up to 150 values available for selection
- Select All and Clear selection feature
- Can leverage existing (select a global) and new option sets
- Supported in Web Client and the new Unified Interface (coming in July as well)
- Supported in Advanced Find and FetchXML queries
- Multi-select option set fields cannot be a calculated or rollup field
Field Properties:
Every time you create new field, remember that there are several properties that you can configure this field
- Display Name: The label of the field, visible in all the places where field is
referenced such as forms, views, and reports. - Name: The logical name of the field; it is used while coding for Microsoft
Dynamics 365 - Description: This is used to provide additional information or setup tooltips
for the field. - Field Requirement: The requirement level of the fields. It has three options:
- Business Required, which is mandatory;
- Business Recommended, which means it is recommended for business: and Optional.
- Searchable: This property defines whether we can query the entity record
based on this field or not using advanced find. - Field Security: Used to protect fields based on the field level security profile;
we can configure the field level security for both custom and system fields. - Auditing: Used to keep track of the changes in field value; provides two
options, Enable or Disable. - Date Type: Available data types in Microsoft Dynamics CRM 2015; we can
select them based on the data that we want to store in a field. - Field Type: Dependent on the data type selected. It has three options:
- Simple,
- Calculated
- Rollup.
- Format: Allows us to format fields based on the data type selected.
Calculated Fields:
In Microsoft Dynamics 365, calculated fields let you automate manual calculations used in your business processes.System administrators can now easily define a field to contain the value of many common calculations without having to work with a developer. Developers can also leverage the platform capabilities to perform these calculations rather than within their own code.First we need to select calculated field from field type from field property. Calculated field was introduced in Microsoft Dynamics CRM 2015.
Datatype Available For Calculated Field:
- Single line of text
- Option Set
- Two Options
- Whole Number
- Decimal Number
- Currency
- Date and Time
Calculated Field Capabilities:
The calculated fields comprise of calculations that use the fields from the current entity or related parent entities.
- The expression support is available on the current entity and the related parent entity fields in the Condition sections and the Actionsections.
- A rich conditional support provides branching and multiple conditions. The logical operations include AND and OR operators.
- The visual editing capabilities include modern user interface and intellisense in the ACTION section.
A seamless integration of the calculated fields with the forms, views, charts, and reports is available in real time.
If you updated your Online organization to December 2016 update for Microsoft Dynamics 365 (online), you can configure calculated fields to use custom controls.
Available Functions:
| Function Syntax | Data types | Description | Return type |
ADDDAYS | whole number, date and time | Returns a new date and time that is equal to the given date and time, plus the specified number of days. | Date and Time |
ADDHOURS | whole number, date and time | Returns a new date and time that is equal to the given date and time, plus the specified number of hours. | Date and Time |
ADDMONTHS | whole number, date and time | Returns a new date and time that is equal to the given date and time, plus the specified number of months. | Date and Time |
ADDWEEKS | whole number, date and time | Returns a new date and time that is equal to the given date and time, plus the specified number of weeks. | Date and Time |
ADDYEARS | whole number, date and time | Returns a new date and time that is equal to the given date and time, plus the specified number of years. | Date and Time |
SUBTRACTDAYS | whole number, date and time | Returns a new date and time that is equal to the given date and time, minus the specified number of days. | Date and Time |
SUBTRACTHOURS | whole number, date and time | Returns a new date and time that is equal to the given date and time, minus the specified number of hours. | Date and Time |
SUBTRACTMONTHS | whole number, date and time | Returns a new date and time that is equal to the given date and time, minus the specified number of months. | Date and Time |
SUBTRACTWEEKS | whole number, date and time | Returns a new date and time that is equal to the given date and time, minus the specified number of weeks. | Date and Time |
SUBTRACTYEARS | whole number, date and time | Returns a new date and time that is equal to the given date and time, minus the specified number of years. | Date and Time |
CONCAT | single line of text, single line of text, … single line of text | Returns a string that is the result of concatenating two or more strings. | String |
TRIMLEFT | single line of text, whole number | Returns a string that contains a copy of a specified string without the first N-characters. | String |
TRIMRIGHT | single line of text, whole number | Returns a string that contains a copy of a specified string without the last N-characters. | String |
DIFFINDAYS | date and time, date and time | Returns the difference in days between two Date and Time fields. If both dates and times fall on the same day, the difference is zero. | Whole Number |
DIFFINHOURS | date and time, date and time | Returns the difference in hours between two Date and Time fields. | Whole Number |
DIFFINMINUTES | date and time, date and time | Returns the difference in minutes between two Date and Time fields. | Whole Number |
DIFFINMONTHS | date and time, date and time | Returns the difference in months between two Date and Time fields. If both dates and times fall on the same month, the difference is zero. | Whole Number |
DIFFINWEEKS | date and time, date and time | Returns the difference in weeks between two Date and Time fields. If both dates and times fall on the same week, the difference is zero. | Whole Number |
DIFFINYEARS | date and time, date and time | Returns the difference in years between two Date and Time fields. If both dates and times fall on the same year, the difference is zero. | Whole Number |
Disadvantage Of Calculated Field:
- You cannot use fields on a related entity in the calculation.
- You cannot change existing fields to calculated fields.
- Calculated fields are not available for floating number or Lookup type fields.
- Maximum 10 calculated field can be created for single entityM
- Maximum 100 calculated field can be created for single organization
How To Create Calculated Field:
Once the calculated is created Click edit button near to field type and it takes us to another window.
We can have condition to trigger our calculated field.Set the action according to our business need and save the field. Now we can test the calculated field.
In the above example if Test field contains data it will trim test field value from left of 3 character and give the result in Test1 field.
Rollup Field:
In Microsoft Dynamics 365, rollup fields are designed to help users obtain insights into data by monitoring key business metrics. A rollup field contains an aggregate value computed over the records related to a specified record. Rollup fields are calculated by Asynchronous System Jobs which runs in background. By default, Mass Calculate Rollup Field job runs every 12 hours after a Rollup field is created or updated. This delay is needed to assure that Mass Calculate Rollup Field job runs during the non-operational hours of an organization.But in some business scenarios, users need this calculation in real-time and on demand. Rollup field was introduced in Microsoft Dynamics CRM 2015.
Datatype Available For Rollup Field:
- Whole Number
- Decimal Number
- Currency
- Date and Time
Rollup Fields Capabilities:
- Visual editing is easy. You can create rollup fields by using the Field Editor, just like you do when you create a regular field.
- Full filter support for aggregation. You can set various filters for the source entity or related entity while setting multiple conditions.
- Seamless integration with the user interface. You can include the rollup fields in forms, views, charts and reports.
- Rollup fields are solution components. You can easily transport the rollup fields as components between organizations and distribute them in solutions.
- Rollup fields and the calculated fields are complementary to each other. You can use a rollup field as a part of the calculated field, and vice versa.
If you updated your Online organization to December 2016 update for Microsoft Dynamics 365 (online), you can configure rollup fields to use custom controls.
Available Functions:
- SUM – Total or sum of the value.
- COUNT – Count total number of record.
- MIN – Gives the minimum of the value.
- MAX – Give the maximum of the value.
- AVG – Gives the average of the value.
Disadvantage Of Rollup Field:
- We can define a maximum of 100 rollup fields for the organization and up to 10 rollup fields per entity.
- A workflow can’t be triggered by the rollup field updates.
- A workflow wait condition cannot use a rollup field.
- A rollup over the rollup field is not supported.
- A rollup can’t reference a calculated field that uses another calculated field, even if all the fields of the other calculated field are on the current entity.
- The rollup can only apply filters to the source entity or related entities, simple fields or non-complex calculated fields.
- A rollup can’t be done over the N:N relationships.
- A rollup can’t be done over the 1:N relationship for the Activity entity or the Activity Party entity.
- The business rules, workflows or calculated fields always use the last calculated value of the rollup field.
- The maximum number of records during the rollup refresh is limited to 50,000 records.
How To Create Rollup Field:
Once the rollup is created Click edit button near to field type and it takes us to another window.
Source entity: We specify the entity for which the rollup field is defined and whether or not you aggregate over a hierarchy. You can add filters with multiple conditions to specify the records in the hierarchy you want to use for rollup.
Related entity: We specify the entity over which you aggregate. This section is optional when you choose to rollup over the hierarchy on the source entity. You can add filters with multiple conditions to specify which related records to use in the calculation.
Aggregate: We specify the metric you want to compute. You can choose available aggregate functions, such as SUM, COUNT, MIN, MAX or AVG.
In the above example it will count the number account is related to the record.
We can see in Test field value the number account is associated the test record.
By default, the job will run 12 hours after you created or updated a field.
At some time we need to see the result immediately. User can refresh the value and see the result manually by clicking the refresh button in the right of the field.
Each Rollup Field Is Comprised Of Three Physical Fields:
- Fieldname – stores the calculated value
- Fieldname_date – stores the last time the field was updated
- Fieldname_state – this appears to track if a rollup calculation has failed or is in a “bad” disposition – although I have not seen a state field in a non-zero value yet, the underlying database objects suggest non-zero values are possible.
No comments:
Post a Comment