Community

Allow questions to be called indirectly in calculations by specifying a range

282
0
11-24-2023 04:59 PM
Status: Open
Labels (2)
j-bromp
by
New Contributor III

I currently have 20 fields that are named ${d1}, ${d2}, ${d3} etc all the way up to ${d20}. These fields refer to 50mm increments of depth going down into the ground. The survey will be used to record how many blows of a dynamic cone penetrometer (dcp) it takes to go down each 50mm increment. On the outset this is very simple, if it takes 2 blows to go the first 50mm then we write 2 in ${d1}. if it takes another 3 blows to go down the next 50mm then we write 3 in ${d2}. however if it takes just 1 blow to go down the first 200mm then we would need to put 0.25 in each of d1, d2, d3, d4. Now, sometimes it might take just 1 blow to go down a full meter! that is 20 increments! imagine sitting there and writing 0.05 into a box 20 times. wouldn't it be good if you could just go down to the 1m mark and put a 1 in that box, because it took you one blow to get there. the calculation behind the scenes would then check through and see if questions d1 - d19 are empty, if they are then it will divide 1 by (19 + 1(for the 1m mark)) and enter 0.05 in each of those boxes. Now, you have to look at this from the perspective of each increment and write a calculation that checks: if this cell is empty then check how many empty boxes are before it until you come across non empty question, and how many are after it until you come across a non empty question. then divide 1 by (the number of empty questions + 1 for the current question).

for my very first question of d1. this is the check that has to happen. keep in mind this will become exponentially more complicated one we are down in the ground further as we have to check forward and backwards:

if(string-length(${d1}) = 0 and ${d2} = 1, round((1 div 2),2), if(string-length(${d2}) = 0 and string-length(${d2}) = 0 and ${d3} = 1, round((1 div 3),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and ${d4} = 1, round((1 div 4),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and ${d5} = 1, round((1 div 5),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and ${d6} = 1, round((1 div 6),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and ${d7} = 1, round((1 div 7),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and ${d8} = 1, round((1 div 8),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and ${d9} = 1, round((1 div 9),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and ${d10} = 1, round((1 div 10),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and ${d11} = 1, round((1 div 11),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and ${d12} = 1, round((1 div 12),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and string-length(${d12}) = 0 and ${d13} = 1, round((1 div 13),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and string-length(${d12}) = 0 and string-length(${d13}) = 0 and ${d14} = 1, round((1 div 14),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and string-length(${d12}) = 0 and string-length(${d13}) = 0 and string-length(${d14}) = 0 and ${d15} = 1, round((1 div 15),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and string-length(${d12}) = 0 and string-length(${d13}) = 0 and string-length(${d14}) = 0 and string-length(${d15}) = 0 ${d16} = 1, round((1 div 16),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and string-length(${d12}) = 0 and string-length(${d13}) = 0 and string-length(${d14}) = 0 and string-length(${d15}) = 0 and string-length(${d16}) = 0 and ${d17} = 1, round((1 div 17),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and string-length(${d12}) = 0 and string-length(${d13}) = 0 and string-length(${d14}) = 0 and string-length(${d15}) = 0 and string-length(${d16}) = 0 and string-length(${d17}) = 0 and ${d18} = 1, round((1 div 18),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and string-length(${d12}) = 0 and string-length(${d13}) = 0 and string-length(${d14}) = 0 and string-length(${d15}) = 0 and string-length(${d16}) = 0 and string-length(${d17}) = 0 and string-length(${d18}) = 0 and ${d19} = 1, round((1 div 19),2), if(string-length(${d1}) = 0 and string-length(${d2}) = 0 and string-length(${d3}) = 0 and string-length(${d4}) = 0 and string-length(${d5}) = 0 and string-length(${d6}) = 0 and string-length(${d7}) = 0 and string-length(${d8}) = 0 and string-length(${d9}) = 0 and string-length(${d10}) = 0 and string-length(${d11}) = 0 and string-length(${d12}) = 0 and string-length(${d13}) = 0 and string-length(${d14}) = 0 and string-length(${d15}) = 0 and string-length(${d16}) = 0 and string-length(${d17}) = 0 and string-length(${d18}) = 0 and string-length(${d19}) = 0 and ${d20} = 1, round((1 div 20),2), ${d1})))))))))))))))))))

if we could do something that checked a range of questions for a particular value each time instead of directly calling each one that would save a lot of time. at this point i'm not even sure if it is possible to complete this workflow.

Tags (2)