When I am creating a summary text field on my parent table, is there a way that I can limit the child records to the most recent 3 records?
Parent (Invoices) -> Child (comments)
We only want to see the 3 most recent comments in the summary field.
Solved
Limit the summary text records to the most recent 3 child records
Best answer by LauraThacker
If you're referring to the new combined-text summary field; then you need to first identify what the 3 most recent child-table records are. You can do that with the following:
1. Summary field between Invoices < Child called [Maximum Record ID#] where you are pulling the Maximum [Record ID#] value
2. Lookup field to Child table [Maximum Record ID#]
3. Summary field between Invoices < Child [2nd Maximum Record ID#] = Max Record ID# where [Record ID#] is NOT equal to [Maximum Record ID#]
4. Lookup field to Child table [2nd Maximum Record ID#]
5. Summary field between Invoices < Child [3rd Maximum Record ID#] = Max Record ID# where [Record ID#] is NOT equal to [Maximum Record ID#] and [Record ID#] is NOT equal to [2nd Max Record ID#]
6. Lookup field to Child table [3rd Maximum Record ID#]
7. Formula field (checkbox)
If([Record ID#] = [Maximum Record ID#] or [Record ID#] = [2nd Maximum Record ID#] or [Record ID#] = [3rd Maximum Record ID#], true, false)
8. Combined Text Summary Field: Combine text where [Formula-Checkbox Field] = true.
This way your combined Text will constantly change as your Child-Records update
A more convoluted way to do this would be via reverse relationships; which would also be helpful depending on how you want to "view" these comments; in a combined text field; they will all be in a single-field separated. Via 3 relationships they will be in separate fields.
1. Summary field between Invoices < Child called [Maximum Record ID#] where you are pulling the Maximum [Record ID#] value
2. Lookup field to Child table [Maximum Record ID#]
3. Summary field between Invoices < Child [2nd Maximum Record ID#] = Max Record ID# where [Record ID#] is NOT equal to [Maximum Record ID#]
4. Lookup field to Child table [2nd Maximum Record ID#]
5. Summary field between Invoices < Child [3rd Maximum Record ID#] = Max Record ID# where [Record ID#] is NOT equal to [Maximum Record ID#] and [Record ID#] is NOT equal to [2nd Max Record ID#]
6. Lookup field to Child table [3rd Maximum Record ID#]
7. Formula field (checkbox)
If([Record ID#] = [Maximum Record ID#] or [Record ID#] = [2nd Maximum Record ID#] or [Record ID#] = [3rd Maximum Record ID#], true, false)
8. Combined Text Summary Field: Combine text where [Formula-Checkbox Field] = true.
This way your combined Text will constantly change as your Child-Records update
A more convoluted way to do this would be via reverse relationships; which would also be helpful depending on how you want to "view" these comments; in a combined text field; they will all be in a single-field separated. Via 3 relationships they will be in separate fields.
This topic has been closed for replies.
If you embed a Report Link on a form, you can limit the Rows to display to whatever you like.
This doesn't help you when the link displays in reports, unfortunately.
This doesn't help you when the link displays in reports, unfortunately.
+12If you're referring to the new combined-text summary field; then you need to first identify what the 3 most recent child-table records are. You can do that with the following:
1. Summary field between Invoices < Child called [Maximum Record ID#] where you are pulling the Maximum [Record ID#] value
2. Lookup field to Child table [Maximum Record ID#]
3. Summary field between Invoices < Child [2nd Maximum Record ID#] = Max Record ID# where [Record ID#] is NOT equal to [Maximum Record ID#]
4. Lookup field to Child table [2nd Maximum Record ID#]
5. Summary field between Invoices < Child [3rd Maximum Record ID#] = Max Record ID# where [Record ID#] is NOT equal to [Maximum Record ID#] and [Record ID#] is NOT equal to [2nd Max Record ID#]
6. Lookup field to Child table [3rd Maximum Record ID#]
7. Formula field (checkbox)
If([Record ID#] = [Maximum Record ID#] or [Record ID#] = [2nd Maximum Record ID#] or [Record ID#] = [3rd Maximum Record ID#], true, false)
8. Combined Text Summary Field: Combine text where [Formula-Checkbox Field] = true.
This way your combined Text will constantly change as your Child-Records update
A more convoluted way to do this would be via reverse relationships; which would also be helpful depending on how you want to "view" these comments; in a combined text field; they will all be in a single-field separated. Via 3 relationships they will be in separate fields.
1. Summary field between Invoices < Child called [Maximum Record ID#] where you are pulling the Maximum [Record ID#] value
2. Lookup field to Child table [Maximum Record ID#]
3. Summary field between Invoices < Child [2nd Maximum Record ID#] = Max Record ID# where [Record ID#] is NOT equal to [Maximum Record ID#]
4. Lookup field to Child table [2nd Maximum Record ID#]
5. Summary field between Invoices < Child [3rd Maximum Record ID#] = Max Record ID# where [Record ID#] is NOT equal to [Maximum Record ID#] and [Record ID#] is NOT equal to [2nd Max Record ID#]
6. Lookup field to Child table [3rd Maximum Record ID#]
7. Formula field (checkbox)
If([Record ID#] = [Maximum Record ID#] or [Record ID#] = [2nd Maximum Record ID#] or [Record ID#] = [3rd Maximum Record ID#], true, false)
8. Combined Text Summary Field: Combine text where [Formula-Checkbox Field] = true.
This way your combined Text will constantly change as your Child-Records update
A more convoluted way to do this would be via reverse relationships; which would also be helpful depending on how you want to "view" these comments; in a combined text field; they will all be in a single-field separated. Via 3 relationships they will be in separate fields.
Yes, I am referring to the new combined text summary field. I am eager to try this out. Thanks for the step by step process!
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.