Messing around with Access 2010 to generate SharePoint lists with lookup lists automatically

I recently wanted to experiment with Excel and Access 2010 to generate SharePoint lists.  I found that it would only generate the main table's and the lookup table's list in SharePoint if I had selected the relationship with these settings.

For example, if we had a main table that was say a list of staff members.  There was a field in it called Department and you wanted the field to become a lookup to the table called tbl_Department

In order to get both tables exported to SharePoint when you export the main staff table, here is what I had to follow for it to work, else I found it either:

1.  Would not export and link the lookup table
2. I would loose the data in the Department column in the main staff table.

Firstly, make all of the values in the Department column matches a lookup record in the tbl_department table,  Else this can cause a lost of data.  This includes trailing spaces in the data.  

Secondly, on the lookup table there needs to be a primary key ID auto increment field if there is one.  This is crucial for a link to be created so that it will export the table also to SharePoint with the main table.  However, if there is already data in the table, this seems to always cause a loss of data on the column in the main table after the link is created.  In order to cater for this, copy the table into say Excel, after the link is created and removed the data, use the Excel copy and re-paste the data back into the table.

Then, in the main staff table's design view choose "Lookup Wizard..." on the field that will become a lookup

Then choose first option click Next

Select the correct table.  In this case Departments.  Press Next.

Select the Department field and click Next

Select the Department field to list out for the field in the main staff table.  Hit Next.

The values are listed.  Hit Next.

Hit Finish and the link gets created.

Click Yes to save.

Click Yes as it will now link the table.  It is highly likely there will then be some data lost while the link is created.  This is why it is recommended a copy of the table be made in Excel before this operation.

Save the staff table.

Now open the staff table again you should see the Department field now has a drop down box that can be invoked and is linked to the tbl_department table.  Although most data may be gone and left blank.

Time to copy the column that has been saved in Excel back into the Access table.  If all the values in the lookup table match properly, it should just paste back in without any problems.

Export the staff table to SharePoint and voila you will find its exported and the tbl_Department table gets taken with it and you have both lists created with a lookup field made.

Screen Flickering on All in One PC. Energy Efficient Intelligence issue?

This is a really crazy situation with a display monitor that I have running Win 8.  It has been flickering a bit I have noticed.  At first, I thought it was a driver issue or may be hardware.  I reverted back to the original drivers that cam with the machine since I know Windows Update has updated it recently with the latest Intel HD Drivers.  But it was to no avail. 

However, what intrigued me was somehow the HDMI in seem to be working flicker free.

I then stumbled upon this http://denis.doublebuffer.net/lablog/2012/11/19/fixing-the-screen-flickering-on-a-dell-inspiron-n5720-and-maybe-many-others/

It offered me an idea what may be wrong.  However, since my machine was not a laptop and cannot run on battery being an all in one, I was a bit lost.

I soon discovered a pattern, the flickering seem to start after a wake up from a machine sleep.  I then played around with the Energy Intelligent module that the machine runs in the background.  

I found if I toggle the settings to High by hitting the button with the leaf in the middle.

 The buttons would glow bright green and the re-toggle back to E mode with the button on the right.

The flickering would subside until the machine sleeps again and wakes up.

Seems like there may be a bug in this Energy Intelligence software, it is affecting the light in the LED when it is trying to save power.  

A thing that I discovered with SSP Profiles and User properties such as email, seems to Cache at Site Collection

From experience working with accounts in Asia SharePoint is that sometimes an account's email settings can get cached into the SharePoint site collection or SSP's profile.  

For example, you may have a user who you look up and has been double checked by the IDM team to have the correct email address entered into AD and SharePoint people lookup dialog shows it.  However, you find repeatedly you can't pick up their email address in a workflow.

I found that sometimes, you may need to first delete the account from the site collections "All People" view first and then re-insert the user back into all the groups they had belonged to for the changes to get updated.  It can be very time consuming as some users belong to many SharePoint groups  and certainly before you delete the user, you need to make sure at least which important groups they had belonged to as deleting them would shut them out of parts of the system.  If AD groups been used instead then I suppose this problem may be less prevalent but I can't say for sure as in Asia SharePoint, we have had to use SharePoint groups rather than AD groups.

If they have a SSP profile, that profile needs updating and tends to take precedence over their AD settings.  Often the profile import synchronisation service may be turned off to alleviate bandwidth demand, therefore, the profiles may need to be updated manually when required. Once the SSP profile is updated, a background SharePoint job will sync the setting usually on the hour.  

Enabling IE Dev Tools Settings if disabled by GPO

Sometimes Network admin policies just get over the top and especially for bods like us who require to have the IE Developer Tools enabled for script debugging etc.

Firstly check whether these 2 registry settings been set 

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Internet Explorer\IEDevTools

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\IEDevTools

If they have been to 1, set them to 0 to disable these disabling keys ;-)

However , it is highly likely a Network GPO policy is overriding this setting therefore the trick is if you have admin rights on your local machine.  Create a local user on the desktop machine.  Then assign it as an administrator locally.  

Then login to the desktop eg .\user then password.

Then you should be able to open gpedit.msc from the run command.

Then check this setting below.

Computer Configuration -> Administrative templates -> Windows Components -> Internet Explorer -> Toolbars -> Turn Off Developer Tools = Set to Disable.  

Restart all IE instances and the F12 developer tools under Tools menu should be activated again.

Space in Field Names

I think numerous people have mentioned this but it still stumped me for a while when I was trying to code a CAML query in SharePoint where I needed to get de-reference a field that had a space in it.

The field was name "A Type" and because of space or any other unsuitable characters, it will set it as such in its internal field name.

You can always find out what its internal name is by going to the List settings and columns, right click on the field hit properties and in the dialog window's look in the bottom of the Address URL property.

It will show something like this for "A Type" that is "A%5Fx0020%5FType"

Using A%5Fx0020%5FType in the JQuery code will not work as instead 

%dF needs to be underscore

Therefore, it should become A_x0020_Type in the field referencing code.

eg

 <viewFields> \
                 <ViewFields> \
                          <FieldRef Name='Title' /> \
                          <FieldRef Name='A_x0020_Type' /> \
                 </ViewFields> \
 </viewFields> \

And when using ows to get the xml nodes returned.

$(this).attr("ows_A_x0020_Type")

Thanks for this post to point me into the right direction.

http://abstractspaces.wordpress.com/2008/05/07/sharepoint-column-names-internal-name-mappings-for-non-alphabet/

I was scratching my head for a while.

To avoid this, never use space, semi colon or non alphanumeric characters when naming fields. Use underscore for space and this problem just gets side stepped.

Portugal Nike shirt WC 14

Now this was what I called a kanny strip.  And Nike can sometimes do it but why can't they do this all the time.  This is so cool.  The pattern is hot and simple.  

The v neck is great and is like a classic shirt.

Badge is comfortably embroidered.

Sleeves and cuffs again nice.  Just the shirt is a bit too big as usual since it is Nike sized.  But it's easy to get altered with their straight seams.

Great looking from the back.  Psychedelic strip...  Shame that Portugal didn't do as well as they could have while wearing this during 14.  Otherwise this must be one of the all time best strips that they have had.  The material is decent but it could have been softer.  

CAML Multiple And Conditions.

Ran into some trouble where I wanted to place 3 And conditions in the where clause of a CAML query to a SharePoint list with JQuery.

I kept on thinking it must be like below.  One And and then just add multiple contains.

<listName>Report Names</listName> \
<query><Query><Where><And> \
<Contains><FieldRef Name='Front_Desk_Dept'/><Value Type='Text'>"+myDeptValue+"</Value></Contains> \
<Contains><FieldRef Name='Report_Type'/><Value Type='Text'>"+myReportTypeValue+"</Value></Contains> \                  
<Contains><FieldRef Name='Region'/><Value Type='Text'>"+myRegionValue+"</Value></Contains> \
                         </And> \
                        </Where><OrderBy><FieldRef Name='Title' Ascending='TRUE' /></OrderBy></Query></query> \
<viewFields> \
<ViewFields> \
       <FieldRef Name='Title' /> \
       <FieldRef Name='Front_Desk_Dept' /> \
       <FieldRef Name='Report_Type' /> \
       <FieldRef Name='Region' /> \
 </ViewFields> \
 </viewFields> \



Instead additional And tag must be nested in the layer above.  I found below worked.

<listName>Report Names</listName> \
<query><Query><Where><And><And> \
<Contains><FieldRef Name='Front_Desk_Dept'/><Value Type='Text'>"+myDeptValue+"</Value></Contains> \
<Contains><FieldRef Name='Report_Type'/><Value Type='Text'>"+myReportTypeValue+"</Value></Contains> \
                                    </And> \
<Contains><FieldRef Name='Region'/><Value Type='Text'>"+myRegionValue+"</Value></Contains> \
                            </And> \
                        </Where><OrderBy><FieldRef Name='Title' Ascending='TRUE' /></OrderBy></Query></query> \
<viewFields> \
<ViewFields> \
       <FieldRef Name='Title' /> \
       <FieldRef Name='Front_Desk_Dept' /> \
       <FieldRef Name='Report_Type' /> \
       <FieldRef Name='Region' /> \
 </ViewFields> \
 </viewFields> \

Must thank the post below for pointed out the syntax

http://aasai-sharepoint.blogspot.hk/2013/02/caml-query-with-multiple-conditions.html

However, for some reason, when I added a 4th condition, it would only work is I further nested an And condition like below.

<listName>Report Names</listName> \
<query><Query><Where><And><And><And> \
<Contains><FieldRef Name='Front_Desk_Dept'/><Value Type='Text'>"+myDeptValue+"</Value></Contains> \
<Contains><FieldRef Name='Report_Type'/><Value Type='Text'>"+myReportTypeValue+"</Value></Contains> \
                         </And> \
<Contains><FieldRef Name='Region'/><Value Type='Text'>"+myRegionValue+"</Value></Contains> \
                         </And> \
<Contains><FieldRef Name='Report_Finder_Type'/><Value Type='Text'>"+myReportFinderType+"</Value></Contains> \
 </And> \
                        </Where><OrderBy><FieldRef Name='Title' Ascending='TRUE' /></OrderBy></Query></query> \
<viewFields> \
<ViewFields> \
      <FieldRef Name='Title' /> \
      <FieldRef Name='Code' /> \
      <FieldRef Name='Front_Desk_Dept' /> \
      <FieldRef Name='Report_Type' /> \
      <FieldRef Name='Region' /> \
      <FieldRef Name='Report_Finder_Type' /> \
</ViewFields> \

</viewFields> \