Contact
My name is Clayton Cobb, and I am here to talk about anything and everything within SharePoint that I can. I am not a developer and therefore try to accomplish as much as possible in a codeless manner. Approaching things this way has allowed me to learn many advanced things that most don’t know are possible. Much of what I do is typically done through code by others, but it can be done without code and still be very effective while still being maintainable by non-developer business users.
I am a former enlisted Marine who served one tour and luckily ended my service in August of 2001 (lucky that it was a month before 9/11). From there, I joined Booz Allen Hamilton where I attained my degree at Strayer University followed by an MBA at Johns Hopkins University (JHU) where I have 4 electives remaining. After 6 years at BAH, I went independent as a consultant to focus 100% on MOSS 2007. I was later hired by Planet Technologies as a Technical Architect and Central Region Practice Manager (Denver-based), which is where I work now in the support of the National Renewable Energy Laboratory.
Feel free to contact me in these ways:
- Personal contact – warrtalon@gmail.com
- Professional Services/Consulting – ccobb@go-planet.com
- Facebook: Clayton Cobb (Denver)
- Twitter: @Warrtalon
- LinkedIn: Clayton Cobb – SharePoint Architect
- Live Messenger: warrtalon
Clayton's SharePoint Madness 
David Lozzi said
Hi Clayton,
I just added you to my blogroll, would you mind returning the favor?
Thanks,
David Lozzi
Sylvia said
Hi,
I happened to stumble across your site while looking for some Infopath/Sharepoint help. I am not a developer, just an engineer who has started playing in InfoPath/Sharepoint for several months now. I dont know if this is the right forum to ask for help, but if you can I would really apprecite it. I have created a form and linked it to a sharepoint site. In my form I have a created a repeating table with a drop-down list box for a ‘distribution list ‘ which is populated by the sharepoint. Therefore, the drop down list ‘value’ is the email address from sharepoint, and the ‘display name’ is the user name from sharepoint. All of this is working fine, however I have added a button below the repeating table (drop down list) intended to send an automatic email to the names selected in the drop down list repeating table. After reading another blog, I tried the following formula for an email Data Connection eval(eval(Distribution_List, ‘concat(@email_address, “;”)’), “..”) where ‘Distibution_list’ is the repeating list from Sharepoint (secondary data source) that populates the dropdown list and “@email_address” is the corresponding email addresses. Executing the button using this formula sends an email to all the names in dropdown list, and not the names that the user actually selects for distribution. Do you know how to manipulate the formula so that the email will only go to the users actually selected on the user interface repeating table/drop down list? Thanks very much, I hope my write up made sense.
Clayton Cobb said
Sylvia, the double eval trick will snag all iterations of data from the data source, so it doesn’t matter that you have chosen only to display a sub-set of that info in the form. I have not tried this exact thing you are trying to do, but we can either try to figure out this exact method or help you come up with another method that achieves the end goal. For now, I think the best thing to do is to go post this exact question to InfoPathDev.com InfoPath forum where you can get help from myself and a bunch of InfoPath MVPs.
Sylvia said
Hi Clayton,
I actually figured it out…ahh! Because I made the “value” of the dropdown list the actual email address (pulled from my Sharepoint connection), I didnt need to go to the secondary data source again to get them. Instead, I used the following formula: eval(eval(repeating_table, ‘concat(my:fieldX, “;”)’), “..”) where ‘repeating table’ is just that, the repeating table that holds the drop down list, and ‘my:fieldX’ is the actual dropdown list field with the value selected by the user. This seems to work…love science. Anyhow, thanks a bunch for replying to me, I really appreciate that and I agree InfoPathDev is a great forum for advice. Sylvia
urk said
Hey Clayton,
Thanks so much for this blog. Sure is a big help for us who are not developers and build forms from out of the box features of infopath.
I have a question about the getuserprofilebyname data connection. I use it extensively on all my forms to get the information of the person filling out the form. My problem is how to pull the information of the next person who will view the form.
As an example: A manager fills out a promotion form then clicks a button in the form that will then save the form in a sharepoint library. Now this will generate an email to Human Resources and IT to inform them of changes. Now my problem is I want to show and hide fields on the form based on the department of the person who had the form currently opened.
I tried having a field populated using the getuserprofilebyname but when the form is saved and then reopened the data won’t refresh. I tried clearing a that field through a workflow and then adding rules to query the dc when that field is blank but it still wouldn’t work.
Thanks in advance and best regards
Clayton Cobb said
Are you getting your values by setting fields with default values? If you populate the department field using a default value, then it will only set once when the first person opens it. All subsequent users will not trigger that default value to change. Instead, you have to set that field’s value using Form Load rules in Tools > Form Options > Open and Save > Rules. This way, whenever the form is opened, that department field will get filled with the current user’s department, which then allows you to show/hide sections/controls based off of that user. You can also do this for any other attribute of the current user that comes from the UserProfileService. I believe this is explained in my post about this topic. I thought that’s what you would have been referring to when referring to the blog’s value. Anyway, it’s the one named InfoPath – Get user information without writing code (extended): https://claytoncobb.wordpress.com/2009/06/21/userprofileservice-extended/. You should be able to see this concept in that blog post. You can also see about leveraging info like that as a means of User Roles for browser-enabled forms. If you are using rich client forms, then you can use the built-in User Roles.
urk said
Found the solution. I just created a rule on the Open Behavior.
urk said
Thanks
urk said
Just want to express my gratitude for puting up this blog and helping out people. The world needs more people like you.
Best Regards
URK
Clayton Cobb said
URK, you’re welcome and thank you for the uplifting words.
Vinay said
Hi Clayton,
I looked at one of your posts on the infopath dev forum – it was regarding creating repeating table from a sharepoint list data source. I used the solution and able to see the table populated, i’ve a further requirement though – i need this list filtered based on a value from another field on the form i.e. the data should change as the value in the other field changes. Is this doable with configuration and without any programming?
Thanks for your help in advance, I’m learning this tool faster just by great solutions posted by guys like you. I truly appreciate that.
Thanks
Vinay
Clayton Cobb said
Vinay, yes you can definitely do this with ease and without programming. The only caveat is that my solution to this only filters the data after it’s been pulled to InfoPath. We can’t filter on the server side without code or something like Qdabra’s SharePoint Query Webservice (Qdabra runs/owns InfoPathDev.com). If you’re ok with client-side filtering, which is transparent to the user but still requires downloading all the data first, then you can do this:
1) On your repeating table that is tied to the SharePoint data connection, double-click the table itself to get to its properties
2) Add conditional formatting that hides the control whenever a value in the row DOES NOT MATCH the value in your form that you’re talking about
3) The result will be that only the rows with the value matching your other form selection will show and all others will be hidden.
Does this work?
Bill said
Hey Clayton,
I am in the process of looking for a solution to index PDFs. I have been reading about Adobe IFilter but I wanted to ask an expert if this is my only option and if this is the best option.
Thank you,
Bill
Clayton Cobb said
Bill, it is pretty standard to install the PDF iFilter in SharePoint so that PDFs are properly recognized in libraries with their icon and so that the text can be indexed. The main concern is that this only works for distilled PDFs, not for PDFs made from scans, because the text cannot be parsed. For something like that, you’d need an additional OCR tool that integrates with SharePoint like the one from KnowledgeLake. Also, there are free PDF iFilters that work fine, but they aren’t as robust as the commercial ones that you can purchase.
Bill said
Thank you for the information. I think we can get by right now with the free IFilter from Adobe but that is good to know about scanned documents. I need to evaluate the data that will be stored on the site and see what our requirement will be with this.
Thanks again.
Bill
Sherry said
I sent you the xsn for my form. Any ideas on why I can get anything but the guid or blank for the submitter field? Does anything make a difference if the server it’s running on is a standalone and not a farm?
Clayton Cobb said
I have it but haven’t had time to review it yet. Standalone shouldn’t matter.
Sherry said
Ok, thanks. I’ll wait to hear from you. I appreciate any insite/help you can give.
Husain said
Clayton,
Thank you for helping non developers like us building SharePoint solutions
I am very impressed to see your responses to our Sharepoint Queries
I am in the process of creating a CUSTOM LIST which has two columns
If you can throw some light on how to do following
Custom list called Projects
Column 1 – Category –> values: Hardware, Software, Operations
Column 2 – Sub category has these values –> 1,2,3 – 4,5,6 – 7,8,9
1,2,3 belongs to Category Hardware
4,5,6 belongs to Category Software
7,8,9 belongs to Category Operations
I want to filter sub category data in that column to display only the related data based on Category column value selected
Please help me if you know how to build column data filtering solution in SharePoint custom List
I saw your excellent helpful reply on building solution to the same type of issue using InfoPath
http://social.msdn.microsoft.com/Forums/en-US/sharepointinfopath/thread/126b6182-2643-46e6-8a84-3f1294fa5beb
But I want to accomplish this in SharePoint List if possible.
Thank You & Appreciate your help
Husain
732-986-8717
Hemant Kamalakar said
Hi Clayton Cobb,
I am a newbie in Sharepoint. I have seen your foum posts on MSDN. They are very useful, Thanks for sharing.
I have some queries.
1.I am looking to store transactional data in SharePoint. The items will easily grow to 100,000+.
2. What is the best practice to store these items in Sharepoint.
3. I am trying to use Infopath forms along with Webservices to store the data in Form librrary. Is it fine to use the Form library in this case?
4. What will be the performance considerations for sharepoint Views?
Kind regards
Hemant
Clayton Cobb said
Hemant, thanks for the comments.
1) I would need more info about this transactional data. What is it, where is it, what does it contain, why do you want it in SharePoint as opposed to whatever database it’s coming from? 100,000+ is not close to what a SharePoint list or library can handle, but it’s a large enough amount that it requires careful planning
2) Depends on what “these items” are, so I’ll need more info.
3) What does the InfoPath form do for you in regards to this data? If you want to use web services to populate the data into an InfoPath XML file, then I’d again ask why the data will not remain in the source database. I’m not sure a Form Library will be the best answer to this situation.
4) The Microsoft recommended limit is 2,000 items per container, and you can think of a view as a container. You basically don’t want to display more than 2000 items in each view, else it will degrade performance.
Joyce Hughes said
Hi Clay,
You have helped me before and I thought of you after looking around the net to no avail regarding the following issue.
I have a list in Sharepoint and want to be able to get the data out and put into a SQL server so I can create Reporting Services reports on the list. Do you know how to do this or have a link to someone who can give me good instructions on how to do this? Any help would be greatly appreciated. Thanks!
Joyce
[Giga] said
Joyce, we looked in the SSRS options with sharepoint, there’s many solutions for this. You may not know that reporting services is in fact datasource agnostics, which means that it supports external datasource, it’s important to note that you need at least SQL Server Standard edition for this. Using SSRS you can configure a XML datasource and mapped it to Sharepoint Web services, so you can query it and return with it’s results which can then be direclty used in the report.
Here’s a pretty good article on how to accomplish this: http://vspug.com/dwise/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux/
Please note that if you want to join data from different list, there’s some products out there that can do this as well, most of them add a special sharepoint datasource to SSRS.
Hope this helps you out!
Mary Ann D said
Clayton, I was not sure where to place this question and you’ve been such a big help in the past I thought I would ask.
I am creating a browser enabled form in InfoPath 2007 using an auto populating template part that gives me (among other things) the user’s badge number (which I should be able to use to send an email). I have two data connections, one for submitting to email and one for the SharePoint library. I am creating submit rules sending the form to both the SharePoint library and via email to the requestor cc’ing myself. The CC to myself works just fine, but the “TO” option does not. I was simply doing an ‘insert field or group’ and selecting ‘badge no’ from my template part data connection. This is not working and I was hoping you might have an idea.
Clayton Cobb said
Are you certain that 1) the badge number is being properly placed into the To field, and 2) the badge number can be resolved to a user’s email address? Typically, only the full domain name or user alias can be used in place of the email address. Is the badge number set as the use alias in AD or something?
Clay Cobb
Mary Ann D said
Clayton,
1) I am not sure how to check this. I can say that the badge number is populated correctly on the form.
2) Corporately we can send emails using just the badge number.
3) I also am using your auto num instructions so the ID field is available (I’m assuming the ID field is the badge number if my case). Should I possibly be using that?
Clayton Cobb said
1) If this was a workflow, you could log it to the history list first before sending the email just to verify. Since you’re sending directly from the form as a submit, then you can only test it by placing that same field value in your subject line or something like that so that when YOU receive the test email, you can verify which value is being used by the form to put in the To line (assuming you don’t already see it in the To line on the email you receive) 2) Doesn’t necessarily mean you can use it in InfoPath 3) The ID of the form in the form library is not related to your badge number – this should not be related to the identity of the person who created the form. It’s just a sequential incrementing unique number created by the SharePoint database.
Mary Ann D said
1) It’s not a workflow just data connections and the submit option. We utilize Nintex workflows and I could get this to send an email to the ‘initiator’ along with the attachment, but the email message said there was an error (although the attachment looked fine). According to our IT department, our configuration does not allow this through Nintex. I did put the badge number in the subject line and it came through fine.
2) Possibly this is not doable.
3) Thanks, I always wondered about that.
Clayton Cobb said
1) How did you see the attachment if you the email errored? I’d be very surprised if something as robust and expensive as Nintex couldn’t send a basic email. I can do everything you’re talking about here without code in a SharePoint Designer workflow, so surely Nintex could do it 20 times over without blinking an eye.
1a) If all you’re trying to do is send an email to the form creator/originator/initiator, then simply put the userName() function in your To line. Simple as can be unless I’m missing something.
Mary Ann D said
1) That was my point to our IT Department. The workflow worked, I viewed the attachment, but the email indicated an error message. I would have been happy if I could just suppress the message but they said that was not possible. They have locked us out of some functions.
2) I am simply trying to allow a user to submit the form, send it to a SharePoint library and have the user receive a copy of the completed form. I have a submit data connection called SubmitEmail. I configured the ‘TO’ by inserting the function username() and also placed that in the ‘SUBJECT’ line. I put my physical email address in the ‘CC’. I submit the form and receive it as the ‘CC’ but not as the ‘TO’. The email I receive as ‘CC’ shows the following:
FROM: SharePoint@companyname.com
TO: my badge number
CC: my name
Subject: my badge number
Clayton Cobb said
1) I’m confused by #1. You are saying that you receive the email like normal but “the email indicated an error message”? What does that mean? How is there an error message if the email went through? I don’t know what that means.
2) That is very simple if you use userName() (not username()) – the syntax is important. You said, however, that when using that value and putting it in the Subject, it shows your badge number? So, everyone’s username is their badge number? Is that how you log into your machine…by typing your badge number then password? If so, then it should work fine. If it’s unable to send to your badge number, then there are two things you can try. 1) append the domain name to your badge number by using concat(“domain\”,userName()) and putting that in the To line. Or, simply use the GetUserProfileByName web method to retrieve the email address of the current user and put it in the email’s To line. I have the article for how to do that right here on my site: https://claytoncobb.wordpress.com/2009/06/21/userprofileservice-extended/
Outlook 2003 does not affect the sending of the email, but it does have an effect on viewing. You can receive “submit to email” InfoPath forms if the user has InfoPath installed, but they can’t interact with the form. Btw, why do you want to send a copy when the form is submitted to SharePoint? Why aren’t you just sending a link to the form where it resides in SharePoint so that they can open it if they want to see it?
Mary Ann D said
1) It was a Nintext error in the transmittal email:
“An error has occured in test workflow.” and yet I received the email and could view the attachment.
2) We logon using our Badge (c12345)and then a password but the userName() did not work, even when adding the domain concat(“ENT/”,userName()). I’ll take a look at the link you suggested but it’s a long process so it may take me a bit.
3) As part of our SLA, the user must receive a push of the completed form versus pulling it from SharePoint.
Clayton Cobb said
1) This whole time, i thought you were saying that the Submit to Email action of InfoPath was giving the error. I assume there is no error with the InfoPath submit to email action, but rather that it never shows up for the person in the To line, right? 2) That is very strange – I’ve never had that occur on any of my forms, but then again I only send emails with my workflows instead of trying to sending a duplicate of the form via email. 3) That’s a shame. it exists in sharePoint where it should exist and not be duplicated, so it’s too bad that someone put that into the SLA. In every form I build, I reference it via URL in workflows and haven’t run across a case where I was _required_ to send another copy via email (luckily).
The link I gave is not actually a long process – it’s very short, and it is something that you will use in every form you create once you learn it. It is the single most powerful tool available for InfoPath forms in a MOSS environment, especially because it requires no code. It is incredibly useful and versatile, so don’t let the article length bother you. Once you do it, you’ll see it’s easy to do over and over again.
Mary Ann D said
Clayton, Just a shot in the dark. Is it possible that this won’t work because I am using Outlook 2003?
Mary Ann D said
Clayton,
I’ve secured permission to send the submitted InfoPath form link via an email using a Nintex workflow so I will use this as you suggested. In the notification workflow I insert the reference ‘item url’. This works fine and places the url in my email, but the recipient will have to cut and paste it into their browser since it is not an actual hyperlink. Looks like this may be as close as I will get. Thanks.
Clayton Cobb said
You should be able to hyperlink it. Just add basic html. I do this in every workflow I create with SharePoint Designer. I use the form Title field and hyperlink it with the item’s URL. I would be surprised if a Nintex workflow can’t do that…
-Clay
Bob said
I’ve been struggling with this problem and surfing the web, it seems others have to. I’ve come across your solution to grabbing multiple user values from the contact selector —
1. Incorporate Contact Selector into your form
2. Create a single text data element to store your list of email addresses (same as the HLinkToEmail field in Alec’s solution)
3. Simply set that field to default to this Xpath expression: xdMath:Eval(xdMath:Eval(../my:Requestor/my:Person, ‘concat(my:AccountId, “;”)’), “..”) In my case, Requestor is my name for replyToControl.
This works, but returns AccountID.
DOMAIN\Username; DOMAIN\Username
Where I’m stumped is how do I convert this to email addresses to populate the “to” field in the SharePoint Designer email workflow?
Thanks in advance for your help… this one is driving me crazy!
Clayton Cobb said
Bob,
You don’t have to convert it to email. That value works for sending emails. That’s exactly what I do with it – use that resulting value in my To: field. That’s why they are separated by semicolons. It doesn’t work in Outlook, but it should work in the workflow. This is how I do all of my SPD emails, so give it a shot, or at least test it with a sample workflow that only sends an email to your domain name.
-Clay
Harsh said
HI Clay,
I have a two dropdown list in the InfoPath form 2010, and want to fill with the sharepoint list that has been done, but on selection changed of first dropdown second dropdpwn should be populate.
Suppose if i change the state name then the city dropdown should be populate accordingly.
and on submit the button the form should go for approval in the documnet set, how can i achieve this?
Thanks in advance
Harsh
Clayton Cobb said
Harsh, 1) Set data connections for retrieving cities and states (be sure that each city has a city associated with it) 2) Set each dropdown to be connected to external data sources from step 1 3) In the City dropdown, set a filter on the data connection where State from the data connection is equal to State from the form (as defined by the first dropdown field)
This will cause your City dropdown to only show cities associated with the State chosen in the State dropdown. I don’t know what you want to do in a document set, though.
mark said
hi clayton,
maybe you can help me with this little prob
we are using content approval for one of our libraries.
i tried to change the “approve/reject” view but didn’t save something. after cancelling, the items are no more grouped by “status” and there is no option were i can check this column. Same happened to the “my submissions” view. Do you know a way to solve this issue?
best regards
markus
Clayton Cobb said
Mark, I haven’t experienced that before. Are you able to confirm that it works fine in other lists where you didn’t do the modify/cancel combo? I don’t have time to go test this out right now, but I wanted to at least reply so that you didn’t wait around. I recommend posting your issue on the MSDN SharePoint forums, because it will get seen by many more people and likely answered.
http://social.msdn.microsoft.com/Forums/en-US/sharepointgeneral/threads
Ben said
Hello Clayton,
I have a problem that I hope you can help me out with. I have written a form in InfoPath 2007 that goes through Microsoft outlook. It is emailed as an attachment that goes to the manager and VP before getting stored in the Document Library. It is basically an approval workflow through Outlook. I have two views and the second view I made read-only, and in the rules I make the form switch views before I submit using a data connection. The form contains textboxes and buttons for submitting information. I have found out that I couldn’t send the form as a regular email because the submit buttons wouldn’t show for security reasons. Now as an attachment, no one can open it up for security reasons, even though I can, if I send it to myself. Also I am having trouble with the textbox being read only when I submit the form to myself. I saw your video about making a leave form and that is along the lines of what I am doing but mine is in 2007 and it is through email. Any help on the read –only textboxes and users opening the form would be greatly appreciated.
Thanks,
Ben
Clayton Cobb said
My recommendation is to not send it around via email, but rather it should be initially submitted to a form library, which triggers a workflow email that notifies the Manager and VP in sequence. In their notification email, they should get a hyperlink to the current form so that they simply click the link, and the form opens from the form library. They are presented with the proper view and click the submit or approve buttons (whatever you want them to see). This is the preferred method over sending it around via email.
If you have a single field that shows up as read-only, then it must have conditional formatting on it that disables it, or you have it set to read-only. It can’t just become read-only by itself. Unless it’s not just a single text box, but rather the whole view, which means you are seeing the read-only view inadvertently.
Ben said
Dear Clayton,
I don’t think my bosses want everyone to know whose got approved/disapproved for what, which is why I was sending it through email. Is the way you are suggesting private? Also, thank you for such a quick response.
Thanks,
Benjamin
Clayton Cobb said
It can be private if you incorporate dynamic permissions into the workflow so that each form is only visible to the people designated in the form.
How were you making it private before when the form ultimately was ending up submitted back to the library?
Clay Cobb
Ben said
Dear Clayton,
I would drop the form if it were disapproved and send it to the manager’s document library if it were approved. Do you have any blogs on the techniques that you described earlier, so I can read up on them? Again thank you for the quick response.
Thanks,
Benjamin
Clayton Cobb said
Not yet, but I do need to write one up. I have so many techniques that need to be blogged, but I just don’t have time.
I am not keen on the way you’re doing it, because if something is rejected, it should not disappear. It should still be saved and logged for historical purposes as well as reporting purposes. Additionally, if it’s approved, it should not go to a document library. It should remain in the form library where the form template originated.
I’ve written up the basic instructions on the MSDN forums many times, which is why I should have blogged it by now (same reason I wrote all my current blog articles), however, it’s pretty simple. Go to Codeplex and find the Custom SPD Activities. Download and install that set of custom activities so that the special permissions actions show up in SharePoint Designer. Then, go create a new workflow on your site that is attached to the form library with the first action being to delete list item permissions on the current item, and the next action grants item permissions to the proper users (user who created the form, manager, VP, and whomever else) on the current item.
Ben said
Dear Clayton,
Much Thanks, at least i’m pointed in the right direction now.
Benjamin
Ben said
Hello Clayton,
I have another question, can you put a document library into another document library? The reason that I ask is because I have some forms that open in a browser and when they are centrally located in a document library they won’t open except in an InfoPath format. Any help would be appreciated.
Thanks,
Benjamin
Clayton Cobb said
I’m not sure what you mean by the question, but if I take your question literally, then the answer is “no.” A document library can’t exist within another document library.
Why are the forms centrally located? You can save the form templates in a central location as content types that everyone must have Read access to, but then the individual content types should be associated with a form library wherever they are supposed to be used (the forms shouldn’t be used within the central doc lib).
Ben said
Hello Clayton,
I have another question for you. Is there anyway that you can have a browser come up in a different view? I have this form that comes up in view 1 to be filled out, then it is saved to the sharepoint site. Once someone else opens up the browser it comes up in view 1, I would like it to come up in view 2. I have tried to use a button in submit form and rules and custom code form, but to no avail, any suggestions?
Clayton Cobb said
Yes, that’s pretty easy. You need to use a Form Load rule that switches the view when the form is opened, and if you ALWAYS want to switch to View 2 by default after the form is submitted, then base it off your filename field. For instance, if you use my Auto-Generating Filenames blog to auto-generate your form filenames, then you have a field named strFilename. All you’d need to do then is add a Form Load rule that says “If strFilename is not blank, then switch to View 2.” That way, View 1 will never open again unless you put in some other form load logic. I typically have several views and lots of Form Load logic for switching views based off the current user’s role and/or the workflow status.
Ben said
Hey Clayton,
Could you send me the link to your Auto-Generating filenames blog? I couldn’t find it.
Thanks,
Ben
Clayton Cobb said
It’s right there on my home page. Click Home or Blog Listing (same page). All blog articles are listed there.
Ben said
Hello Clayton,
I have a question, I am interested in learning SHarePoint 2010 and I feel that hands on learning would be the best. I am getting a Laptop with VM ware installed to practice some SharePoint 2010. I would like to know what you recommend for using as a server? Should I download windows 2008 and sql 2008?
Thanks,
Benjamin
Clayton Cobb said
Windows Server 2008 is required at a minimum for installing SharePoint. For SQL, you can use SQL 2005 with the right service packs, but I recommend using Windows Server 2008 R2 and SQL Server 2008 R2 for everything.
armin said
Hi
Tanx for your blog
I’m designing my sharepoint site and i have some problems .
i just read this blog : http://erikswenson.blogspot.com/2010/01/style-individual-navigation-items-for.html
but i donno how he did it , how did he eidt the html code and save it ?!
did he use sharepoint designer ?!
do u have an idea !
please help le out here
please !
Clayton Cobb said
I’m not sure about someone else’s blog post. Have you asked the author yet?
InfopathRook said
Hey Clayton,
You have a great site with a lot of content and the leave request form that you posted up on your YouTube page is exactly what I need to do for my company. I am extremely new at Infopath and I don’t know how to use code at all. I have done simple things in Infopath (designing, creating data connections,…), but I am getting into the more complicated features that I need to make the form really useable. I would like the PTO form to, upon open, to display the current date(done), current name of user (done), PTO hours taken already in the year(problem). How do I go about querying that info (PTO hours taken for the current user) from either a SQL or Access database? It would also be great if the PTO hours would be updated in the database to make it more dynamic. I will also eventually get into the workflow features but one thing at a time!
Thank you so much for your help!
Clayton Cobb said
I’m updating the data in the database in the video – you saw that part, right? I’m doing that by creating an external content type to the database table that contains PTO hours, and that table also contains the usernames. I then created an external list for that ECT so that it would be exposed in SharePoint and available to both my InfoPath form and SPD2010 workflow. In my InfoPath form, I consume that contents of that External List with a basic list data connection, but I use a parameterized query to only retrieve the PTO days for the current user. I’m getting total days remaining, but you said you want to receive PTO used for that year? Why that instead of PTO available? It seems that available PTO would make more sense, but you could also show hours used in the current year IF that data is actually kept somewhere for you to reference.
When the # of PTO hours is chosen AND THEN APPROVED, you use an SPD2010 workflow to update the External List, and you only update the item relevant to the current user. You first do the math in the form for how many days should be remaining, and then you write that resulting number into the database.
InfopathRook said
Hi Clayton,
Thanks for your response. I haven’t been able to work on this until now but I tried adding a ECT with the SQL database. I was getting errors and found out that we are running SQL 2000. I researched it a little and found out that other people are having the same problem. Not sure if my boss wants to upgrade the SQL on that server, but do you have any other ideas/solutions for the form I want to create?
Thanks!
Clayton Cobb said
2000 is very, very old. I don’t have a solution for doing it through BCS, so my only suggestion is to build some web services for interacting with the data. InfoPath works well with web services, but the organization really should get within the same decade, imo.
InfopathRook said
Hey Clayton,
We were able to connect to a SQL 2008 database and created an external list. I opened up a blank Infopath 2010 form and followed your instructions you had in this post:
http://social.msdn.microsoft.com/Forums/en-US/sharepointinfopath/thread/5aa69eae-b02e-4b11-81e0-f0dad43f8bdd
It worked, but when I tried to add the external list as a data connection on the a form that I had created awhile back, I don’t see all of the available fields. I removed the other data connections that I had in that form to see if that is the problem, but it wasn’t. Any ideas?
Thanks!
Kris Reynolds said
Clayton,
Thanks for all the information. You are the only person I’ve seen describe the issues I’ve run across trying to create status reports in InfoPath. I’ve downloaded qrules and want to run them in the SharePoint 2010 sandbox but am having trouble figuring out how to do this. Do you have information on what to upload into the sandbox to enable the qrules functionality for InfoPath 2010 forms?
Thanks,
Kris
Clayton Cobb said
Kris,
You don’t actually upload anything. You just build the form with qRules, then publish it to your form library. As long as User Code solutions are activated, and you have permissions, and the code doesn’t require Full Trust, then it should create the code project while publishing and deploy straight to the sandbox. I’ve done it with qRules, and it worked directly without uploading anything. I just had to ensure the User Code service was running on my WFE.
Kris Reynolds said
Thanks that seems to work. I’m able to publish my injected form to my Forms Library without seeing any errors. The correct form is invoked when I add a new report. However, I’m not getting the CopyTable functionality to work. Any best practices for debugging? I have the data connection set up correctly and have made sure the list doesn’t have any blanks.
I have a question about the CopyTable “rowsrc” value. SharePoint 2010 uses a syntax with a colon character, “:”, in it, e.g., d:SharePointListItem_RW. Would this work, /rowsrc=dfs:d:SharePointListItem_RW? I tried the command with and without the “d:” but both don’t work.
Clayton Cobb said
I’m not sure on those off the top of my head, and I don’t have time to dig into it at the moment.
Kevin H said
Clayton:
Thank you for taking the time to document the information you have, it is a great help to all of us getting up to speed with SharePoint. My question, if you have some time, is related to the post back in 2009 about autonumber fields. Has any of the actions changed with 2010? While following your steps I have not been able to find the function that adds the @ symbol in front of the ID such as (@ID). I greatly appreciate you experience.
Kevin
Clayton Cobb said
The @ symbol just shows up whenever referencing a field from an data source.
When creating your data connection to the list, you get a screen that lets you choose the fields you want to retrieve. Be sure that ID is checked on this screen, and if so, then the ID field will be usable in your formula.
Kevin H said
Clayton, I still have not been able to get the @ symbol to appear before the ID in Infopath 2010. Not sure if that is something that changed with the new version; however, I have been able to follow your directions and make this form autonumber! THANK YOU VERY MUCH. I appreciate your help. Kevin
Beckie W said
Clayton,
I was hoping you could point me in the right direction on how to use one (external data) repeating table to populate a table in the main data source with the items in the external source. I need this for mananging pricing on inventory & am apparently deficient in searching for the correct words on this! (hours!!)
The external source will grow and shrink. Oh & I’m not a developer, so code free would be ideal! (But will learn to code if necessary for this.)
Have a great weekend,
Beckie
kimvlas said
Ey Clayton,
I’m working with designer 2010.
whitin sharepoint i have a custom list that holds several holidays.
the date stand in like this : 05/11/2010
i have imported this data with the data connection wizard.
what i want to with my form is the following.
a user can select 2 dates, when a holiday in between those dates, the amount of days of vacation is than be lowerd with 1 if its 1 holiday, …
you get the picture.
if i select a date in my form the date is: 05/11/2010.
when i place the date that comes from my list in a textfield i recieve: 2010-11-05T00:00:00
the 2 date’s are not the same and my code will not work, how can i change the date format of the impoterd list.
regards,
kim
Clayton Cobb said
Kim,
The first key is not to import data like that, because it’s a crap shoot with data types and conversions. You didn’t mention where you imported it from, so I’ll assume Excel. What you do is create a plain custom list, then you create all the columns manually so that you get the exact name and data types that you want for each (site columns preferably). Next, add the columns to your list. After that, change your default view so that the columns are in the exact order of your spreadsheet (or change the order of the spreadsheet columns). Lastly, click Edit in Datasheet for that list, and copy/paste the data from the spreadsheet. After this, all of your data will be clean and in the exact format/datatype that you want.
The second key is not to use a text field, because that is going to show raw XML instead of showing you a date. If you’re using dates, then use date controls and date type fields. If it’s a list you converted to InfoPath (you said you’re using Designer, so I’m a little confused, because that would mean SharePoint Designer), then the data types are going to be whatever the data connection wizard created when you did the import. This is also why it’s important to make the columns yourself.
kimvlas said
Clayton,
thanxs for the quick response.
the data come’s for a custom sharepoint list.
the reason wy i want to do this is the following.
when i use a standard xml document and i place my data like this:
this will work, if a data changes my form must e published again.
so i thout if i import my sharepoint list with infopath, there is a connection between the form and the sharepoint list data.
if i check the details from the imported list date is a “dateonlytype”.
thill now i havent had any control of how my list data will appear.
could it bee that i’m thinking wrong?
so yes do you have a different solution for pointing to the wright way.
N03L said
Hi Clayton.
I’ve read most everything you’ve written and I’m at the point where I consider myself a fan. Wierd huh?
I’m trying to do something fairly simple so I hope you don’t mind ‘dimbing down’.
I’ve got a Section in an InfoPath form that is hidden until the user selects a specific choice from a dropdown in the form. The form is an expense claim form and if the user selects ‘Mileage’ from the ‘Expense Type’ drop down in a repeating table then the section appears and must be completed. The problem I have is that when the user adds a new row to the repeating table, the section disappears again until they select ‘Mileage’ again in the expense type drop down choice options. If they don’t the section remains hidden.
My question, after all that is, How do I set the formatting rule so that after the section is displayed it stays displayed?
Thanks in advance for any light you shed on this.
N03L.
Clayton Cobb said
Noel, change the condition on your section so that it says “If All occurrences of Expense Type are not equal to Mileage, hide this control”
Ryan said
Clayton,
Hi, I have a question pertaining to an article that was posted and on which you commented. I’m new to SharePoint/Infopath and am kind of stuck, so I hope you don’t mind helping me out. Here is the article:
http://www.infopathdev.com/blogs/matt/archive/2006/04/18/Automatically-Populate-Fields-from-a-Drop_2D00_Down-Selection.aspx
Like a few others in the comments section, I am trying to populate a bunch of fields from one dropdown, but from a SharePoint list rather than an XML file. Where I run into problems is when I try to set the rule. When I click the “Edit XPath (advanced) box, this is the path I get:
xdXDocument:GetDOM(“Pyrimont Employees”)/dfs:myFields/dfs:dataFields/dfs:Pyrimont_Employees/@Name.
I add the “current” part so that it looks like:
xdXDocument:GetDOM(“Pyrimont Employees”)/dfs:myFields/dfs:dataFields/dfs:Pyrimont_Employees/@Name[../Name = current()]
But I get the following error:
“”xdXDocument:GetDOM(“Pyrimont Employees”)/dfs:myFields/dfs:dataFields/dfs:Pyrimont_Employees/Name” does not point to a valid location path of a field or group.”
The Main data source “myfields” are named “Name” and “Email”, as are the secondary data source fields in the SharePoint list.
I’m not sure where I’m going wrong.
Clayton Cobb said
Ryan, I don’t know the exact syntax off the top of my head, but my suggestion is NOT to hand type it. Instead, build the function the normal way using the wizard. The current() function will automatically show up if you build the function properly. To do this, you set the rule, add an action to “set a field’s value,” then you single click the secondary data node, click filter, and add the appropriate filter. Do all of this from the dropdown field in your repeating table. If current() still doesn’t show up, then set default values on those other fields using the same method. Doing it the right way allows it to create the current() statement for you.
Ryan said
Thanks Clayton! It’s working great now.
Just out of curiosity, is it possible to do the same thing in SharePoint without InfoPath… create a form that populates a bunch of fields based on what is inputed into another?
Clayton Cobb said
Not out-of-the-box, but you could write some code to do it.
Ryan said
Can you tell me if I’m on the right track?
I have a list called “Customers” that has a bunch of text columns (Store, address, city, state, zip, priority code, etc.)
I created a new tasks list where the first field is a dropdown that looks up the priority code. I’m trying to make the second column populate based on the store field in the Customers list.
Here is what I am trying to input:
=IF([PriorityCode]=[247PriorityCode],[Store],)
I’m getting the following error:
“One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.”
My thinking was to make it return the “Store” field if the priority code from the Tasks list equaled the priority code from the Customer list (which it always would). It doesn’t seem to like that, though.
Mike Lee said
Hi Clayton. I am evaluating qRules and Submit to Sharepoint list for a specific project. I am encountering problems with the submit to sharepoint lists. I have watched the video demo but the form we are evaluating is constructed slightly differently.
I am using Infopath2010 and Sharepoint2010 (enterprise).
The form has (without the qRules addins) two drop down fields populated from other sharepoint lists (secondary datasources) and a “Run query” button. This when run returns the rows from the main sharepoint list (again this has been setup as a secondary datasource – but does not have to if I have done it wrong) filtered to the selections made in the drop down lists.
When I try to use the “submit to sharepoint” mapping tool I do not see any fields to map to. I understand that it is returning the “main” fields, of which I have none.
My question is that as the form is functioning and querying the relevant sharepoint lists, is there a way I can then submit this repeating data back to the Sharepoint list using qRules. If this can be done then we will most definitely be purchasing the products and most likely dbxl as well. This functionality will remoe our need for dedicated VPN’s for database transactions and sharepoint lists being used for smaller datasets where appropriate.
Any help/guidance would be appreciated.
Cheers
Mike
Clayton Cobb said
Mike,
What is the basis of this form? Is it a list form where you customized it with InfoPath, or is it a regular form in a form library? I ask, because this qRule was made for 2007 where there is no such thing as a “list form.” That’s a new thing in 2010. So, I’ve only used this qRule for taking repeating data from the XML file of a form library and submitting it to a separate list as individual list items. I can’t tell for sure, but it almost sounds like you’re trying to do it from a customized list form, but I don’t know where you’re trying to submit the data. Is there a 4th list involved, or are you trying to submit the data back to the same list where the form resides?
Mike Lee said
Hi Clayton thanks for the response! This is not a list form using 2010 but is created from a blank form template. I created two dataconnections to two sharepoint lists used to populate the drop down selections. I then created a dataconnection to the custom list which I want to populate to/from. The custom list will never have new items entered or deleted, only existing rows updated. I preview the form and make the selections. then click the run query button and the repeating table returns with the filtered query dataset. We need to be able to then update the required fields and have that populate back to the Sharepoint list. The form itself will be sent to a forms library for audit purposes. Sorry if my questions are noobish.
Any help would be appreciated.
Mike
Clayton Cobb said
Right, so that’s what the qRule does. It lets you send repeating data to a list as individual items. However, the data you’re retrieving from the data connection is secondary data. To use the Submit to SharePoint, it needs to be in the main data source. So, I believe you’ll need to use the CopyTable qRule to copy from secondary to main, then manipulate the main data table, then submit to SharePoint. Upon submit, it will update the proper rows as long as you maintain the ID for reference purposes, and then you can immediately re-query to see the data update in the secondary data source. I have a blog for the CopyTable piece, but the Submit to SharePoint blog is on the InfoPathDev site (run by Qdabra).
Mike Lee said
Thanks Clayton, I’ll have a crack at that. Does my logic sound feasible? Do you know of any demo’s online that do similar to what I am trying to do? By that I mean, make a selection, get a subset of results, modify the data and submit back to a sharepoint list….Again thanks heaps for the advice.
Clayton Cobb said
It’s very feasible except for the fact that you’re trying to modify repeating data – that part just isn’t available at all with InfoPath unless you use code, thus the qRules. InfoPath is for submitting one item at a time, but you’re trying to manipulate and submit multiple items at once from a single source. Nothing in SharePoint even does that except for Datasheet view, I guess.
kimvlas said
ey clayton,
when i make a data connection with my sharepoint list.
i recieve my data, is it possible to make a function like for(…;…;…) like within c#?
so i can check if a value starts with a specifiek letter.
i want to count ownly the amount of entries beginning with a specifiek letter.
or wath is the infopath way?
regards,
Clayton Cobb said
Yeah, you can use the count function, choose the field to count, then add a filter for “where value starts with Letter”
kimvlas said
how do you recieve the “where value starts with”?
than is it, with the expression function?
Clayton Cobb said
Do you not see it as an available operator when creating a condition?
kimvlas said
eh,
i can’t follow for a moment excuse me.
can i not fill in a function in the field properties that is must be checked for a specific number and than count the result.
my field from the sharepoint list is called “RGA”.
like: count(RGA[substring(RGA;1;4)= substring(today();1;1)]) ?
the first 4 caracterd form the RGA is the year and i want to count ownly the local year not the entry entry of the year before.
Jaws01 said
Hi,
I read your blog post on Infopath dev for promoting People Group fields (Contact Selector) to Sharepoint. I have it working via the SPD workflow using the AccountId for 1 user. How can I make this work when multiple users are picked in the Contact Selector. Thanks in advance.
Clayton Cobb said
Either make sure you use Merge when promoting the AccountId field so that all repeating values show up, or use the Double Eval trick from Alec Pojidaev’s blog to concatenate all the values into one field with them being delimited by semicolons, then promote that hidden field. Try to take this delimited set values and push it into the People Picker field.
Jaws01 said
Clayton Sir, you rock…thanks..Got this to work.
Clayton Cobb said
Great job getting it to work. That isn’t the easiest thing to setup the first time.
AML Boss Steve said
Hello Clayton:
I’m not a developer or anything close to it but I’ve noticed how helpful you’ve been to those facing InfoPath issues. That being said, I’ve had my manager, who is somewhat familiar with MS InfoPath but certainly not an expert, create a form that is crucial within our workflow having to do with required reporting to the government related to suspicious transaction activity.
When an investigator is completing comments specific to a type of activity they are researching (such as “Check Activity”), they do so in an “explanation” text box. As they continue their investigation research, they complete additional explanation text boxes for each activity (e.g., Wire Actvity, ACH Activity, ATM Activity, etc), so several explanation boxes may be built throughout the form.
What we need is to be able to “auto-fill” the “Master Narrative” text box at the end of the form with all of the content from the various explanation text boxes, hopefully by cascading it into the master narrative (e.g., explanation box 1 content, followed by explanation box 2 content, and so forth).
In addition, we have certain sentences that have dropdown and/or fill-in choices that create specific explanations of activity as well, which of course, we would also like to cascade into the same master narrative box as well. Here is an example of such a dropdown sentence:
“The suspect, (fill-in), (DD selection: deposited) a transaction of (DD Selection: cash) for the amount of $(fill-in) (DD: into) account number (fill-in) in the name of (fill-in), conducted on (DD: day), (DD: calendar date selection).”
The entire sentence including dropdowns and fill-ins would need to “auto-fill” into the master narrative box, and not overwrite any previous cascading content that had already been pushed into the narrative box (i.e., “The suspect, Maria Holly, deposited a transaction of cash for the amount of $9,900.00 into account number 123-456789 in the name of Buddy Holly, conducted on Monday, October 18, 2010”).
Right now, our investigators copy & paste the various content they have previously completed, or re-input entire sentences they’ve already completed, into the master narrative – which is very inefficient and why the auto-fill feature we desire would help us out so much.
OK…so how can we accomplish this goal? Is it even possible?
Hopeful and looking forward to your answer.
Steve (AML Boss)
Clayton Cobb said
Well, this would take some time and effort to get it right – it’s too much to just give a quick answer online without all the details. I would need to know what this “master narrative” thing is. Is it a huge plaintext field or a rich text field? Is it a repeating table or just a text box?
For those sentences, you’ll need to use the concat() function for concatenation into a single text sentence. However, dynamically moving that into some other large text control without overwriting “any previous cascading content” is tough. I don’t know what you mean by “cascading content,” and I’m not sure why something would be there arleady to overwrite. These are all critical details that I’m not sure of. You can concatenate all this text into one big text field, but I’m not sure if that’s what you want. Anything beyond that, especially if you’re referring to repeating fields, would likely require custom code.
AML Boss Steve said
First, a bad choice of words on my part to use ‘cascading;’ rather you are right, as it is truly a concatenation.
Secondly, the Master Narrative text box is just a large rich text field/box that enables the investigator to put the final analysis into a single narrative. The rest of the form walks the investigator through with questions pertaining to the specific activity they are researching.
Let me paint an example:
Check Activity:
1. Were you able to establish the source of funds for the checks issued from the account? Y/N (If no, fill-in the Explain text box below: rich text up to 250 characters)
• The source of funds for the check activity were from an unknown cash sources deposited into the account in a structured manner below the $10,000 CTR reporting threshold.
2. Were you able to establish that the use of funds through check activity was commensurate to known and reasonable patterns of activity for the suspect or the business type/occupation of the suspect? Y/N (If no, fill-in the Explain text box)
• Explain: The use of funds were unusually large purchases of phone equipment that does not align with the suspect’s known business type (construction).
3. Were you able to establish the ultimate benefit of funds for the check activity? Y/N (Both Y or N; Fill-in the Explain text box)
• The benefit of funds to ABC Telephone Products is considered unusual and suspicious for the business type of the suspect.
Specific Suspicious Activity (Dropdown or Fill-in):
The suspect, Mike X, deposited a transaction of cash for the amount of $9,900.00 into account number 123-456789 in the name of XYZ Construction, conducted on Monday, October 18, 2010.
Master Narrative (concatenation from above – rich text up to 2500 characters):
The source of funds for the check activity were from an unknown cash sources deposited into the account in a structured manner below the $10,000 CTR reporting threshold.
The use of funds were unusually large purchases of phone equipment that does not align with the suspect’s known business type (construction).
The benefit of funds to ABC Telephone Products is considered unusual and suspicious for the business type of the suspect.
The suspect, Mike X, deposited a transaction of cash for the amount of $9,900.00 into account number 123-456789 in the name of XYZ Construction, conducted on Monday, October 18, 2010.
The investigator can then edit the narrative section into a ‘story’ that will then be copied and pasted into the submission form.
Kevin Hinderleider said
Clayton:
Have you ever heard of organizations having issues with filling out an InfoPath 2010 web form while a person is connected to their organization via a Cisco SSL VPN? For us, when we are connected via the Cisco SSL (through an ASA) the form will come up; however, some rich text boxes will not allow editing as well as the top ribbon is greyed out not allowing an individual to submit the form. Just wondering if in any of the folks you meet you have seen this issue.
Thanks Kevin
Clayton Cobb said
Kevin,
I haven’t heard of that specific set of issues happening yet despite working with SSL VPN products a lot. However, I am used to their being limitations and quirks whenever using an SSL VPN where the user is required to get to everything through a web interface. In other words, if you remotely log into a web page to invoke the SSL VPN connection and access your internal resources through this web interface, then there are a lot of things that can go wrong. However, if you connect to VPN and get an IP directly on the internal network and are able to behave as if you’re actually at work on the network, then those limitations go away. Also, if you can RDP to your desktop on the network after connecting via VPN, then things work fine.
Chandra Shekhar Rameneni said
Hello Clayton,
I am not sure where to post this query, but here goes.
In SharePoint 2010, I created a survey of 3 questions and enabled users to fill up the survey questions. Now I want to display the results using charts either in 2D or 3D view. Please suggest me the approach.
Thank U
Clayton Cobb said
Chandra, what have you tried so far? Survey lists have their own charts built-in. Were those not good enough? If not, did you try using the OOTB Chart Web Part? IF that’s not good enough, then open a new, blank Excel file, then in your Survey list, click Actions > Export to Spreadsheet > Create Pivot Table and Chart > then design your chart with the pivot table. Next, publish the Excel file or just the chart to a trusted file location in SharePoint and use an Excel Web Access web part to display the graph. Beyond this, we’d have to start purchasing 3rd-party tools or working with PerformancePoint Services.
Parth said
Hi Clayton,
Whenever i have problems with Infopath i see your blogs and it has always helped me out. But today i am in a fix. I designed a form with attachment control. Now i need to change the attachment control to support multiple attachments. The Problem is that the attachment control is not under a group. so only option which i could come up was creating a reference of the attachment control in a repeating group.
But that way in my form i have two controls. one single attachment and second with repeating table.
Eagerly waiting for a better solution from you.
Parth said
one clarification the form with Single attachment control is already in use. So now if i move the control and place it under repeating table i am getting errors in my old forms.
Clayton Cobb said
Yes, you have to put it in a repeating group so that it can be repeated on demand by the user. This does require changing the XML schema, and this would definitely affect those old forms. My recommendation is to save this as a new form template and publish it as a content type, then add that content type to the same form library. Then, set the form library to only show the new template in the New button so that all new forms use the new template and all existing forms use the old template. When doing this, please test in a dev environment to ensure you don’t accidentally cause the creation of duplicate columns. The new content type template must be changed so that all the form fields are promoted and tied to the EXISTING fields of the form library. If you don’t do this, it will create duplicate columns with the same name.
Parth said
Hi Clayton,
Thanks a lot for your suggestion. You cleared my doubts.
I too was thinking on the same lines but thought maybe there might some way out of not creating a different template just for one field.
I learned one thing from this is too always keep attachment control under a group even when the requirement is for single attachment.
Parth said
Hi Clayton,
I made the change but did not create the second template but instead i modified the original template by moving the attachment control into a repeating group, from which i created a repeating table.
This modified the XML schema, so the old forms started giving error. So to rectify it i modified their XML schema to match with the current schema in notepad and saved them back.
So this way the old forms were working properly and even they now have the new attachment functionality.
There were not a large number of Old forms but a number which i can modify manually in a couple of hours.
Mick said
Hello Clayton,
Im trying to create a codeless solution for my client using External Lists with Infopath forms.
After going through the usual steps to create the external list and have sharepoint create the infopath enabled forms (editifs.aspx, newifs.aspx) I want to be able to create a web part/page where the user can search the list and open an existing item into infopath (web enabled)
How can I find/build the correct URL for the infopath edit form and pass in the BCS ID of the External List.
Thanks
Mick
Clayton Cobb said
Mick, I haven’t tried this with external lists, but if you right-click on an existing item and click “open in new window,” then it will open the item in a page instead of the modal dialog. From there, you can get the URL out of the address bar and use that for constructing your dynamic URL.
Jaliya Udagedara said
Hi Clayton,
When I was going through TechNet, I was just inspired by your knowledge. I have added you to my LinkedIn, would you please do me the favor of responding to them.
Regards,
Jaliya
Leslie said
Hi Clayton,
I have created an InfoPath form that is published to a SharePoint library using a standard “submit to SharePoint library” data connection. The form was created on a computer that is on the same domain as the SharePoint library. We work in a single sign on environment, which should allow users to open and submit the form without having to provide credentials. However, my users are prompted to enter their credtials. After providing their credentials, they are prompted again. After canceling the prompt for credentials, the form opens and the user is able to complete and submit the form. Any thoughts or suggestions?
Thanks,
Leslie
Michael said
Hi Clayton
I’m currently looking at utilising tasks in a sequential workflow (which are attached to a form library).
I need some advice with regard to updating the promoted fields of an item in the associated form library (from a task form) but I don’t want to overwrite the initiating form.
I initially setup an additional submit procedure in the task form to write back to the item in the form library, this didn’t work how I wanted it to because it replaced the submitted form with the task form.
Would you have any suggestions on how I can move forward with this?
I’m using InfoPath 2010/SharePoint Designer 2010 to compile my forms/workflows.
Thanks in advance for any suggestions/solutions.
Michael
Clayton Cobb said
Michael, I don’t fully understand your question. You don’t promote fields from task forms. You only do that from a form library form template, which is different than a task form. YOu definitely don’t want to submit your task form as an XML file back to your form library. If you need to update fields in your original form based on input to the task form, then you should use an SPD workflow to “Update list item” to update the fields in the original item (form), which requires that you make those fields editable when promoting them during the publishing process.
Michael Parker said
Hi Clayton
Let me clarify, I have a form library, which hosts a workflow process (utilising the out of the box approval workflow). The form library has promoted fields that I wish to be able to update from the associated approval task form.
I will try your suggestion of updating via workflow as opposed to the submit action from the task form. I guess I would write the values I need to a workflow variable first and then get the workflow to update the promoted fields with the workflow variables?
Thanks for your help.
Michael
Clayton Cobb said
No, you shouldn’t need to mess with variables for that purpose. Just use “Update List Item” and match fields.
Patty said
Hi Clayton,
Did you know that the COSPUG (www.cospug.com)website is down? I understand there is a job board that I would like to post on…
Thanks,
Patty
Heather Zerfass said
I posted this to the MSDN website as a reply to one of your posts but haven’t heard back so thought I would post it here as well. Thanks!
Hi Clayton,
You are so knowledgeable about InfoPath and Sharepoint. I used your Auto-Generating File Names for InfoPath forms and am now a big fan of yours! 🙂 Anyway, could you explain how to use a workflow to create the number of XML files that would be needed to submit data from a spreadsheet into the document library? We have a lot of data that we use in excel spreadsheets that we have to manually enter into the InfoPath 2007 form which then populates a document library that a workflow then runs off of….if there is a way to get this data to be copied and pasted from the spreadsheet, this could save us tons of time.
Thank you!
Heather
Bob said
Hi Clayton.
Your posts have helped me solve a lot of problems in the past. Like you, I’m not a programmer, so I like to find solutions that are codeless in design.
I have a problem with setting a default value to the contact selector for InfoPath 2007 with form services. Here’s the background… This form has a contact selector that represents the name of the contact for any follow-up information. The idea is that when a new form is created, the field will be automatically populated with the user’s name. If the user is not the contact, that user can select another name. Everthing works, until you try to select another name.
I can create the contact selector without any problems, and I can use the GetUserProfileByName web service to create the default value. The problem happens when I try to change the name in the contact selector. No matter what name I select, it will result in the default name (e.g., If Person “A” is the default value, and I try to change it to Person “B”, the result is Person “A”. At the same time, if Person “A” is the default value and I add Person “B”, then I get Person “A”; Person “A”).
Am I missing something in the settings, or is this just a bug and there’s no workaround? Thanks for your help.
Bob
Clayton Cobb said
I do this quite often and have never seen that happen, so I would presume that maybe the way you’re setting “the default value” may be wrong. With a Contact Selector, you have to select _3_ default values, not just one. I don’t know if it’s just semantics, but they way you wrote it was as if you are only setting one value and thought it was working fully. YOu need to set AccountId to the full Domain Name (AccountName value from UPS), AccountType to User, and DisplayName to the PreferredName value of UPS.
Bob said
I could have sworn that this worked for me last week. I’m not aware of any changes to my system over the weekend. Here is how everything is set up:
The contact selector is bound to a non-repeating group. Underneath this group is a repeating group called Person. Under that repeating group is three fields: DisplayName, AccountId and AccountType. I uploaded the context.xml file as a resource file and created the receive data connection. As I mentioned, this part works without any problems.
To create the default value, I first created the GetUserProfileByName web service data connection. I then set the default value for each of three above fields to the following:
DisplayName = Value where Name is equal to PreferredName
AccountId = Value where Name is equal to AccountName
AccountType = User
For DisplayName and AccountId, the Update this value when the result of the formula is recalculated” is checked.
The default name appears correctly, but whenever I try to change or add a name, it will result in the default user name. I have no clue as to why it’s doing this.
Thanks again for your help.
Bob
Clayton Cobb said
Bob, you’re definitely doing it right then, but I don’t know what would cause changed values to revert back to the current user. Do you maybe have some rules that are firing that are resetting the field values?
Bob said
There aren’t any rules on the form. Just to check, I created a new template with just the contact selector and the GetUserProfileByName web service, and I get the same results. I’m starting to think there’s something wrong with the application itself. I’ll try to repair Office and see if that fixes the problem. Thanks.
Chau said
Clayton,
You are a popular man as you seem to show up for most of the SharePoint issues I Googled. 🙂
I am creating an Office Supply Requisition form. In the form I have a repeating table so staff can order more than one item at a time. The Product and Pricing are populated from a separate SharePoit list. I need a way to capture the data in the form the moment they submit it as pricing for the items may change. Do you have a suggestion on how this can be achieved?
Thank you so much!
Clayton Cobb said
Chau, what exactly do you need a suggestion for? Isn’t the data captured the moment they submit anyway? I didn’t understand what it is you needed to do that isn’t already being done.
Kurtis Bock said
Mr. Cobb,
First and foremost thank you for your service to our country. Secondly, thank you for providing Infpath and Sharepoint information – you are a true resource.
I have a question regarding InfoPth 2010. I am trying to show a total in a field based on conditions in two other fields, that may need to include several fields. In Excel I could achieve this using sumif() including the range of cells where the criteria resided and the range where the value resided. Infopath does not have a similar function. my example would be:
Field 113: show if fields 103, 113, 124 = “A”; show value of fields 104, 114, 125
Thank you for any advise you may offer.
Semper Fi!
Kurtis
Clayton Cobb said
Kurtis, you’ll need to make this a rule instead of a default value, and in the rule, add a condition that matches the If statement you wrote above.
Kurtis Bock said
Mr. Cobb,
Good morning I have tried several variations to include:
1) Using mutliple rules for each field
2) Using a single rule with multiple and/or conditions
in the field where the total would be shown value: field + field were the fields are filtered.
The is:
Condition field103 = .365 or
field125 = .365 or field146 = .365
action set a fuild’s value = field104 + field126 + field 147
the text box property value: field104 + field126 + field 147
The above does not work. When I use just the rules nothing populates to field 199.
The one variation that somewhat worked was usign the text box properties value and using: field104[ . and field103 =.365] + field126[ . and field125 = .365] + field147[. and field146 = .365] The probelm this this is that is one of the fields (104, 126 or 147) was empty the entire formula did not work. This there a good source for XPath formulas or is it trail and error? Thank you.
R/s
Kurtis
Clayton Cobb said
Kurtis, I can’t really follow what you’re doing nor why it wouldn’t work. Let me explain in words what your formula is doing, and you let me know if you either didn’t type it correctly or didn’t realize that’s what you were “saying” in the formula.
The condition you wrote says, “If field103 equals the exact value of .365 (36.5%) OR field125 equals the exact value of .365 OR field146 equals the exact value of .365.” So, in other words, if field103, field125, or field146 equals the value .365, then perform the following action. Is that your intention? One of those 3 fields has to be EXACTLY .365 for the rule to trigger?
Then, IF the condition is met, the calculation simply adds 104, 126, and 147 together for a basic sum. Is that what you want to do? I don’t know why you wrote “the text box property value” after mentioning the action. What did you mean by that?
Also, what does that variation with the brackets mean? I don’t know what that is trying to say.
Kurtis Bock said
Mr. Cobb I apoligize if the previous information was confusing. Here is the rule I currently have:
Rules
Field199
Rule1
Condition:
field103 = 0.365 or
field125 = 0.365 or
field146 = 0.365 or
field167 = 0.365
Action:
Run these actions:*
Set a field’s value:.= field104 + field126 + field147 + field168
In the Field199 Text Box Properties on the Data tab
Binding
Clayton Cobb said
Kurtis, that rule above will only fire if Field199 changes. Rules only fire on data changes, so that rule will not fire based on other fields. You need to add the rule to the field(s) that changes and triggers the calculation. Otherwise, that rule looks like it should work fine and would add those 4 fields together no problem if triggered.
I still don’t know what you’re referring to with “Text Box Properties on the Data tab Binding.” When looking at the Data tab of the Field properties, I see Name, Type, Data Type, and Default Value. In the Data tab of the Control properties for the text box, I see Field Name, Data Type, and Default Value.
Muzaffar said
Hi Clayton,
I am very much impressed with your blog and based on one of your blog I have designed a Vacation Form in InfoPath 2007 and MOSS 2007 (as this moment it is in a draft format)
This is a first time I ever use this kind of blog so I don’t know blog etiquette
As I have designed a Vacation form, I want to get ‘# of Vacation’, ‘Purchased Vacations’ and ‘Total Vacation’ from a SharePoint list based on userID or user name.
SharePoint list has ‘Employee Name’, ‘# of Vacation’, ‘Purchased Vacation’ and ‘Total Vacation’.
I am using http:///_vti_bin/UserProfileService.asmx?WSDL to get userID.
You input would be much appreciated.
Thanks,
Muzaffar
Clayton Cobb said
Muzaffar, to clarify, you don’t get the user’s ID from the profile service. You SEND the user ID to the profile service in order to retrieve profile information. You get the user ID with the userName() function.
If you want to match vacation data from a list with a known user in InfoPath, you must have a foreign key to match the user with the list data. Since your SharePoint list only has Employee Name, then I don’t think that’s a good enough foreign key UNLESS that Employee Name field is a People Picker. Is it a People Picker, or is it just a text field? If it’s a text field, that should be changed. If it’s a People Picker, then the true underlying value is AccountName, which looks like domain\username. If you make a direct data connection to the list, then it will only show whatever value you’ve configured the PEople Picker to show, which is usually the DisplayName (PreferredName in the profile database). If you are assured that the DisplayName of every user will be absolutely unique, then you can retrieve PreferredName from the profile service and then compare with the Employee Name field of your SharePoint list for then determining the values you want. The only problem is that in 2007, you have to retrieve ALL data from the SharePoint list and then filter the results. In 2010, you would be able to query only for that user and retrieve only that user’s relevant data.
Scott said
Hi Clayton,
Many thanks for all your efforts on your blog, it is much appreciated, especially by newbs like me.
Clayton Cobb said
Scott, you’re very welcome. Such comments make it worth the time spent.
Chris said
Hi Clayton,
You seem to know a thing or two about InfoPath 🙂 I’ve been dropped in on a project where they are having some problems with InfoPath and SharePoint 2010. I know SP2010 fairly well, but InfoPath is still a mystery. I’m hoping you might have some ideas.
The project has decided to customize the standard forms (new, edit, display) for lists using InfoPath. When the forms are displayed in this traditional context, everything works fine.
On a seperate page (in SitePages) they have created a type of dashboard page. It receives an ID on the query string, uses the QueryStringFilterWebPart to extract this information and pass it on to various web parts. One of the web parts in question is a BrowserFormWebPart. This should display information using an InfoPath form. The thing is that it’s displaying some information, but not all.
The following things don’t work:
– Date fields indicate a wrong form. In the normal forms, only a date is displayed. In the dashboard page, time and date are displayed
– Users that were added to a user field are missing
– Attachment that were added are not shown
– Lookup fields don’t contain any data
– When making changes and saving, get a peculiar message “my:Modified Error: Only date or date and time allowed ” I imagine this is refering to the modified date of the item. Don’t belive we are doing anything that should prevent the standard functionality to work.
Simpler fields such has choices, strings, numbers work without any problem.
If you have any ideas…..
Clayton Cobb said
Chris, I’d have to see it directly to find out why all those things are happening. I use the QueryString input method with the InfoPath Form Web Part without any such issues, but I can’t really tell you why you guys are having that issue. It’s strange they would drop you on the project without any InfoPath experience, though. There is a massive amount to learn about how to use it properly, especially in 2010, so good luck. I’ll help if I can, but this is a full project with a whole team on it, so it’s likely I would need direct interaction to figure it out if you guys haven’t been able to so far.
Chris said
If I understand you correctly, you have succesfully displayed a list form written in InfoPath on a page outside the traditional contexts. Just knowing that it is possible is a step forward.
I’m only helping out a couple of days. People tend to bunch a whole range of products under ‘SharePoint’. Since I know the core SharePoint functionality rather well, it must mean I know InfoPath, PerformancePoint and Excel Services as well? Yeah right!
Clayton Cobb said
Yes, I have done that. You only ever actually do it on a page outside the normal context really. I hear ya on the other comment – people just don’t understand the magnitude of what SharePoint is – especially 2010.
Dean Steeves said
Hi Clayton,
I am new to SharePoint and InfoPath in the past few months. I have used information on your blog to help move me along. So many thanks to you for all the information you share.
Here’s my situation. I have set up an InfoPath form on a SharePoint 2010 form library that I want to email to four distinct members of a project team. We have about 30 different project teams. I plan to accomplish this as follows:
1) I shall setup a SharePoint list with column for each project team number and three separate columns containing the three project team members’ email addresses. Periodically, a new project number shall be added to the list.
2) The form I am using is a template on a form library. The form shall contain a project number field, the initiator’s email address field and three team members’ email address fields. A fifth email field shall be populated from another dropdown list.
3) The initiator selects the project number from a dropdown field on the form. Through conditions and rules, the form shall use the project number to populate the three email address fields on the form. The fifth email address is selected based on information on hand.
4) The form is submitted to the library which assigns a master number to the form.
5) The form is now to be emailed. What I want to do is have the email pull the email addresses from the form fields into the email. I want to populate the “To” line with the first project team member’s email address. I want the email to populate the “Cc” line with the initiator’s email address, the other two project team email addresses and the fifth email address.
My questions are:
1) Does my plan make sense or is there a better method?
2) Most importantly, how do I set up an email that grabs the email addresses from the form fields? I would like to do this without code.
In a short time, I plan to create a workflow that launches when the form is submitted to the library and this email would be the first of about four or five steps in the workflow. Can I do the email without a workflow or is it easier to do it within the workflow?
Thx, Dean
Clayton Cobb said
Dean,
1) Sure, it seems to make sense except I never email forms. I prefer to use a workflow to notify the user(s) that there is a new form, and I provide a direct link to that form. Emailing the form requires that all recipients have that version of InfoPath installed, it duplicates the data throughout the mail system, the data is immediately stale, and the formatting is never 100% (and often times very poor). I create SPD workflows and send a dynamic hyperlink to the form so that the data stays in one place, it’s ALWAYS real-time (the moment they click the link, they are presented with the CURRENT form data), the formatting is exact, and the users don’t need InfoPath at all (if you’re using browser forms).
Also, why pull the emails into the form from SharePoint only to then submit them back to SharePoint? Does the form really need the emails? I would have the user select the NAMES (not emails – those aren’t the way we refer to people), and then the workflow would populate the email addresses for the selected people dynamically.
2) You use submit to email? Did you not know about this at all? YOu mentioned not wanting to do code, but this is a regular built-in submit option. It should be fairly easy I would think.
If you have a workflow with four or five steps, then why are you sending the form as an email? You would definitely just send the notification via email in that case since it’s a much bigger workflow anyway.
Chris DuPlantis said
After attempting your connection, I receive the following message…. Im new to Infopath/SP.
I want to fill a form out submit to a sharepoint site. Eventually with workflow. Be able to see a unique file auto incremented.
Any idea where I could be going wrong?
InfoPath cannot submit the form.
Some rules were not applied.
InfoPath cannot submit the form.
An error occurred while the form was being submitted.
One of the required values for submitting the form is missing. The value is created using the following XPath expression: my:strFileName
If you know the value in the form that specifies this value, revise it and try again.
Clayton Cobb said
Chris, you have an error in the rules of your submit button, and it’s related to the strFileName field. I can’t tell for sure just based off the info given, but you definitely have a rule that is failing related to the strFileName field.
Chris DuPlantis said
ok … I got the form to autonumber in the form library… when submitted it creates 2 now… one with NaN and the second with the autonumber in the name field? what would cause this?
Clayton Cobb said
You’re not submitting the same filename. The first submit doesn’t yet have the number, which is the point of the blog – it shows you how to RETRIEVE the next number before submitting the file, because the filename includes this number. Your NaN filename indicates a submit without yet having a number to put in the filename.
Xing said
Hi Clayton,
I am amazing your popularity in the MS SharePoint and Infopath. I have a problem that I hope you can help me out with. How can I add Active directory in Custom Workflow so that originators do not need to type email address. i want to select email address from AD same as Out of Box approval workflow . I published an Info Path 2007 Travel Form in SharePoint 2007 of our organization general site. Originators has to type email address of Approver. Different departments have different Approver. So I can not define the email address for a single Approver. So I have add an Active directory so that originators find email address from AD. Is there any guide to add AD? I appreciate your valuable sugesion.
Regards,
Xing
Frank said
I created two forms to use to request two (2) different types of training. We are using MOSS for our Intranet and uploaded the forms to the training site. Here is the problem, before I uploaded the forms they worked fine on my computer. After uploading them to the same location both forms show the same information. I created two (2) links on the main site to each form. The same form opens with both links. Hope someone can help.
Thanks,
Frank
Clayton Cobb said
Frank, when you say you created 2 forms, do you mean you created 2 form TEMPLATES? Big difference between the two, so I wanted to make sure. One reason I ask is because you would never UPLOAD a form template. You would only publish a form template, and if you wanted two templates to be used in the same form library, then you’d have to publish them as content types and add the content types to your form library. When doing this, you’d want to be very careful about how you promote the properties, because you’d likely want to share some of the columns, and that requires special attention.
Do you really need two form templates? Could you not have one form template where the user first selects the type of training and is then sent to a view that is specific to that type of training?
I don’t really know what you mean about the forms showing the same information.
mm said
Sub : using https for login page and then switching to http after user authenticated in sharepoint 2010
Clayton,
i am emailing this to all the expects like you since i am extremly frustrated with this limitation and just can find an answer anywhere.
Envirnoment :
Sharepoint 2010 enterprise\ IIS 7
Claims based authentication with both FBA and NTLM
i want to use https for login page and after user is authenticated use http.
As far as the redirection is concerned i am using URL rewriter tool but even taking the redirection out of the equation, after a user is authenticated and is still on https if i manully replace https with http and the browser looses state and if i manually replace http to https it sees the session again.
i can;t beleive with Publishing and external sites no one has ever felt the need of it. i have posted this question on MSDN as well but no solid anwers yet.
http://social.msdn.microsoft.com/Forums/en/sharepoint2010setup/thread/73fd92d4-f965-4f92-94c3-fb444569acb5
woudl be great if you can shed some light
thanks in Advance.
MM
Ashley said
Clayton, I just wanted to say thank you! You rock. I was voluntold to take up SharePoint and InfoPath and I think without all of your help I might have quit my job and moved to South America to run drugs. Every time I Google a problem I am having, you have addressed it. You have anonymous fans on the internet
Clayton Cobb said
Thanks for the comments, Ashley.
Lak said
Hello Clayton,
I stumbled upon this site while looking for some infopath help.
I’ve a requirement where i’ll need to pull data from and submit data to multiple external lists.
Can you please let me know if this would be possible with a infopath form? and some points on how to accomplish this.
Appreciate your help.
Thanks,
Lak
Clayton Cobb said
Lak, InfoPath is not built to submit to lists – it’s only built to submit to form libraries except when it’s a list form. List forms can submit to the list they are bound to but not to other lists. To submit to multiple lists, you have to start with a form library template and add custom code. http://www.bizsupportonline.net/blog/2008/12/5-ways-to-submit-an-infopath-form-to-a-sharepoint-list/
However, to retrieve data from multiple lists is easy by just creating data connections.
Lak said
Thank you so much for the reply. We also have a requirement where once the data is submitted to multiple lists the user need to have the ability to edit those items by opening the infopath form. Is that possible too? or you think it’ll be easy taking an other route(not infopath) all together.
Clayton Cobb said
Sorry, InfoPath does not have that ability.
Lak said
So all that infopath form can do is sbmit data to the list one time?
Lak said
Hi Clayton,
I would like to get you suggestion on populating all the form fields from a sharepoint list? This needs to happen on loading the form and based on a ‘id’ of the item passed from user input or through query string parameters.
I can do this on the form load event by using the primary data connection or by calling the list webservice passing the id.
But which one is the best? Please advise.
Thanks.
Clayton Cobb said
I just use a regular list data connection and query based on the ID. No need for a web service in this case.
veekillLak said
I’ve multiple lists that i’m submitting the data to from form code. will it still work as the form contains data from two different lists.
Clayton Cobb said
I don’t understand the question, Lak.
Lak said
Thanks.
gaurav goyal said
Hi,
I am using Infopath 2010 & want to show Alerts. I went to google & got a solution which says “thisXDocument.UI.Alert(‘This is Alert’);”
I want to use thisXDocument object in my infopath form to perform actions like showing alerts.
I am not able to get this object. Can you please tell me how to use or create this object?
Julian said
Clayton,
I have a question.
We are currently using an Infopath form (2007) as our form for Leave Requests on SharePoint 2010. Everything works just fine (no codes). My question is if and when we do migrate to the new SharePoint (2013 sometime in the future, no time soon)…will the 2007 forms work on it? Currently we do not have Office 2010 but i am going to assume that sometime in the future we will have it. Thanks again for any feed back.
Clayton Cobb said
Julian, yes, your 2007 forms will still work. They just won’t be able to leverage 2010 functionality. It is surprising that you have SharePoint 2010 but not Office 2010 when so much new functionality in 2010 depends on Office 2010. Even if you alone had InfoPath 2010 for building forms, you could leverage all the new 2010 functionality for browser forms without your users needing Office 2010.
Julian said
unfortunetly where we work we do not have Office 2010 on our computers and we do not know when we will get it pushed to our computers. But when we do move forward to the new SharePoint (2013) and we get Office 2010, will it be an easy task to update the 2007 form to a 2010 form. I would think we would just have to update our data connections correct?
Mastana Mahi said
Clayton,
Your blog and other helpful posts are amazing.
That’s why I am here to ask for a solution to my problem.
I was following your post on using “InfoPath – Get user information without writing code (extended)”. In my problem, I have a drop down list where I make a selection and it auto-populates another field in the form (through lookup and filters) in the form of an email address. What I wanted to do was to be able to take that email address and use UserProfileService to retrieve user name that matches that email address.
In your example, you are doing the same thing but by using AccountName field. Can this also be done by using an email address? I can’t use AccountName because my company uses rather unique way of assigning account names and I have no idea of the account name for another user just by looking at their email address.
Following your example I saw that only UserProfileService property to pass a query is AccountName. If there was another one for WorkEmail, I would have found a solution.
I hope I have explained my question. Any help would be appreciated. Thanks, Enaybee.
Clayton Cobb said
Enaybee, the UserProfileService can only accept AccountName as a query parameter.
Mastana Mahi said
Clayton, Much appreciated your speedy reply. Thank you very much.