InfoPath 2010 get the highest or lowest value from two or more disparate fields using Rules

InfoPath 2010 get the highest or lowest value from two or more disparate fields.

I was working on an InfoPath 2010 project where I would pull in the pilot, copilot and crew with the respective hours of flight experience.  Then I needed to know the lowest number because that determined the highest risk factors.  They could then mitigate those risks, but it was important to identify ahead of time the risk factor for the mission.

Since the data came from different fields, I could not use the max or min functions built in for a repeating row.  The display requirements also didn't fit repeating table either, so even if I could smush my data into one, I'd have to pull it back out to fit the display requirements.

I created a button that executed a series of rules.  There is no IF THEN ELSE for InfoPath 2010 rules, so I needed to use a little trickery and some Algebra. 

I had a field named DayRisk1.  Clicking the button first sets the field to 10,000.  This makes it beyond the range of a risk factor.   Next I set the field's value using this formula.

((PIDayHr1 <= DayRisk1) * PIDayHr1) + ((PIDayHr1 > DayRisk1) * DayRisk1) + (not(PIDayHr1 >= 0) * DayRisk1)

There's a lot going on there, so I'll explain it bit by bit.

(PIDayHr1 <= DayRisk1)

This will return True (1) or False (0). DayRisk1 was initially set to 10,000, So if Pilot1's daytime flight hours are less than or equal to 10,000 (they are) this returns 1 for true.

(PIDayHr1 <= DayRisk1) * PIDayHr1

This 1 is then multiplied by the value of Pilot1's daytime flight hours (say 1,234)

The next step adds in the reverse of that:

((PIDayHr1 > DayRisk1) * DayRisk1)

Since PIDayHr1 is 1,234, it's not greater than 10,000 this segment is 0 times 10,000 which is 0

these two numbers are added together

(PIDayHr1 <= DayRisk1) * PIDayHr1) + ((PIDayHr1 > DayRisk1) * DayRisk1)

Giving you 1234.  If DayRisk1 was lower, (like when we get to the step for CoPiDayHr1, Crew1DayHr1, etc) it would return that value.  Since only one will EVER be true, you're ALWAYS adding zero to the lowest number.

So what's the last step, you ask?  Well, if PIDayHr1 is blank, then both segments will return 0. In my use case, there were instances where the crew could be 2 people instead of three, so I wanted to ignore nulls.

+ (not(PIDayHr1 >= 0) * DayRisk1)

So if PIDayHr1 is not greater than or equal to 0, this returns true (1) which is then multiplied by DayRisk1 and added to the two other numbers.  Since my data was only counting numbers, and will never be negative, this section will only be true if BOTH the others are 0, so we're still adding three numbers - two of which are Zero.



If you find this clever or useful, please leave a comment below.  I was scratching my head on this one for a while, but finally came up with this.

Comments

Post a Comment

Popular posts from this blog

Setting SharePoint announcements to auto delete after expiration

Image
Setting announcements to auto delete after expiration One thing that always bugged me was the expiration date attached to an Announcement list that does nothing out of the box. Sure, you could always add a filter and change the default view but as soon as you add an Announcements web part to a page the view is UNFILTERED even if a filtered view is the only one to choose from.  You or I could change the default view, but we can’t trust all users to do so. A workflow would be fine to delete the item – simply pause until the expire date then delete the current item.  The workflow would stick with the template if you saved your list off as a template and then still work when you created a new list from that template.  That’s good, but still relies on the user creating the right Announcement list. The better answer was to change the Announcements default content type.  If anyone asks, tell them it’s called Governance, Sparky. The first step was to go to site sett...
Read more

Updating a single field in a SharePoint List using Power Automate Flows

Image
  Updating a single field in a SharePoint List using Power Automate Flows Updating a single field in a SharePoint List using Microsoft Flows (aka Microsoft Power Automate) can be a bit tricky with longer lists with lots of required fields and drop downs possibly resetting your data. The work around is code, but if there are non-developers creating flows, then this process is even more cumbersome. As a result, the Flow below will do the heavy lifting using a separate list and then anyone can populate that list from a different Flow when such a thing is needed. First step: create the list Here’s the input needed in this list. List: The name of the list SiteLink: The link to the site (trim off /lists/xyzlist) IDFromSource: This is the internal ID of the item to update FieldToUpdate: The name of the field you want to update. This is case sensitive. ValueForField: What you want to set it to. Next, create the Flow. Here’s what it will look like   When a Shar...
Read more

SharePoint driven rich text dashboard using jqueryui. (JQuery file)

Jquery file for Operations Dashboard Download this file <script type="text/javascript"> $(document).ready (       function()       {             SP.SOD.executeFunc('sp.js', 'SP.CamlQuery', RetrieveListValues);       } ); function RetrieveListValues() {       var query = new SP.CamlQuery(); query.set_viewXml("<View><Query><OrderBy><FieldRef Name=\"SortOrder\"/></OrderBy></Query></View>");                                     var clientContext = new SP.ClientContext("https://sharepoint.name/section/OD");       var oList = clientContext.get_web().get_lists().ge...
Read more