Got something that doesn't fit in the other forums?

Reference specific child record

It would be nice to have a way to reference a specific child record. Currently I can see the records associated with a parent record but I am unable to reference data from a specific record.

example:
Parent 1234 has associated children 20, 22, 23, 24 in a secondary table. Goal is to access data from record 22 or 23. Currently only able to access data from 20 and 24 using min and max functions.

Thoughts or Ideas on how to implement this.

1 vote
Vote
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
You have left! (?) (thinking…)
Kurt Jordan shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • Kimberly commented  ·   ·  Flag as inappropriate

    Hello Kurt,

    Thank you for reaching out!

    When it comes to referencing data from a child table, aside from the max and min functions, there are a couple options depending on the use case. If you want to have all four child values, you could use the concatenate function. If you are wanting specific values from child records, then you would need to use logic functions. In the child table, you would use a calculated field to determine if the record meets specific conditions, and if so, have it output the number you're wanting to use.

    For example, if you wanted to know the total sales from orders that have been processed this month, you would use logic functions. The logic in the child table might look as so:

    if( and({Order Status}="Complete", month({Order Date}) = month(today() ), year({Order Date}) = year(today() ) ), {Order Total}, null() )

    This formula would output the order total for everything that was processed in the same month and year as the current date. If an order does not meet these rules, then the calculated field would remain blank. In the parent table, you could then use aggregate functions to either count the number of orders, sum the order totals, identify the max and min order totals, and so forth. The first step would be to establish the logical conditions in the child table.

    Hopefully this helps! Please feel free to call and speak to someone on our support team for additional assistance.

    Regards,

    Kim

Feedback and Knowledge Base