General

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Thu, 02 Dec 2010 14:12:59 GMT

As always... you are amazing :).

I hope this is all he asks for! I am definitely understanding DOM and InfoPath a lot more after your help. Thanks for everything! I hope I don't have to bug you again.. hah!

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

Hilary Stoupa — Wed, 01 Dec 2010 20:45:55 GMT

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"))

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Wed, 01 Dec 2010 20:32:52 GMT

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.

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

Hilary Stoupa — Wed, 01 Dec 2010 20:23:51 GMT

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?

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Wed, 01 Dec 2010 20:01:29 GMT

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.

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

Hilary Stoupa — Wed, 01 Dec 2010 19:52:23 GMT

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?

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Wed, 01 Dec 2010 19:11:56 GMT

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!

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Wed, 01 Dec 2010 18:18:07 GMT

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! :)

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

Hilary Stoupa — Wed, 01 Dec 2010 18:11:08 GMT

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. :)

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Wed, 01 Dec 2010 18:00:21 GMT

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?

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

Hilary Stoupa — Wed, 01 Dec 2010 17:30:31 GMT

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).

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Wed, 01 Dec 2010 16:51:00 GMT

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!!

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Wed, 01 Dec 2010 13:19:35 GMT

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

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

Hilary Stoupa — Tue, 30 Nov 2010 20:31:00 GMT

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.

Re: Sharepoint populated repeating table - unable to calculate field from repeating row values

msacco — Tue, 30 Nov 2010 19:32:02 GMT

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