To create the data entity, follow these steps:
- In the project, add a new item. Within the Add New Item dialog, select Data Model from the left-hand list, and then Data Entity from the right.
- Enter ConWHSVehicleTableEntity as Name and press Add.
- We will then get a Data Entity Wizard dialog and select ConWHSVehicleTable in the Primary datasource drop-down list.
The Entity category is not correct by default; use the following table to select the correct category:
Table group |
Entity category |
Main, Group |
Master |
Worksheet (all types) |
Document |
Transaction (all types) |
Transaction |
Parameter |
Parameter |
Reference |
Reference |
- The dialog made a guess that Con was a prefix and stripped this from the Public entity name and Public collection name fields; the prefix should be put back to avoid the chance of naming collision.
- Click on Next.
- If we check Convert labels to field names, it will use the field's labels for the field names. This is not usually desirable; the label may need the context of field group in order for us to know which field it relates to. Do not check this checkbox.
A grid is created based on the table's definition, which will be used as the settings used to generate the data entity. These settings are usually correct; in our case, the grid is as follows:
- Click on Finish.
The wizard has created the following objects for us:
Element |
Description |
ConWHSVehicleTableEntity |
This is the data entity |
ConWHSVehicleTableStaging |
This is a table used to stage data when importing via the Data Import/Export Framework |
ConWHSVehicleTableEntityMaintain |
This is a security privilege to allow us full access to the data entity |
ConWHSVehicleTableEntityView |
This is a security privilege to allow view-only access to the data entity |
- Build the project and synchronize it with the database.
- Open the main form for the data entity; in our case, the Vehicles form, which can be accessed directly using the following URL:
https://usnconeboxax1aos.cloud.onebox.dynamics.com/?cmp=usmf&mi=ConWHSVehicleTable
- On the top right of the screen, the Office icon has a new option, OPEN IN EXCEL, as shown in the following screenshot:
- If you hover the mouse over the Vehicle table (usmf) link, you will see that it is our entity, as shown in the following screenshot:
- This is the public entity name we specified in the wizard, and we can change it by changing the Public Entity Name property on the data entity.
- Click on the Vehicle table (usmf) link, and then click Download in the Open in Excel dialog.
- Once Microsoft Excel opens, you may get the following warning:
- Click on Trust this add-in.
- Next, click on Sign in, and sign in using the same account you used for logging into Dynamics 365 for Operations.
Once signed in, it will populate a sheet with the data from the ConWHSVehicleTable table, but only add the mandatory fields. To test the entity, we should add a few fields.
- In the add-in, click on Design and then click on the edit icon next to the ConWHSVehicleTable table, as shown in the following screenshot:
- In the next page, select all of the fields in the Available fields list and press the Add button that is just above the Selected fields list.
- Click on Update, and then click Yes to the warning.
- Click on Done, which takes us out of the design experience, and then press Refresh to fetch the new data.
- Our sheet should now have the data from the vehicle table, as shown in the following screenshot:
- Edit one or more of the fields using the add-in to select the values when they have a drop-down list or date picker. Do not change the Vehicle Id value, but you can test this yourself in a different test to see what happens.
- Click on New in the add-in in order to add a new vehicle, and complete the sheet as required. Once done, the result should be similar to the following screenshot:
- Once done, click on Publish; the entity will be refreshed with the company ID that was actually used when the records are created.
- Finally, refresh the Vehicles form in Dynamics 365 for operations, and you will see the records within the vehicles list page.
- Close the Excel worksheet.
- Ideally, we would want to control which fields are available, so open the ConWHSVehicleTableEntity data entity in the designer.
- Add the fields you would like to see by default to the AutoReport field group.
- You may also have noticed that the VehicleGroupId field did not have a drop-down list in Excel, and the foreign key relation does not help in this case. We will need a custom lookup, as shown in the following piece of code:
/// <summary>
/// A custom lookup for vehicle group ids
/// </summary>
/// <param name = "_fields">
/// This is the fields' meta data provided by the
/// office add-in
/// </param>
/// <returns>
/// A serialized list of vehicle group ids
/// </returns>
[SysODataAction(
'ConWHSVehicleTableEntityVehicleGroupLookup',
false),
SysODataCollectionAttribute('_fields', Types::String),
SysODataFieldLookup(fieldStr(ConWHSVehicleTableEntity,
VehicleGroupId))]
public static str LookupVehicleGroupId(Array _fields)
{
RefFieldName vehicleGroupIdFld;
vehicleGroupIdFld = fieldStr(ConWHSVehicleTableEntity,
VehicleGroupId);
// Build a field and value map from the _fields Array
Map fieldMap;
fieldMap = OfficeAppCustomLookupHelper::getFieldMap(
tableStr(ConWHSVehicleTableEntity), _fields);
// Determine the company that the office add-in is
// connected to, otherwise it will return data from DAT
DataAreaId dataAreaId = curExt();
RefFieldName dataAreaIdFld;
dataAreaIdFld = fieldStr(ConWHSVehicleTableEntity,
DataAreaId);
if (fieldMap.exists(dataAreaIdFld))
{
dataAreaId = fieldMap.lookup(dataAreaIdFld);
}
// Construct the result object, and add our ID
// field to the list as the first element in the array
OfficeAppCustomLookupListResult result;
result = new OfficeAppCustomLookupListResult();
result.determinationFields().value(1,
vehicleGroupIdFld);
// declare the resultString here (latest) as it needs
// to be in scope for when it is set, and returned to
// the caller
str resultString;
// Check that the key field is in the supplied metadata
if (OfficeAppCustomLookupHelper::fieldsBound(
result.determinationFields(), fieldMap))
{
int counter = 1;
// change to the company the office add-in
// is connected to
changecompany(dataAreaId)
{
ConWHSVehicleGroup vehicleGroups;
// Add the vehicle group ids to the value array
while select VehicleGroupId
from vehicleGroups
order by VehicleGroupId
{
result.items().value(counter,
vehicleGroups.VehicleGroupId);
counter++;
}
}
resultString = result.serialize();
}
return resultString;
}
- Rebuild the project and test the add-in again; you will get the fields that you added to the field group along with the drop-down list on the Vehicle group column.