Newbie Questions

Re: Formula to calculate days off

acarita — Mon, 27 Jul 2009 15:40:34 GMT

That worked! Thank you for taking the time to help me out. I was able to create my formula using the value returned from the stored procedure and I have the correct final value that I am expecting.

I appreciate your help.

Re: Formula to calculate days off

Jimmy — Sat, 25 Jul 2009 02:58:55 GMT

Once you have the value stored in the rp variable you are free to manipulate it, perform calculations with it, or store it in a field in your main data source so it can be manipulated within the form:

XDocument.DOM.selectSingleNode('my:myFields/my:RemainingPays').text = rp;

Re: Formula to calculate days off

acarita — Fri, 24 Jul 2009 18:48:44 GMT

My apologies. I put it back and the command went through without error.

I'm still not clear on how I can see the value that should be returned. I'm trying to understand, so forgive me if my question seems silly.

I dont understand the var = rp... line that we entered. This

'dfs:myFields/dfs:dataFields/d:row/@RemainingPays'

looks to me like it is referencing a field in the forms datasource. When I go to the SQL datasource, I don't have "row" below dataFields, I just have the name of the table. Do I need to setup a datasource using that path?

Re: Formula to calculate days off

Jimmy — Fri, 24 Jul 2009 18:32:39 GMT

If the P is capitalized here:

dc.Command = 'DECLARE @remainingPays smallint ' + 'EXECUTE "dbo"."VCS_SP_Remaining_Pays" \'' + HireDateV + '\', @Remainingpays OUTPUT ' + 'SELECT @Remainingpays as RemainingPays';

Then it should be capitalized here:

var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@Remainingpays').text;

There was really no need to change any of the capitalization from what I provided you in the first place.

Re: Formula to calculate days off

acarita — Fri, 24 Jul 2009 18:24:44 GMT

dc.Command = 'DECLARE @remainingPays smallint ' + 'EXECUTE "dbo"."VCS_SP_Remaining_Pays" \'' + HireDateV + '\', @Remainingpays OUTPUT ' + 'SELECT @Remainingpays as RemainingPays';

The only thing I changed from the code was making pays lowercase in @Remainingpays. In the storedprocedure the "p" is lowercase. The post from 30 min ago I just copy and pasted those 2 lines from your reply and forgot to change the "p".

Re: Formula to calculate days off

Jimmy — Fri, 24 Jul 2009 18:16:09 GMT

The part that looks something like this:

dc.Command = 'DECLARE @remainingPays smallint '
+ 'EXECUTE "dbo"."VCS_SP_Remaining_Pays" \'' + HireDateV + '\', @remainingPays OUTPUT '
+ 'SELECT @remainingPays as RemainingPays';

Did you copy it exactly, or did you modify it somehow?

And why is "Pays" capitalized in your post from 30 minutes ago

var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@RemainingPays').text;

while it's lowercase in your post from 10 minutes ago?

var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@Remainingpays').text;

Re: Formula to calculate days off

acarita — Fri, 24 Jul 2009 18:08:04 GMT

I apologize if I dont completely understand. @Remainingpays is set as the output in the stored procedure.

set @Remainingpays = (select sum(1) --as Remainingpays

Are you lookiing for a line of code out of the form code, or from the stored procedure?

Re: Formula to calculate days off

Jimmy — Fri, 24 Jul 2009 17:54:59 GMT

Where's the part where you're setting the query text?

Re: Formula to calculate days off

acarita — Fri, 24 Jul 2009 17:52:23 GMT

dc.Query();

var ds = XDocument.DataObjects["PayPeriodsDataSource"].DOM;

var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@Remainingpays').text;

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

Re: Formula to calculate days off

Jimmy — Fri, 24 Jul 2009 17:43:32 GMT

Could you paste the section where you are currently seting the query text?

Re: Formula to calculate days off

acarita — Fri, 24 Jul 2009 17:35:56 GMT

I put the 2 var codes after dc.query() and got the error below.

The following error occurred:

Object required
File:script.js
Line:43

Line 43 is

var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@RemainingPays').text;

Re: Formula to calculate days off

Jimmy — Fri, 24 Jul 2009 16:51:44 GMT

Up near the top of the JScript code, you should see a section where SelectionNamespaces is being defined. You'll need to add this line right after it:

XDocument.DataObjects["PayPeriodsDataSource"].DOM.setProperty("SelectionNamespaces", 'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');

Once you've done that, you should be able to access the RemainingPays value after the query like this:

var ds = XDocument.DataObjects["PayPeriodsDataSource"].DOM;
var rp = ds.selectSingleNode('dfs:myFields/dfs:dataFields/d:row/@RemainingPays').text;

Re: Formula to calculate days off

acarita — Fri, 24 Jul 2009 16:09:47 GMT

I tried the code snippet you gave me and it executed without error. Thank you for that.

Now the next step I need to figure out is how can take the value that is being returned, @Remainingpays, and set it equal to, /my:myFields/my:RemainingPays? @Remainingpays is not a column in any table, otherwise I would create that datasource and set the field equal to it.

The end result is to have that RemainingPays value get multiplied by number to return the # of paid time off.

Re: Formula to calculate days off

Jimmy — Fri, 24 Jul 2009 02:20:41 GMT

As I suspected, the form is not opening because of this line:

dc.Command = 'execute "dbo"."VCS_SP_Remaining_Pays" ', "{d \'" + HireDateV + "'}" ', ' + Remainingpays + ' output';

Here you have a comma where you should have a plus sign:

Remaining_Pays" ', "{d \'"

And here you have two separate strings with nothing joining them:

"'}" ', '

If you put plus signs in those two places, the form will open.

However, there are still a few issues.

After looking at the stored procedure I can see that your colleague has modified the stored procedure to accept HireDate as an nvarchar and convert it to a date, so that means you do not need the {d } bit anymore.

Also, you are passing in the value of the Remainingpays field, but the server will expect an SQL variable there since it is an output parameter.

Please try the following and let me know if the query executes without an error:

dc.Command = 'DECLARE @remainingPays smallint '
+ 'EXECUTE "dbo"."VCS_SP_Remaining_Pays" \'' + HireDateV + '\', @remainingPays OUTPUT '
+ 'SELECT @remainingPays as RemainingPays';

Re: Formula to calculate days off

acarita — Fri, 24 Jul 2009 01:10:55 GMT

yes. It's just a button that I labeled "Refresh" and attached the code to the On_Click event.