Forum Discussion

DeepaPrashant1's avatar
DeepaPrashant1
Qrew Cadet
4 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

Sort By
  • JonFroderberg2's avatar
    JonFroderberg2
    Qrew Trainee
    6 days 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).

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    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
    ------------------------------
    • DeepaPrashant1's avatar
      DeepaPrashant1
      Qrew Cadet
      4 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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        I suggest that you give an example of the child data and the result that you expect.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------
    • DeepaPrashant1's avatar
      DeepaPrashant1
      Qrew Cadet
      4 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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        Are you looking to count the unique combinations of the SvcOrderLine# and the Reason not processed?

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------