We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Searching for associate.name using webservices?

Hi,
I'm trying to use the webservices (specifically the Find.asmx) to search for an Associate based on the associate.name. However, I can't find an applicable Archive Provider. Isn't there some sort of generic search provider that can be used to search using tablename instead of predefined provider?

I see there is a provider called Criteria, can it be used to find associate?

Frode

Re: Searching for associate.name using webservices?

Hi Frode,

Nope, there is no provider that searches just the associate table. It's pretty simple to whip one up though and call it via Find.asmx. Here is an example writing one just for contacts only (no implicit join on Persons as the Find contact does):

https://community.superoffice.com/en/forums/ShowPost.aspx?PostID=7937

Just replace all references to ContactTableInfo with AssociateTableInfo and you're 90% of the way there.

Let me know if you need more help and I'll post it here.

Best regards.

Av: Tony Yates 17. mar 2010

Re: Searching for associate.name using webservices?

Ah, that is unfortunate.

I'm playing around with an application that is going to communicate solely with the webservices - and hopefully without having to have any custom webservices on the webserver. Kind of a prototype application to see how far I can take the standard webservices.

Frode

Av: Frode Lillerud 17. mar 2010

Re: Searching for associate.name using webservices?

There is no need for any custom web services. All you have to do is compile your own archive provider and call it via the existing web services.

Here is a very simple AssociateProvider, compile it and add it to the webservices bin directory - also open the web.config file in the webservices directory and add the assembly to the DynamicLoad list.

<Factory>

  <DynamicLoad>

    <add key="DefaultSoMail" value="SoMail.dll"/>

    <!-- Add custom plugins etc here -->

    <add key="CustomArchiveProviders" value="YourArchiveProviders.dll"/>

  </DynamicLoad>

</Factory>


Then in code, you could easiler query for an assocaite based on a name value.

IFindAgent findAgent = AgentFactory.GetFindAgent();

 

//What criteria to search on

ArchiveRestrictionInfo [] restrictions = { new ArchiveRestrictionInfo("name", "=", SOME_NAME_VALUE) };

 

//Which columns to return

string[] columns = { "associateId", "name"};

 

FindResults results =

    findAgent.FindFromRestrictionsColumns(restrictions, "AssociateProvider", columns, int.MaxValue, 0);



using System;

using System.Collections.Generic;

using System.Text;

using SuperOffice.CRM.ArchiveLists;

using SuperOffice.Data.SQL;

using SuperOffice.CRM.Data;

using SuperOffice.Data;

 

namespace SuperOffice.DevNet.Providers

{

    [ArchiveProvider("AssociateProvider", int.MaxValue / 2)]

    public class AssociateProvider : QueryProviderBase<ArchiveRow>

    {

        private AssociateExtender _associateExtender;

        private Select _select;

 

        #region Properties

        public override SuperOffice.Data.SQL.TableInfo PrimaryTable

        {

            get { return _associateExtender.TableToExtend; }

        }

 

        public override Select Query

        {

            get { return _select; }

        }

 

        #endregion

 

        protected AssociateProvider()

        {

            _associateExtender = AddAndInitializeExtensionProvider(new AssociateExtender());

            _select = S.NewSelect();

 

            RegisterEntity(new ArchiveEntityInfo("Associate", "Associate", false));

        }

 

        protected override void InnerPopulateRowFromReader(SuperOffice.Data.SoDataReader reader, ArchiveRow row)

        {

            AssociateTableInfo ati = _associateExtender.TableToExtend as AssociateTableInfo;

            int associateId = reader.GetInt32(ati.AssociateId);

            //assuming nav=pageName, row link hint for single & double click operations

            row.AddLinkHint("nav=associate&associate_id=" + associateId);

        }

    }

 

    public class AssociateExtender : DevNetAssociateExtenderBase

    {   

        internal AssociateExtender()

        {

            SetIconHint(Constants.IconHints.Associate);

        }

 

        protected override AssociateTableInfo SetJoin()

        {

            return TablesInfo.GetAssociateTableInfo();

        }

 

    }

 

    public abstract class DevNetAssociateExtenderBase : ExtenderBase<AssociateTableInfo>

    {

        private AssociateTableInfo _assocTableInfo;

 

        #region Columns to be picked up by reflection

        protected ArchiveColumnInfo _colAssocId = new ArchiveColumnInfo("associateId", "[SR_ADMIN_ASSOCIATE_ID]", "[SR_ADMIN_ASSOCIATE_ID_TOOLTIP]", Constants.DisplayTypes.Int, AllowOrderBy, Invisible, ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int);

        protected ArchiveColumnInfo _colName = new ArchiveColumnInfo("name", "[SR_ADMIN_ASSOCIATE_NAME]", "[SR_ADMIN_ASSOCIATE_NAME_TOOLTIP]", Constants.DisplayTypes.String, AllowOrderBy, Visible, ColumnHelper.DefaultStringWidth, Constants.RestrictionTypes.String);

        protected ArchiveColumnInfo _colRank = new ArchiveColumnInfo("rank", "[SR_ADMIN_ASSOCIATE_RANK]", "[SR_ADMIN_ASSOCIATE_RANK_TOOLTIP]", Constants.DisplayTypes.Int, AllowOrderBy, Visible, ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int);

        protected ArchiveColumnInfo _colToolTip = new ArchiveColumnInfo("tooltip", "[SR_ADMIN_ASSOCIATE_ASSOC]", "[SR_ADMIN_ASSOCIATE_ASSOC_TOOLTIP]", Constants.DisplayTypes.String, AllowOrderBy, Visible, ColumnHelper.DefaultStringWidth, Constants.RestrictionTypes.String);

        protected ArchiveColumnInfo _colDeleted = new ArchiveColumnInfo("deleted", "[SR_ADMIN_ASSOCIATE_DELETED]", "[SR_ADMIN_ASSOCIATE_DELETED_TOOLTIP]", Constants.DisplayTypes.Checkbox, AllowOrderBy, Visible, ColumnHelper.DefaultCheckboxWidth, Constants.RestrictionTypes.Bool);

        protected ArchiveColumnInfo _colGroupIdx = new ArchiveColumnInfo("groupIdx", "[SR_ADMIN_ASSOCIATE_GROUP_ID]", "[SR_ADMIN_ASSOCIATE_GROUP_ID_TOOLTIP]", Constants.DisplayTypes.String, AllowOrderBy, Visible, ColumnHelper.DefaultStringWidth, Constants.RestrictionTypes.ListAny, TablesInfo.GetUserGroupTableInfo().Definition.Name);

        protected ArchiveColumnInfo _colPersonId = new ArchiveColumnInfo("personId", "[SR_ADMIN_ASSOCIATE_PERSON_ID]", "[SR_ADMIN_ASSOCIATE_PERSON_ID_TOOLTIP]", Constants.DisplayTypes.Int, AllowOrderBy, Invisible, ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int);

        protected ArchiveColumnInfo _colType = new ArchiveColumnInfo("type", "[SR_ADMIN_ASSOCIATE_TYPE]", "[SR_ADMIN_ASSOCIATE_TYPE_TOOLTIP]", Constants.DisplayTypes.Int, AllowOrderBy, DenyOrderBy, ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int);

        #endregion

 

        protected override void InnerModifyQuery()

        {

            _assocTableInfo = SetJoin();

 

            MapIdField(_assocTableInfo.AssociateId);

            MapSimpleReturnField(_assocTableInfo.AssociateId, _colAssocId);

            MapSimpleReturnField(_assocTableInfo.Name, _colName);

            MapSimpleReturnField(_assocTableInfo.Rank, _colRank);

            MapSimpleReturnField(_assocTableInfo.Tooltip, _colToolTip);

            MapSimpleReturnField(_assocTableInfo.Deleted, _colDeleted);

            MapSimpleReturnField(_assocTableInfo.PersonId, _colPersonId);

            MapSimpleReturnField(_assocTableInfo.Type, _colType);

            MapSimpleListReturnField(_assocTableInfo.GroupIdx, TablesInfo.GetUserGroupTableInfo().TableName, _colGroupIdx);

        }

 

        //UnNecessary, the base will do that from the Mapping.

        protected override void InnerPopulateRowFromReader(SuperOffice.Data.SoDataReader reader, ArchiveRow row)

        {

            if (WantColumnForOutput(_colGroupIdx))

            {

 

            }

        }

 

        public override SuperOffice.Data.SQL.TableInfo TableToExtend

        {

            get { return _assocTableInfo; }

        }

    }

}


Av: Tony Yates 17. mar 2010

Re: Searching for associate.name using webservices?

Hi Frode,

Here's one that leverages an existing associate base class. Read the comments.

using System;

using System.Collections.Generic;

using System.Text;

using SuperOffice.CRM.ArchiveLists;

using SuperOffice.Data.SQL;

using SuperOffice.CRM.Data;

using SuperOffice.Data;

 

namespace SuperOffice.DevNet.Providers

{

    /// <summary>

    /// This associate provider goes against an existing associate extender base class

    /// that leverages the AssociateCache and exposes related information from contact,

    /// email and person table - without any implicit joins. This class also incorporates

    /// an extender class (AssociateExtenderEx) to bring forward additional field data

    /// from associate table fields not exposed by AssociateExtenderBase.

    /// <remarks>

    /// The associateId field is defined in the AssociateExtenderBase class as associateDbId.

    /// </remarks>

    /// </summary>

    [ArchiveProvider("AssociateProvider", int.MaxValue / 2)]

    public class AssociateProvider : QueryProviderBase<ArchiveRow>

    {

        private AssociateExtender _associateExtender;

        private Select _select;

 

        #region Properties

        public override SuperOffice.Data.SQL.TableInfo PrimaryTable

        {

            get { return _associateExtender.TableToExtend; }

        }

 

        public override Select Query

        {

            get { return _select; }

        }

 

        #endregion

 

        protected AssociateProvider()

        {

            //obtain the initial set of fields from AssociateExtenderBase

            _associateExtender = AddAndInitializeExtensionProvider(new AssociateExtender());

 

            //obtain the additional set of fields from AssociateExtenderEx

            AddExtensionProvider(new AssociateExtenderEx());

 

            _select = S.NewSelect();

 

            RegisterEntity(new ArchiveEntityInfo("Associate", "Associate", false));

        }

 

        protected override void InnerPopulateRowFromReader(SuperOffice.Data.SoDataReader reader, ArchiveRow row)

        {

            AssociateTableInfo ati = _associateExtender.TableToExtend as AssociateTableInfo;

            int associateId = reader.GetInt32(ati.AssociateId);

            row.AddLinkHint("associate_id=" + associateId);

 

        }

    }

 

    public class AssociateExtender : AssociateExtenderBase

    {

        AssociateTableInfo _ati;

 

        internal AssociateExtender()

        {

            _ati = TablesInfo.GetAssociateTableInfo();

            SetIconHint(Constants.IconHints.Associate);

        }

 

        protected override AssociateTableInfo SetJoin()

        {

            return TableToExtend as AssociateTableInfo;

        }

 

        public override TableInfo TableToExtend

        {

            get

            {

                return _ati;

            }

        }

 

        protected override FieldInfo GetAssociateField()

        {

            return _ati.AssociateId;

        }

    }

 

    [ArchiveExtender("AssociateExtenderEx", "AssociateProvider", int.MaxValue / 3)]

    public class AssociateExtenderEx : ExtenderBase<AssociateTableInfo>

    {

        private AssociateTableInfo _assocTable = null;

 

        //no need to defined the AssociateId field, it is already defined in the

        //AssociateExtenderBase class as associateDbId.

 

        protected ArchiveColumnInfo _colName     = new ArchiveColumnInfo("name", "Name", "Associate Name", Constants.DisplayTypes.String, AllowOrderBy, Visible, ColumnHelper.DefaultStringWidth, Constants.RestrictionTypes.String);

        protected ArchiveColumnInfo _colRank     = new ArchiveColumnInfo("rank", "Rank", "Associate Rank", Constants.DisplayTypes.Int, AllowOrderBy, Visible, ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int);

        protected ArchiveColumnInfo _colToolTip  = new ArchiveColumnInfo("tooltip", "Tooltip", "Associate Tooltip", Constants.DisplayTypes.String, AllowOrderBy, Visible, ColumnHelper.DefaultStringWidth, Constants.RestrictionTypes.String);

        protected ArchiveColumnInfo _colDeleted  = new ArchiveColumnInfo("deleted", "Deleted", "Associate Deleted", Constants.DisplayTypes.Checkbox, AllowOrderBy, Visible, ColumnHelper.DefaultCheckboxWidth, Constants.RestrictionTypes.Bool);

        protected ArchiveColumnInfo _colGroupIdx = new ArchiveColumnInfo("groupIdx", "Group", "Associate Group", Constants.DisplayTypes.String, AllowOrderBy, Visible, ColumnHelper.DefaultStringWidth, Constants.RestrictionTypes.ListAny, TablesInfo.GetUserGroupTableInfo().Definition.Name);

        protected ArchiveColumnInfo _colPersonId = new ArchiveColumnInfo("personId", "Person ID", "Person Identity", Constants.DisplayTypes.Int, AllowOrderBy, Invisible, ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int);

        protected ArchiveColumnInfo _colType     = new ArchiveColumnInfo("type", "Type", "Associate Type", Constants.DisplayTypes.Int, AllowOrderBy, DenyOrderBy, ColumnHelper.DefaultNumberWidth, Constants.RestrictionTypes.Int);

 

        protected override void InnerModifyQuery()

        {

            AssociateTableInfo assocInfo = TablesInfo.GetAssociateTableInfo();

 

            // look to see if the associate table already exists in the query

            // if not, define it and map fields.

            List<TableInfo> tableInfos = new List<TableInfo>(RootQuery.GetTableInfos());

            _assocTable = (AssociateTableInfo)tableInfos.Find(delegate(TableInfo table) { return table.TableName == assocInfo.TableName; });

            if (_assocTable == null) _assocTable = assocInfo;

 

            MapSimpleReturnField(_assocTable.Name,     _colName);

            MapSimpleReturnField(_assocTable.Rank,     _colRank);

            MapSimpleReturnField(_assocTable.Tooltip,  _colToolTip);

            MapSimpleReturnField(_assocTable.Deleted,  _colDeleted);

            MapSimpleReturnField(_assocTable.PersonId, _colPersonId);

            MapSimpleReturnField(_assocTable.Type,     _colType);

            MapSimpleListReturnField(_assocTable.GroupIdx, TablesInfo.GetUserGroupTableInfo().TableName, _colGroupIdx);

        }

 

        protected override AssociateTableInfo SetJoin()

        {

            return _assocTable;

        }

 

        public override SuperOffice.Data.SQL.TableInfo TableToExtend

        {

            get { return _assocTable; }

        }

    }

}



Best Regards
Av: Tony Yates 18. mar 2010

Re: Searching for associate.name using webservices?

Thanks Tony, good stuff - but I'll see how far I come with just the plain webservices first.

The webservices should have had a "GetMyAssociate()" call in the Associate.asmx webservice, that would have helped.

I'll see if I can work around it by calling Contact.asmx->GetMyBizCard(), look through the Contact.Persons returned for anyone with the given AssociateName and then maybe call Associate.asmx->GetAssociate() once I've got the Associate_id.
I'm trying to get the CurrentAssociate, i.e. the associate that I use in the SOCredentialsHeader.

Frode

Av: Frode Lillerud 18. mar 2010

RE: Searching for associate.name using webservices?

Hi Frode and Tony,

I know this is an old post, but I'm having kinda the same issue with the REST API:

I need to update the Associate of a list of Contacts and I only have the User IDs at my end.

Is there any way I can query for the Associate IDs of Users in one shot? Or do I need to call the REST API for each User ID as in /api/v1/User/{UserID} and get the Associate ID from there?

Currently when I query the /api/v1/User/ endpoint I only get a subset of around 9 users out of the 685 total associates that I see in the User Interface. Can't figure out why that is?

It seems like an /api/v1/Associate/ endpoint would be nice.

Or maybe there is a way to map the User ID to => Associate.Name?

Best regards,
Jakob

Av: Jakob Engelbrecht Olesen 26. mar 2018

RE: Searching for associate.name using webservices?

Hi Jakob, maybe a silly question, but isn't UserID the same as the Associate ID...?

Av: Frode Lillerud 26. mar 2018

RE: Searching for associate.name using webservices?

And when it comes to the missing users - what happens if you re-save one of the missing users inin WebAdm? Does it the appear in the REST call? It is a common problem that all users needs to be saved again after upgrading to 8.1+ for them to be listed properly.

Av: Frode Lillerud 26. mar 2018

RE: Searching for associate.name using webservices?

Hi Jakob,

When you say User ID, do you mean username? User ID to me is the same as Associate ID, but since your question involves a look up to get associate IDs, then I will assume you mean username. 

You can get a list of associateIds using the Agent REST-ish endpoints.

Use the GetArchiveListByColumns2 URL to POST a body containing the archive provider query details:

https://sod.superoffice.com/Cust12345/api/v1/Agents/Archive/GetArchiveListByColumns2
{
    "ProviderName": "InternalUsers",
    "Columns": "associateDbId,assocName",
    "Restrictions": "associateDbId > 0",
    "Entities": "all",
    "Page": 0,
    "PageSize": 500
}

You will get back a list of all associates, well the first 500 anyway, with the fields you specified in Columns property. See here for all columns available from the InternalUsers archive provider.

Hope this helps!

Av: Tony Yates 26. mar 2018

RE: Searching for associate.name using webservices?

Hi both,

Thanks for your prompt replies! :)

Sorry about the confusion regarding the user's ID and "User ID", but "User ID" is what the WebAdm displays as the column heading for Associates in the Users tab (and also what the reference for InternalUsers Archive Provider displays under assocName: User ID : User ID:)

But yes, by "User ID" I mean "User name" / "Associate Name".

@Frode: Thanks for the tip regarding re-saving the user in the WebAdm, but unfortunately that doesn't seem to make any difference.

@Tony: Thanks for the hint for the Agents REST-ish endpoints.
I actually tried something similar yesterday through the actual REST/Archive endpoint: /api/v1/Archive/InternalUsers?$select=associateDbId,assocName
That DOES return me the correct data/structure, but it suffers from the problem mentioned above, in that it only returns me 9 of the (hundreds of) total associates.

The REST-ish endpoint (GetArchiveListByColumns2) DOES work in the sense that it DOES returns all the rows, but the data structure is quite verbose compared to the actual REST endpoint (and I have to parse the DisplayValue for INTs "[I:10]"etc).
But I guess I'll have to use that for now...

I'm quite puzzled that the 2 endpoints doesn't return the same number of records? Is that a bug or intentional?

 

Av: Jakob Engelbrecht Olesen 27. mar 2018

RE: Searching for associate.name using webservices?

Try the /api/v1/user/ endpoint.

It uses the AllUsers archive provider.

Read the nice documentation to see all the tweaks that can be made to the archive provider output.

Av: Christian Mogensen 27. mar 2018

RE: Searching for associate.name using webservices?

Hi Christian,

As I wrote in my first post:Currently when I query the /api/v1/User/ endpoint I only get a subset of around 9 users out of the 685 total associates that I see in the User Interface. Can't figure out why that is?"Do you know if there is a bug in the AllUsers provider? It seems to work with the InternalUsers provider as suggested by Tony:

 /api/v1/Agents/Archive/GetArchiveListByColumns2

That gives me all records, but suffers from bloated data and the need to parse etc.

To me it seems like an error that the 2 doesn't produce the same number of records...

//Jakob

Av: Jakob Engelbrecht Olesen 28. mar 2018

RE: Searching for associate.name using webservices?

Hi Jacob,

I suspect this is an upgraded database and that you may experiencing this bug - if you select all in the admin - user list, do you see them there?

Try to check this query: 

select * from crm7.LICENSEASSOCLINK where moduleLicenseId in (
select modulelicense_id from crm7.MODULELICENSE where tooltip like 'user plan%') order by validTo

Now - is validTo 9999-12-31 23:59:59.000 or 1760-01-01 00:00:00.000 - to make them appear they should have 9999-12-31 23:59:59.000

Av: Margrethe Romnes 28. mar 2018

RE: Searching for associate.name using webservices?

Margrethe, if the crm7.licenseassoclink.validTo is set to 1760-01-01 in crm7.licenseassoclink, I assume the value cannot be updated via SQL, since there is a 'encryptedCheck' column in the same table that should check for tampering?

Av: Frode Lillerud 28. mar 2018

RE: Searching for associate.name using webservices?

Hi Frode,

no you can't update it using SQL - as you say it will fail the encrypted key check

What you need to do is force a save on the associate in the SOAdmin - just pressing the save button is not enough:

You need to make the associate record dirty first by changing something - EG change their UserLevel, press save, change the user level back and save again.

 

That should then also fix the filtered list counts

Cheers James

Av: James Carter 28. mar 2018

RE: Searching for associate.name using webservices?

Hi all,

Thanks for your suggestions :)

@Margrethe: You're right, this is excactly the bug I'm seeing and yes I do see all 685 users in the web admin interface. The 9 records that DOES show up in the query are excactly the ones with the validTo date set to 9999-12-31 23:59:59.000.

@Frode @James: I've also confirmed that if I change the user level in the web admin interface and save (and change it back again) that the LICENSEASSOCLINK.validTo gets updated to 9999-12-31 23:59:59.000 and the user thereby shows up in the queries.

Is there any other (read: automated) fix for this bug than manually updating each of the 685 users?

Cheers,
Jakob

Av: Jakob Engelbrecht Olesen 28. mar 2018

RE: Searching for associate.name using webservices?

Hi Jakob, 

if you have the "SuperOffice Expander Services" license, then you should have access to running CRMScripts.

This script will loop over all associates, and save the user.

SearchEngine se;
se.addField("associate.associate_id");
se.addCriteria("associate.type", "equals", "0");
//se.addCriteria("associate.deleted", "equals", "0");
for (se.execute(); !se.eof(); se.next())
{
  NSUserAgent userAgent;
  NSUser user = userAgent.GetUser(se.getField(0).toInteger());
  log("About to resave user: " + user.GetName());
  user = userAgent.SaveUser(user);  
}

printLine("Done, all users have been saved.");
Av: Frode Lillerud 29. mar 2018

RE: Searching for associate.name using webservices?

Hi Frode,

just asking out of interest as I haven't used CRMScripts (I've only just been allowed to upgrade from 7.1!)

Will that code actually perform a save and a regeneration of the missing Associate Licence Module Link Rows (LicenseAssocLink Table) as the userAgent isn't actually dirty as no data has been changed

Cheers James

Av: James Carter 29. mar 2018

RE: Searching for associate.name using webservices?

Yes, that script is used as part of our standard routine when upgrading customers. It has a proven trackrecord :)

It is also common that this script will fail on some users, typically for retired associates that still has open tickets, which is no longer allowed. So some manual intervention is required.

Av: Frode Lillerud 29. mar 2018

RE: Searching for associate.name using webservices?

Hi Frode,

Thanks for your suggestion, I'll look into that.

Best regards,
Jakob

Av: Jakob Engelbrecht Olesen 4. apr 2018