How to include User Defined Fields in SuperOffice Analyze

SuperOffice CRM version 7.0.4

How are things connected?

As you probably already know, fields are controlled in the CRM admin client. It will look something like this:

Its import to realize the connection between the name of the tab and the database table it’s based upon.

How do I obtain the necessary meta info of the fields in my SuperOffice installation?

You need access to a computer that has SQL Server Management Studio installed. Connect to the SuperOffice database with a login that has sufficient privileges. It must have execute privileges to the stored procedure ba_p_main4. SuperOffice Analyze must be installed.

In this document the schema owner is crm7. It may be different in your database. Please ensure that you use the correct schema owner in your SQL code.

Copy & paste for your convenience:

exec crm7.ba_p_main4 @pList='more_help', @access_mode=0

Once you’re connected to the correct database, execute the statement as shown above. It will give you the meta information you need to include fields in your datatables.

Note that the Table and Field Name columns show which tables to use for data retrieval. The Type column shows the type and makes it easy to use the best suited SQL example.

How can I use this to include a field in a datatable?

Log in to SuperOffice Analyze with user that has admin access. The professional version is required.

Press the admin button, and go to the desired datatable. It should look like this:

1. Edit the Query in the data table definition. A basic knowledge of SQL is recommended.
   Use the meta information obtained and follow the logic given for the different types of fields in Appendix A.
    
2. Press the Validate Query button. Your query is verified, and if the syntax is correct, you will see a dialog saying it’s OK. Otherwise you have introduced an error.
    
3. Press the Column tab. Locate the new column and set the default aggregation function. Set the allow grouping checkbox according to your needs. Also remember to set the correct chart usage, if you want to use it in charts.
    
4. Press the Preview tab to verify that the data looks ok.
    
5. Check the “Update dashboards and visualizations...” and press Save.
   
    
6. You may want to include the new column in the relevant data visualizations.
   
    
7. Right click in the data visualization and select columns or grouping. Include the new column.
   
    
8. Press Save.

Appendix A: SQL examples for all types of fields

The lines that are yellow must be included in the select section, and the grey in the form section. In these examples the Label is used as the column alias. This is by choice only, and you may use any name you like as long as it’s unique to the datatable.

Several types of fields may exist in the SuperOffice database. This is a list of examples that cover all the types:

Number

select

         s.sale_id

         ,uss.long06 as [Konsulent (NOK):]

from

         crm7.sale s

         left join crm7.udsalesmall uss on s.userdef_id = uss.udsalesmall_id -- udsalesmall

Decimal

select

         prj.project_id

         ,ups.double05 as [Estimert tid (timer):]

from

         crm7.project prj

         left join crm7.udprojectsmall ups on prj.userdef_id = ups.udprojectsmall_id -- udprojectsmall

Date

select

         c.contact_id

         ,ucs.long03 as [Vedlikehold til: (UnixDate)]

         ,dateadd(s,ucs.long03,'1970-01-01') as [Vedlikehold til: (Datetime)]

from

         crm7.contact c

         left join crm7.udcontactsmall ucs on c.userdef_id = ucs.udcontactsmall_id -- udcontactsmall

Shorttext

select

         prj.project_id

         ,ups.string06 as [TT Fargekode]

from

         crm7.project prj

         left join crm7.udprojectsmall ups on prj.userdef_id = ups.udprojectsmall_id -- udprojectsmall

Longtext

select

         c.contact_id

         ,ucl.string46 as [Merknad (vedlikehold):]

from

         crm7.contact c

         left join crm7.udcontactlarge ucl on c.userdef2_id = ucl.udcontactlarge_id -- udcontactlarge

Checkbox

select

         prj.project_id

         ,case

                   when ups.long07 = 0 then 'Unchecked'

                   when ups.long07 = 1 then 'Checked'

                   else 'Unknown'

         end as [Published:]

from

         crm7.contact c

         left join crm7.udprojectsmall ups on prj.userdef_id = ups.udprojectsmall_id -- udprojectsmall

Drop-down list (Base table)

select

         prj.project_id

         ,catl.name as [Publish to category:]

from

         crm7.project prj

         left join crm7.udprojectsmall ups on prj.userdef_id = ups.udprojectsmall_id -- udprojectsmall

                   left join crm7.category catl on catl.category_id = ups.long12 -- Join in category table

Drop-down list (UDList table)

select

         c.contact_id

         ,udl.name as [SO versjon:]

from

         crm7.contact c

         left join crm7.udcontactsmall ucs on c.userdef_id = ucs.udcontactsmall_id -- udcontactsmall

                   left join crm7.udlist udl on ucs.long20 = udl.UDList_id -- Join in udlist table - it contains all userdefined list items

For more information on the inner workings of the SuperOffice database see the SuperOffice database SDK documentation.

It can be found here http://devnet.superoffice.com/