Re: Linking lists
Hi Marko, Turning this on it's head it is possible to created a Linked Table in Access which will make the SharePoint List available in your database. You can then craft some SQL queries in Access to do the 'import' and association of the reference data. (i.e. insert into LinkedSPList select blah from AccessTable). Regards, Craig On 9 March 2011 09:04, Marko Salonen wrote: > Hi Daniel > > Thanks for the link. I know this work for new lists, but when I am > importing a linked table from access after I add the lookup column its > obviously empty. > I was more after how can I programatically perhaps enforce something like > > table 1 join table 2 where t1key = t2key > set t1value = t2value > > I suppose I could do this in the access database before importing the data? > But I don't think converting the column to a lookup field would then work... > > > Regards, > > Marko Salonen > > > -- > *From:* ozmoss-boun...@ozmoss.com [mailto:ozmoss-boun...@ozmoss.com] *On > Behalf Of *Daniel Brown > *Sent:* Tuesday, 8 March 2011 5:49 PM > *To:* 'ozMOSS' > *Subject:* RE: Linking lists > >HI Marko, > > > > Welcome back to the world of SharePoint!!! (rocks back and forth), a lot of > places are still on 2007, but there is a move to SP2010 which has been > building steady for awhile now, I would say it’s pretty much how new SP are > well and truly done these days, unless there are requirements not to. > > > > Lookup Fields are what you are after, You simply create a column in your > desired list (table a) of type “lookup”, select the list (table b) and what > you want for the ID/Value and done. > > > > A good example is at http://phase2.com/blog/?p=101 > > > > Hope this helps J > > > > Cheers, > > > > Daniel > > > > > > *From:* ozmoss-boun...@ozmoss.com [mailto:ozmoss-boun...@ozmoss.com] *On > Behalf Of *Marko Salonen > *Sent:* Tuesday, 8 March 2011 5:31 PM > *To:* ozMOSS > *Subject:* Linking lists > > > > Hi all > > > > I have been away from SharePoint for a bit (almost a year), and I am > guessing most places have gone to 2010? Anybody other than where I > work still using 2007? Nice to be back in the world of SharePoint anyway... > > > > I have a requirement to import some old access databases into SharePoint > lists and I am wondering how to link the tables in SharePoint? > > Specifically if Table 1 has a column A that is the value from Table 2 > column B how can I get it to lookup the value? > > > > Cheers! > > Marko > > MORETON BAY REGIONAL COUNCIL (MBRC) PRIVILEGED PRIVATE AND CONFIDENTIAL - > The information contained in this e-mail and any attachments is confidential > and may attract legal privilege. It is only intended for the named > recipient/s. If you are not a named recipient any use of this information > including copying, distribution and publication is prohibited. > Confidentiality and legal privilege are not waived or lost as a result of > mistaken or erroneous delivery. If you are not a named recipient, please > delete all copies immediately and contact the sender to advise of the error. > > It is recommended that you scan this email and any attachment before > opening. MBRC does not accept any responsibility or liability for loss or > damage arising directly or indirectly from opening this email, opening any > attachments or any communication errors. > > The views expressed in this email and any attachments are the personal > views of the sender unless otherwise stated. > > ___ > ozmoss mailing list > ozmoss@ozmoss.com > http://prdlxvm0001.codify.net/mailman/listinfo/ozmoss > > ___ ozmoss mailing list ozmoss@ozmoss.com http://prdlxvm0001.codify.net/mailman/listinfo/ozmoss
Re: Linking lists
I think you can populate the lookup column in code if it helps, maybe using a console application ... Just saw this on a blog to update lookup columns http://blogs.msdn.com/b/sridhara/archive/2007/08/25/update-quot-lookup-quot-fields-in-sharepoint-2007.aspx string result = string.Empty; SPSite site = new SPSite(""); SPWeb web = site.OpenWeb(); SPListItemCollection listItems = web.Lists["listwithlookupfield"].Items; foreach (SPListItem listItem in listItems) { if (listItem["Quantity"].ToString() == "100") { // update Product lookup field result += "Current value: " + listItem["ProductLookUp"].ToString() + System.Environment.NewLine; listItem["Product"] = new SPFieldLookupValue(1, "A"); listItem.Update(); result += "Updated value: " + listItem["ProductLookUp"].ToString(); } } tbresults.Text = result; On Wed, Mar 9, 2011 at 11:04 AM, Marko Salonen < marko.salo...@moretonbay.qld.gov.au> wrote: > Hi Daniel > > Thanks for the link. I know this work for new lists, but when I am > importing a linked table from access after I add the lookup column its > obviously empty. > I was more after how can I programatically perhaps enforce something like > > table 1 join table 2 where t1key = t2key > set t1value = t2value > > I suppose I could do this in the access database before importing the data? > But I don't think converting the column to a lookup field would then work... > > > Regards, > > Marko Salonen > > > -- > *From:* ozmoss-boun...@ozmoss.com [mailto:ozmoss-boun...@ozmoss.com] *On > Behalf Of *Daniel Brown > *Sent:* Tuesday, 8 March 2011 5:49 PM > *To:* 'ozMOSS' > *Subject:* RE: Linking lists > > HI Marko, > > > > Welcome back to the world of SharePoint!!! (rocks back and forth), a lot of > places are still on 2007, but there is a move to SP2010 which has been > building steady for awhile now, I would say it’s pretty much how new SP are > well and truly done these days, unless there are requirements not to. > > > > Lookup Fields are what you are after, You simply create a column in your > desired list (table a) of type “lookup”, select the list (table b) and what > you want for the ID/Value and done. > > > > A good example is at http://phase2.com/blog/?p=101 > > > > Hope this helps J > > > > Cheers, > > > > Daniel > > > > > > *From:* ozmoss-boun...@ozmoss.com [mailto:ozmoss-boun...@ozmoss.com] *On > Behalf Of *Marko Salonen > *Sent:* Tuesday, 8 March 2011 5:31 PM > *To:* ozMOSS > *Subject:* Linking lists > > > > Hi all > > > > I have been away from SharePoint for a bit (almost a year), and I am > guessing most places have gone to 2010? Anybody other than where I > work still using 2007? Nice to be back in the world of SharePoint anyway... > > > > I have a requirement to import some old access databases into SharePoint > lists and I am wondering how to link the tables in SharePoint? > > Specifically if Table 1 has a column A that is the value from Table 2 > column B how can I get it to lookup the value? > > > > Cheers! > > Marko > > MORETON BAY REGIONAL COUNCIL (MBRC) PRIVILEGED PRIVATE AND CONFIDENTIAL - > The information contained in this e-mail and any attachments is confidential > and may attract legal privilege. It is only intended for the named > recipient/s. If you are not a named recipient any use of this information > including copying, distribution and publication is prohibited. > Confidentiality and legal privilege are not waived or lost as a result of > mistaken or erroneous delivery. If you are not a named recipient, please > delete all copies immediately and contact the sender to advise of the error. > > It is recommended that you scan this email and any attachment before > opening. MBRC does not accept any responsibility or liability for loss or > damage arising directly or indirectly from opening this email, opening any > attachments or any communication errors. > > The views expressed in this email and any attachments are the personal > views of the sender unless otherwise stated. > > ___ > ozmoss mailing list > ozmoss@ozmoss.com > http://prdlxvm0001.codify.net/mailman/listinfo/ozmoss > > ___ ozmoss mailing list ozmoss@ozmoss.com http://prdlxvm0001.codify.net/mailman/listinfo/ozmoss
RE: Linking lists
Hi Daniel Thanks for the link. I know this work for new lists, but when I am importing a linked table from access after I add the lookup column its obviously empty. I was more after how can I programatically perhaps enforce something like table 1 join table 2 where t1key = t2key set t1value = t2value I suppose I could do this in the access database before importing the data? But I don't think converting the column to a lookup field would then work... Regards, Marko Salonen From: ozmoss-boun...@ozmoss.com [mailto:ozmoss-boun...@ozmoss.com] On Behalf Of Daniel Brown Sent: Tuesday, 8 March 2011 5:49 PM To: 'ozMOSS' Subject: RE: Linking lists HI Marko, Welcome back to the world of SharePoint!!! (rocks back and forth), a lot of places are still on 2007, but there is a move to SP2010 which has been building steady for awhile now, I would say it's pretty much how new SP are well and truly done these days, unless there are requirements not to. Lookup Fields are what you are after, You simply create a column in your desired list (table a) of type "lookup", select the list (table b) and what you want for the ID/Value and done. A good example is at http://phase2.com/blog/?p=101 Hope this helps :) Cheers, Daniel From: ozmoss-boun...@ozmoss.com [mailto:ozmoss-boun...@ozmoss.com] On Behalf Of Marko Salonen Sent: Tuesday, 8 March 2011 5:31 PM To: ozMOSS Subject: Linking lists Hi all I have been away from SharePoint for a bit (almost a year), and I am guessing most places have gone to 2010? Anybody other than where I work still using 2007? Nice to be back in the world of SharePoint anyway... I have a requirement to import some old access databases into SharePoint lists and I am wondering how to link the tables in SharePoint? Specifically if Table 1 has a column A that is the value from Table 2 column B how can I get it to lookup the value? Cheers! Marko MORETON BAY REGIONAL COUNCIL (MBRC) PRIVILEGED PRIVATE AND CONFIDENTIAL - The information contained in this e-mail and any attachments is confidential and may attract legal privilege. It is only intended for the named recipient/s. If you are not a named recipient any use of this information including copying, distribution and publication is prohibited. Confidentiality and legal privilege are not waived or lost as a result of mistaken or erroneous delivery. If you are not a named recipient, please delete all copies immediately and contact the sender to advise of the error. It is recommended that you scan this email and any attachment before opening. MBRC does not accept any responsibility or liability for loss or damage arising directly or indirectly from opening this email, opening any attachments or any communication errors. The views expressed in this email and any attachments are the personal views of the sender unless otherwise stated. ___ ozmoss mailing list ozmoss@ozmoss.com http://prdlxvm0001.codify.net/mailman/listinfo/ozmoss
RE: Linking lists
HI Marko, Welcome back to the world of SharePoint!!! (rocks back and forth), a lot of places are still on 2007, but there is a move to SP2010 which has been building steady for awhile now, I would say it's pretty much how new SP are well and truly done these days, unless there are requirements not to. Lookup Fields are what you are after, You simply create a column in your desired list (table a) of type "lookup", select the list (table b) and what you want for the ID/Value and done. A good example is at http://phase2.com/blog/?p=101 Hope this helps J Cheers, Daniel From: ozmoss-boun...@ozmoss.com [mailto:ozmoss-boun...@ozmoss.com] On Behalf Of Marko Salonen Sent: Tuesday, 8 March 2011 5:31 PM To: ozMOSS Subject: Linking lists Hi all I have been away from SharePoint for a bit (almost a year), and I am guessing most places have gone to 2010? Anybody other than where I work still using 2007? Nice to be back in the world of SharePoint anyway... I have a requirement to import some old access databases into SharePoint lists and I am wondering how to link the tables in SharePoint? Specifically if Table 1 has a column A that is the value from Table 2 column B how can I get it to lookup the value? Cheers! Marko MORETON BAY REGIONAL COUNCIL (MBRC) PRIVILEGED PRIVATE AND CONFIDENTIAL - The information contained in this e-mail and any attachments is confidential and may attract legal privilege. It is only intended for the named recipient/s. If you are not a named recipient any use of this information including copying, distribution and publication is prohibited. Confidentiality and legal privilege are not waived or lost as a result of mistaken or erroneous delivery. If you are not a named recipient, please delete all copies immediately and contact the sender to advise of the error. It is recommended that you scan this email and any attachment before opening. MBRC does not accept any responsibility or liability for loss or damage arising directly or indirectly from opening this email, opening any attachments or any communication errors. The views expressed in this email and any attachments are the personal views of the sender unless otherwise stated. ___ ozmoss mailing list ozmoss@ozmoss.com http://prdlxvm0001.codify.net/mailman/listinfo/ozmoss