Forum Discussion

DeepaPrashant1's avatar
DeepaPrashant1
Qrew Assistant Captain
5 years ago

Summarize distinct child records by a text field

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
------------------------------
Relationships

7 Replies

    • DeepaPrashant1's avatar
      DeepaPrashant1
      Qrew Assistant Captain
      5 years ago
      Yes, but the reasons are recorded on the child record in text format in a field 'Reason not processed'. Each child record has a field called SvcOrderLine# which if unique will determine which child records to count. But in the child table itself, the line record is not marked as unique. While I can do a distinct count of unique child records, how do I do a distinct count of each 'Reason not processed'?

      ------------------------------
      Deepa Deepa
      ------------------------------
    • DeepaPrashant1's avatar
      DeepaPrashant1
      Qrew Assistant Captain
      5 years ago
      To clarify by counting the reasons, I assume i will know by reason description the count.
      1. PWO only - count
      2. Locked by user - count etc.

      ------------------------------
      Deepa Deepa
      ------------------------------
  • JonFroderberg2's avatar
    JonFroderberg2
    Qrew Trainee
    2 years ago

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