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

Return Child Value Base on Most Recent Record

I need to be able to return a value in the most recent child record to a calculated field in its parent. I am completing a series of inspections (children) on a unit (parent) ; I want to continue to populate the inspection status and update it as time moves forward. Is it possible to create a calculated field that will update an inspection value of pass/fail as more recent inspection records are made?

I have tried to use the MAX function but that simply returns the largest text value based on the letters.

Thanks in advance!

3 votes
Vote
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
You have left! (?) (thinking…)
Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

3 comments

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

    Hi Noelle!

    The formula below is showing you the most recently created child record on the parent table. To sum a number that falls within a date range, a different approach would be taken. On the child table, you could add a field with a logic statement that shows you the metric only if it falls within a certain date range. Then on the parent table, you would perform a sum on the field that you just created, which means you would only be summing the numbers that fall within the desired date range.

    For example, on the child table, you could add a syntax similar to the one below. That logic is saying, if the month of the day the record was created is equal to the month of today, show me the desired metric, otherwise, do nothing. Let's call this field "Example A".

    Example A = if(month({Created})=month(today()),{Metric},null())

    Then, on the parent table, do a sum on the field you just created, which would look like the syntax below.

    sum({Child Table Name}.{Relationship Name}.{Example A})

    This syntax can vary depending on the date ranges you wish to include. If you have any issues or would like further assistance, feel free to send us an email to support@trackvia.com.

    Regards,

    Cristina

  • Noelle commented  ·   ·  Flag as inappropriate

    Hi Cristina,

    I have a question for you - can you use the formula below to sum a number within a date range?

    Ie. We have metrics imported into TV on a weekly basis, i want a MTD of each metric to show on the parent table.

    Thanks!

  • Cristina commented  ·   ·  Flag as inappropriate

    Hi there!

    Thank you for reaching out with your question.

    If you are looking to have a calculated field on your parent table to show a specific field of the most recent child record, you can try the formula below. This formula would need to be placed on the parent table and it will show the field of your choice of the most recently created child record.

    if(contains("|",childconcatenate({Table Name}.{Relationship Name}.{Field Name} ,"|")), reverse(snip(reverse(childconcatenate({Table Name}.{Relationship Name}.{Field Name} ,"|")),"","|")), childconcatenate({Table Name}.{Relationship Name}.{Field Name} ,"|"))

    If you encounter any issues when setting up your formula, feel free to send us an email to support@trackvia.com.

    Regards,

    Cristina

Feedback and Knowledge Base