locked
Find the highest values across multiple fields (Infopath 2010) RRS feed

  • Question

  • I have a section of my infopath 2010 form that I want to work out the highest valeus across.

    There are four fields; whichever is the highest of the four fields, I want this to be displayed in another field.

    How do I make this happen?

    Friday, January 27, 2012 2:34 PM

Answers

  • Code for THAT???

    Assign default value of the field where you going to store max value to this formula:

    max(field1|field2|field3|field4)

    also make sure that checkbox about formula recalculating is checked.


    http://alecpojidaev.wordpress.com
    • Edited by Alec Pojidaev Friday, January 27, 2012 5:44 PM
    • Proposed as answer by John Liu Monday, January 30, 2012 2:26 AM
    • Marked as answer by gurgling_mrd Monday, January 30, 2012 5:30 PM
    Friday, January 27, 2012 5:41 PM

All replies

  • You could do this using Rules in the InfoPath form. Here you can check if the value of a field is greater then the value of another field.
    Kind regards,
    Margriet Bruggeman

    Lois & Clark IT Services
    web site: http://www.loisandclark.eu
    blog: http://www.sharepointdragons.com

    Friday, January 27, 2012 3:04 PM
  • I tried something like that. Let's say that I have fields 1, 2, 3 and 4 and I want the highest of those to appear in field 5

    I put a rule in field 1 as:

     

    Action: Set a fields value

    Field: Field 5

    Value: "Field 1"

    Condititions: Field 1 > Field 2 AND Field 1 > Field 3 AND Field 1 > Field 4

     

    ...and then made corresponsidng rules for the other 3 fields. But nothing happend.

     

    What do you think I did wrong?


    Friday, January 27, 2012 3:46 PM
  • Try setting the action for each of those rules to Set Field 5 = Field 1, Set Field 5 = Field 2, etc.
    Friday, January 27, 2012 4:08 PM
  • Sorry, what? I don't understand. (Probably me being a bit thick). I think what you've described is what I have already done: each of the first 4 fields has the corresponding rule attached to it. As in:

    Field 1's Rule is:

    Action: Set a fields value

    Field: Field 5

    Value: "Field 1"

    Condititions: Field 1 > Field 2 AND Field 1 > Field 3 AND Field 1 > Field 4

     

    Field 2's Rule is:

    Action: Set a fields value

    Field: Field 5

    Value: "Field 2"

    Condititions: Field 2 > Field 1 AND Field 2 > Field 3 AND Field 2 > Field 4

     

    Field 3's rule is...

    etc, etc, etc...

     

    Do you see what I mean?

    Friday, January 27, 2012 4:23 PM
  • Sorry I misunderstood your rule definition, it appears you are already doing that..

     

    So it's an Action rule with condition stated above, and action is Set field value to X for each Field 1-4?

     

    You could always use code to do it.. I have found that InfoPath functionality is much easier when done with code..

     

    To start it you could have a button that when clicks, compares each of the four values and writes the highest one to the fifth field, or if you don't want a button you can have the code run when the last (4th) field is changed ie entered, and then do the same functionality.

     

    If you want the second way:

     

    Select the fourth field in the field list, right click and select Programming > Changed Event

     

    VSTA will open with the form code. At the very top, right under where it says Public Class FormCode, put one of these functions for each field:

     

    Private Property _Field1() As Object
    
                Get
    
                    _Field1 = FormState("_Field1")
    
                End Get
    
                Set(ByVal value As Object)
    
                    FormState("_Field1") = value
    
                End Set
    
    
    
            End Property
    

     

    Then in the function Field4_Changed near the bottom, where it says "Write your code here" put this:

    Dim root As XPathNavigator = MainDataSource.CreateNavigator()
    
    _Field1 = root.SelectSingleNode("/my:myFields/my:Field1", NamespaceManager).Value
    
    _Field2 = root.SelectSingleNode("/my:myFields/my:Field2", NamespaceManager).Value
    
    _Field3 = root.SelectSingleNode("/my:myFields/my:Field3", NamespaceManager).Value
    
    _Field4 = root.SelectSingleNode("/my:myFields/my:Field4", NamespaceManager).Value
    
    
    
    Dim max As Integer
    
    max = System.Math.Max(_Field1, System.Math.Max(_Field2, System.Math.Max(_Field3, _Field4)))
    
    root.SelectSingleNode("/my:myFields/my:Field5", NamespaceManager).SetValue(max)
    


     

     

    This SHOULD work to find the max of the four fields and then sets it to the fifth field. 

    NOTE: The code will fire when the fourth Field is changed, as so if the other 3 are empty, it will set field 5 = field 4.

    To fix this you could maybe add some formatting rules so that if field 1 is blank. field 2 is disabled, if field 2 is blank, field 3 is disabled, etc. So that as the user enters field 1, field 2 becomes available and so on, and then when they enter field 4, the code fires and sets field 5 to the highest value :)

     


    • Edited by AZandbergen Friday, January 27, 2012 4:50 PM
    Friday, January 27, 2012 4:50 PM
  • I just tested this myself.

     

    You can't perform this code on field4 being changed because at the instant that it detects the field being changed, it appears that the value that the user enters hasn't yet been written to the field value, so the code detects it as Null.

    SO, you can use a button like I first suggested. I tested this with a button:

    -Create the four Whole Number(integer) fields

    -Create a button below/beside

    -Create a fifth STRING field. ****This WILL NOT WORK if the FIFTH field is NOT a STRING****.

    -Select the button, click Button Properties, and then click Edit Form Code...

    -Drop this into the CTRL#_#_Clicked Function at the bottom where it says Write code here..

     

    Dim root As XPathNavigator = MainDataSource.CreateNavigator()
    
    Dim f1 As Integer = root.SelectSingleNode("/my:myFields/my:field1", NamespaceManager).Value
    Dim f2 As Integer = root.SelectSingleNode("/my:myFields/my:field2", NamespaceManager).Value
    Dim f3 As Integer = root.SelectSingleNode("/my:myFields/my:field3", NamespaceManager).Value
    Dim f4 As Integer = root.SelectSingleNode("/my:myFields/my:field4", NamespaceManager).Value
    
    Dim max As Integer
    
    max = System.Math.Max(f1, System.Math.Max(f2, System.Math.Max(f3, f4)))
    Dim str As String = max.ToString
    
    root.SelectSingleNode("/my:myFields/my:field5", NamespaceManager).SetValue(str)
    

     

    Ignore the code I posted in my previous post, I tested that and it doesn't work without tweaking it. The code in THIS post though works, I tested it and it works perfectly.

     

    NOTE: The code language is VB, so if you already have code in C#, you'll have to translate it to C#

    NOTE: There are other events that you can detect to get the max. For instance, if you don't need the users to see the fifth field with the max in it, you can do the calculation when the user submits the form, or when another field is changed maybe. 

    NOTE: Depending on how your form is set up, you will most probably have to change the path of the fields in the four Dim f# lines (/my:myFields/my:field4). If you have more groups than just the myFields group you might have to change it to something like:

    /my:myFields/my:group1/my:nextGroup/...

    It works exactly like the address for a document in a folder in Windows Explorer (C:/Program Files/Common Files/Microsoft Shared/etc.. except that for each group and field name, you have to put "my:" in front of it, which you will notice in all the examples I've posted.

    Let me know if this helps :):)


    • Edited by AZandbergen Friday, January 27, 2012 5:40 PM
    Friday, January 27, 2012 5:28 PM
  • Code for THAT???

    Assign default value of the field where you going to store max value to this formula:

    max(field1|field2|field3|field4)

    also make sure that checkbox about formula recalculating is checked.


    http://alecpojidaev.wordpress.com
    • Edited by Alec Pojidaev Friday, January 27, 2012 5:44 PM
    • Proposed as answer by John Liu Monday, January 30, 2012 2:26 AM
    • Marked as answer by gurgling_mrd Monday, January 30, 2012 5:30 PM
    Friday, January 27, 2012 5:41 PM
  • Wow.. 

     

    Yeah I totally forgot about that built in function!!!! :P

     

    Doing it with code is fun though ;)

    Friday, January 27, 2012 5:46 PM
  • Thanks Alec! This looks like the best solution, although it doesn't seem to be working (with the following error)

     

    Expected value type: end-of-string

    Actual value: (
    max(../my:Field1)-->(<--../my:Field2)(../my:Field3)(../my:Field4)

     

    I tried replace the individual parethesis with commas instead (i.e. MAX(Field1, Field2, Field3, Field4) ) and I then get this error:

     

    Invalid number of parameters.

    Error occurred during a call to property or method 'Max'.

     

    Any chance i've got it wrong? Can you add to the solution?

    I'm not really much of a coder, so the OOTB solution would be amazing.

    Thanks for all your ideas, guys.

    Monday, January 30, 2012 8:18 AM
  • the separators between parameters are shift+backslash characters "|"

     

    so what Alec has is the exact way the function should look:

     

    max(field1 | field2 | field3 | field 4)

    Monday, January 30, 2012 5:17 PM
  • Ah! My mistake - hadn't registered the characters properly!

    That is a good tip - thank you.

    And, as predicated, it works a charm - thank you both very much indeed!

    Monday, January 30, 2012 5:30 PM
  • I tried this solution but this error appeared when i wanted to open the form :

    "There has been an error when processing the form"

    and when I delete that max function  form opened with no error!!!

    so is there  another way to use the max function??

    tnx

    Wednesday, April 12, 2017 4:22 AM