Creating contact from Lookup API

[danny.little]

Is there a way to create an Order Contact from using the Lookup API? Or am I totally thinking the wrong way with this. I am particulary concerned about the underwriter, since it is used in rate calculations.

An example would be great.

Any help is greatly appreciated.

[Jeff Connelly]

Hi Danny,
Sorry for the delay in someone responding to your question.

I wasn't sure what you were referring to. Are you literally trying to create a new order contact through the Lookup API, or are you trying to add an entry to a lookup table?

To create a new order contact, you can go through the order properties. e.g.

IOrder order = OrderTracking.CreateOrder();
IUnderwriter underwriter = order.Underwriter.CreateNew();

If you're trying to add an entry to a lookup table I can pass along your question.

[danny.little]

Well, you are correct that I want to create an order contact and I do using what you stated. But I want to load that Order Contact with information from the contacts lookup tables. I hope this makes sense,

uw = order.Underwriter.createNew();
uw.LookupCode = "CM"; // this does not seem to load the contact, which i guessed

I want the underwriter/contact I create to use the Underwriter/contact information from the Underwriter/Contact Lookup table, with the lookup code "CM", being Commerce Title. I do not want to have to copy all the information of the underwriter, e.g. logo, seal image, and then have to go into the order and SoftPro add "U2" when I choose "CM" on the "Title Insurance Premiums" screen, because that is the correct one.

If this is simple, please forgive my ignorance, I am just not seeing it.

If this still does not make sense please reply.

[jbright]

The Select client will overlay all the mapped columns from a lookup table, when the designated key field for that table is modified. Unfortunately, this functionality is not exposed via the API. You can get very similar behavior by hard-coding the mapping from lookup entries to the individual elements of an underwriter. This code will be brittle to the extent that your hard-coded mappings should be revisited whenever the lookup table changes. You'll end up with a block of code like this:

Code: Select all

underwriter.SomeProp1 = row["Lookup column name for some underwriter prop 1"].Value as string;
underwriter.SomeProp2 = row["Lookup column name for some underwriter prop 2"].Value as string;
underwriter.SomeProp3 = row["Lookup column name for some underwriter prop 3"].Value as string;

[danny.little]

Here is an example since I see a lot of people looking at this. This is for other contact and Underwriter, I noticed they are not mapped exactly so i had to debug and look at all the rows columns to get the names to the Classes Properties.

Now all I need to figure out is how to put a formula in the FeeTransferLedger. Looking for that now.

And I guess to get a Trustee would have to search that also and add reference to marketing Rep, but I don't care about that one.

Code: Select all

                    LookupQuerySpec luspec = new LookupQuerySpec();
                    luspec.Table = "Other Contact";
                    luspec.Filter = "[{KEY}] LIKE @val"; // 
                    luspec.FilterParameters.Add("val", "SCBSC");
                    ILookupTable table = lookupService.QueryTable(luspec);
                    if (table.Rows.Count > 0)
                    {
                        ILookupRow row = table.Rows[0];

                        if (row["LookupCode"].Value != null) bsc.LookupCode = row["LookupCode"].Value.ToString();
                        if (row["Name"].Value != null) bsc.Name = row["Name"].Value.ToString();
                        if (row["PayeeName"].Value != null) bsc.PayeeName = row["PayeeName"].Value.ToString();
                        if (row["Address1"].Value != null) bsc.Address1 = row["Address1"].Value.ToString();
                        if (row["Address2"].Value != null) bsc.Address2 = row["Address2"].Value.ToString();
                        if (row["City"].Value != null) bsc.City = row["City"].Value.ToString();
                        if (row["State"].Value != null) bsc.State = row["State"].Value.ToString();
                        if (row["Zip"].Value != null) bsc.Zip = row["Zip"].Value.ToString();

                        if (row["FeeTransferLedger"].Value != null) bsc.FeeTransferLedger = row["FeeTransferLedger"].Value.ToString();
                        if (row["Phone"].Value != null) bsc.Phone = row["Phone"].Value.ToString();
                        if (row["Fax"].Value != null) bsc.Fax = row["Fax"].Value.ToString();
                        if (row["IsMarketingSource"].Value != null) bsc.IsMarketingSource = (bool)row["IsMarketingSource"].Value;
                        //if (row["MarketingRep"].Value != null) bsc.MarketingRep = (ITrustee)row["MarketingRep"].Value;
                        if (row["LicenseNo"].Value != null) bsc.LicenseNo = row["LicenseNo"].Value.ToString();
                        if (row["Include On Revenue Reports"].Value != null) bsc.IncludeOnRevenueReports = (bool)row["Include On Revenue Reports"].Value;
                        if (row["Bank Name"].Value != null) bsc.BankDrawnOn = row["Bank Name"].Value.ToString();
                        if (row["ABA Routing Number"].Value != null) bsc.ABARoutingNumber = row["ABA Routing Number"].Value.ToString();
                        if (row["Credit Account Name"].Value != null) bsc.CreditAccountName = row["Credit Account Name"].Value.ToString();
                        if (row["Credit Account Number"].Value != null) bsc.AccountNumber = row["Credit Account Number"].Value.ToString();
                        if (row["Email"].Value != null) bsc.Email = row["Email"].Value.ToString();
                        if (row["Special Instructions"].Value != null) bsc.SpecialInstructions = row["Special Instructions"].Value.ToString();
                        if (row["Further Credit"].Value != null) bsc.FurtherCredit = row["Further Credit"].Value.ToString();
                    }

                    IUnderwriter uw = order.Underwriter.CreateNew();

                    luspec.Table = "Underwriter - Texas";
                    luspec.Filter = "[{KEY}] LIKE @val"; // 
                    luspec.FilterParameters.Clear();
                    luspec.FilterParameters.Add("val", "CM");
                    table = lookupService.QueryTable(luspec);
                    if (table.Rows.Count > 0)
                    { 
                        ILookupRow row = table.Rows[0];

                        if (row["Lookup Code"].Value != null) uw.LookupCode = row["Lookup Code"].Value.ToString();
                        if (row["Name"].Value != null) uw.Name = row["Name"].Value.ToString();
                        if (row["Payee Name"].Value != null) uw.PayeeName = row["Payee Name"].Value.ToString();
                        if (row["Address (line 1)"].Value != null) uw.Address1 = row["Address (line 1)"].Value.ToString();
                        if (row["Address (line 2)"].Value != null) uw.Address2 = row["Address (line 2)"].Value.ToString();
                        if (row["City"].Value != null) uw.City = row["City"].Value.ToString();
                        if (row["State"].Value != null) uw.State = row["State"].Value.ToString();
                        if (row["Zip"].Value != null) uw.Zip = row["Zip"].Value.ToString();
                        //if (row["Marketing Rep"].Value != null) uw.MarketingRep = row["Marketing Rep"].Value.ToString();
                        if (row["Tax ID"].Value != null) uw.TaxID = row["Tax ID"].Value.ToString();
                        if (row["Agency ID"].Value != null) uw.AgencyID = row["Agency ID"].Value.ToString();
                        if (row["Claim Address (line 1)"].Value != null) uw.ClaimAddress1 = row["Claim Address (line 1)"].Value.ToString();
                        if (row["Claim Address (line 2)"].Value != null) uw.ClaimAddress2 = row["Claim Address (line 2)"].Value.ToString();
                        if (row["Claim City"].Value != null) uw.ClaimCity = row["Claim City"].Value.ToString();
                        if (row["Claim State"].Value != null) uw.ClaimState = row["Claim State"].Value.ToString();
                        if (row["Claim Zip"].Value != null) uw.ClaimZip = row["Claim Zip"].Value.ToString();
                        if (row["Claim Phone"].Value != null) uw.ClaimPhone = row["Claim Phone"].Value.ToString();
                        if (row["Claim Fax"].Value != null) uw.ClaimFax = row["Claim Fax"].Value.ToString();
                        if (row["Fee Transfer Ledger"].Value != null)
                        {
                            uw.FeeTransferLedger = string.Format("{0}{1:yyyy}{1:MM}", 
                                row["Lookup Code"].Value.ToString(),
                                DateTime.Now);
                        }
                        if (row["State Of Incorporation"].Value != null) uw.StateOfIncorporation = row["State Of Incorporation"].Value.ToString();
                        if (row["Marketing Source"].Value != null) uw.IsMarketingSource = (bool)row["Marketing Source"].Value;
                        if (row["Include On Revenue Reports"].Value != null) uw.IncludeOnRevenueReports = (bool)row["Include On Revenue Reports"].Value;
                        if (row["Logo"].Value != null) uw.LogoImage = (System.Drawing.Image)row["Logo"].Value;
                        if (row["Seal"].Value != null) uw.SealImage = (System.Drawing.Image)row["Seal"].Value;
                        if (row["Directors and/or Officers"].Value != null) uw.DirectorsAndOrOfficers = row["Directors and/or Officers"].Value.ToString();
                        if (row["Disclosures"].Value != null) uw.Disclosures = row["Disclosures"].Value.ToString();

                        if (row["Bank Name"].Value != null) uw.BankDrawnOn = row["Bank Name"].Value.ToString();
                        if (row["ABA Routing Number"].Value != null) uw.ABARoutingNumber = row["ABA Routing Number"].Value.ToString();
                        if (row["Credit Account Name"].Value != null) uw.CreditAccountName = row["Credit Account Name"].Value.ToString();
                        if (row["Credit Account Number"].Value != null) uw.AccountNumber = row["Credit Account Number"].Value.ToString();
                        if (row["Email"].Value != null) uw.Email = row["Email"].Value.ToString();
                        if (row["Special Instructions"].Value != null) uw.SpecialInstructions = row["Special Instructions"].Value.ToString();
                        if (row["Further Credit"].Value != null) uw.FurtherCredit = row["Further Credit"].Value.ToString();
                    }

[danny.little]

Found the SetFormula, guess I should have not jumped the gun, and good coding would be to check all values to see if they are formulas. But hey, it keeps me employeed.

Code: Select all

                        if (row["FeeTransferLedger"].IsFormula)
                        {
                            uw.SetFormula("", row["FeeTransferLedger"].Value.ToString());
                        }
                        else
                        {
                            uw.FeeTransferLedger = row["FeeTransferLedger"].Value.ToString();
                        }

Here is an example since I see a lot of people looking at this. This is for other contact and Underwriter, I noticed they are not mapped exactly so i had to debug and look at all the rows columns to get the names to the Classes Properties.

Now all I need to figure out is how to put a formula in the FeeTransferLedger. Looking for that now.

And I guess to get a Trustee would have to search that also and add reference to marketing Rep, but I don't care about that one.

Code: Select all

                    LookupQuerySpec luspec = new LookupQuerySpec();
                    luspec.Table = "Other Contact";
                    luspec.Filter = "[{KEY}] LIKE @val"; // 
                    luspec.FilterParameters.Add("val", "SCBSC");
                    ILookupTable table = lookupService.QueryTable(luspec);
                    if (table.Rows.Count > 0)
                    {
                        ILookupRow row = table.Rows[0];

                        if (row["LookupCode"].Value != null) bsc.LookupCode = row["LookupCode"].Value.ToString();
                        if (row["Name"].Value != null) bsc.Name = row["Name"].Value.ToString();
                        if (row["PayeeName"].Value != null) bsc.PayeeName = row["PayeeName"].Value.ToString();
                        if (row["Address1"].Value != null) bsc.Address1 = row["Address1"].Value.ToString();
                        if (row["Address2"].Value != null) bsc.Address2 = row["Address2"].Value.ToString();
                        if (row["City"].Value != null) bsc.City = row["City"].Value.ToString();
                        if (row["State"].Value != null) bsc.State = row["State"].Value.ToString();
                        if (row["Zip"].Value != null) bsc.Zip = row["Zip"].Value.ToString();

                        if (row["FeeTransferLedger"].Value != null) bsc.FeeTransferLedger = row["FeeTransferLedger"].Value.ToString();
                        if (row["Phone"].Value != null) bsc.Phone = row["Phone"].Value.ToString();
                        if (row["Fax"].Value != null) bsc.Fax = row["Fax"].Value.ToString();
                        if (row["IsMarketingSource"].Value != null) bsc.IsMarketingSource = (bool)row["IsMarketingSource"].Value;
                        //if (row["MarketingRep"].Value != null) bsc.MarketingRep = (ITrustee)row["MarketingRep"].Value;
                        if (row["LicenseNo"].Value != null) bsc.LicenseNo = row["LicenseNo"].Value.ToString();
                        if (row["Include On Revenue Reports"].Value != null) bsc.IncludeOnRevenueReports = (bool)row["Include On Revenue Reports"].Value;
                        if (row["Bank Name"].Value != null) bsc.BankDrawnOn = row["Bank Name"].Value.ToString();
                        if (row["ABA Routing Number"].Value != null) bsc.ABARoutingNumber = row["ABA Routing Number"].Value.ToString();
                        if (row["Credit Account Name"].Value != null) bsc.CreditAccountName = row["Credit Account Name"].Value.ToString();
                        if (row["Credit Account Number"].Value != null) bsc.AccountNumber = row["Credit Account Number"].Value.ToString();
                        if (row["Email"].Value != null) bsc.Email = row["Email"].Value.ToString();
                        if (row["Special Instructions"].Value != null) bsc.SpecialInstructions = row["Special Instructions"].Value.ToString();
                        if (row["Further Credit"].Value != null) bsc.FurtherCredit = row["Further Credit"].Value.ToString();
                    }

                    IUnderwriter uw = order.Underwriter.CreateNew();

                    luspec.Table = "Underwriter - Texas";
                    luspec.Filter = "[{KEY}] LIKE @val"; // 
                    luspec.FilterParameters.Clear();
                    luspec.FilterParameters.Add("val", "CM");
                    table = lookupService.QueryTable(luspec);
                    if (table.Rows.Count > 0)
                    { 
                        ILookupRow row = table.Rows[0];

                        if (row["Lookup Code"].Value != null) uw.LookupCode = row["Lookup Code"].Value.ToString();
                        if (row["Name"].Value != null) uw.Name = row["Name"].Value.ToString();
                        if (row["Payee Name"].Value != null) uw.PayeeName = row["Payee Name"].Value.ToString();
                        if (row["Address (line 1)"].Value != null) uw.Address1 = row["Address (line 1)"].Value.ToString();
                        if (row["Address (line 2)"].Value != null) uw.Address2 = row["Address (line 2)"].Value.ToString();
                        if (row["City"].Value != null) uw.City = row["City"].Value.ToString();
                        if (row["State"].Value != null) uw.State = row["State"].Value.ToString();
                        if (row["Zip"].Value != null) uw.Zip = row["Zip"].Value.ToString();
                        //if (row["Marketing Rep"].Value != null) uw.MarketingRep = row["Marketing Rep"].Value.ToString();
                        if (row["Tax ID"].Value != null) uw.TaxID = row["Tax ID"].Value.ToString();
                        if (row["Agency ID"].Value != null) uw.AgencyID = row["Agency ID"].Value.ToString();
                        if (row["Claim Address (line 1)"].Value != null) uw.ClaimAddress1 = row["Claim Address (line 1)"].Value.ToString();
                        if (row["Claim Address (line 2)"].Value != null) uw.ClaimAddress2 = row["Claim Address (line 2)"].Value.ToString();
                        if (row["Claim City"].Value != null) uw.ClaimCity = row["Claim City"].Value.ToString();
                        if (row["Claim State"].Value != null) uw.ClaimState = row["Claim State"].Value.ToString();
                        if (row["Claim Zip"].Value != null) uw.ClaimZip = row["Claim Zip"].Value.ToString();
                        if (row["Claim Phone"].Value != null) uw.ClaimPhone = row["Claim Phone"].Value.ToString();
                        if (row["Claim Fax"].Value != null) uw.ClaimFax = row["Claim Fax"].Value.ToString();
                        if (row["Fee Transfer Ledger"].Value != null)
                        {
                            uw.FeeTransferLedger = string.Format("{0}{1:yyyy}{1:MM}", 
                                row["Lookup Code"].Value.ToString(),
                                DateTime.Now);
                        }
                        if (row["State Of Incorporation"].Value != null) uw.StateOfIncorporation = row["State Of Incorporation"].Value.ToString();
                        if (row["Marketing Source"].Value != null) uw.IsMarketingSource = (bool)row["Marketing Source"].Value;
                        if (row["Include On Revenue Reports"].Value != null) uw.IncludeOnRevenueReports = (bool)row["Include On Revenue Reports"].Value;
                        if (row["Logo"].Value != null) uw.LogoImage = (System.Drawing.Image)row["Logo"].Value;
                        if (row["Seal"].Value != null) uw.SealImage = (System.Drawing.Image)row["Seal"].Value;
                        if (row["Directors and/or Officers"].Value != null) uw.DirectorsAndOrOfficers = row["Directors and/or Officers"].Value.ToString();
                        if (row["Disclosures"].Value != null) uw.Disclosures = row["Disclosures"].Value.ToString();

                        if (row["Bank Name"].Value != null) uw.BankDrawnOn = row["Bank Name"].Value.ToString();
                        if (row["ABA Routing Number"].Value != null) uw.ABARoutingNumber = row["ABA Routing Number"].Value.ToString();
                        if (row["Credit Account Name"].Value != null) uw.CreditAccountName = row["Credit Account Name"].Value.ToString();
                        if (row["Credit Account Number"].Value != null) uw.AccountNumber = row["Credit Account Number"].Value.ToString();
                        if (row["Email"].Value != null) uw.Email = row["Email"].Value.ToString();
                        if (row["Special Instructions"].Value != null) uw.SpecialInstructions = row["Special Instructions"].Value.ToString();
                        if (row["Further Credit"].Value != null) uw.FurtherCredit = row["Further Credit"].Value.ToString();
                    }

[BBenson]

We are hoping to migrate to SPS v3.0 in the near future and I have a new requirement to add fully hydrated Underwriters to new orders created through the SPS API.

Will I still need to follow this suggested workaround in v3.0?

Thanks,
Bob

[john.morton]

No. The API for version 3.0 is being rewritten such that issues like this should not exist in the same fashion. Obviously, we cannot guarantee that there won't be issues, but the API, in general, will give much more access to everything within the order.