Trust Indicators for Companies in B2B Transactions

• Download Source Code - 636.6 KB

Introduction

This article describes an application that uses data from the D&B sandbox. I previously wrote an idea for an app and was encouraged to make an application and submit it to the contest. This application targets business users, while the former application's target audience is consumers.

Background

Companies with mobile teams working at remote job sites may continuously add new vendors to their general ledger for accounts payable. This application provides a way for such a company to get an indication of whether a newly entered vendor is trustworthy or questionable. This may prevent fraud at companies where it would otherwise be possible for superintendents to enter bogus vendors and siphon off company funds. It could also reveal issues that were overlooked when selecting a vendor or increase trust for reliable vendors.

In a complete implementation, this information would be retrievable from within a given accounting system (via a plugin) or made available by importing a batch of vendor information. As part of this submission, a demo site is available where vendors can be submitted one at a time. It is left as an exercise to the reader to implement a plugin for their favorite accounting software.

As this is just a demonstration application, there is no warranty made by the author as to the accuracy of the data or its fitness for any particular purpose (even for the ones suggested above).

Overview

This article is divided into several sections.

1. Connecting to the D&B Sandbox
2. Getting Started
3. Autocomplete and Validation
4. Analyzing Indicators
5. HTML Helpers 
6. Conclusion 
7. Points of Interest

Connecting to the D&B Sandbox

Signing up for access to the D&B Sandbox is rather straight-forward, especially if you have a Microsoft Account.

After signing in, you be able to create a Marketplace account. Note: you don't have to agree to receive offers from Microsoft when creating that account.

When I was reading the Marketplace Terms of Use, I found a construct that seemed to preclude using the data for testing and development. "Marketplace Services is for COMMERCIAL PURPOSES ONLY" If this concerns you, contact Microsoft for clarification of their intention.

Afterward, you're able to sign up for access to the D&B sandbox and will receive a 12 month license grant to use and display that data. Unfortunately, however, the license only allows internal use (by you and your employees) and doesn't allow a developer to expose the data to a third party.

NOTE: I'm hoping that Chris Pardo (D&B Developer Evangelist) will comment about this predicament. Without additional license to display this data to third parties, anyone using the hosted demo site would have to enter their own API key to use the site. UPDATE: Given the other information in the contest that encourages using and sharing the data, I've decided to proceed and make my application available online.  

If you continue with the free transaction, you'll be shown a receipt that includes several suggestions for getting started with the data.

At this point you can view the data online, analyze it in Microsoft Excel, or connect to it as as a strongly-typed dataset. To do the latter, first launch Visual Studio and load a project. Then, follow these steps.

Click Project -> Add Service Reference

Past in the API Address as shown.

You can click Go to see the list of services available at that address.

Enter a namespace (for example, DeveloperSandbox) and click OK.

The Address that you provided will be used to generate proxy classes that will be used to connect to the data store.

Getting Started

I do not usually create unit tests for all of my code, but it felt like creating test methods would help me iterate more quickly as I tried to come up with the proper queries and connection parameters for the sandbox. I had to do a bit of discovery because I couldn't find any documentation. It turns out that the competition page includes links to videos that demonstrate how to get started (and an extension in my browser was removing the links). If you would prefer to watch a video (over reading this section), you may find those videos helpful.

I'll continue to explain how I proceeded by using the guess-and-test method. I created a simple test method that would instantiate an object which would be used in a fashion similar to a repository. Note that my test methods are akin to integration tests as I don't disconnect the tests from the underlying data connection; I'm using them to explore the data in advance to creating the user interface.

[TestMethod]
public void Construct_CompanyData()
{
    var data = new CompanyData();
    Assert.IsNotNull(data);
}

Within the constructor of the CompanyData class, I instantiate the class that was created by the Service Reference dialog (to connect to the sandbox). You'll need the assigned account key to access the data.

public CompanyData()
{
    serviceUri = new Uri(SERVICE_ROOT_URL);
    context = new DNBDeveloperSandboxContainer(serviceUri);
    // We try to make our app a little more robust in case of changes to the API
    context.IgnoreMissingProperties = true;
    // It appears like you can pass your live account ID or your API key as the username.
    context.Credentials = new NetworkCredential(Properties.Settings.Default.DeveloperSandboxApiKey,
        Properties.Settings.Default.DeveloperSandboxApiKey);
}

The first test ran without a hitch, but I wasn't actually opening the context, and the next test required refining the code.

[TestMethod]
public void GetDuns_FromCompanyName()
{
    var data = new CompanyData();

    string actual = data.GetDunsNumber("INDUSTRIOUS CARDIFF LTD");
    const string expected = "234155468";
            
    Assert.AreEqual(expected, actual);
}

I began trying all kinds of strategies for querying the sandbox before I determined that I would not be able to look up a company by name. The reference guide, which includes a list of filtering options, doesn't show an index on company name anywhere. We certainly need this feature for this application, so I created a DunsLookup helper class that would return the DUNS number, given a company name. The sample data in the sandbox includes more than one entry for some companies -- THE SALVATION ARMY and PAYLESS SHOESOURCE, INC as examples -- so I simplified the data by including only a couple thousand entries. I think these concessions were reasonable given the constraints of the sandbox. See the discussion at the bottom of the page. 

The GetDunsNumber() method is as follows:

public string GetDunsNumber(string query)
{
    if (String.IsNullOrEmpty(query))
        throw new ArgumentException("query is null or empty.", "query");

    if (DunsLookup.Companies.ContainsKey(query))
    {
        string number = DunsLookup.Companies[query];
        return number;
    }

    return null;
}   

Autocomplete and Validation

With a reasonable way to begin querying the sandbox, I began to develop the user interface and added autocompletion and some validation. This ensures a more pleasant user experience. 

Autocompletion probably wouldn't be needed if this were an enterprise solution that was importing the accounts payable from a financial software package. Since users of the demo site will enter vendors one at a time, this feature can be rather convenient. Also, since we only have access to a subset of business entities, we are helping prompt the user to select a company for which there is some data. 

I brought in bootstrap by using a nuget package. Then I added an ApiController that could return autocomplete suggestions and I wired it up using a bit of JavaScript.

To provide some validation, I create a struct that would be accepted when the form is posted back. This struct is decorated with DataAnnotations.

public struct AccountPayable
{
    public AccountPayable(string vendor, int? amount): this()
    {
        Vendor = vendor;
        Amount = amount;
    }

    [Required]
    public string Vendor { get; set; }

    [Range(0, 10000000)]
    public int? Amount { get; set; }
} 

The form on the home page will send its data to the Details action which verifies the ModelState is valid as follows:

public ActionResult Details(AccountPayable account)
{
    if (ModelState.IsValid)
    {
        // ... clipped code that gets indicators
        return View(indicators);
    }

    return View("Index");
}  

And the view renders a validation summary when needed.

Should the user opt not to use autocomplete, they may enter a company that isn't in our database. In that case, we show this message:

Analyzing Indicators

The way in which this app provides value to businesses is by surfacing indicators that might suggest a particular vendor is not trustworthy, or help reassure the business that a given vendor seems like a legitimate one. We surface a number of indicators based on the information available in the sandbox and thoughtful consideration of how to interpret the data. Each of these indicators has an associated enum that indicates whether the indicator is informational, a warning, an error, or a positive indicator. 

Some of these indicators are pretty easy to calculate with data from a simple query. For example, we compare the payment amount with the company's annual sales to suggest whether the payment amount might be too large. If there is a negative indicator here it is bubbled up to the user.

public IEnumerable<Indicator> GetFirmographicIndicators(string dunsNumber, int amount)
{
    var q = from r in context.Firmographics
            where r.DUNSNumber == dunsNumber
            select r;

    var data = q.FirstOrDefault();
    if (data == null)
    {
        yield break;
    }

    Nullable<double> sales = data.AnnualSalesUSDollars;
    if (sales.HasValue && sales.Value > 0.0)
    {
        // It might be a risky situation if this payment represents 50% of this firm's revenue.
        if (amount >= sales.Value / 2)
        {
            yield return new Indicator(Levels.Warning, String.Format("It appears you are becoming this firm's largest client. Total sales last year were {0:c0}.", sales));
        }
        else if (amount >= sales.Value / 24)
        {
            yield return new Indicator(Levels.Info, String.Format("If this amount represent a monthly payment, it appears you are becoming this firm's largest client. Total sales last year were {0:c0}.", sales));
        }
    }
} 

The code uses yield statements to simplify returning an IEnumerable<Indicator>. If the situation warrants, the user would now see this warning (in addition to any other indicators, which might also be positive).

Unavailable Records

Just like I stumbled across the situation where it is not possible to query the sandbox for DUNS numbers by company name, I found out during development that FirstOrDefault() doesn't work as expected with data in the sandbox. Instead of returning null when no data is found, an exception is thrown. Here's sample code:

var q = from r in context.Green
        where r.DUNSNumber == dunsNumber
        select r;

var data = q.FirstOrDefault();

When the code is executed with a dunsNumber that doesn't match data available in the Green table, the server sends the following response:  

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
 <code></code>
 <message xml:lang="en-US">Resource not found for the segment 'Green'.</message>
</error>

 And an exception is thrown (instead of data being set to null):

q.FirstOrDefault()' threw an exception of type 'System.Data.Services.Client.DataServiceQueryException'
    base {System.InvalidOperationException}: "An error occurred while processing this request."

To provide the proper behavior for situations when this exception is thrown, and to provide a little future-proofing, we handle the exception and check the value for null (in case an update of the Service Reference classes or the Marketplace causes the expected behavior of null).

Here's the implemented version, which will show an informational banner if the company is a eco-friendly company. I ended up using SingleOrDefault() because it would be a strange situation if more than one record is returned for a given DUNS number, and the returned data can be ignored if that is the case:   

private IEnumerable<Indicator> GetGreenIndicators(string dunsNumber)
{
    var q = from r in context.Green
            where r.DUNSNumber == dunsNumber
            select r;

    Green data;
    try
    {
        data = q.SingleOrDefault();
    }
    catch (DataServiceQueryException)
    {
        // if the data is not present for this DUNS number,
        // a 'Resource not found for the segment' error is presently returned.
        yield break;
    }
    if (data == null)
    {
        yield break;
    }

    if ("Y".Equals(data.GreenBusinessIndicator, StringComparison.OrdinalIgnoreCase))
        yield return new Indicator(Levels.Info, "This is a 'Green' company.");
} 

Be sure to check out the code or the demo app if you'd like to see the implementation of additional indicators. There are 16 indicators in all.  

Examples 

Industrious Cardiff 

Tiffany & Co 

Loving Arms Mission 

H&H Heading and Cooling 

Mudmasters 

HTML Helpers 

You may have noticed from the screenshots above that the company name is a clickable, collapsed accordion. When clicked, it expands to show the company address. Since the Demographic objects returned directly from the D&B service comprise individual component parts of an address (like City), they are combined with the proper commas and formatting before being displayed.

Razor allows putting such logic directly in the view, but due to the amount of logic required it felt more natural to use an HtmlHelper extension. The helper will be passed the Demographic object, and can be called as follows:

@Html.FormatAddress(Model.Company) 

We place the FormatAddress() method in a static class and include our logic for constructing the appropriate HTML. Returning MvcHtmlString indicates that the method's output has been properly encoded and should be emitted directly on the page without being encoded.  

public static class AddressHelper
{
    public static IHtmlString FormatAddress(this HtmlHelper html, Demographics demographics)
    {
        StringBuilder sb = new StringBuilder();
        if (!String.IsNullOrWhiteSpace(demographics.Address))
        {
            sb.Append(demographics.Address);
            sb.Append("<br />");
        }
        if (!String.IsNullOrWhiteSpace(demographics.Address2))
        {
            sb.Append(demographics.Address2);
            sb.Append("<br />");
        }
        if (!String.IsNullOrWhiteSpace(demographics.City))
        {
            sb.Append(demographics.City);
        }
        if (!String.IsNullOrWhiteSpace(demographics.StateAbbrv))
        {
            sb.Append(", ");
            sb.Append(demographics.StateAbbrv);
        }
        if (!String.IsNullOrWhiteSpace(demographics.ZipCode) && demographics.ZipCode.Length>=5)
        {
            sb.Append(", ");
            sb.Append(demographics.ZipCode.Substring(0, 5));
        }
        return MvcHtmlString.Create(sb.ToString());

    }
} 

Similarly, an HTML helper is used to display the banner for each indicator.  

Conclusion 

Developing an application with D&B data can be an enjoyable and fruitful experience. With a bit of trial and error I was able to create an elegant application that provides an analysis using data from the sandbox. I hope it might be useful to you! 

Discovered a bug or thought of a companion feature? Please comment below at the bottom of the article. Codeproject is all about sharing what you've learned.

If you'd appreciate additional elaboration on a particular area, feel free to comment about that as well.

Points of Interest

It might be interesting to display the commercial credit score provided by Hoovers (a subsidiary of Dun & Bradstreet). For vendors with low scores, a short list of top competitors could be suggested (D&B also provides data outside the sandbox that provides access to top competitors).

History

7/31/2013 Added sample results and HTML Helper section.