One of the key advantages I thought of for using Access Web Services in SharePoint is for populating fields with lookups. Whilst you can do this in SharePoint using the whole list as a lookup there is no way to filter that list. So if you want to filter out all assignments which are marked with a status of complete then you are stuffed!
But with Access Web Services I thought you could use a query for the lookup so this would solve the problem. BUT NO you cant use queries for table field lookups using Access Web Services ! As with most Microsoft technologies its two steps forward one back - why do they do this!?! However I have found a workaround which uses forms to filter the lookup and the detail is at the end of this article.
Firstly
if you create a new field and follow the diagrams below you see that lookups from queries are greyed out (why Microsoft !?!). Worse than that, for a new table , you will not see any of the existing tables either. To see the existing tables first save your new table and then fully Sync it to SharePoint. Only then will you get a full list of the tables you can use for lookups.
Lookup query workaround
Firstly create a form which includes the lookup field you have defined. As expected you will see that it returns all values from the table. Then make sure your in "Layout View" and highlight the field and bring up the property sheet. In the properties you will see a value for row source and its SQL !! If you update this by either carefully updating the text or using the query editor (much safer) you can put your "where clauses" into this query. And bingo it works on both the local access and on SharePoint. Hurrrah!
No comments:
Post a Comment