I am wanting to find out if there is a way when capturing a new case form, if its possible to check if there is another case on the device that has the same case properties as the case the user is currently creating and then if there is, not allow them to submit.
So for example in this scenario there is already a case which has been created for an unique FDP location (FDP location is a case property) by the current user which isnt closed yet. When they try and create another case for the same FDP location I would like a warning to pop up telling them that there is already a case for this FDP location and that case needs to be closed first. So there would need to be a check in that calculation as well to see if the case has been closed if one is found.
One extra bit of info, this most likely will happen to a user in the field when they dont have connectivity.
Any help would be much appreciated to find a solution to this.
Kind Regards,
@Calvin you can create a hidden value question that does a CaseDB lookup as shown below. This value can then be referenced in the display condition and validation condition of a label question.
Hidden value question called fdb_case_count with calculate condition:
The expression in the label question would then be:
/data/fdb_case_count > 0
Assumptions:
The case type is fdb
The case property is called fdb_location
The value in the form is at /data/fdb_location
Depending on the number of cases on the device this CaseDB query could be slow since custom case properties are not indexed. If you find this to be an issue you could switch to using the external_id case property which is an indexed property designed for storing a user defined case ID.
Also note that this will only search cases currently on the user's device.
Awesome! Thank you so much for the response @Simon_Kelly. I was trying to figure it out on my own and was getting close, you just confirmed a few things I wasnt sure of thank you.
For the count in my particular scenario this code seems to be working for me.
count(instance('casedb')/casedb/case[FDP = 'belela'][@status = 'open'][owner_id = 'calvin'])
Obviously then I will substitute my name 'calvin' with /data/meta/username to get the current user_id,
and similarly for the FDP case value I will substitute the value from the question which is pulled from a dropdown list on the form.
You should always put indexed filters first in CaseDB expressions. So in your example you should put the status filter before any others. This helps with performance. Also if you can restrict it to a subset of cases base don the case type that would further improve performance.
I think you want /data/meta/userID instead of username
Thank you so much @Simon_Kelly for that extra input.
So my updated count looks as such.
count(instance('casedb')/casedb/case[@status = 'open'][FDP = #form/RegisterFdp/FDP][owner_id = /data/meta/userID])
With the above I have put the status filter first now as you suggested, however I am not sure what you mean by, "Also if you can restrict it to a subset of cases based on the case type that would further improve performance." How could I further improve performance in this scenario by using the case type?
yes you are 100% correct I meant userID, good catch.
I was suggesting that if your application is using multiple case types but you are only interested in searching one specific type then you can add a case type filter to the query:
(Replace my_case_type with the case type of the cases you are wanting to search).
In general this is a good idea anyway even if you are only using one case type in your application since it prevents you from having to alter the query later on if you decide to create cases with other case types.
Oh sorry @Simon_Kelly I had a brainfart when you said case types and wasn't following what you meant. Yup we have more than one case type in the app and this one in particular has child cases so I am sure by adding that it will improve the performance. I have implemented that as well. Thank you so much for your recommendations.