Extending an archive to include extrafield?

RE: Extending an archive to include extrafield?

Hi Frode,

You should be able to add your field to the main query and then obtain the values from the query results in the reader, like so:

[ArchiveExtenderExtender("ContactWithExtraFieldsExtension", typeof(ContactExtenderBase), int.MaxValue / 2)]
public class ContactWithExtraFieldsExtender : TableExtenderBase<ContactTableInfo>
{
    private ArchiveColumnInfo _colDiscountInfo = new ArchiveColumnInfo(
        "x_discount_info", "Discount Info", "The discount set in Service",
        Constants.DisplayTypes.Int, true, true, "5c", Constants.RestrictionTypes.Int
        );

    FieldInfo xDiscount = null;

    public ContactWithExtraFieldsExtender()
    {
        ContactTableInfo cti = Parent.TableToExtend as ContactTableInfo;
        xDiscount = cti.FindField("x_discount_info");
    }

    protected override void InnerModifyQuery()
    {
        if (xDiscount != null)
        {
            RootQuery.Query.ReturnFields.Add(xDiscount);
        }
    }

    protected override void InnerPopulateRowFromReader(SoDataReader reader, ArchiveRow row)
    {
        base.InnerPopulateRowFromReader(reader, row);
        row.AddOverlappingIntColumn(_colDiscountInfo.Name, reader.GetInt32(xDiscount));
    }

    protected override ContactTableInfo SetJoin()
    {
        return Parent.TableToExtend as ContactTableInfo;
    }
}

Hope this helps!

RE: Extending an archive to include extrafield?

Thanks, that looks exactly like what I need! I'll test it when I get back infront of a computer.

My case has a second scenario as well, that I need to figure out how to do. I have an extratable, y_contact_data, that has three relevant fields. x_contact_id, which is a reference to the contact, x_user_id which is a user relationship, and x_has_agreement, which is a bool.

In this case, do I need to create a TableInfo class for my y_contact_data table? And if so, how do I add a Join in the C# code to get the data?

RE: Extending an archive to include extrafield?

Hi, I'm testing your code now. For some reason the Parent property used in the constructor is allways NULL. What could cause that?

RE: Extending an archive to include extrafield?

Hi Frode,

Yeah, sorry having that constructor in there wasn't the way to go... I was going off memory and didnt test it...

I have testing this though and should get you where you want:

[ArchiveExtenderExtender("ContactWithExtraFieldsExtension", 
    typeof(ContactExtenderBase), 
    int.MaxValue / 2)]
public class ContactWithExtraFieldsExtender : TableExtenderBase<ContactTableInfo>
{
    private ArchiveColumnInfo _colDiscountInfo = new ArchiveColumnInfo(
        "x_discount_info", "Discount Info", "The discount set in Service",
        Constants.DisplayTypes.Int, true, true, "5c", Constants.RestrictionTypes.Int
        );

    FieldInfo xDiscount = null;

    protected override void InnerModifyQuery()
    {
        ContactTableInfo cti = Parent.TableToExtend as ContactTableInfo;
        xDiscount = cti.FindField("x_discount_info");

        if (xDiscount != null)
        {
            RootQuery.Query.ReturnFields.Add(xDiscount);
        }
    }

    protected override void InnerPopulateRowFromReader(SoDataReader reader, ArchiveRow row)
    {
        base.InnerPopulateRowFromReader(reader, row);

        if(xDiscount != null)
        {
            row.AddOverlappingIntColumn(_colDiscountInfo.Name, reader.GetInt32(xDiscount));
        }
    }

    protected override ContactTableInfo SetJoin()
    {
        return Parent.TableToExtend as ContactTableInfo;
    }
}

In regards to the other issue, yes we have a way to do this. Give me a few minutes to type up an example and add that here afterwards.

Hope this helps (for now... )

RE: Extending an archive to include extrafield?

Here is an example how you would go about including an extra table into an extender. If you need to set up additional joins, depending on your relationships, etc, I'm confident you can work that out :-) If not, just give more details. :-)

(not thoroughly tested code)....

[ArchiveExtenderExtender("ContactWithExtraTableExtension", 
    typeof(ContactExtenderBase), int.MaxValue / 2)]
public class ContactWithExtraTableExtender : TableExtenderBase<TableInfo>
{
    private ArchiveColumnInfo _colUserId = new ArchiveColumnInfo(
        "x_user_id", "user identity", "user identity tooltip",
        Constants.DisplayTypes.Int, AllowOrderBy, Visible, 
        ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int
        );

    private ArchiveColumnInfo _colHasAgreement = new ArchiveColumnInfo(
       "x_has_agreement", "Has Agrerement", "Has Agreement Tooltip",
       Constants.DisplayTypes.Bool, AllowOrderBy, Visible, 
       ColumnHelper.DefaultCheckboxWidth, Constants.RestrictionTypes.Bool
       );

    private ContactAgreementCache _contactAgreementTable = null;


    protected ContactWithExtraTableExtender()
    {
        _contactAgreementTable = ClassFactory.Create<ContactAgreementCache>();
        if (_contactAgreementTable.Y_ContactData == null)
            return;

        SetIconHint(Constants.IconHints.Contact);
    }
    protected override void InnerModifyQuery()
    {
        if (_ourTable != null)
        {
            MapSimpleReturnField(_contactAgreementTable.X_UserId, _colUserId);
            MapSimpleReturnField(_contactAgreementTable.X_HasAgreement, _colHasAgreement);
        }
    }

    protected override TableInfo SetJoin()
    {

        if (_contactAgreementTable.Y_ContactData == null)
            return null;

        var parentcontactInfo = Parent.TableToExtend as ContactTableInfo;

        RootQuery.Query.JoinRestriction.LeftOuterJoin(
            parentcontactInfo.ContactId.Equal(_contactAgreementTable.X_ContactId));

        RootQuery.Query.ReturnFields.Add(
            _contactAgreementTable.X_UserId, 
            _contactAgreementTable.X_HasAgreement);

        return _contactAgreementTable.Y_ContactData;
    }

    //No need to override unless you wanted custom tooltips, etc.
    //protected override void InnerPopulateRowFromReader(SoDataReader reader, ArchiveRow row)
    //{
    //}
}

/// <summary>
/// Cached data for the X-Table extenders
/// </summary>
[SoInject(InstanceContainers.Database)]
public class ContactAgreementCache
{
    public TableInfo Y_ContactData;

    public FieldInfo X_ContactId;
    public FieldInfo X_UserId;
    public FieldInfo X_HasAgreement;


    public ContactAgreementCache()
    {
        Y_ContactData = TablesInfo.GetTableInfo("y_contact_data");
        if (Y_ContactData != null)
        {
            X_ContactId     = Y_ContactData.FindField("x_contact_id");
            X_UserId        = Y_ContactData.FindField("x_user_id");
            X_HasAgreement  = Y_ContactData.FindField("x_has_agreement");
        }
    }
}

Hope this helps!

RE: Extending an archive to include extrafield?

Thanks, got the first example sort of working now. I can display the column in the result of a Company-selection, and it correctly displays the information from the extrafield. Very cool :)

However, if I use the Find-dialog to search for a company with a specific value in the extrafield, I always get the same two (wrong) results.

Do I need to write code for handling the search restrictions?

RE: Extending an archive to include extrafield?

Hi Frode,

No, you shouldn't need to handle any restrictions set in the UI.

You should be able to step into InnerModifyQuery and check the restrictions on RootQuery.Query.Restriction - and the whole query for that matter. 

Using your column name, you should be able to look at it using ExtractRestriction(...).

Best regards.

RE: Extending an archive to include extrafield?

Hi Tony, thanks for your help so far. 

When I inspect the RootQuery.Query.Restriction it is always null, even when I use standard fields in the Find-dialog.

If I override the AddLocalRestriction method, I see that the two restrictions are passed to it, as expected:

If I use the ExtractRestriction method inside InnerModify, I get the correct restriction back, even though RootQuery.Query.Restriction is null:

So my restriction seems to appear some places in the archiveprovider, but the searchresult is not correct.

RE: Extending an archive to include extrafield?

Hi again, 

I decided to expand on the example to include a lot of different field types. So, at this point I'm able to display several of the extrafields in the selection result:

There are some hickups; 

- Bools never get checked

- reader.GetBoolean crashes when databasefield contains NULL

- Not sure how I can display the company name in the Company Relation?

- DateTime only displays datepart, not time.

- Date uses wrong format

- User relations doesn't show anything

- Searching on any of the fields will always give me the same, wrong, result.

Here is the full code of the archive provider so far:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SuperOffice.CRM.ArchiveLists;
using SuperOffice.CRM.Data;
using SuperOffice.Data;
using SuperOffice.Data.SQL;

namespace GEASExperiments
{
    /*
     * An ArchiveExtender for exposing ExtraFields from Service in Sales.
     * This example assumes there are extrafields of various types on the crm7.contact table.
     * https://community.superoffice.com/en/developer/forum/rooms/topic/netserver-api-group/core-components/extending-an-archive-to-include-extrafield/
     * Frode Lillerud, 6.des 2017
     */

    [ArchiveExtenderExtender("ContactWithExtraFieldsExtension", typeof(ContactExtenderBase), int.MaxValue / 2)]
    public class ContactWithExtraFieldsExtender : TableExtenderBase<ContactTableInfo>
    {
        //Definition of the columns. NOTE - the variablename MUST start with _col to be detected by SuperOffice
        private ArchiveColumnInfo _colNumberColumn
            = new ArchiveColumnInfo("x_number", "Service Number", "Number field in Service",
                Constants.DisplayTypes.Int, true, true, ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int);
        private FieldInfo xNumberField = null;

        private ArchiveColumnInfo _colDateColumn
            = new ArchiveColumnInfo("x_date", "Service Date", "Date field in Service",
                Constants.DisplayTypes.Date, true, true, ColumnHelper.DefaultDateWidth, Constants.RestrictionTypes.Date);
        private FieldInfo xDateField = null;

        private ArchiveColumnInfo _colBoolColumn
            = new ArchiveColumnInfo("x_bool", "Service Bool", "Bool field in Service",
                Constants.DisplayTypes.Bool, true, true, ColumnHelper.DefaultCheckboxWidth, Constants.RestrictionTypes.Bool);
        private FieldInfo xBoolField = null;

        private ArchiveColumnInfo _colTextShortColumn
            = new ArchiveColumnInfo("x_text_short", "Service Text Short", "Text (short) field in Service",
                Constants.DisplayTypes.String, true, true, ColumnHelper.DefaultStringWidth, Constants.RestrictionTypes.String);
        private FieldInfo xTextShortField = null;

        private ArchiveColumnInfo _colTextLongColumn
            = new ArchiveColumnInfo("x_text_long", "Service Text Long", "Text (long) field in Service",
                Constants.DisplayTypes.String, true, true, ColumnHelper.DefaultStringWidth, Constants.RestrictionTypes.String);
        private FieldInfo xTextLongField = null;

        private ArchiveColumnInfo _colCompanyRelationColumn
            = new ArchiveColumnInfo("x_company_relation", "Service Company Relation", "Company Relation field in Service",
                Constants.DisplayTypes.Int, true, true, "15c", Constants.RestrictionTypes.Entity);
        private FieldInfo xCompanyRelationField = null;

        private ArchiveColumnInfo _colFloatColumn
            = new ArchiveColumnInfo("x_float", "Service Float", "Float field in Service",
        Constants.DisplayTypes.Decimal, true, true, "15c", Constants.RestrictionTypes.Decimal);
        private FieldInfo xFloatField = null;

        private ArchiveColumnInfo _colDateTimeColumn
            = new ArchiveColumnInfo("x_datetime", "Service DateTime", "DateTime field in Service",
        Constants.DisplayTypes.Datetime, true, true, "15c", Constants.RestrictionTypes.Datetime);
        private FieldInfo xDateTimeField = null;

        private ArchiveColumnInfo _colTimeColumn
            = new ArchiveColumnInfo("x_time", "Service Time", "Time field in Service",
        Constants.DisplayTypes.Time, true, true, "15c", Constants.RestrictionTypes.Datetime);
        private FieldInfo xTimeField = null;

        private ArchiveColumnInfo _colUserRelationColumn
            = new ArchiveColumnInfo("x_user_relation", "Service User", "User Relation field in Service",
        Constants.DisplayTypes.Icon, true, true, "15c", Constants.RestrictionTypes.Associate);
        private FieldInfo xUserRelationField = null;

        public ContactWithExtraFieldsExtender()
        {
            //Add our field under the "Company" heading, and not the "Others" heading.
            SetIconHint(Constants.IconHints.Contact); 
        }

        protected override void InnerModifyQuery()
        {
            //If we haven't gotten the field definitions yet, then get them.
            if (xNumberField == null)
            {
                ContactTableInfo cti = Parent.TableToExtend as ContactTableInfo;
                xNumberField = cti.FindField("x_number");
                xDateField = cti.FindField("x_date");
                xBoolField = cti.FindField("x_bool");
                xTextShortField = cti.FindField("x_text_short");
                xTextLongField = cti.FindField("x_text_long");
                xCompanyRelationField = cti.FindField("x_company_relation");
                xFloatField = cti.FindField("x_float");
                xDateTimeField = cti.FindField("x_datetime");
                xTimeField = cti.FindField("x_time");
                xUserRelationField = cti.FindField("x_user_relation");
            }

            //Add the fields to the returned result.
            if (xNumberField != null)
                RootQuery.Query.ReturnFields.Add(xNumberField);
            if (xDateField != null)
                RootQuery.Query.ReturnFields.Add(xDateField);
            if (xBoolField != null)
                RootQuery.Query.ReturnFields.Add(xBoolField);
            if (xTextShortField != null)
                RootQuery.Query.ReturnFields.Add(xTextShortField);
            if (xTextLongField != null)
                RootQuery.Query.ReturnFields.Add(xTextLongField);
            if (xCompanyRelationField != null)
                RootQuery.Query.ReturnFields.Add(xCompanyRelationField);
            if (xFloatField != null)
                RootQuery.Query.ReturnFields.Add(xFloatField);
            if (xDateTimeField != null)
                RootQuery.Query.ReturnFields.Add(xDateTimeField);
            if (xTimeField != null)
                RootQuery.Query.ReturnFields.Add(xTimeField);
            if (xUserRelationField != null)
                RootQuery.Query.ReturnFields.Add(xUserRelationField);
        }

        protected override ContactTableInfo SetJoin()
        {
            return Parent.TableToExtend as ContactTableInfo;
        }

        protected override void InnerPopulateRowFromReader(SoDataReader reader, ArchiveRow row)
        {
            base.InnerPopulateRowFromReader(reader, row);

            //Fill the column in the returned row with the value from the field.
            if (xNumberField != null)
                row.AddOverlappingIntColumn("x_number", reader.GetInt32(xNumberField));
            if (xDateField != null)
                row.AddOverlappingDateTimeColumn("x_date", reader.GetDateTime(xDateField));
            if (xBoolField != null)
            {
                //bool b = false;
                //try
                //{
                //    b = reader.GetBoolean(xBoolField);
                //}
                //catch (Exception)
                //{
                //    //Data in field is NULL.
                //}
                row.AddOverlappingBoolColumn("x_bool", true); //Problem - reader.GetBoolean crashes when DB contains NULL
            }
            if (xTextShortField != null)
                row.AddOverlappingColumn("x_text_short", reader.GetString(xTextShortField));
            if (xTextLongField != null)
                row.AddOverlappingColumn("x_text_long", reader.GetString(xTextLongField));
            if (xCompanyRelationField != null)
                row.AddOverlappingIntColumn("x_company_relation", reader.GetInt32(xCompanyRelationField));
            if (xFloatField != null)
                row.AddOverlappingColumn("x_float", reader.GetDouble(xFloatField).ToString());
            if (xDateTimeField != null)
                row.AddOverlappingDateTimeColumn("x_datetime", reader.GetDateTime(xDateTimeField));
            if (xTimeField != null)
                row.AddOverlappingDateTimeColumn("x_time", reader.GetDateTime(xTimeField));
            if (xUserRelationField != null)
                row.AddOverlappingIntColumn("x_user_relation", reader.GetInt32(xUserRelationField));
        }
    }
}

RE: Extending an archive to include extrafield?

For future reference, Marek has a relevant example in his video from Expander World 2016: https://www.youtube.com/watch?v=XZWpevKS2DE&t=730s