Banishing Shadows in eConnect

While I prefer working through GP Web Services, sometimes the functionality you want/need simply isn’t there. Many people drop from the Web Services directly to table access, but I prefer seeing if I can’t get what I need through eConnect instead. Generally speaking, I can.

Unfortunately, the eConnect SDK documentation is sparse and only really useful for simple cases. Raw schema files and cryptic explanations are par for the course and this can be frustrating.

Pulling a Customer Card

So, I’m working on an application that requires that I take a Universal Locator Id and walk that back to the customer I need to ship something to. For reasons buried in the depths of time, the UL is stored on the COMMENT2 field of the Customer Card. Since GP Web Services doesn’t expose COMMENT2 in its search criteria, I dropped back to eConnect.

Simple right? I mean, the request document has a “WhereClause” element for just that purpose so how hard can it be? Here’s the documentation:

So I cranked up my eConnect library project and came up with the following code:

private RQeConnectOutType getRequest(string CustomerUL)
{
	eConnectOut outDoc = new eConnectOut()
	{
		DOCTYPE = "Customer",
		OUTPUTTYPE = 1,
		INDEX1FROM = "A001",
		INDEX1TO = "Z001",
		WhereClause = string.Format("COMMENT2 = '{0}'", CustomerUL)
	};
	RQeConnectOutType outType = new RQeConnectOutType()
	{
		eConnectOut = outDoc
	};
	return outType;
}

The only problem is, this returns every customer record. All the time. And it does so for every alternative expression I could think of. Questioning my assumptions on this to get it to work was an exercise in futility.

And no amount of search-fu helped me find a satisfactory answer to getting this to work. As far as the internet is concerned, everybody who tries this either gives up or doesn’t have to be told how to get it working.

Getting it to Work

Well, I eventually got it to work, and I thought I’d share so the next schmuck who goes through this doesn’t have to suffer through the despair I did to get there (at least, not if their search engine of choice can point him here).

The key to this working is to tell eConnect that you don’t want to work from the “shadow” tables. What’s a shadow table, you ask? Well, I can’t be certain, but I think that it refers to the e_Connect_Out table stuck in your database by eConnect. This table has summary records that you might want to work with in eConnect, with details on where to get the full record. I can’t tell you for certain because the SDK documentation doesn’t actually have a section explaining shadow tables.

The problem is that when working with the shadow tables, things that aren’t in an index field aren’t really available for filtering purposes. Thus, my WhereClause referring to COMMENT2 doesn’t do a thing because the shadow table doesn’t know from COMMENT2. The fix for this is to use the woefully misnamed element “FORLIST”. Here’s what the documentation says:

Well, hoodyhoo! That’s exactly what I needed (though I didn’t know it until I tried it). So here’s the code that actually works:

private RQeConnectOutType getRequest(string CustomerUL)
{
	eConnectOut outDoc = new eConnectOut()
	{
		DOCTYPE = "Customer",
		OUTPUTTYPE = 1,
		FORLIST = 1,
		INDEX1FROM = "A001",
		INDEX1TO = "Z001",
		WhereClause = string.Format("COMMENT2 = '{0}'", CustomerUL)
	};
	RQeConnectOutType outType = new RQeConnectOutType()
	{
		eConnectOut = outDoc
	};
	return outType;
}

I’ve bolded the only change. Not only does this work, but it works fast. So now you know. If you want your filter to work in the WhereClause and it just won’t take, try pointing it at the real tables. I know that “FORLIST” is the obvious place to do this, but I thought I’d point it out, anyway (in case I forget).