File column
| Dataverse/Dynamics |
|---|
|
|
File columns on Dataverse are a type of table column that can hold binary data. Since release 16.0, file columns can be also used in Resco mobile apps.
What are file columns
Dataverse introduced file columns in late 2019. They represent an alternative to traditional notes. Microsoft recommends using file columns for file sizes >5M.
File is just another column type. Unlike conventional column types such as "Single line of text" (storing strings) or "Lookup" (storing references to CRM records), file columns store files. Files are stored in the Dataverse File Storage (actually in Azure) and not in Dataverse, i.e., they do not consume database space. (This applies to all types of attachments.)
Any number of file columns can be added to standard/custom tables. When adding a file column to a table, you can specify the maximum file size (default 32 MB, maximum 128 MB). This value cannot be changed later.
To learn more about this feature in Dataverse, see Microsoft documentation.
How are file columns supported in Resco mobile apps
In release 16.0, we have added basic support for File columns.
In release 16.1, the support is improved:
- Users can now create, modify, or delete the contents of file columns on entity forms in Resco mobile apps.
- Reports can be saved into file column.
- File columns are a part of synchronization (allowing for a full offline experience).
What is supported in Resco
- File and Filename fields are visible in Woodford. You can enable them in your projects.
- File field - This field must be enabled. When added to a form, it is represented as a lookup with a label and a clickable filename.
- Filename field - This field is optional. When you enable the file field, the filename is enabled as well. We don't recommend disabling it. When disabled, "Unknown" is presented on the form instead of the file name.
- Files are treated like standard documents; you can use operations such as print, export, and email.
- File fields are fully supported in offline mode. They are synchronized with other attachments. If a user tries to open a file that is not yet synchronized, it is immediately downloaded (if an internet connection is available) and displayed to the user. If the file is changed on the server once synced, it is only updated in the local database on the next synchronization. In online mode, files are not cached locally and are thus downloaded every time a user opens them.
What is not supported
- Views - You cannot open the file from a view using commands (there is no OpenAttachment command). It can only be opened from forms.
- Media tab - You cannot use the media tab to show files inline on the form (such as a signature).
- Questionnaires - You cannot use files as storage for binary questions (such as images or signatures).
- Form rules - It is impossible to access or modify file contents using form rules.
- Resco JavaScript Bridge - it is not possible to get file content via JS bridge. As a workaround, it is possible to use a file identifier (guid) in the File field to construct the download URL and download the content manually from Azure storage (Microsoft documentation).
File column versus annotation
In a sense, file columns are similar to conventional attachment entities - notes etc., except they are conceptually simpler to use. If you want to use annotations related to (say) accounts, you need to add a new relation annotation->account and use a more complex UI - a media tab. (Images can use simpler image control.) If you need additional annotations (signature, image...) you have to organize multiple relations. In effect, the data schema gets more complex (in particular sync filters) which may negatively impact the performance.
File columns make most of these things simpler. If you need a 1:1 relation such as "product photo" or "PDF help file", add a File column to the table definition and a file control to the UI. No need to add a new relation or complicate sync filters; life is simply easier. However, if you have an N:1 relation (such as a variable number of images attached to an account record) File columns won't help - you have to use traditional note attachments instead.
- Conclusions
- As far the app creation is concerned, File columns are easier to work with. However, if you need true N:1 relations, stick to annotations.
- File columns offer simpler security - row-level security of the record where they reside applies. Annotations are different: they are associated records (with specified cascading behavior).
- If you decide on file columns, expect weaker sync performance and only partially working filtering. This might become important if you use many attachments.
- (Resco has plans to optimize file column sync in the future if this feature becomes more popular.)
Implementation details and performance
The most important conclusions from this section are summarized above. If you're interested in nitty-gritty details, read on.
Storage
On the client device, files are stored in the app blob storage folder using this schema: blob\<entity_name>\<property_name>\<record_id>.
You can browse the blob store on Windows. But because of encryption, you won't be able to read blob content.
Classic attachments
Annotations (activitymimeattachments, knowledgebasearticles) offer extensive filtering possibilities: Document filters.
Attachments download is heavily optimized:
- Small blobs are grouped and downloaded using a single web request
- Larger blobs are downloaded simultaneously using parallel multi-downloader
- Only extra large (>100M) blobs are downloaded serially one by one.
However, attachments have also disadvantages, which may prove to be important exceptionally:
- Client DB overhead: 250+ Bytes per annotation record (typically negligible against actual file storage)
- FullSync: (possibly) complex sync filter may cause server performance problems.
- IncSync: In some installations IncSync might have lots of unneeded downloads for the annotation entity.
- Unneeded downloads: Annotations carry also textual information - notetext field. If this field changes, annotation body is downloaded as well.
File columns: sync download
- Weaker filtering: The size of the file blobs is unknown at sync time. Any filtering based on the file size (MaxAttachmentSize, parts of the BlobStoreFilter) is ignored.
- Every file blob is downloaded separately, one blob at a time (1 Azure web call per blob).
- Unneeded downloads are avoided: If a CRM record is modified, but its file blobs are unchanged, those blobs are not downloaded.
File columns: sync upload
In general, both implementations (annotations or File columns) upload similar data. However, annotations need fewer web calls, i.e., they should be faster.
As an example, imagine this situation: A new account record with two medium-sized attachments:
- If we use classic annotations, we need two web calls - 1 for the account record, and 1 for both annotation records.
- If we use file columns, we need three web calls - 1 for the account record (except file columns), 1 for each file.
See also