simple tip - passing values from Infopath 2010 to sharepoint (easy way)

1. click File tab and click Publish and then click SharePoint Server Button

2. Enter the location of Sharepoint

3. click Next unless you want to use advanced settings

4. click Update the form template in an existing form library and select the name of your target list

5. click Add button and pick a field from the list

6. click Next and click Publish

It's done

I removed a phase column from the column view using site's modify view(under Library tools)

Infopath 2010 multiple views, repeating tables, and external database

This was my first infopath/sharepoint project last year.
It was straight forward and I was very glad that I had a chance to learn about infopath and sharepoint.
However, it was never published.. The reason? idk. :(

Here's the screenshot for an infopath form.

It has six different views for ItemAdded, ItemChanged, and ItemRemoved and three approval views.

Example of ItemAddedReview view



Each button(ItemAdded, ItemChanged, ItemRemoved) has only one rule. - Switch to view:viewname

Let's take a look at an ItemAdded view table.
I created a section and put a repeating table and buttons for other views.

Here are some rules for textboxes.

ItemCount_ItemAdded field is for counting how many row were added.
CatLvl1Changes field has one action rule to set Catlvl2_ItemAdded, Catlvl3_ItemAdded, Catlvl4_ItemAdded to blank when the field changes
CatLvl2Changes field has one action rule to set Catlvl3_ItemAdded, Catlvl4_ItemAdded to blank when the field changes
CatLvl3Changes field has one action rule to set Catlvl4_ItemAdded to blank when the field changes

here's drop-down box properties for CatLvl1 and CatLvl2

There are two ways of getting data for multiple drop-down box data. Maybe more....
First is that you create a full database table with all information  like this

so all drop-down box don't have to compare its value with other drop-down box values.

Another way of doing this is comparing values for two drop-down boxes.
For instance, you get the first drop-down box value, then you can populate second drop-down box menu by using a formula. value[this = that]

I used the simple one for this example since it was much better for me to create a one big database table connection then many small database tables.

Here's my data connections

POS Update Form Testers : to get people's information manually. (on my tesing environment, I can't use GetUserProfileByName nor User Information List)
GetUserProfileByName : to get people's information (check Itay Shakury's blog )
User Information List : just like GetUserProfileByName (at least on my production environment it works like GetUserProfileByName)
ItemMenuList : external database
ItemCategories  : external database
CategoryMAP : external database
CategoryMST : external database
Main submit : testing
SharePoint Library Submit : sharepoint submit

and again, this will help you to have a better form for your workflow.

My Cheat Sheet for SSRS

From various websites with my try-catch-fail method :P

Thank you all who posted valuable information on the web

· Format numbers with leading zeros

=format(Fields!name.Value,"00000000")

=strdup(10-InStr(Fields!CHECK_AMT.Value, "."), "*") & Fields!name.Value & "*" è “****1111.11*”

· to int to string

=cstr(Fields!name1.Value & format(Fields!name2.Value,"00000000"))

· next line/new line

vbCrLf

· Grouping

To use 2 or more inner groups

1. Use ‘add group’ to add parent group

2. Delete the column(delete the column only)

3. Do it one more time

4. Insert a row outside group X 2 times

5. Do it all over again

6. If you can remove “[=” it would be easier so look for it

· Page break by grouping (group properties)

=Fix((RowNumber("tablename")-1) / rownumbers)

· To keep header and footer on the same page this should be used in a list

????? where did it go?

Display multi-valued parameter

=join(Parameters!processLevel.Value,",")

· Two datasets from different servers

If two tables have 1:1 relationship

=Lookup(p.key from dataset1, p.key from dataset2, column that I need, “dataset name”)

If two tables have 1:many relationship

=join(LookupsetLookup(p.key from dataset1, p.key from dataset2, column that I need, “dataset name”))

If more than one condition is needed in order to filter out or show/hide

=>break it down to one condition at a time(it is sequential)

· To Keep headers on each page

<TablixRowHierarchy>

<TablixMembers>

<TablixMember>

<RepeatOnNewPage>true</RepeatOnNewPage> ADD this Line!!

<KeepTogether>true</KeepTogether>

OR

Change row/column groups to advanced mode

open the first static property and change FixedData, KeepTogether, KeepWithGroup, RepeatOnNewPage to True as you need

· Suppressing Null or Zero Values at Run Time

=IIF(Field!B.Value=0, 0, Field!A.Value / IIF(Field!B.Value =0, 1, Field!B.Value))

*** to avoid Attempted to divide by zero Error, use =iif(A=0, 0, B / iif(A=0, 1, A))

· Multiple value parameters

Dataset Properties => Parameters => add a parameter that has multiple values

Parameter Value => =split(join(Parameters!Name.Value,","),",")

And within the where clause, add columnName in (@Name)

· First day of week(Monday)

=DateAdd("d", -(WeekDay(Today(),2))+1, Today())

So It can be changed any day

DateAdd("d", -(WeekDay(Parameters!Parameter1.Value,2))+1, Parameters!Parameter1.Value)

--next monday:

=DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())

--First day of month:

=DateAdd("d",1-DatePart("d",Today()),Today())

--First day of next month:

=dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))

--First day of year:

=DateSerial(YEAR(Today()),1,1)

--First day of next year:

=DateSerial(YEAR(Today())+1,1,1)

================More===================

Set First Day of previous week (Monday)

=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

Set First Day of Current Week (Monday)

=DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())

Set Last Day of previous week (Sunday)

=DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

Set First Date of last month

=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

*format decimals with expression

=Format(Fields!Price.Value, "C")

*to remove time from getdate()

convert(varchar(8),dateadd(dd,-1,GETDATE()),112)

*Page number by groups

*need to find a way to do it on BI

Open up with reporting builder and change the value in Group properties => Group => PageBreak

BreakLocation to Between and ResetPageNumber to True

SQL Report Service with Visual Studio 2008 - date (my cheat sheet)

There are many ways to calculate dates.

Here are some useful date calculations.

Thank you all who created and posted each one of these on their websites or blogs.

--Monday (current week):

=DateAdd("d", -(WeekDay(Today(),2))+1, Today())

--Next Monday:

=DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())

--First day of month:

=DateAdd("d",1-DatePart("d",Today()),Today())

--First day of next month:

=dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))

--First day of year:

=DateSerial(YEAR(Today()),1,1)

So based on these expressions we can change Today()'s value and numbers to calculate other days.

For instance,

--using a parameter value

=DateAdd("d", -(WeekDay(Parameters!Parameter1.Value,2))+1, Parameters!Parameter1.Value)

--First day of next year:

=DateSerial(YEAR(Today())+1,1,1)

--Last Day of previous week(Sunday)

=DateAdd("d", 1-Weekday(today),Today)

and so on....

 

Infopath 2010 multiple approvals

Infopath 2010 Sections with numbers(hiding certain parts) for a multiple approval form

This was a part of my project for last 6 weeks to bulid a multiple approval workflow with a loop.
It took more than what I thought but I think it's ok :P.
Once again, planning is very important.

here's the first part of the form.

This part of the form will initiate the workflow.
and here's some rules on this section.

and here are more rules (I got this idea from Laura Rogers from sharepoint911)

and here's the rules for submit button

Count field is the key. It will keep track of each section and pass the value to workflow to process.
Steps field can be ignored.
Initiator field holds who created this form. In our workflow some phases need to go back to a certain person or phase.
Phase field indicates whether it was submitted or approved.

here's the approval section after submitting the reqeust

now section1 is locked and the approver receives an email and open this form.

There are common rules for each section and controls.
1. if nextperson != currentuser
2. if count != 2(section number, in this example it is 2)

and here are some rules for PhaseApproval button

and submit button rules

again you can ignore steps field.
I set NextPerson = Initiator since in my workflow, it has to go back to the creator for the phase 0, 1, and 2.
Phase field is equal to one since this is an approval. (zero for submission)

that's it. now I can have a multiple approval form.
By using publishing wizard, I can now add columns in SharePoint Site that I need for workflow.