InfoPath Dev

So I created a sample list in SharePoint 2007. I also have InfoPath 2007. The list columns are; Bolt, Bolt Cost, Washer, Washer Cost, Nut, Nut Cost, and a Calculated field that displays a subTotal. 

I then opened InfoPath and linked to the list. In InfoPath, I associated my dropdown boxes so that if a user selects a specific Bolt, only the corresponding washers and nuts are displayed. I did not add any of the cost columns as I was hoping to add the hidden subtotal field. I also added a Quantity field and finally a box that would take the total and multiply the quantity and display to the user in the final read only field.

I have since learned you cannot have a calculated or lookup field. I did read on the web where you can use XML and the GUID and to add the subtotal field, however when doing so, all I get is a "float;#1.50 0000000000" listing and my final total field reads NaN. I understand the NaN means I've got something wrong such as a numeric filed trying to add a text field. However, I don't understand the float issue. 

Nonetheless, I'm prepared to abondon the current design and add all of the cost fields, perhaps hidding them, but I am not sure how to associate (filter) the cost boxes to the users selections. The Drop Down boxes I'm using have a filter button, but the text boxes do not.  Any suggestions?

**Update: Since posting above, for grins I added went ahead and added dropdown boxes for the costs and associated them to the existing dropdown choice boxes. The data is filtered correctly. However, I have to obvisouly select the drop down box to enable or activate the costs. The math is working. Guessing I need to now figure out how to replace the temporary dropdown cost boxes with expression boxes or text fields that are filtered. 

***Update: I think I'm on to something... Looks like I can use a list box to return a value and have the filter functionality I am seeking. Life is good!

Thank you      

So close and yet..

Now I have list boxes that display the correct associated costs but now I can't get a text box to sum the list boxes. Odd thing is, if I click on each value in the list box... then it works.

Surely there is a very easy way to add; Box 1 + Box 2 + Box 3 and display in field. What am I missing? Should I not use List Boxes? 

You can set the default value of your total field to be Box1 + Box2 + Box3.  Just make sure that the refresh value checkbox is enabled and it should update as you change the values of the other fields.

Hi:

First, I'm confused as to this:

What does that mean?

When you say XML and the GUID - do you mean you added an owssvr data connection to the form?

All you need here is to be able to subtotal fields that have values that look like this:

"float;#1.50 0000000000"

Is that correct?

So, perhaps:

number(substring-after(fieldwithdata, "#"))

Would work?

Same challenge. When the list boxes are filtered and display the correct data, the txt box I am using to total them up remains blank. So for example

Cost A 1.00

Cost B 2.00

Cost C 3.00 

Total Box:  nothing...

However,

If I click on each with my mouse, (1.00, 2.00, 3.00) then the total box displays 6.00

Is the field that is bound to the list box being set to a default value of the entry of the list box? It sounds as if when you click on them, you are then setting the value of the field.

I have a calculated column (total) in my SharePoint list,

ID | Bolt | Bolt Cost | Washer | Washer Cost | Nut | Nut Cost | Total

1      2         1.50          2             1.00              2      1.50         4.00

2      2         1.50          2             1.00              3      2.50         5.00

3      2         1.50          3             2.00              4      3.50         7.00

----------------------------------------------------------------------------------------------------------

Using InfoPath to connect to a SharePoint list, it will not see the Calculated column. I delete my simple connection in InfoPath, create a new one using an XML (owssvr) connection. Now it will see the column, but will not display the value of 4.00 for ID 1, Instead it displays something like, "float;#4.00 0000000000"

I stopped trying and simply added the Cost columns to my InfoPath form. I added them as List Boxes...only becasue I am learning and they seem to work well. 

So the user selects a Bolt, an associated Washer and an associated Nut. The washers and nuts are filtered to only display what is available for that bolt. It's working great. The list box totals are working great. They are displaying the corresponding costs.  

At the bottom of the user choice dropdowns are three additional boxes.

SubTotal:              Quantity:              Total:

I can't get the subtotal to read the values in the list boxes unless you click on them. Thinking I should use something else besides a list box...perhaps an expression. 

When I select the first cost value with my mouse, the total box will display the correct value of 1.50. When I select the second cost with my mouse for the washer, it will add the two together and display 2.50, when I select the nut cost, it will correctly total 4.00. Not sure why the values display in the list box, but won't total until clicked on with a mouse. This really has to be something simple that I am missing.

Can you attach your form to a reply? Under the Options tab.

Please see attached.

What is the need to use List Boxes for the costs?

He was using list boxes because he understood how to filter them. :-)

I ended up taking this to email, so I could get a copy of the form w/ the data connections intact, and added some default values to better explain what I meant, as well as an expression box showing how he could subtotal the items he wanted.

Ah ok, thats what I figured. Glad he got it figured out!

Yep, it's brutal sometimes being a rookie. :)

  I finished tinkering with the modifications you did for me. THANK YOU Jilary!!! It helped so much. Gave me a much better understanding of how it should be built. Going to start adding other features tomorrow.

Have a very blessed evening.