In Dataport Designer, you can also change the format of the external file that the data is exported to. For example, you can change the format of the exported fields so that decimal numbers are not included or numbers are exported as thousands.

The following procedure illustrates how you use the SourceExpr property to export the decimal fields, Balance at Date and Net Change as thousands so that the number 1,444,723.67 is exported as 1444. These fields are used as an example because they are fields in the G/L Account table. For more information, see How to: Create a Dataport for Exporting Data.

To change field formats

  1. In Dataport Designer, select the data item, click View, and then click Dataport Fields.

  2. In the Field Designer window, select the field, such as Balance at Date, click View, and then click Properties.

  3. In the Properties window, in the SourceExpr property, type the relevant expression. For example, to export the value of a decimal field as thousands, type the following expression.

      CopyCode imageCopy Code 
    FORMAT(ROUND("field name"/1000,1,'='),0,1)

    This C/AL statement ensures that the value of the decimal field is divided by 1000. The result is rounded by the ROUND function, and then the FORMAT function is used to render the value returned by the ROUND in format 1. For a decimal value, this means <Sign><Integer><Decimals>. For more information, see ROUND Function (Decimal).

When you run the dataport, the data in the external file will reflect this formatting.

Example

The following example illustrates the data that is exported if you run a dataport with a fixed file format. This dataport contains a single data item for the G/L Account table, where the format of values in a decimal field has been changed.

  CopyCode imageCopy Code 
1000				 BALANCE SHEET				0			0		 
1002				 ASSETS						 0			0		 
1003				 Fixed Assets				 0			0		 
1005				 Tangible Fixed Assets		0			0		 
1100				 Land and Buildings			 0			0		 
1110				 Land and Buildings			 1,479,480.6  1479	
1120				 Increases during the Year	147.73	 0		 
1130				 Decreases during the Year	0			0		 
1140				 Accum. Depreciation, Buildings -526,620.38  -527	
1190				 Land and Buildings, Total	953,007.95   953		 
1200				 Operating Equipment			0			0		 
1210				 Operating Equipment			582,872.18   583		 
1220				 Increases during the Year	25,116	 25	
1230				 Decreases during the Year	0			0		 
1240				 Accum. Depr., Oper. Equip.	 -508,176.74  -508	
1290				 Operating Equipment, Total	 99,811.44	100		 
1300				 Vehicles					 0			0		 
1310				 Vehicles					 49,473.91	49	
1320				 Increases during the Year	87,000	 87	
1330				 Decreases during the Year	0			0		 
1340				 Accum. Depreciation, Vehicles  -60,603.78   -61		 
1390				 Vehicles, Total				75,870.13	76	
1395				 Tangible Fixed Assets, Total   1,128,689.52 1129	
1999				 Fixed Assets, Total			1,128,689.52 1129

In the example, the data is exported from the Microsoft Dynamics NAV demo database. A filter has been applied to only export records where the value of the No. field is less than 2000. Also, the source expression for the Net Change field has been changed to FORMAT(ROUND("Net Change "/1000,1,'='),0,1) to export the values of this field as thousands, but the source expression for the Balance at Date field remains unformatted and returns data that is not rounded. For more information, see How to: Create a Dataport for Exporting Data.

See Also