Skip to main content
Question

Summarize distinct child records by a text field

  • August 31, 2020
  • 0 replies
  • 173 views
Forum|alt.badge.img+8
I have a parent table (Botruns) that has many children (Line items). The line items could either be processed or not processed and the data for that resides in the line item record in the child table. These line items have an identifier SvcOrderLine# which need not be unique.

I know how to summarize the distinct SvcOrderLines in the parent table. But how do I summarize the reasons the distinct lines were not processed when those reasons are captured on the line item record? I was able to create a 'combined text' summary field in the Parent table for 'Reason not processed' but how do I count them now?

- Deepa

------------------------------
Deepa Deepa
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
Are you simply trying to count the number of unique reasons for not being processed. Is that the goal?

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Forum|alt.badge.img

Deepa, try this out.
Create a formula numeric field for say [PWO Only Count] and set it equal to this formula:

If(IsNull(ToNumber(ToText([Combined Text Field]))),0,
(Length(ToText([Combined Text Field]))-Length(SearchAndReplace(ToText([Combined Text Field]),"PWO Only","")))/8)

The idea is that the formula measures the total length of the string, then subtracts the total length of the string minus any entries of "PWO Only", then divide that number by the length of the text string that was subbed out (in this case eight characters). This should yield the total number of times that particular substring appeared in the combined text field. If the string being searched for did not appear in the overall string, no characters would be substituted, the total lengths would be the same and the result would be 0/8 which is zero.

You could make dedicated formula fields to search for and count each specific string you are looking for, or you could build it out to be a nested if formula.

I got this idea from a post by Pushpakumar Gnanadurai (PushpakumarGna1).

Terms & ConditionsAccessibility statement