Learning How to Write an expression - needing a little guidance - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Learning How to Write an expression - needing a little guidance

Last post 01-26-2012 03:03 PM by TxSteve. 13 replies.
Page 1 of 1 (14 items)
Sort Posts: Previous Next
  • 01-26-2012 07:00 AM

    Learning How to Write an expression - needing a little guidance

    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      

     

  • 01-26-2012 10:14 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    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? 

  • 01-26-2012 10:23 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    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.

    Anson Hidajat
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™

  • 01-26-2012 10:25 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    Hi:

    First, I'm confused as to this:

    I have since learned you cannot have a calculated or lookup field.

    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?

    Hilary Stoupa

  • 01-26-2012 10:40 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    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

  • 01-26-2012 10:58 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    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.

    Hilary Stoupa

  • 01-26-2012 11:00 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    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. 

     

  • 01-26-2012 11:09 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    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.

  • 01-26-2012 11:17 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

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

    Hilary Stoupa

  • 01-26-2012 11:44 AM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    Please see attached.

  • 01-26-2012 02:14 PM In reply to

    Re: Learning How to Write an expression - needing a little guidance

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

  • 01-26-2012 02:19 PM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    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.

    Hilary Stoupa

  • 01-26-2012 02:30 PM In reply to

    Re: Learning How to Write an expression - needing a little guidance

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

  • 01-26-2012 03:03 PM In reply to

    Re: Learning How to Write an expression - needing a little guidance

    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.

Page 1 of 1 (14 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.