Don't lose value initially from CSV if that value is no longer in the CSV on edit

I have a calculation that pulls data from a csv file. There are some cases where I do not want the form to evaluate the calculation so I can't use once() because it depends on a condition.
If I use an if-statement, then I have to provide a result for each outcome (True and False)

My requirement is I only want to evaluate the calculation if a certain condition is met. If it's false, then I want the form to do nothing. Especially when I am editing the submission, if the condition is false, I want to keep the original answer.

I tried an if-statement without the "else" part and it didn't work. The if functions takes three parameters (Condition, True, False).

Additionally, this requirement is inside a repeat-group, I am getting the repeat_count dynamically from csv and it could be 1 or more. I used a trigger for the calculation inside the repeat and the trigger only works on the first iteration. When I am editing a submission, the trigger works and the first iteration is kept intact, but the following repeats are changing because the form is evaluating the calculations from the 2nd repeat iteration onwards (according to my own understanding).

Thank you.

Hi @Isslam,

To return nothing when the condition you specified evaluates to FALSE, you can try the if expression like this If(condition, "True", " "). The false statement will return nothing. See what I created in the attached form
form_3.xlsx (11.7 KB)

Can you share an example that replicates what you want to achieve with the dynamic repeat?

Thank you.

Thanks @Yakub . But won't this return an empty string when it's False?
I am editing a submission with an existing value inside a repeat question. This repeat question has calculation that i only want it to evaluate when the condition is true. If False, i want to keep the existing value.

I will share an example once I get back to the office.

If I understand you correctly, the calculation is within the repeat group. Every repeat will have the calculation question. I replaced the If(condition, "True", ${name}), the existing value will be returned when it evaluates to false. I added a read_only row to view the result.
form_3(1).xlsx (12.3 KB)

In general, you can use . to reference the current field's value. You may have used this before in constraints where you write expressions like . >= 18.

In this case, what you would like is something like if(<condition>, <expression>, .): if a condition is true, evaluate the expression, and otherwise keep the current value. However, this currently is not allowed because the . is considered a self-reference. Rejecting expressions using . in calculates prevents bad expressions like . + 5 which would lead to infinite loops. However, it also blocks really useful things like the expression above. There is a (technical) thread discussing tradeoffs for considering a change in behavior at

once() is a shortcut for if(. = '', <expression>, .). Because it's a function, it gets around the limitation I described above.

Once you provide more context around what the condition and the expression actually reference, we may be able to come up with an alternate solution. Specifically, it will be really important to know the shape of the condition and what form fields it references. It would also help to know whether you're targeting web forms or ODK Collect because repeats can have subtle differences between the two.

Depending on what the needs are, I do think you could consider splitting out the user-provided value and the calculated value as @Yakub has proposed. It's hard to know whether that's appropriate without more details on the actual need. The more detail you can provide on what real-world problem you're trying to solve, the easier it will be to match that need.

1 Like

Hi @LN and many thanks for your answer. I have attached my form with two csv files I'm pulling from.
fam_code.xlsx (14.0 KB)
csv_file.csv (550 Bytes)
detail_csv.csv (127 Bytes)

There are 3 main codes in the csv_file, 20, 59, and 113. Use any of them to fill the form and it will auto-fill the rest by pulling the details from detail_csv.

Submit a form (e.g., using code 20) and then edit the csv_file by removing any row corresponding to code 20, re-upload the csv and edit the submission. Naturally, it will not pull that deleted row but in this case, I want to preserve the already submitted data so the pulldata should not fire. Also I cannot use once() because in other cases I want to see the change.

I am using timestamps to check if 60 seconds passed since the form was initially opened (I don't know any other way to check if this is an "Edit submission" or "Add submission"). If > 60 seconds then don't perform the calculation.

And thanks @Yakub but the age you are referencing inside the calculation was manually entered before the calculation, mine is produced by pulling data from an external csv and I must perform this condition in the same question, otherwise it wouldn't make sense to have the user manually type the data then I pull the same data from csv. My goal is to reduce user input as much as possible + the value I am pulling from csv is a long alphanumerical ID which is not an easy task to do manually.


Here's what I think you're trying to achieve:

  • You want the form user to select a beneficiary
  • You want to show some known data about that beneficiary and you want to allow the form user to edit that known data. In other words, you want a dynamic default that will maintain the user-provided value if the default is overwritten
  • You want those dynamic defaults to update if the chosen beneficiary changes (which is why you don't want to use once)
  • You want all entered data to stay if the beneficiary is deleted from the attached data file

With these assumptions, I've modified your example to fam_code.xlsx (14.0 KB)

One really important thing to note first is that you want to make sure not to have fields that are user-editable and have calculations on them. If you do that, the user-provided value will always be overwritten by the calculation. If you want a dynamic default, you need to either use the default column or the trigger column to make sure the calculations are only run when it's appropriate to replace the current value. You can learn more in the docs about defaults.

In this case, I think you want your dynamic defaults to be triggered on a change in ${host_code} and you want the default expression to be something like if (${B_ID} != '', pulldata('detail_csv', 'Name', 'Beneficiary_ID', ${B_ID}), .). This means that if there was a match for the beneficiary ID in the CSV, the corresponding value in the CSV should be used. But if there's no match, then the user-provided value or the default from when the form was previously filled should be kept. That's what the . means. Because the expression is used with a trigger, it gets around the limitation on self-references that I described above.

You'll see that unfortunately this doesn't work in the repeat. I haven't had a chance to look into that any more deeply but I did want to share some initial ideas in case you found them helpful.

Hi @LN
Many thanks for taking the time to look into this.

The crucial part is getting it to work inside the repeat. I have other forms with the same logic and this is making it difficult to edit submissions.

Yes, the reference to the value itself is working when there's a trigger but doesn't work when the trigger is removed. However, keeping the trigger will make the repeat display only the first row of data and the rest will be empty.

My current workaround is whenever an edited is needed and the data had been changed, I re-upload an old version of the csv to match whatever data was there at the time of submission. It's an ugly solution but until something better can be found, I'll have to do it like this.

If you had the time to look into the logic of repeat in this case and find something, please do.

Thanks again

Do note that there were issues with your logic at the top level, too, especially my note about dynamic defaults.

One quick idea for you to consider is that you could "unroll" your repeat. That is, instead of using a repeat at all, you could use groups with relevance based on the count that you need. This works really well if you expect around 10 or fewer repeat instances. You can write the group once and then copy it. You'll need to adjust the field names to be unique and add relevance on each of them so that they only show up if the count is high enough.

I'll try to get back to this soon but I don't have any immediate ideas as to what's going on in the repeat.

1 Like

That is very helpful. In my case, the max currently is 8 and there won't be more than 10 so I believe this should work fine.

1 Like

That's definitely the option I would go for, then. It will likely make your analysis easier, too.

Please consider sharing your form as you finalize it for any further advice and so that someone looking to solve a similar problem can have an example to work from. Thanks and I hope the rest of the form design is smooth!