Nested IF Statement in Calculations

asked on October 20, 2017 • Show version history

I have a form that is going to be used by our Finance Department. It queries the GSA Per Diem API https://www.gsa.gov/technology/government-it-initiatives/digital-strategy/per-diem-apis/api-for-per-diem-rates

And gets rates for Meals, Lodgings, etc. Whenever the user types in the Zip code automatically.

The user then can select from a drop down list what their expenses are, and the data populates from the Table that is populated by the API as such.

And uses this formula in Calculations.

And this works great, Until I try to add in a Nested IF statement for Lunch and Dinner(IF Breakfast is Selected, IF Lunch Is Selected, or IF Dinner is Selected). So My question is, how do I add a nested IF Statement using Laserfiche Calculations to successfully pull back the lunch and dinner rate from the table above and populate in the table below.

2 0

SELECTED ANSWER

replied on October 20, 2017

You can nest IF, but you need to have the full syntax for each one nested more like this:

IF(condition1,"True1",IF(condition2,"True2",IF(condition3,"True3","All Three False")))

3 0

replied on October 20, 2017

Perfect, Solved my Problem

Thanks!

2 0

replied on November 9, 2017

Hello Mark,

Can you show me how you used the API to get the Per Diem rates in forms.

Thanks for the help.

1 0

replied on November 9, 2017 • Show version history

Sure

So the rates can be one of Seven rates, according to https://www.gsa.gov/travel/plan-book/per-diem-rates/meals-and-incidental-expenses-mie-breakdown

Keep this chart, it will come in handy in a minute.

So what I do is I construct a call to the API using JQuery with the current year(Captured from a Variable) and a zip code(Captured from user input). JQuery has a getJSON function that handles getting JSON from external sources.

  $("#q61 input").change(function(){
         $('.mieBreakfast input').val('');
         $('.mieLunch input').val('');
         $('.mieDinner input').val('');
         $('.mieIE input').val('');
         var mealPrice = "";
         $('#q66').hide();
    
    	var currentYear = (new Date()).getFullYear();
    	var currentZip = $(this).val();
		$.getJSON( "https://inventory.data.gov/api/action/datastore_search?resource_id=8ea44bc4-22ba-4386-b84c-1494ab28964b&filters=%7B%22FiscalYear%22:%22"+currentYear+"%22,%22Zip%22:%22"+currentZip+"%22%7D", function( data ) {
          	var mealPrice = data.result.records['0'].Meals;
               mealPrice = parseInt(mealPrice);
               switch(mealPrice){
                 case 51:
                   $('#q66').show();
                   $('.mieBreakfast input').val('11.00');
                   $('.mieLunch input').val('12.00');
                   $('.mieDinner input').val('23.00');
                   $('.mieIE input').val('5.00');
                   break;
                case 54:
                   $('#q66').show();
                   $('.mieBreakfast input').val('12.00');
                   $('.mieLunch input').val('13.00');
                   $('.mieDinner input').val('24.00');
                   $('.mieIE input').val('5.00');
                   break;
               case 59:
                  $('#q66').show();
                  $('.mieBreakfast input').val('13.00');
                  $('.mieLunch input').val('15.00');
                  $('.mieDinner input').val('26.00');
                  $('.mieIE input').val('5.00');
                  break;
              case 64:
                  $('#q66').show();
                  $('.mieBreakfast input').val('15.00');
                  $('.mieLunch input').val('16.00');
                  $('.mieDinner input').val('28.00');
                  $('.mieIE input').val('5.00');
                  break;
                case 69:
                  $('#q66').show();
                  $('.mieBreakfast input').val('16.00');
                  $('.mieLunch input').val('17.00');
                  $('.mieDinner input').val('31.00');
                  $('.mieIE input').val('5.00');
                  break;
                case 74:
                  $('#q66').show();
                  $('.mieBreakfast input').val('17.00');
                  $('.mieLunch input').val('18.00');
                  $('.mieDinner input').val('34.00');
                  $('.mieIE input').val('5.00');
                  break;
               }
          });
});

So basically after pulling the value in using .getJSON, it then activates a switch statement depending on the value pulled in, and sets the classes accordingly via the chart.

2 0

Question

Question

Nested IF Statement in Calculations

Answer

Replies

Sign in to reply to this post.