Forum Discussion

CharlotteM's avatar
CharlotteM
Qrew Cadet
25 days ago

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.

  • 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.

    • CharlotteM's avatar
      CharlotteM
      Qrew Cadet

      Thanks Mark.  The formula query works great for now.  Are there any concerns if the child table gets very large?  For a given sum, there would usually be 1-3 records in the child but there could be as many as 20 occasionally.

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        20 is a very small number for a Formula Query to handle, so no problem at all.