How can I calculate area when I have multiple equations for many feature types?

[KaitlynAbrahamson]

Hi All,

I have another formula to create, but I am new to Survey123 and am not sure how to get what I need on the survey.  The project I am working on is sign inventory and the question I am having trouble with is with the area of each sign (200 different sign types).  I will have the width and height of each of the sign, but the signs, being all different shapes, have different area formulas. 

These are all the different formulas:

(Width * Height)/144 (The majority of the signs have this equation)

(SQUARE(Height/12)) * .76

((1 + (SQRT(2))) * 2) * (SQUARE((Width / 12)/2.4))

(Height * Height /333)

(Width * Width * .0052083)

(Width)/12

(Width * Height)/182.5

(Height * Height * .0042918)

I have this for an equation (${SignWidth}*(${SignHeight})div144) for the majority of the signs, but the more advanced equations I am clueless on.  If you know how to write out some of these, that would be very helpful.  I am not even sure which survey column I should be writing these in either (calculation, relevent, etc.?)

I think I am in need of a very intense, nested if statement, but maybe it needs to be broken up in some way. I've included what my data looks like for reference.  Please help!

[KaitlynAbrahamson]

It looks like I am having trouble with the "or" statements in my calculations and the "Max" line for the final calculation.  Any idea why?

Also, it tells me I am missing a ")" when I get rid of "selected."

Ideas?

[DougBrowning]

If you take out selected you must remove the ( and the ) wherever you used it.  See below.

For the Or statements you must not be a programmer .

Cannot do this

if(selected(${signtype},'S1-1 - School' or 'SS4-5 - School Speed 15 MPH Ahead (Symbol)')

Must be a full compare for each Or.

if(selected(${signtype},'S1-1 - School') or selected( ${signtype},'SS4-5 - School Speed 15 MPH Ahead (Symbol)'))

Or if a select one it is cleaner to do 

if(${signtype}='S1-1 - School' or ${signtype}='SS4-5 - School Speed 15 MPH Ahead (Symbol)',yes,no)

Also are your Names all of this - 'SS4-5 - School Speed 15 MPH Ahead (Symbol)' or is that just the Label?  You need to compare with Name not label.  I would not put all those special chars in my data.  For sure you cannot have new lines or any commas in your lists!

[KaitlynAbrahamson]

Hi Doug,

I think I am getting really close now.  I am having trouble with the last statement though.  

I think it is obvious by now that I have never been and am not now a programmer.  Your suggestions really helped.  Technically, the sign names are as long and horrible as you say.  It is not the label.  I could and probably should separate them and make them easier to work with, but I am scared to undo my work and potentially mess something up.  

I'm not sure what to do for my last statement.  All the sign areas are still 0, even when the sign is specified to use a different equation.  I want all signs to use my final equation if they were not previously mentioned in the other sign area equations.  This is my last statement:

if(${signtype}='R1-1 - Stop' or ${signtype}='T3B - Type 3 Barricade' or ${signtype}='W10-1 - Grade Crossing Advance Warning' or ${signtype}='W14-3 - No Passing Zone (Pennant)' or ${signtype}='S1-1 - School' or ${signtype}='SS4-5 - School Speed 15 MPH Ahead (Symbol)' or ${signtype}='M1-7 - NFS ROUTE MARKER' or ${signtype}='M1-6 - County Route Sign (1 - 3 digits)' or ${signtype}='R1-2 - Yield 36x36' or ${signtype}='R1-2 - Yield (Low Volume Roads)',0,(${widthnote} * ${heightnote}) div 144)

I basically just reversed the statement and included all the signs that use other equations, but I think that just defaults ALL the signs to use the last equation.  I'm thinking I'm not going about it the right way.

Thanks for all your meticulous help.

[DougBrowning2]

Well if all the other options have not been picked then you know all your calcs are 0.  So just check that instead.

If(max(calc1, calc2,calc3,calc4) =0, Do default calc,0)

[KaitlynAbrahamson]

I'm not sure I know what you mean by "Well if all the other options have not been picked then you know all your calcs are 0."  All of the calculations are turning up 0 when they absolutely should not be.

[DougBrowning]

Sounds like your formulas are not working.  Got to fix those first.

[KaitlynAbrahamson]

Do you suppose I could use the pulldata function instead?  I tried setting the 'areaformula' column with the area calculations from Survey123.  I'm not sure I can make it work, but I can't get it to actually calculate; it just pulls the calculation up.  Here's my calculations:

pulldata('signcatalog','areaformula','signnumber',${signtype})

${areacal}

Any way you can think of how to get it to actually calculate?  Thanks!

[DougBrowning]

I have not found a way to pulldata into the Calculation column.  May be possible but not sure.

[KaitlynAbrahamson]

I got my equations to work because I ended up shortening the sign name for each sign.  Thank you for all your help!

[KaitlynAbrahamson]

Hi Doug, I tried that last equation you sent.  It works except for the last part.  The default calculation is not running if all the others are 0.  Any ideas?