Parent table with custom key from a formula
I have a child table that is built in Form Engine. A pipeline takes info from the child table and creates a parent table with a custom key. That key is from a text formula field in the child table.
It works but now I need a summary field (sum of some amounts) on the parent table, which will not work because the key is from a formula.
I'm wondering if there is some way I can create a text field in the child table that gets its value from the formula field, so that static text field can be used as the key rather than the formula. Is this possible? Or is there another, better strategy?
There are two ways to go here. One method is to have a pipeline take the value of that formula field on the child record and write it to a scalar field in the child record. Then use that field for the relationship and roll up your totals with a normal summary field on the relationship.
The pipeline can be set to Trigger, every time a child record is created or modified, and when the formula field is not equal to the scalar field.
I would suggest you also set up a safety net process each night to look for any child records that for some reason, the regular pipeline didn't handle during the day.
I typically use that method above, but the other possibility is to use a formula query on the parent record to get the totals from the children.
