•  What are Info Fields ?
•  Where Info Fields are used ?
•  Why use them ?
•  Structure
•  How values are stored
•  List of the db_info_category
•  How to create info fields with the API
•  How to fill db_info_data with the API

Odyssee is a SaaS solution used by different types of companies that each have their specific needs. There already are a lot of properties on big entities like Company, Project or Article, but it might be that other, additional, information is required, like a text, a date+time or a numeric field.

Instead of having few “empty” fields in the table, Odyssee developed a complete system to be able to create dynamic fields that are linked to these entities. The User Interface (in the tab “Extra info”) is built dynamically by reading the info fields in the database.

Info fields are grouped in chapters and different kind of fields are handled (e.g. textbox, textarea, datetime picker, dropdown list, radio button, etc …)

There are 2 kinds of info fields: Global and Specific info fields. The system of info fields has also been used to generate report templates. The difference between all three is explained in the next chapter.

• Global fields

  When creating a Global Info Fields for a certain entity, it is available for each item of the entity.

  For example if you create a chapter “ERP Fields” and a field “ERP Creation Date” on the Global Info Fields of Companies, each company will have the field and will have its own value for the field “ERP Creation Date”.

  Entities that can have Global info fields: Article, Company, Contact, Work Order, Equipment, User, Task

• Specific fields

  Specific info fields are fields that are linked to an entity type. These specific fields are then only available for entities of that specific type.

  For example if you create a field “Cause of failure” linked to the job type “Repair”, only jobs of the type “Repair” will have the field “Cause of failure”.

  Entities that can have Specific info fields: Equipment Family, Job Type

• Reports

  The Reports in the Odyssee system are using the concept of Info Fields as a Form builder, allowing the user to create form templates that will be filled by other users throughout the application.

  Types of reports: Visit Reports (mostly Sales), Job Reports (Service)

As an integrator that is linking an ERP to Odyssee, it’s possible that some information that comes from the ERP cannot be inserted in the existing fields of the entities. In this case, you can use the Info Fields to synchronize them.

You have also the option to flag these info fields as read-only allowing you to add extra informational lines between the fields that need to be filled.

The Info fields structure can be divided in 2 parts :

• The configuration of the info fields
• Value storage

1) Configuration

Which tables are used to configure the fields

Db_info_field :

This is the main table that contains the different fields

• Db_info_id : Foreign key (FK) to the chapter (Db_info) to which this field belongs
• Db_input_type_id : FK to the type of field (e.g. Textbox, TextArea)
• Name : name/code of the field
• Title : value that will be shown on the User interface
• Description : Internal description to help the user
• Sequence : numeric value to order the fields within the chapter
• Is_readonly : the user is not able to modify the field
• Hide_in_view_if_empty : will hide the field if it is not filled after saving (used only for the reports)

Db_info_field_property :

This table contains the different properties of the info fields. For example if the field is a dropdown list, it will contain all the items of that list.

• Db_info_field_id : FK to the field (db_info_field)
• value : key of the property (store there your ERP PK)
• Name : Item.text that will be shown in the User Interface
• Sequence : numeric value to order the items

Db_info :

This table contains the chapters.

• Name : Name of the chapter that will be shown in the User Interface
• Sequence : numeric value to order them
• Parent_id : in case of sub chapter

Db_info_category :

Links the field to the entity where info fields can be used.

Cf chapter of the db_info_category to have the values that can be used.

Db_info_category_list :

Cross table to link the chapters (db_info) and the entity where they have to be shown (db_info_category). In the major case, it’s only 1.

2) Storage

Db_info_data :

Contains the values for a specific info fields, for a specific object.

• db_info_field_id : FK to the db_info_field
• target_id : the id of the object (like company.id)
• db_table_id : FK to the db_table to specify to which table the target_id refers. This field is mandatory because a chapter of info fields can be shared on multiple entities in specific cases.
• value : the value of the field (CF chapter “How values are stored”)

Entity (not actual table) :

Represents one of the tables of the system (like company, jobs or project).

• id : the primary key of an entity is always a field called "id"

Db_table :

Each table of the system is listed in the table “db_table”.

• name : name of the table, like “company”, “jobs” or “project”

Because the information needs to be stored in one single column (string type), a conversion between the real type and the string is done:

DateTime : format yyyy-MM-dd HH:mm:ss , Example 2015-02-01 13:45:15 for February 1st 2015 at 13h45m15

Boolean : 0 for false, 1 for true

Decimal : No thousand separator, “.” as decimal separator

Multiple values (in case of a multi select box) : each value separated by a coma “,”

When you are creating chapters, on the fly, using the API, you have to link the chapter to a valid entity of info fields.

Here are the values can uses

Tables	Guid	Code
Article	62130B5B-6749-48D0-8E2A-A438375DFB06	Article
Company	AAD3C635-7CAB-452A-BA82-B8CB678EB2E7	Company
Contact	827F60F3-195B-49BD-AA27-67DB40FA32C8	Contact
Equipment Type	2694CD20-C3AA-4472-985B-945D3A5E53F7	EquipmentFamily
Jobs	CA58E657-8306-4ED5-9444-6119B41DF08E	Jobs
Opportunity	10BAC2B7-61DD-4A86-9037-59E1CB57A17B	Opportunity
Project	0449AE56-BCC4-DF11-B6D9-0030488C6C36	Project
Task	159269A7-CAC5-49db-823E-4CEF3AE1A126	Task
User	C9B3F4B5-EB54-4F1F-B28C-3C1D5BC90C9D	User

Thanks to embedded entities, it’s possible to create an info fields in one call. Like in the following example

<db_info_field>
    <name>TESTAPI_COMPANY_TXT1</name>
    <title>Field added using API</title>
    <db_input_type_code>TextArea</db_input_type_code>
    <db_info>
        <code>TESTAPI_CHAPTER_1</code>
        <name>Chapter Test API</name>
        <db_info_category_code>Company</db_info_category_code>
    </db_info>
</db_info_field>

As we can see, we supply a node of db_info (the chapter) and specify that this chapter should belong to the Company InfoFields.

Now, let’s try a more complex field: a combobox with some item values (items in the combobox). Let’s first add the info field

<db_info_field>
    <name>TESTAPICOMBOBOX</name>
    <title>my first combobox</title>
    <db_input_type_code>ComboBox</db_input_type_code>
    <db_info>
        <code>TESTAPI_CHAPTER_1</code>
        <name>Chapter Test API</name>
        <db_info_category_code>Company</db_info_category_code>
    </db_info>
</db_info_field>

HTTP STATUS 201 (and the id of the db_info_field returned, in this case ‘4877F5AA-011D-4771-99B7-5BA02752E1C5’). Then add 1 item for this combobox using the db_info_field_id received.

<db_info_field_property>
    <value>TESTAPICMB1_Value1</value>
    <name>My first value</name>
    <db_info_field_id>4877F5AA-011D-4771-99B7-5BA02752E1C5</db_info_field_id>
</db_info_field_property>

Now, let’s try to add the second value using embedded entities for the db_info_field

<db_info_field_property>
    <value>TESTAPICMB1_Value2</value>
    <name>My second value</name>
    <db_info_field_name>TESTAPICOMBOBOX/db_info_field_name>
</db_info_field_property>

To store the value "for one info field, for one value", we need to fill the object db_info_data as explained earlier.

For db_info_field_id you can supply an embedded entity (db_info_field_name or db_info_field) to create/update the db_info_field.

For db_table_id you can supply the name (db_table_name) of the table (like 'company') instead of his Guid.

Because the entity target_id points to a dynamic entity (like a company.id or project.id) it's not possible to handle full embedded entity.
But you can fill the field target_code with your ERP primary key !
If API found a valid object that belongs to this table with this code then API automatically fills the target_id.

Here is an example of a POST to fill the value for a company with company.code='COMPANY123' with the info field (the textbox)created previously.

<db_info_data>
    <db_table_name>company</db_table_name>
    <target_code>COMPANY123</target_code>
    <value>value filled with he API</value>
    <db_info_field_name>TESTAPI_COMPANY_TXT1</db_info_field_name>
</db_info_data>

Now, we can use the embedded entity for db_info_field_id.

<db_info_data>
    <db_table_name>company</db_table_name>
    <target_code>COMPANY123</target_code>
    <value>value filled with he API</value>
    <db_info_field>
         <name>TESTAPI_COMPANY_TXT1</name>
         <title>Field added using API</title>
         <db_input_type_code>TextArea</db_input_type_code>
         <db_info>
            <code>TESTAPI_CHAPTER_1</code>
            <name>Chapter Test API</name>
            <db_info_category_code>Company</db_info_category_code>
        </db_info>
    </db_info_field>
</db_info_data>