InfoPath Dev

This is my first post on this dev site and I've read a lot of helpful hints and tips, so I decided to try and get this problem resolved here... I've spent over 30 hours on this particular, seemingly easy task that my boss has been pushing me to get done for this form. Any help would be greatly appreciated because I've exhausted all resources that I can think of..

I am currently working on this Form that pulls data from a Sharepoint site. Because of this, each of the rows are filled in automatically as soon as I "preview" the form, and the table values are filled in appropriately. The problem I have is that once the data is pulled and the form is populated, I cannot calculate a "total" for each row.

In other words, I have the following fields derived from Sharepoint:

         Size:  /dfs:myFields/dfs:dataFields/dfs:Job_Data_List/@Size

         List cost:  /dfs:myFields/dfs:dataFields/dfs:Job_Data_List/@List

and I am trying to calculate, in the same row, TotalCost =  @Size * @List. (/my:myFields/my:Calc/my:TotalCost) I have a footer variable that is currently calculating the total of everything, but I cannot get the calculated field on each row to calculate. Instead, it takes the value from the first row values of size and list and then extrapolates that down for the rest of the arbitrarily long list of rows.

 

I couldn't figure out how to do this through XPath alone, so I went to try with VBScript and my plan was to do the following:

Grab the value from Size and List fields and multiply them together to get the TotalCost for that repeating row item. I accessed the TotalCost field just fine using:

         XDocument.DOM.selectSingleNode("/my:myFields/my:Calc/my:TotalCost").text

but could not do the same with the Sharepoint generated items. I'm assuming there is some kind of namespace involved that I don't know how to use? I would like to grab the "text" or "value" of its contents to be able to do my computation. After I obtained this TotalCost, I was going to try and loop through each of the rows and update the TotalCost field based off that current row's values. This would be initiated by some Button that I put at the bottom that just, upon clicking, initiated the loop and updating of the TotalCost values.

 

The form is basically in this kind of form:

Row 1. Field1        Field2       @Size         @List       TotalCost

 Row 2. Field1       Field2       @Size         @List       TotalCost

etc.

 

If anyone knows the solution to this problem, or an easier solution through XPath or any other way, please let me know. I have been spending way too much time on this and this task seems so simple.

Thank you!

Mike

 

 

 Bump -- still in need of this problem to be resolved. PLEASE help!

You shouldn't need code to calculate your total cost for items, I don't think... Any chance you can attach your form to a reply so I can take a quick peek at it? Under the Options tab, there is a place you can attach a file.

Unfortunately, the work I am doing is material-sensitive so I can't really send the form out.

What do you think is the alternative to code? I'll do whatever I have to to get this resolved. I looked up some tutorials online where people are using current() method but it seems my default value field has to use up that spot because the data I am bringing it seems to be multi-layered (I'm not sure of this because I am quite new to InfoPath...)

Currently, the Formula field has:

@Size[@Job_Num = Job] * @List[@Job_Num = Job]

in it... the detailed version is:

xdXDocument:GetDOM("Job Data List")/dfs:myFields/dfs:dataFields/dfs:Job_Data_List/@Size[../@Job_Num = xdXDocument:get-DOM()/my:myFields/my:Job] * xdXDocument:GetDOM("Job Data List")/dfs:myFields/dfs:dataFields/dfs:Job_Data_List/@List[../@Job_Num = xdXDocument:get-DOM()/my:myFields/my:Job]

________

This works great and just fine and updates every value for when I manually add a line in it once I am previewing the form, but it just repeats the same value calculated from the first row of the repeating table when the form is pre-populated from SharePoint

 As an update, I got permission from my boss to give out the form. Obviously you can't access the Sharepoint information but at least you can see the way the data is laid out and stored.

     Looking to update: TotalCost field in myFields/Calc/TotalCost

                                 Value: @Size + @List

Any help you can give would be VERY useful and appreciated.

Thanks!!

I don't think your data structure is workable. The field you want to calcuate the total cost in is in your main data source. Your other data is in your secondary data source. Even though you have made the field repeating, InfoPath won't automatically insert enough instances of the field to have one for each line item.

Remember, your secondary data source data won't ever be saved with your stored form data. If any of that SharePoint list data needs to stay with the filled out form, you'll need to rethink your approach.

All that being said, I've added an expression box that should show the correct Size * List for each row (the current formula in total cost uses *, although your post above says + -- not sure what your desired result is).

 It is not necessary to have this information on the Sharepoint site -- this will be manipulated as an offline form for administrative purposes. Thanks for the heads up!

 

I'm trying to open your file but I think it has to be published by you before I can open it?

Right click the file and save locally. Right click the saved file to open in design mode. Then you'll be able to re-save. IP forms don't much care for being moved about. :)

 Yes! It works beautifully! After all this effort and I just needed an expression box?!

 Thank you so much for all your help -- this just saved me from the wrath of the higher ups. (AND you did responded promptly) -- I am forever grateful! :)

 One more quick question...

Manager just asked me to sum up all of the totalCost stuff but I can't add expression box values since they do not store. Am I correct about this?

Sorry to bother you again!

You are correct -- they don't store. They merely reflect the state of your secondary data source. This is why I expressed concern over the schema for you form.

What is the actual requirement you are working toward?

 Yes, I originally thought this wouldn't matter because he never asked me before to total up all the fields -- this was an addition.

 So right now, the expression box is correctly adding up the data from the two sharepoint fields, but now he wants a total of all those totals to show up in the footer of the repeating table (similar to how I had field12). As it seems to be leading to, I don't think I'll be able to do this unless I actually create the totalCost field inside the sharepoint form so that when I grab the sharepoint data I can then do an expression box or text file to sum up all of these fields similar to how it's done now (or in field12).

Is there any way I can do this without actually manipulating the sharepoint fields? It seems expression box/grabbing the data sources is the only way to display information from the secondary source as you have done. If there is no other way, manipulating the way sharepoint stores its data (i.e. adding an additional column to total the values before I pass them into this form) isn't necessarily unfeasible, just not preferable.

Thanks, again.

If you need to set a single field in your main data source to the total of your ShP list data, you can do that with a formula -- look into the sum function (you may need eval, depending on what you are trying to do). Be careful with defaults that get updated when the data changes -- if the ShP list data changes and you re-open the form, you have to think about whether the total cost should change.

For example:

I fill out your form on June 1. The total for the list items at that time is $7.00. I save my XML, the field has a value of 7.00.

You open my XML August 15. The total for the list items is now $10.00. Should the value of the field in the XML I saved change?

 Yes, field 12 adds all the "@Units" fields and totals them. However, I can't just do:

total_in_footer = sum(@Size[@Job_Num = Job] * @List[@Job_Num = Job]) because it gives an error.

 

Currently, I am using your expression box to calculate the Cost in each row (@Units * @List) and now I need the footer to include the total of all of those Costs to give a TotalCost.

 

field12 works like this:  sum(@Size[@Job_Num = Job]) --- which works fine for just tallying up all the sizes. But now that I have a cost that is the calculation of @Units * @List, how can I sum this up?

 

Sorry if I am confusing you -- this is all quite confusing to me.

I think you'll need to use eval. So, for example, try this formula in field13 (by the way, before your form goes into production, you'll want to get your data source renamed and tidied up):

sum(xdMath:Eval(xdXDocument:GetDOM("Job Data List")/dfs:myFields/dfs:dataFields/dfs:Job_Data_List[@Job_Num = xdXDocument:get-DOM()/my:myFields/my:Job], "@Size * @List"))