Survey123, 2 fields calculate off each other. Which direction they are calculated is dependent on a select_one

[MosquitoGIS]

The scenario is this.  We are treating using granular larvicides/pesticides. The field user estimates acreage and enters it.  They then weigh their bag before treating, then after treating they weigh it again to see how much pesticide was used.  To determine the rate in which they discharged the pesticide, they put the amount of pesticide dispersed in the Amount field and choose to calculate it by Amount.  It calculates their rate and displays that calculated rate in the Rate Field.

However there are times where they can't weigh it before and after, and so must make a judgement on the rate they put the pesticide out instead of going on the amount.  In this case, I would like them to say they are going to calculate using Rate (instead of amount) and have them enter their rate into the Rate field and it calculates and displays the amount of pesticide used in the Amount field.  

And both fields need to be visible to the user.

Here is a basic setup of what I am trying to accomplish:

Form--- 3 numeric fields and 1 "Select One" field.

Acreage:

   Numeric

Calculate method

   Select one

•       Rate
•       Amount

Rate:

   Numeric

Amount:

   Numeric

Calculations---

 Rate is to Calculate via if(selected(${RateOrAmount}, 'Amount'), ${Amount} div ${Acres}, '0')

 Amount is to Calculate via if(selected(${RateOrAmount}, 'Rate'), ${Rate} * ${Acres}, '0')

_________________________________________

However, this throws an error.

I am pretty sure, the problem is in the 'else' portion.  Since I am defining the else to be 0 (as one usually does) then if Rate was to be chosen as the calculation method, the Rate field would find it shouldn't follow the if/then statement and and instead go 'else' and place a 0 in the field, which then affects the Amount field that was trying to legitimately calculate based on what was entered into the Rate field (That was just ripped out from underneath itself).

If I am to remove either If/Then and run it, it has no issues.  But if both exist, It will not run.

Anyone know how to make it so that instead of defining it as 0, I can just say, "Then don't worry about it.  Just sit and be happy" or something.

Or have a different idea on how to set this up so it will be successful?  Thanks.  Hopefully this makes sense and is clear.

[DougBrowning]

Well if it is a number then you need to lose the '' around the 0.  '0' is a string.

To do nothing you can leave it as '' (blank string).  I know this seems to contradict the above statement but it works.

Slicker option is to use relevant and just hide the field completely.

Hope that does it.

[MosquitoGIS]

Hey Doug, thanks for your reply.

I did notice that I was using the string instead of the integer, but it worked and I was being lazy.  Haha!  But, now that you called me out on it, I guess I should buck up, fix it and do it right to avoid possible other issues. 

So, I did try using a blank string and still had the same problem.  Unfortunately a "" is still considered an entry that can be stored (Even null would technically qualify for that as well ) , and so it is still causing the same problem.

[DougBrowning]

What do you mean by a value that is stored?  123 will store a '' for any field that has no value.  Do you mean you want the field to not be in the database?  You can make the field never there but no database in the world can have a field for some records and not others.  I guess I am not sure what you are asking now.

If you just want one value or the other then you could make a 3rd field.

Make field 1 and 2 type hidden - that way they are never in the database or stored.

Field 3 would have a calc of if(field1='',field2,field1}.  

Then set your field1 and 2 to the if(check,formual,'') like you wrote above but with a '' instead of '0'.

This way only 1 final value will ever get stored.  Then if you want field 1 and 2 to not be in the database at all set the bindtypeESRI field to null.

Will that work for you?

[MosquitoGIS]

As mentioned originally, I want both fields to display and store a number.  I just want a select_one to choose which field the user types in and which field officiates the calculation.

If I choose that I want it to calculate the rate, I would choose Rate on the select_one, enter the Amount and let it calculate the Rate. In return, If I choose I want it to calculate the amount, I would select Amount ,enter the Rate and let it calculate the Amount.  I want both of those numbers, despite which way was chosen, to be stored within those fields.

The problem that I perceive happening is this (endless loop, division of 0, limit to survey123 capabilities?  I honestly have no idea.):

I treat 20 acres. So Acres=20

I weigh my bag and decide I want to calculate by Amount.  So, I choose 'amount' on the select_one and enter the amount (lets say 10 pounds) in the amount field.  

Acres = 20

Amount = 10

Now, the Rate field becomes read only and attempts to trigger the following:  if(selected(${RateOrAmount}, 'Amount'), ${Amount} div ${Acres}, 0) which results in 0.5.

Acres = 20

Amount = 10

Rate = 0.5

However, there is still code that hasn't run, namely the Amount field calculation, which is: if(selected(${RateOrAmount}, 'Rate'), ${Rate} * ${Acres}, 0) which results as a 0.

Acres = 20

Amount = 0

Rate = 0.5

But wait, something has changed within the form, so re run the code:

Now, the Rate field becomes read only and attempts to trigger the following:  

Rate field = if(selected(${RateOrAmount}, 'Amount'), ${Amount} div ${Acres}, 0) which results in Not possible.

Amount field = if(selected(${RateOrAmount}, 'Rate'), ${Rate} * ${Acres}, 0) which results as a 0.

Acres = 20

Amount = 0

Rate = Not possible

In reverse, lets say I choose rate (10):

Acres = 20

Amount = 0

Rate = 10

Rate field = if(selected(${RateOrAmount}, 'Amount'), ${Amount} div ${Acres}, 0) which results in 0.

Amount field = if(selected(${RateOrAmount}, 'Rate'), ${Rate} * ${Acres}, 0) which results as a 200.

Acres= 20

Amount = 200

Rate = 0

But wait, something changed:

Rate field = if(selected(${RateOrAmount}, 'Amount'), ${Amount} div ${Acres}, 0) which results in 0.

Amount field = if(selected(${RateOrAmount}, 'Rate'), ${Rate} * ${Acres}, 0) which results as a 0.

Acres = 20

Amount = 0

Rate = 0

Even then, it probably recognizes there was a change and begins a loop of putting 0's into the fields, as the calculation says something must be placed in there at that moment if that calculation runs.  If this (trigger) than X (20), otherwise Y (0).  Something is always being put into the field every time it runs, even if it is the same number.

I am wanting to bypass the 'else' portion, so that it isn't always putting something in the field (making it less trigger happy).  Or set it up so that I can achieve the same results of both fields showing and storing its associated content and it being able to calculate the direction the user chooses.

It is possible I might be able to use your suggestion for a kind of bait and switch behind the scenes that gives the same appearance and effects, but just has a extra steps of moving numbers across fields and creative validating (through If/Then's) to get the results I want. I was just hoping that there is an easier solution before I begin creating a Rube Goldberg Machine to accomplish, what I am still hoping is, a simple set of results.

[DougBrowning · Accepted solution]

Like you said having just the 2 fields creates an endless loop.  You are going to need some new fields.  

If the user is picking which to use just have the other field hide.  Then have a 3rd and 4th field for the calcs that is a note type.  Your if would check the type value (amount or acres) or look for '' and calculate accordingly.

[MosquitoGIS]

Going through old posts and giving credit where it was due.  Thanks for this.  I ended up doing exactly what you said and made two 'note' fields that did the calculations themselves, and were tied to fields for storing their content.  The original fields only needed to collect the typed in data, where the 'note' fields actually was the data I wanted to stored.  So the original typed in data fields are set up to appear and disappear depending on what they selected to calculate by and are a null type value so that they don't store any data in any fields.

[MosquitoGIS]

I don't know for sure, but I am thinking the only way to pull this off would be to have the else statement basically not exist or equivalent. 

I know there has to be the 3 arguments (If, Then, Else), so it is required for it to exist.  So, my hope and guess is that maybe there is something in the else position that I can put that achieves the equivalent.  And I honestly just don't know if that thing exists in Survey123 at this time.  Perhaps, I am wrong or there is a different/better way to do this.  I am just not sure what that would be.