InfoPath Dev

Hi... newbie here of course; I have two questions. I don't have very much programming experience so would prefer not to go that direction if possible.

1.) How can I fill in the default value of a text field using an if/then sort of statement? I searched but the only response I found talked about using the OR | but didn't really explain it. I need this for an if/then to determine if the submittor has a dotted line manager or not. Something like this: if SPS-Dotted-line is not blank then SPS-Dotted-line, else Manager.

2.) I need to lookup a person's manager's information (WorkEmail, FirstName, LastName, etc.). I already have the form automatically pulling in the form's submittor's information including their manager (which returns the managers user name) using GetUserProfileByName data source. Basically what I need it for is after the submittor submit's the form, the first step in a workflow is started to send an email, notice, task, whatever to either their manager or dotted line manager. Something like: lookup WorkEmail for [managerusername].

 Hope all of that makes enough sense and thanks for any help that can be given.

Hi:

 I don't quite understand question 2, but have a suggestion for question 1. I have used 'method 2' from this post with success for a conditional default value for a field: http://blogs.msdn.com/infopath/archive/2006/11/27/conditional-default-values.aspx

The basic structure of the formula they suggest looks like this:

concat(
substring(TrueResult, 1, (BoolCondition) * string-length(TrueResult)),
substring(ElseResult, 1, (not(BoolCondition)) * string-length(ElseResult)))

What this formula does is concatenate two substrings. Depending on the result of the boolean (true/false) condition, one of the substrings won't return any characters and the other will return all of it's characters. Let's say you have a form with a very simple schema -- textField, textField1 and textField2. If textField is blank, you would like textField2 to default to textField1. If textField has a value, though, textField2 should default to that. First you would right click on textField2 and select properties, then in the default value section, select the formula button. Using the above formula, the logic for this scenario would look like:

concat(
substring(textField, 1, not(textField = "") * string-length(textField)),
substring(textField1, 1, (textField = "") * string-length(textField1)))

Let me know if you need further explanation for this, as I know I found the formula a bit confusing the first few times I worked with it.

Thanks... at first glance I think I follow the logic fairly well but if I run into any issues implementing it I'll come back and let you know.

 As for question 2, basically for the submittor of the form, I'm pulling their user information from Sharepoint (username, workemail, firstname, lastname, manager (username), sps-dotted-line (username), etc.) I'm wanting to use the Sharepoint datasource connection to also look up the user information for the submittor's manager, so it would look something like this:

submittor username: domain\johndoe
submittor workemail: john.doe@domain.com
submittor firstname: john
submittor lastname: doe
submittor manager: domain\janedoe
submittor sps-dotted-line: domain\jimmydoe

manager username: domain\janedoe
manager workemail: jane.doe@domain.com
manager firstname: jane
manager lastname: doe

 The submittor information is already being pulled in automatically because it defualts to "current user", but I want to pull in the manager information. I assume it somehow uses the filter when filling in the field with the secondary data source but I can't quite follow the proper logic to get it done... assuming I'm even doing it the right way.

I see. Yes, you probably have a filter issue. Let's say you want to put the manager email into field1 in your form and that the current username is in field2. Right click field1, select properties, and in the default value field select the formula button. Then, in the Insert Formula window, select the Insert Field or Group button. The Insert Field or Group window opens. In the dropdown for data source, select your SharePoint data source. Select the field that has the manager email, then select the filter button from the lower left corner. For the first dropdown, select the submittor username from the SharePoint data source, the second dropdown should be "is equal to" and in the third, select field2 from your main data source (where the current username is). 

Hilary Stoupa:

I see. Yes, you probably have a filter issue. Let's say you want to put the manager email into field1 in your form and that the current username is in field2. Right click field1, select properties, and in the default value field select the formula button. Then, in the Insert Formula window, select the Insert Field or Group button. The Insert Field or Group window opens. In the dropdown for data source, select your SharePoint data source. Select the field that has the manager email, then select the filter button from the lower left corner. For the first dropdown, select the submittor username from the SharePoint data source, the second dropdown should be "is equal to" and in the third, select field2 from your main data source (where the current username is). 

If I understand what you're saying correctly, you're referring to if I'm pulling the manager information from a Sharepoint list, correct? I'm hoping to pull it from the GetUserProfileByName data source. If I'm mistaken, certainly let me know but also I suppose I need a bit more information because I don't quite follow. This is what I have, tell me if it makes much sense or not.

Value[AccountName = Requestor_SPS-Dotted-line and Name = "WorkEmail"]

Basically what I'm saying is that I want to return the Value of "WorkEmail" for the username found in Requestor_SPS-Dotted-line. Sorry if I'm not making much sense... I can provide screenshots or detailed instructions on what I'm doing if it would be of any help.

I think some screen shots would really help -- I can't quite visualize what you are doing and I haven't used GetUserProfileByName, so I am unfamiliar. Also, I found a blog post that may be helpful to you: http://blogs.microsoft.co.il/blogs/itaysk/archive/2007/04/05/InfoPath-_2D00_-Get-the-current-user-without-writing-code.aspx

 

I may be starting to get this, sorry to be so slow! What you are getting from GetUserProfileByName is all your current user info, right? and you need to basically query the same web service again with the name of the manager to get the info for the manager?

Hilary Stoupa:

I may be starting to get this, sorry to be so slow! What you are getting from GetUserProfileByName is all your current user info, right? and you need to basically query the same web service again with the name of the manager to get the info for the manager?

Exactly! But one thing to note, it won't necessarily always be the direct manager, it could also be the SPS-Dotted-line manager. Basically if they have a Dotted-line manager, I want it to use that first, if it's blank, then use the manager (hence my 1st question in the initial post).

Got it. So, can you tell me how you are getting your user information the first time through? Are you running  a query when the form loads, or when a field changes, or ... ?

Basically, what you will need to do is (after you have gotten all the user info you need from the data fields in your GetUserProfileByName data source) set the correct query field to either the dotted line or direct manager and then run the query. This can be done with rules or code. If you are uncertain which query field to set, expand the query fields folder in the data source in the task pane, take a screen shot and post it and I'll see if I can help you figure it out....

To get the current user's information, I'm simply using text boxes with the default fields set to something similar to these (depending on the field)

Value[Name = "AccountName"]
Value[Name = "WorkEmail"]
Value[Name = "Manager"]
Value[Name = "SPS-Dotted-line"] 

 Here are the steps I'm following to try to pull the manager's information

1. Insert Formula for the default value of "approving_mgremail" field
2. Click Insert Field or Group...
3. Select the GetUserProfileByName data source from the drop box which gives me this image (with categories expanded)
   
4. I highlight the Value field above, then click the Filter Data button.
5. For the filter data, I use the following information
   
6. Which gives me this resulting formula:
   Value[AccountName = approving_mgrusername and Name = "WorkEmail"]

approving_mgrusername is currently just a static username but will eventually house the calculation discussed earlier that determines if SPS-Dotted-line field is blank or not. This returns a blank value so obviously I'm doing something wrong. I came up with this formula because for the current user WorkEmail, you use this Name | is equal to | "WorkEmail" filter.

 

Let me know anything more you need to see or have described in order to help. I greatly appreciate all the help you've given me so far and especially the quick responses.

From the little I've read on the GetUserProfileByName datasource, when it is first configured if you don't change the defaults for the parameters (that is, if the Account Name in the query fields is blank), it will, by default, return the current user's information. Your issue is that once you have that current user's information, you'd like to get the same information (well, at least some of the same information, the email address) for a different user.

The filter scenario won't work, because those data fields currently only hold information about the currrent user.

To demonstrate what I mean, try this. Put a button on your form. Right click and in the button properties, go into rules. For your first rule, set a field's value and set the query field account name in your GUPBN datasource to the value in your approving_mgrusername field. For your second rule, run a query, and select the GUPBN datasource. Drag the entire PropertyData group in the data fields for the GUPBN somewhere on your form (doesn't matter if it is ugly, we just want to see the values). Then preview your form. When you first see it, I'm guessing the PropertyData stuff will relate to the current user. Then click your button with the rules connected, after making sure that you have a user name in approving_mgrusername that is different from the current user.

I'm going to be travelling this afternoon, so I won't be around, but I'll check back later today or early tomorrow, so please do post if you have trouble.

OK! I think I have something working now. Tell me if you can think of any obstacles I could run into.

After reading through the comments of blog post you linked earlier, I saw someone suggest creating a second GUPBN data source, titled GUPBNMGR, then assigning the AccountName of that data source to the username of the manager.

Using what you recommended above about setting a rule to assign the AccountName and then submit the query, I created a rule on the Submittor's (current user from GUPBN) name that when it's filled, it would assign the AccountName of the GUPBNMGR to be the approving_mgrusername, then submit the query. I then tied the approving_mgremail to the WorkEmail of the GUPBNMGR and voila! It retains my current user's info and as well pulls the manager's information.

The question I suppose I need to ask, is there ever a time when the rule tied to the Submittor's name would be re-ran. Meaning if a second user opens the form for review, would it pull in that 2nd user's manager name and replace the original submittor's manager name? I can test this obviously but also curious if you could foresee any other gotchas I should look out for.

Thanks for all of your help. I'm sure I'll have more questions as I continue to develop this but for now I'm at least on the right path (I think).

I guess my real concern with this scenario is whether the queries that pull the data for the GUPBN connection are run automatically when the form is opened, and what then loads up the fields with the user information in your form. If the queries run automatically, and there is nothing in place from preventing another person who opens the form from triggering any rules that fill the fields you are using, you could run into exactly that situation. Give it a thorough test, and maybe think about using conditional formatting to make some controls read only after their initial values are filled to prevent these types of situations.