Request a topic or
contact an Arke consultant
404-812-3123
June 2010

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments

Archive

Authors

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Integration with Fedex Ship Manager

Fedex Ship Manager has a nice option to map your field values to their field values… but they sure don’t make it easy to track down what their field values should really be in the underlying data. 

The help file includes a basic definition of the data types under “Creating and Editing Profiles / FedEx Ship Manager Data Fields”.  This tells you we have a 2 digit numeric column for Package Type and Service Type.  But it doesnt tell you what 2 digit numbers to use.

screenshot[4]

 

 

The current user manuals don’t seem to mention valid values.  What you want to track down is the “Transaction Coding Reference”.  An old one is at http://fedex.com/us/solutions/FSMS_v_7_6_TCR_081505.pdf ; for the latest you'll need to register at http://www.fedex.com/us/developer/index.html then you should be able to download https://www.fedex.com/wpor/web/jsp/drclinks.jsp?links=fsms/pdf/100/FSMS_1001_TCR_Aug2009.pdf

Also take a look at the user guide, I find it a bit more clear about rules like for example if using FedEx Ground or FedEx Home, Package Type must be 01 – Customer Packaging for FedEx Ground.  http://fedex.com/us/solutions/FSMS_v_7_6_User_Guide_081505.pdf ; I don't know where the current version of that doc lives

Finally, note that FedEx uses 2 char IATA country codes, while a lot of other systems use 3 char codes.

Page 39 of the TCR has packaging type codes:

Available for all:
01 – Customer Packaging
02 – FedEx Pak
03 – FedEx Box
04 – FedEx Tube
06 – FedEx Envelope
FedEx International Only
(including U.S. export):
15 – FedEx 10KG Box
25 – FedEx 25KG Box

And our service types are:

The service type provided by the shipper.
Valid values:
U.S. Domestic Services:
01 – FedEx Priority Overnight
03 – FedEx 2Day
05 – FedEx Standard Overnight
06 – FedEx First Overnight
20 – FedEx Express Saver
*70 – FedEx 1Day Freight
*80 – FedEx 2Day Freight
83 – FedEx 3Day Freight
90 – FedEx Home Delivery
92 – FedEx Ground
FedEx International Services (including U.S. export):
01 – International Priority
03 – International Economy
06 – International First
57 – FedEx Europe First (See the FedEx Service
Guide for served countries.)
*70 – International Priority Freight
*86 – International Economy Freight


Posted by David Eison on Wednesday, June 16, 2010 11:49 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics CRM 4 and systemuserid

Sometimes you need to know how User ID works “under the hood.”

We see ‘systemuserid’ all over the database identifying a user … but how does it decide which systemuserid to use?

CRM appears to figure out which user is logging in by querying the MSCRM_CONFIG database systemuserauthentication table looking for a value in the authinfo column that matches the objectsid for the user from activedirectory (SID, *not* GUID).

You can get the user SID (and GUID) from active directory by registering a dll from the windows server 2003 resource toolkit ( http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe ) on a 32-bit OS (I don't know how to make it work on 64 bit; docs say 64 bit is not supported) - install toolkit, copy acctinfo.dll to c:\windows\system32, "regsvr32 acctinfo.dll" , then browse to user in AD ( start/run/dsa.msc ) and look at the 'additional account info' tab (doesn't show up when user is found using find, unfortunately).

Then you can dig up the entry for the sid in systemuserauthentication, which gives you a userid, which you can find in systemuserorganization, which gives you a crmuserid, which you can go find as systemuser in the [orgname]_mscrm database (which is NOT the same as systemuser in the mscrm_config database).

If you have a problem with a mismapping between the two databases (for example, somebody mistakenly doing a backup of one but not the other), there are two easy ways to fix the problem:

The first thing to try is to edit the user the in the normal CRM interface and change the AD login name to a not-yet-used-by-CRM value, save the entity, then change it back to the value you want to be used.  This often cleans up a broken mapping.

The next thing to try is to use the crm deployment manager to export and import the organization; during the import there is a user mapping step where you can have CRM set up these relationships properly for you.

Finally, filtered views have their own method of identifying the user – they use [fn_FindUserGuid] which checks for a GUID on context_info(), and if not found, they query SystemUserBase where DomainName = SUSER_SNAME() .

I recommend against trying to operate on this data at the raw sql level, system user id is simply referenced in too many places to reliably get everything.  You would end up with orphaned records at best, for example due to all the ‘createdby’ and ‘modifiedby’ references on every entity.  So ‘select’ is fine to learn things, but ‘delete’ or ‘update’ at your own risk and preferably not even then.  Still, if you have to, the AD SID is where to start.


Posted by David Eison on Monday, June 7, 2010 5:45 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Sitecore: A Simple Site Map

Similar to creating a menu – creating a site map is even easier, there are modules that can do this but with a little preparation it can be very easy to create yourself.

To start we are assuming every template used for the pages includes some ‘base template’ for common aspects for all pages.

To do this, create (or select if already created) the Base Template with the information you want to have and to that base template, add:

InSiteMap: Checkbox

Then create or modify the Page templates with the other information you want. From those templates, click to the Content tab, and select the Base template to be included:

 

sitecoretemplatecontent

In the Standard values for the base template, you may want to have InSiteMap checked so that it will be on by default for all the pages.

For the sitemap itself, we’re going to create a rendering and then we can place it wherever we would like.

The rendering creates a basic nested list of all the items that have InSiteMap checked.

<xsl:template match="*" mode="main">
    <ul>
      <li>
        <sc:link select="$home">
          Home
        </sc:link>
        <xsl:variable  name="hasSubItems" select="$home/item[sc:fld('InSiteMap',.) = '1']"/>
        <xsl:if test="$hasSubItems">
          <xsl:call-template name="subitems">
            <xsl:with-param name="itemparent" select="$home" />
          </xsl:call-template>
        </xsl:if>

      </li>
    </ul>
  </xsl:template>

  <xsl:template name="subitems">
    <xsl:param name="itemparent" />
    <ul>
      <xsl:for-each select="$itemparent/item[sc:fld('InSiteMap',.) = '1']">
        <li>
          <sc:link>
            <sc:text field="Page Title" />
          </sc:link>
        </li>
        <xsl:variable  name="hasSubItems" select="item[sc:fld('InSiteMap',.) = '1']"/>
        <xsl:if test="$hasSubItems">
          <xsl:call-template name="subitems">
            <xsl:with-param name="itemparent" select="." />
          </xsl:call-template>
        </xsl:if>
      </xsl:for-each>
    </ul>
  </xsl:template>

Above we list the link to the home item in an unordered list:

 <sc:link select="$home">
    Home
 </sc:link>

<sc:link> with no parameters will link to the current item that is being processed by the xslt file – if not specified by any settings this will default to the page that the rendering is on. Since the current page is Not actually the home page, we are choosing to use $home as the source for the link (if the source for the link was actually in a field in the item you’d used field=”field name”).

 

Moving on, if there are any items under home that are listed as ‘InSiteMap’ it will call our template named subitems and pass the value for the parent of the item.

        <xsl:variable  name="hasSubItems" select="$home/item[sc:fld('InSiteMap',.) = '1']"/>
        <xsl:if test="$hasSubItems">
          <xsl:call-template name="subitems">
            <xsl:with-param name="itemparent" select="$home" />
          </xsl:call-template>
        </xsl:if>

 

Here the variable hasSubItems is created and the value for this is determined by any items within $home containing the field InSiteMap being checked (=’1’ – unchecked would be =’0’).

If we have any items that will be in the sitemap under $home, we call our template.

Within the template, we create a new unordered list, and then go through each item which will be listed in the site map and display a link using the ‘Page Title’ (this is any field that we want to have displayed to represent the item). We then check to see if we have any subitems to display and run the template again! With a bit of recursion, we head through all the items on the site as long as they and their parents have InSiteMap checked.

All that is left is adding the rendering to a page to be seen, and it will display a nested list of the pages on the site to be styled as needed!

Alternately, instead of checking for a selected checkbox, it could run through all pages with a specific template(or selection of templates) if you did not need to worry about excluding any specific pages that are using it.


Categories: Sitecore
Posted by Amy Winburn on Friday, June 4, 2010 5:29 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics CRM and Editing data in Excel

Customers sometimes need a good way to bulk edit data in CRM. 

One solution is to export the data, adjust it outside of CRM in a tool like Excel, then re-import it.

You can get an ms crm import tool from http://mscrmimport.codeplex.com/ (read about it at  http://blogs.msdn.com/b/crm/archive/2008/01/25/data-manipulation-tool.aspx )

A more powerful and user friendly Excel editing tool is EBAX: http://www.mscrmexcel.com/

In general I think EBAX is a good compromise of raw editing-in-Excel power and CRM's use-our-web-services restrictions, so I recommend it particularly to clients who have a few users who need some powerful or bulk access. 

The only consideration I’m aware of for a bulk editing tool in a deployment is that it’s important to train both the power users and developers that fields which were disabled via Javascript aren’t really disabled – users should avoid editing a custom calculated total field, and developers should avoid expecting a user to be unable to edit a custom calculated total field simply because it was disabled on the web form.


Posted by David Eison on Tuesday, June 1, 2010 1:22 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics 4.0 and SQL Updates

Customers often want to know why they aren’t supposed to update their data via raw SQL and are instead directed to use the CRM web forms or the SDK.

These are the reasons I am aware of:

1) Various important parts of CRM rely on C# code that will only run properly if everything is accessed through the web services (plugins and workflows)

2) Microsoft wants to be able to change the inner workings in future upgrades, and if everyone developed relying on particular columns being in particular tables upgrades would become impossible.

3) It's a complicated system and it's easy to miss a detail, such as the isdeleted column or state codes or timezone conversions.  These details can be abstracted away for read only access when querying against the filtered views, which automatically removed columns with the deleted flag and convert utc timezones to local timezone and check that the user querying has permission to view the record, etc.  But you can't readily do such abstraction for updating.

4) There is a security model in place that gives each user permissions to do certain things based on roles granted to them, and if you rely on working as a priviledged user in the database you might accidentally circumvent that. 

5) Some calculated fields like totals are 'not valid for update', but this is only enforced at the web services level (for example, totals can’t be directly updated because any value you might try to submit will be overwritten by a plugin calculating it).

6) There is an added level of complexity at the raw database level - base vs extensionbase tables, for example - that is more difficult to work with.

7) One wrong value can lead to system errors.  For example, deleting a row from the extensionbase but not the base table, or setting a picklist to an impossible value, can cause spurious errors when later trying to work with the information through the normal interface.

8) Schema changes in particular are tracked with various metadata that you really don't want to get into and you can break everything in very difficult to recover ways by manually changing column definitions.


Posted by David Eison on Tuesday, June 1, 2010 1:15 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics CRM 4.0 and SQL selects

Customers also want to know why they are supposed to use ‘Filtered’ views to query their CRM data directly from the database.

These are the reasons I’m aware of for using filtered views:

The filtered views do five things for you:

1) They are ‘fully supported’, meaning that they shouldn’t break on upgrades. 

2) Convert times from utc to user’s local timezone (UTC times are still available in the filtered views, but the default time column is changed to local time)

3) Filter out records flagged as to be deleted.

4) Filter out records the current user doesn't have permission to see.

5) Clean up and hide a bunch of implementation details, like mapping state codes from ints like 0 to strings like 'Active'.

The only drawback is that filtered views may slow down queries as they join in a lot of tables to present their data, and you will want to keep track of timezone conversion challenges (if a user entered a time in eastern daylight time, it’s stored in UTC/Greenwich Mean, then the querying user is in Australia… what day did the event happen on?)


Posted by David Eison on Tuesday, June 1, 2010 1:13 PM
Permalink | Comments (0) | Post RSSRSS comment feed