add record to lookup programmatically

Discussions related to writing and managing custom business rules.

Moderator: Phil Barton

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

add record to lookup programmatically

Post by czentman » Tue Jan 13, 2009 4:00 pm

I want to be able to add a record to the attorney lookup programmatically. The attorney table in the db includes order id's. I want to add a record programmatically or manually but in the backend to have another attorney record for to choose from within in order, but not link to any specific order, as if I added it in the spadmin ->Managers->Lookups->Attorney section. how can i do this?

John Morris
Posts: 411
Joined: Thu Sep 11, 2008 11:35 am
Location: Raleigh, NC, USA
Contact:

Re: add record to lookup programmatically

Post by John Morris » Tue Jan 13, 2009 5:14 pm

Yes. This can be done using the lookup table service. Here is an example:

Code: Select all

//connect using the select server object
NetworkCredential creds = new NetworkCredential("admin", "Passw0rd", "[SERVER]");
SelectServer sps = new SelectServer(new Uri("http://localhost:8080/SelectServer"), creds);
sps.EnsureAuthenticated();

//this code loads the table data – currently, it only supports a single column filter specification
//the table name matches what is seen in SPAdmin Lookups Manager
LookupQuerySpec spec = new LookupQuerySpec()
{
Filter = "[Code] = 'ALTA6*'"  //the filter wildcards are typical SoftPro wildcards (see search screen help)
};
Lookups lookups = sps.GetService<Lookups>();
DataTable table = lookups.GetTable("Endorsement", spec);

//this code will add a new row – this sample uses a column index, but the column name works too
//IMPORTANT: column 0 is the row id and must be populated with a new guid when creating a new row
DataRow row = table.NewRow();
row[0] = Guid.NewGuid();//NOTE: In order to update data, simply edit the row inplace
((LookupValue)row[1]).Value = "TEST3"; //NOTE: The field data type is SoftPro.OrderTracking.Common.LookupValue
((LookupValue)row[1]).IsFormula = true;
table.Rows.Add(row);

//this form will let your visualize the data
using (Form f = new Form())
{
    DataGrid grid = new DataGrid();
    grid.Dock = DockStyle.Fill;
    grid.DataSource = table;
    f.Controls.Add(grid);
    f.ShowDialog();
}
            
//to save changes back to the server, do this:
lookups.ApplyChanges(table);
John Morris
Sr. Software Architect
SoftPro

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: add record to lookup programmatically

Post by czentman » Wed Jan 14, 2009 11:30 am

Thanks but I also want to know how the data is stored in the database. Can you tell me where the Attorney lookup data is stored in the database? The attorney table includes order IDs and that's not what I'm looking to see.

John Morris
Posts: 411
Joined: Thu Sep 11, 2008 11:35 am
Location: Raleigh, NC, USA
Contact:

Re: add record to lookup programmatically

Post by John Morris » Wed Jan 14, 2009 12:00 pm

The lookup data is stored in the database using a highly normalized structure. we do not support the direct use of the database like this. Using the API is the only supported integration mechanism.

The lookup table API that I mentioned is accessing the lookup data in the database, not the Attorney table data with the Order ID.
John Morris
Sr. Software Architect
SoftPro

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: add record to lookup programmatically

Post by czentman » Wed Jan 14, 2009 12:27 pm

Well I'm tring to use your code and I get errors on the following. I tried adding several references that I though would do the trick. THis is my code as follows:

using SoftPro.Select.Client.SelectServer;
using SoftPro.Select.Client.SelectServerObject;
using SoftPro.Select.Server;
using SoftPro.Select.Server.Common;

public class MyEditorControl
{

private void MyEditorControl_Load(object sender, System.EventArgs e)
{

//connect using the select server object
NetworkCredential creds = new NetworkCredential("admin", "Passw0rd", "[SERVER]");
SelectServer sps = new SelectServer(new Uri("http://localhost:8080/SelectServer"), creds);
sps.EnsureAuthenticated();

//this code loads the table data – currently, it only supports a single column filter specification
//the table name matches what is seen in SPAdmin Lookups Manager
//the filter wildcards are typical SoftPro wildcards (see search screen help)
LookupQuerySpec spec = new LookupQuerySpec();
Lookups lookups = sps.GetService<Lookups>();
DataTable table = lookups.GetTable("Endorsement", spec);

//this code will add a new row – this sample uses a column index, but the column name works too
//IMPORTANT: column 0 is the row id and must be populated with a new guid when creating a new row
DataRow row = table.NewRow();
row(0) = Guid.NewGuid();
//NOTE: In order to update data, simply edit the row inplace
((LookupValue)row(1)).Value = "TEST3";
//NOTE: The field data type is SoftPro.OrderTracking.Common.LookupValue
((LookupValue)row(1)).IsFormula = true;
table.Rows.Add(row);

//this form will let your visualize the data
using (Form f = new Form()) {
DataGrid grid = new DataGrid();
grid.Dock = DockStyle.Fill;
grid.DataSource = table;
f.Controls.Add(grid);
f.ShowDialog();
}

//to save changes back to the server, do this:
lookups.ApplyChanges(table);

}
}


I get errors on 1-NetworkCredental not defined, 2-Softpro.Select.Server.SelectServer is not accessible in this contexts because it is 'Friend', 3- Type 'LookupQuerySpec' is not defined, 4- 'Lookups' is not defined, 5-'LookupValue' is not defined and 6-'Form' is not defined

can you tell me how and which references to reference these adding dll's/references?

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: add record to lookup programmatically

Post by czentman » Wed Jan 14, 2009 12:33 pm

Actually I was able to add a couple more references which removed a couple of errors. I still have the first 2/3, 1 - networkcredential not defined, 2-selectserver not accessible because it's a 'Friend' and 3- Lookups not defined. The 3rd is because of the 2nd.

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: add record to lookup programmatically

Post by czentman » Wed Jan 14, 2009 12:39 pm

I was able to explicity create that sps by doing Dim sps As New SoftPro.Select.Client.SelectServer(New Uri("http://localhost:8080/SelectServer"), creds) and avoid that error.

I still can't find the reference for NetworkCredential and Lookups. I tried adding other references from select and I can't seem to find it.

John Morris
Posts: 411
Joined: Thu Sep 11, 2008 11:35 am
Location: Raleigh, NC, USA
Contact:

Re: add record to lookup programmatically

Post by John Morris » Wed Jan 14, 2009 12:51 pm

What version of Select do you have?
John Morris
Sr. Software Architect
SoftPro

czentman
Posts: 157
Joined: Tue Dec 02, 2008 12:02 pm

Re: add record to lookup programmatically

Post by czentman » Wed Jan 14, 2009 1:28 pm

Not sure but I think the latest version. We did have an upgrade at one point. Also, what's with the lookups service? I tried copying and pasting something I found lookups.svc and putting it my service references but I'm not sure how to reference it my code (if it's necessary).

roteague
Posts: 292
Joined: Thu Sep 25, 2008 4:49 pm
Location: Honolulu, Hawaii

Re: add record to lookup programmatically

Post by roteague » Wed Jan 14, 2009 2:29 pm

czentman wrote:I still can't find the reference for NetworkCredential
using System.Net;
Robert

Post Reply