This is the fifth article in a series that discusses creating a new database connection to the Sage X3 database using the standard Sage CRM features.
Views that are intended to be used to list data from Sage X3 need to share a common key with the corresponding data in Sage CRM. We used as an example of how the 'acc_int_reference' in the Sage CRM table 'accounts' corresponds to the field 'BPCNUM_0' in the Sage X3 table 'BPCUSTOMER'.
The fields included in the view that is linked into Sage CRM can have meta data setting made that will add CRM user interface behaviour to them. This would allow fields that hold a code (e.g. APC100, APC200, APC300) to display user-friendly field values (e.g. Small, Medium, Large) translated into each language used by the Sage CRM users. It would allow date fields to be formatted to display either a Date and Time or a Date alone in summary pages and would allow easy to understand captions to be added to identify fields and columns.
Below shows how several views in Sage X3 have been linked as new table connections in Sage CRM.
Once we have created the link to the view in Sage X3 we can start to edit the field metadata descriptions.
The views that exist in the Sage X3 database can be treated like they are tables in Sage CRM. They will appear as secondary entities under the Administration screens.
The linked Table can then be selected and by default, the list of fields will display.
Each field in the external table is treated with default characteristics based on the physical data type.
In the example above the view, CRMSupplier contains fields that have the following physical MS SQL Server datatypes and lengths.
- bpsnum_0 is nvarchar(15)
- bpsnam_0 is nvarchar(35)
- bpssho_0 is nvarchar(10)
- bpsrem_0 is nvarchar(250)
Fields that are nvarchar in the physical database are mapped automatically to the Sage CRM Entry Type 'Text' but actually could be changed and mapped to other Sage CRM Entry Types.
The database datatype 'nvarchar' can be mapped to the EntryType of Selection, Currency Symbol, Checkbox, Phone number, E-mail address or Web Address among others. Each field can then be edited to allow the correct caption and behaviour you need.
The full options are documented in the System Administration guide.
In the example above the fields from the view have only been edited to match the field descriptions of the BPSUPPLIER table from the Sage X3 documentation.
These fields all belong to the Sage CRM secondary entity 'CRMSuppliers' and the new column definitions can be seen in the database stored in the table 'custom_edits'.
[code language="sql"]
select * from custom_edits where colp_entity = 'CRMSuppliers';
[/code]
The new labels for the fields have been added to the custom_captions table at the same time. All the fields in my example start with the same three letters.
[code language="sql"]
select * from custom_captions where capt_code like 'bps%'
[/code]
The next article will look at how a List can be created from meta data.
Creating a new database connection to Sage X3