InfoPath Dev

Hi,

Let me thank you in advance, and let you know that I am learning much of this as I go.

 I am using InfoPath 2007, and I have a form created right now with conditional formatting to show/hide sections based on a couple different fields.  In some of those sections, there is a spot i have alocated for a formula, this is where I need help.

An example of the formula is ".385x", where "x"=the number of remaining pay periods. The result would be rounded up/down to the nearest whole number. 

 What I would like to be able to do is have it figure out the # of pay periods left based on the date the form is filled out.  I have all of the dates of each pay period, i just can't figure out what to do with them.

There are a total of three different formulas that will be used, this is just one of them.  Is there a way to have it calculate on its own when that section is visible?  

 

Thanks for your time

You can do all sorts of things, but we need the exact details to figure it out or help you figure it out.

 There are 27 pay periods in 2009 as listed below.  This is an example of what I would like to do.

An employee gets hired on 7/27, there are 10 remaining pay periods in the year.

So, using the formula .385x, where x=remaining pay periods, we get .385*10=3.85

The answer should than be rounded up to 4. 

There are a total of 3 different formulas, .577x, .385x and .769x.  I assume whatever I do for one I can do for all 3.  The fields all show up in the form based on the answers in two other fields, ddlPosition and ddlEmployeeType.  

I would like for the answer to the formula populate automatically when that field is visible.  I do have a date field called HireDate that can be used to help trigger the formula.

12/14/2008 – 12/27/2008

12/28/2008 – 1/10/2009

1/11/2009 – 1/24/2009

1/25/2009 – 2/7/2009

2/8/2009 – 2/21/2009

2/22/2009 – 3/7/2009

3/8/2009 – 3/21/2009

3/22/2009 – 4/4/2009

4/5/2009 – 4/18/2009

4/19/2009 – 5/2/2009

5/3/2009 – 5/16/2009

5/17/2009 – 5/30/2009

5/31/2009 – 6/13/2009

6/14/2009 – 6/27/2009

6/28/2009 – 7/11/2009

7/12/2009 – 7/25/2009

7/26/2009 – 8/8/2009

8/9/2009 – 8/22/2009

8/23/2009 – 9/5/2009

9/6/2009 – 9/19/2009

9/20/2009 – 10/3/2009

10/4/2009 – 10/17/2009

10/18/2009 – 10/31/2009

11/1/2009 – 11/14/2009

11/15/2009 – 11/28/2009

11/29/2009 – 12/12/2009

 

I don't think it will be a lot of efforts to adopt that solution to what you need.

 Thanks for the link to your blog.  I'm sure I will find it helpful, but I am having difficulty figuring out how to take your examples and modify them to achieve my goals.  Reading your posts, however, did give me an idea.

I created an XML datasource with all of the pay periods, and made a drop down list that uses the XML datasource.  My thought, but I have no clue how to begin, is if HR pulls the appropriate payperiod from the drop down, the form will somehow know the # of remaining periods and enter that number in the formulas above.  Spitting out the correct # of days off. 

 Does that make sense?  As I'm typing this I can see I might be able to modify your example, but I'm not sure yet.  Any other advice would be helpful.

 Thanks!

 I started looking at other ways to accomplish this.  I found out it's possible to pass a filed from InfoPath into a Stored Procedure to get desired output.  We have a database with our payroll calendar, so it made sense to create a stored procedure to pull remaining pays based off of HireDate.  I want to pass my HireDate field as the variable for the stored procedure.  Here is the code I am using on a refresh button i created.

 

var HireDate = XDocument.DOOM.selectSingleNode("my:myFields/my:HireDate")

 That code  returns 3 errors.

Name 'var' not declared

Name 'HireDate' not declared

Name 'XDocument' not declared

 XDocument.QueryAdapter.Command = 'execute "dbo"."storedprocedure" ' + HireDate;

This code gives an error on the "="

 XDocument.Query()

This code returns XDocument not declared

 

 Any ideas? 

 Is there a reason why you quintuple-nested all your code snippets?

The code there is JavaScript.  Is your form set up to use JavaScript as the language?  It doesn't look like it is.  And if you were using JavaScript, the first line you posted above should start with XDocument.DOM, not XDocument.DOOM

Yea, sorry for that. I didn't realize that what that button did. :) I'm pretty sure it is setup to us JavaScript. Is that something I can change or do I need to start over if it isn't? The first line is DOM, I mistyped it when I entered it here.

 If you open Form Options... from the Tools menu and then opening the Programming section, you should see which language is currently selected for your form.

Thanks for that, the form was setup using Visual Basic, I changed it to JScript and was able to get some where.  I preview the form and click my Refresh button, I get the following error.

The following error occurred:

Object required
File:script.js
Line:25

Below is the code i'm using.  Line 25 is the XDocument.QueryAdapter.Command

// Do not modify the name of the function, or the name and number of arguments.

//======= function CTRL120_5::OnClick(eventObj)

{

 var HireDate = XDocument.DOM.selectSingleNode("/dfs:myFields/my:HireDate").text;

XDocument.QueryAdapter.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ' + HireDate;

XDocument.Query();

}

Where would I tell the form where to put the output? 

I think you can use that code if your main data source is set up as a database connection.  Is it set up that way?

 No, I set it up as a blank form and added the data source.  The way I understood it, I only had to base the form off of a data source if I was writing data to the database.  Since I was just querying the data that is there I thought it would be ok to create the secondary source. 

acarita:

 No, I set it up as a blank form and added the data source.  The way I understood it, I only had to base the form off of a data source if I was writing data to the database.  Since I was just querying the data that is there I thought it would be ok to create the secondary source.

That is correct.

acarita:

Since I was just querying the data that is there I thought it would be ok to create the secondary source.

 Yes, it certainly is, but in that situation, you need to query that data source, and not the main one.  Please try some code like this:

          var dc = XDocument.DataObjects["dataConnectionName"].QueryAdapter; 
          var originalCommand = dc.Command;     // backup the connection's original command    
          dc.Command = "query";
          dc.Query();                
          dc.Command = originalCommand;         // restore the original command to the data source

Replacing the underlined parts with the appropriate values.

Jimmy:

acarita:

Since I was just querying the data that is there I thought it would be ok to create the secondary source.

 Yes, it certainly is, but in that situation, you need to query that data source, and not the main one.  Please try some code like this:

          var dc = XDocument.DataObjects["dataConnectionName"].QueryAdapter; 
          var originalCommand = dc.Command;     // backup the connection's original command    
          dc.Command = "query";
          dc.Query();                
          dc.Command = originalCommand;         // restore the original command to the data source

Replacing the underlined parts with the appropriate values.

Thanks,  I see where I was querying the wrong data source.  I copied the code and made the necessary changes, this is what I ended up with.

var dc = XDocument.DataObjects["PayPeriodsDataSource"].QueryAdapter;        

var originalCommand = dc.Command;     // backup the connection's original command

dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ' + "myFields/my:HireDate";          

dc.Query();

dc.Command = originalCommand;    // restore the original command to the data source

When I preview the form and try to run it, i get the following error.

The query cannot be run for the following DataObject: PayPeriodsDataSource
InfoPath cannot run the specified query.
[0x80040E14][Microsoft OLE DB Provider for SQL Server] Line 1: Incorrect syntax near '/'.

I'm going back through the code to make sure I didn't enter something wrong, but wanted to post it while I was thinking about it.

 

Another questoin I have.  What is the best way to pass the output from the SQL query to the InfoPath form?  The result is not being witten to a database, but the query gives it label of "RemaingPays".