Linq Query Questions

I have a function created to do a look up on what regional sales person we have in specific zip code. The zip codes are listed in a comma separated list in Character01 filed on my ud Table. I have key2 being my country code and key3 being my state. I have three reps that cover a state like TX.

Questions is how do i lookup the zip code in character01 and see if it is there for the correct regional sales person. Or should this be done with dynamic BAQ or anther tool.

Is there a reason you didn’t use the built-in Territory manager in Epicor? You can then use the Epicor Get Territory to return this.

Yeah we thought about it but we have Actual Sales reps and RSM that will cover the same areas and we have an additional requirement to handle with product groups as well.

You can list multiple sales reps per territory. The territory maintenance includes places to fill in zip codes.

The built-in territory manager does this. We have multiple reps on each territory. You can setup reps as Work Force and assign them to the territory.

I’m sure it’s all possible via a custom module and tables in Epicor. Just would be a lot more work.

so if i am following can i add these ud fields that i need for territory maintenance and get it working for what i want and not have it be used in the Sales Person field on the customer and ShipTo as this would be used for providing commission to our Sales Reps.

We have a Incentive plan that we pay our RSM that have different territories than our sales reps domestically and internationally. So i have identified the RSM that needs to set on an order separate from the Salesperson because the order could have both.

My Data looks like this.

image

im i am trying to return the correct ShortChar01

this is what i have trying to attempt this

RSM = Db.UD12.Where(r => r.Company == callContextClient.CurrentCompany && r.Key2 == “SEC” && r.Key3 == varCountryNum.ToString() && r.Key4 == varState && r.Character01.Contains(","+ varZip +",")).Select(r => r.ShortChar01).FirstOrDefault();

You probably shouldn’t be adding the comma’s in the contains. You just want to know if it contains the zip code right?

RSM = Db.UD12.Where(r => r.Company == callContextClient.CurrentCompany && r.Key2 == “SEC” && r.Key3 == varCountryNum.ToString() && r.Key4 == varState && r.Character01.Contains(varZip)).Select(r => r.ShortChar01).FirstOrDefault();

I think that you can use the SPLIT command combined with the contains… this way you dont have to worry about the commas, because the split will automatically take care of it. Something like this (I have not verified).

RSM = Db.UD12.Where(r => 
    r.Company == callContextClient.CurrentCompany && 
    r.Key2 == “SEC” && 
    r.Key3 == varCountryNum.ToString() && 
    r.Key4 == varState && 
    r.Character01.Split(',").ToList().Contains(varZip)
    ).Select(r => r.ShortChar01).FirstOrDefault();