Populate parent field with data from child field

[tghoward]

Hi all,
I would like to do some pretty complex queries on data in a sub-form and put the results in fields in a parent form. I found this item:

Getting data in a linked_table entry for parent form

The mechanism to do this has not been generalized and folded into the core software. The ‘agriculture’ form demonstrates one possible implementation of the mechanism. In this form, there are custom prompt types for asynchronous assignment of values. Defining a custom prompt type requires a ‘prompt_types’ sheet with the prompt type name and the datatype that the prompt type manipulates (number in this example). And a customPromptTypes.js file that defines the implementation of that prompt type. …

Which is the core of what I’m trying to do. But it is five years old and I can’t get the ‘agriculture’ form to use these async_assign prompts. The ‘customPromptTypes.js’ file is still there, but I thought it worth asking:

1. Is this still the appropriate way to query and summarize data from a child table?

2. The result that’s closest to what I want would be ‘sum’ or ‘count’, but I’d like to filter the records beforehand. I assume I can’t use SQL? All the work on the data needs to be within the js file?

3. Perhaps this is obvious, but could someone check the ‘agriculture’ form to make sure it is behaving as expected? I’ve tried ApplicationDesigner_v2.1.9 and 2.1.10 and don’t see a subform. I’m running App Designer on Ubuntu 22.04 LTS.

Thanks in advance.
Tim

[tghoward]

I understand the workings of the agriculture form a little better so I retract #3, above. It isn’t calculating data from a subform, it is doing calculations on the other rows of the same form.

To expand on what I’d like to do. Consider the Household Survey form. What if I wanted to count the number of people in a household with age < 20 (age data are collected in members subform), and use that value in a calculation for the household table?

Or, even more relevant, what if the Household Members form asked what their favorite color was, and I wanted to count the number of cases of “Green” within the household?

[elmps2018]

Hi @tghoward!

You can definitely do these complex types of queries, I do them a lot!

1. They do currently use async_assign and queries.
2. You can filter the records using queries and SQL syntax. See this example: Working with Repeat groups (now Sub-forms) - #4 by Larry_Li we filter to count the # of people in the household based on the household identifier.

You could just add an age<20 to that query and get what you want. You can do something similar with counting the number green.

[tghoward]

Hi @elmps2018,
Thank you for the reply! This is a great help and I’ve made good progress on it. I’ve managed to get the counts I need, with the exception that I’d like to find duplicate entries. So that would be applying the UNIQUE qualifier to the query, if at all possible. Or, how else might I find duplicates in a certain field?

Translating to the Household Members form, what if someone entered the same person twice? Can I query the names field and find the number of unique values is fewer than the total number of records?

I’ve tried this syntax:
queries tab:
selection column: site_id = ? and UNIQUE(species_name)
selectionArgs: [ opendatakit.getCurrentInstanceId(), data(‘species_name’)]

with no luck …

Or perhaps there’s another way to count unique entries?

[elmps2018]

Hi @tghoward,

I am not particularly familiar with the UNIQUE command, but if what I wanted to do was check for duplicates (I think this is what you are trying to do?) I would do a query that counts the number of instances with that name and household identifier within the individual subform, and constrain the result to be exactly one.

[tghoward]

@elmps2018 - yes, that’s what I realized after replying and I’m working on that now. Thanks!