Archive

Archive for January, 2014

How to hide fields for new items using InfoPath

January 23, 2014 Leave a comment

 

A client had a fairly simple request to hide some fields when a new SharePoint list item is created but have these fields shown once the item has been created and people edit the form. The short version is that you can use the inbuilt ID field to see if an item is new or not. If the ID field is blank then it is a new form.

 

Here are the steps required so that you can hide fields when an item is being created. Make sure not to hide any required fields or the form cannot be saved.

These steps require InfoPath 2010 client to be installed

1. Navigate to the list

a. I have created a very simple list where users will ask a question. The goal is only to allow users to fill out the title field when an item is created

 

2. From the ribbon select the Customize Form option

a. clip_image001

b. If this option is missing then following feature is probably not activated

c. clip_image002

3. A form will now have opened in InfoPath 2010

a. clip_image003

4. Each field can be hidden manually or all of the fields you want to hide can be added to a new section and then you just hide the section.

a. Select the Section control and add to the bottom of the form

b. Copy or cut the required rows or fields from the table into the section. It will look similar to the following screen shot

c. clip_image004

5. Now we need to add a rule to the section to hide it.

a. Select the Manage Rules option from the ribbon

b. clip_image005

c. Highlight the section (Click on it)

d. clip_image006

e. From the Rules menu on the left hand side select New and then Formatting.

i. Change the name to be Hide Section

ii. Click on the None under condition and change it to the following

iii. clip_image007

iv. Now select the Hide this control option

v. The whole rule should look like

vi. clip_image008

6. Using the publish button in the very top left of InfoPath publish you changes back to the server. clip_image009

a. You should receive an OK message once this is complete

7. Now navigate to the list and test to see if it has worked

Here is the new form

clip_image010

This is what the form looks like when an existing item is edited

clip_image011

This is just a very basic example. The overall style of the form can be changed using the formatting option within InfoPath.

Rule can also be applied directly to some fields so they do not have to be moved into a section if you do not want to. The attachment control must be added to a section as rules cannot be applied against this control directly.

Categories: work Tags:

Updating database collation

January 10, 2014 Leave a comment

Had some fun trying to update a database collation. I didn’t have to worry about the data i just needed to the collation changed so that some cross database scripts would still function in order to carry out some testing.

 

Here is a nice little script I found that will generate scripts to change the collation on the required columns. Some of it fails if primary keys or indexes are against the columns but this will do 90%.

 

SELECT
      CAST(o.table_name + ‘ -> ‘ + c.name AS VARCHAR(60))
    , ‘ALTER TABLE ‘ + o.table_name +
        ‘ ALTER COLUMN [‘ + c.name + ‘] ‘ +
        UPPER(t.name) + ‘(‘ +
        CASE
            WHEN t.name IN (‘nchar’, ‘nvarchar’) AND c.max_length != -1 THEN CAST(c.max_length / 2 AS NVARCHAR(10))
            WHEN t.name IN (‘nchar’, ‘nvarchar’) AND c.max_length = -1 THEN ‘MAX’
            ELSE CAST(c.max_length AS NVARCHAR(10))
        END + ‘) COLLATE ‘ + @collate +
        CASE WHEN c.is_nullable = 1
            THEN ‘ NULL’
            ELSE ‘ NOT NULL’
        END
FROM sys.columns c WITH (NOWAIT)
JOIN (
    SELECT
          table_name = ‘[‘ + s.name + ‘].[‘ + o.name + ‘]’
        , o.[object_id]
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = ‘U’
) o ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN (‘char’, ‘varchar’, ‘text’, ‘nvarchar’, ‘ntext’, ‘nchar’)
    –AND c.collation_name != @collate
ORDER BY
      o.table_name
    , c.column_id

Categories: work Tags:

Changing the format of date parameters on SSRS 2012

January 10, 2014 Leave a comment

 

Had an issue today where the date parameters were being forced into US data format and not using any of the regional settings from the OS or SharePoint.

 

This has apparently been fixed in SQL 2012 SP1 CU5 update but i have not confirmed this.

This post http://www.tachytelic.net/2013/01/changing-the-format-of-date-parameters-on-a-sql-server-2012-reporting-services-report-that-is-sharepoint-integrated/

Has a workaround that required you to add a culture into the RSViewerPage.aspx located in

 

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\ReportServer

 

or

 

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\LAYOUTS\ReportServer

in SharePoint 2013

 

You need to add UICulture="en-GB"

 

to the end of

<%@ Page language="C#" Codebehind="RSViewerPage.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.SharePoint.UI.RSViewerPage,Microsoft.ReportingServices.SharePoint.UI.ServerPages,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91" %>

 

 

The full string looks like

 

<%@ Page language="C#" Codebehind="RSViewerPage.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.SharePoint.UI.RSViewerPage,Microsoft.ReportingServices.SharePoint.UI.ServerPages,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91" UICulture="en-GB"%>

 

If you are using Windows 2012 i had to save the updated file to a different location and then copy the file in and gain the correct permissions.

Categories: work Tags: