Dynamically create a calculation formula in a repeat loop

[janna]

Hi!

I’m creating a dynamic data attachment of a form to itself. (e.g., the form is both the parent and the child form).

Inside the form, I’m collecting 30 integers.

When I open up the form a second time, I’m then going to check each integer entered against all integers entered previously in all 30 fields.

For example, if I’m entering an integer, I want to check that it doesn’t match an integer from any of the 30 fields in any previous submission.

This could get very tedious in the programming of the XLSForm, as for each of the 30 integers, I’d need to check against 30 values (would that be 900 lines of code?)

So instead, what I’m trying to do is to create a little ‘repeat loop’ that dynamically checks the integer entered against all 30 fields.

I’ve used ‘concat’ to create the calculation string needed for the dynamic data attachment calculation, but I essentially then need the ‘string’ created in each repeat loop to be turned into a dynamic formula.

So, say I have my calculated string as something like this (forgive the weird formatting, as this is needed to prevent errors in the concat function): concat(“count(instance('xlsfo”, “rm’)/root/data[group/”,${dynamic_name}," = cur", “rent(”, “)/.”, “./label_id_1])”)

Then, I want to have a ‘calculate’ question that actually calculates this formula in repeat loop #1:
count(instance(‘xlsform’)/root/data[group/label_id_1 = current()/…/label_id_1])

And it would calculate this formula in repeat loop #2:
count(instance(‘xlsform’)/root/data[group/label_id_2 = current()/…/label_id_1])

etc, etc.

However, when I put a calculate question in the repeat loop, I just put ${dynamic_formula} in the calculation column, but instead of evaluating the string as a formula, it just places the string as the value of that field.

So it doesn’t allow me to creata a dynamic formula.

Any idea how to create a dynamic formula?

Thanks!
Janna

[Xiphware]

@janna

Inside the form, I’m collecting 30 integers.

Are you collecting these in a repeat group? Or are there 30 different questions?

[janna]

30 different questions - my understanding is that dynamic data attachments don’t work to dynamically retrieve data from within repeat groups. Although this is an interesting post that I haven’t dug into fully - I wonder if there’s a workaround…

[Xiphware]

@janna

Inside the form, I’m collecting 30 integers.

When I open up the form a second time, I’m then going to check each integer entered against all integers entered previously in all 30 fields.

For example, if I’m entering an integer, I want to check that it doesn’t match an integer from any of the 30 fields in any previous submission.

I’m not 100% sure exactly what your workflow is - at least in terms of singlular parent+child form, checking ‘previous submissions’, etc - but maybe take a quick look at this. I’ve reduced it to 3 questions instead of 30 for brevity, but its readily expandable. It will check you dont re-enter any values from previous submissions (at least on the same device…) by accumulating them via a last-saved.

Note, this form doesn’t prevent you from entering the same (but new) value multiple times during the same submission [your description didn’t preclude this!] but it’d be easy enough to add a constraint for this too if needed.

BTW I asked whether the questions were in a repeat group because it’ll simplify the allvalues calculation; you could then use a join() instead of having to list all 30 questions in the concat(), but the basic approach remains the same.

lastsaved.xlsx (18.2 KB)

Because allvalues basically keeps appending to previous, the last-saved list of numbers keeps accumulating everything you enter each time you reuse the form. Tested successfully on Collect, but not Enketo.

Does this help?

-Gareth

[janna]

@Xiphware I want to thank you very much for this solution.

It’s probably not the solution to the problem in terms of my original question (as the workflow will need to work with multiple data collectors collecting data on multiple devices, and will go into the thousands of submissions over multiple months)…which is why I thought dynamic data attachments is most likely the better solution.

However, there is a lag (potentially of 1-7 days) between the time when data collectors can renew their form download on their device, collect data in remote areas, and then reconnect to the internet. So I think your suggestion would actually work quite well in helping to catch duplicate errors when they are ‘offline’ in between internet connections, when accumulating these values and checking them will be useful.

So I’m going to implement this approach in the next couple of days for my initial testing - thank you!

I’m still wondering if there’s a way to force ODK Collect to interpret a string formula as the formula itself and calculate the value to the formula.

I might post on the ODK forum as well to see if anyone’s seen this done or done it before (albeit, not with dynamic data attachments, but with any other calculated dynamic variable formula…).

[Xiphware]

@janna

I’m still wondering if there’s a way to force ODK Collect to interpret a string formula as the formula itself and calculate the value to the formula.

Short answer is you cant.

For reference, calculations in XLSForms are converted (like other questions…) into XForm bindings, which in the case of an XLSForm calculation dont have any associated UI element in the body. Its just an XPath binding to the relevant element in the instance XML (ie the blob of data that gets send back upon submission) with a calculate attribute containing the XPath expression to evaluate.

This is best describe with a simple example. The following XLSForm becomes this actual XML XForm:

calculation.xlsx (18.1 KB)

<?xml version="1.0"?>
<h:html
    xmlns="http://www.w3.org/2002/xforms"
    xmlns:h="http://www.w3.org/1999/xhtml"
    xmlns:ev="http://www.w3.org/2001/xml-events"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:jr="http://openrosa.org/javarosa"
    xmlns:orx="http://openrosa.org/xforms"
    xmlns:odk="http://www.opendatakit.org/xforms">
    <h:head>
        <h:title>Simple calculation</h:title>
        <model odk:xforms-version="1.0.0">
            <instance>
                <data id="calculation" version="1">
                    <name/>
                    <result/>
                    <show_result/>
                    <meta>
                        <instanceID/>
                    </meta>
                </data>
            </instance>
            <bind nodeset="/data/name" type="string"/>
            <bind nodeset="/data/result" type="string" calculate="concat('Hello ', /data/name )"/>
            <bind nodeset="/data/show_result" readonly="true()" type="string"/>
            <bind nodeset="/data/meta/instanceID" type="string" readonly="true()" jr:preload="uid"/>
        </model>
    </h:head>
    <h:body>
        <input ref="/data/name">
            <label>What is your name?</label>
        </input>
        <input ref="/data/show_result">
            <label>
                <output value=" /data/result "/>
            </label>
        </input>
    </h:body>
</h:html>

In this example, the XLSForm calculation “concat('Hello ',${name})” becomes the binding

<bind nodeset="/data/result" type="string" calculate="concat('Hello ', /data/name )"/>

which basically says evaluate the XPath expression “concat('Hello ', /data/name )” and store the result in the /data/result.

What you are trying to do basically requires a new XPath function, say eval(), which instead of evaluating the provided expression string (ie “concat('Hello ', /data/name )”) instead evaluates the string contents of one a specified element in the instance XML document; eg

calculate="eval(/data/myexpression))"

where you have previously stored, say, the string “concat('Hello ', /data/name )” in the myexpression form field.

Unfortunately, neither the javaRosa XPath engine nor the equivalent XPath engine used by Enketo have such a function.

Basically, if you want to - essentially - parse a string to do something depending on what it contains, you’ll probably have to write a fairly complicated if-then-else expression to do it. It may be feasible, depending on exactly what you are trying to do.

[Aside: @janna if you look carefully in the above link you may notice “if (name.equals("geofence"))...” ]

[janna]

@Xiphware

Short answer is you cant.

This is what I figured, but I knew if it was possible you’d probably know!! Thanks for the explanation, it’s helpful to see the XML explanation.

@Xiphware

[Aside: @janna if you look carefully in the above link you may notice “if (name.equals("geofence"))...” ]

So good!

[Xiphware]

Again, it may be possible to write a rudimentary string parser to ‘dynamically’ evaluate a (simple) calculation, depending on its complexity, but obviously not an arbitrary XPath expression.

Could you perhaps post 2 or 3 example calculations that you think you would want to be able to evaluate, with their variations, and we can see if or how they might be re-encoded into a format that could potentially be evaluated with a few nested-if statements.