Forum Discussion

NOELHODGES's avatar
NOELHODGES
Qrew Cadet
2 months ago

Pipeline Help

We need help building a pipeline for our Overall Status fields that has multiple conditions. We want to grab Overall status based on the criteria given below.

QBID Overall Status Program Status
A. If all QBIDs are in Completed status Completed
B. If A is false, & if one or more QBIDs are in In Production status, follow these rules based on Overall Status
1. If any Overall Status = On Hold On Hold
2. If 1 is false, & if any Overall Status = Development Paused Development Paused
3. If 2 is false, & if any Overall Status = In Jeopardy In Jeopardy
4. If 3 is false, & if any Overall Status = At Risk At Risk
5. If 4 is false, & if all Overall Status = On Track or (blank) On Track
C. If B is false, & if one or more QBIDs are in On Hold status On Hold
D. If C is false, & if one or more QBIDs are in Backlog status Backlog
E. If D is false, & if one ore more QBIDs are in Solutioning status Solutioning


------------------------------
NOEL HODGES
------------------------------

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 months ago

    This does not sound like it needs a Pipeline solution, but rather a formula. 

    Can you help us understand the structure of your app and what QBIDs are?  You're referring to every single Quickbase record in the whole table, or is there a parent child relationship and you want to calculate the status of the Parent record based on the condition of the children?

    The latter, then you would create summary fields to summarize the record counts meeting those criteria up to the parent record and then you would have an IF(   )  formula which would stepwise check your conditions looking for the first one that is true. 



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • NOELHODGES's avatar
      NOELHODGES
      Qrew Cadet
      2 months ago

      Hi Mark,

      There is a parent child relationship and we want to calculate the status of the Parent record based on the condition of the children. The Parent Table is Programs and the Child Tables contain the multiple QB records. Essentially for each program (parent table) we want to get an overall status based off of the status of the child table. There are two status fields for the child table that have to be considered to make the Overall Status for the Programs table (parent table).



      ------------------------------
      NOEL HODGES
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew #1 Challenger
        2 months ago

        OK great. So this is just a formula on the Programs Table but before you make the formula,  just like baking a cake,  you need to get all your ingredients (fields) all ready on the Programs Table.

        So just make a series of summary Fields on that relationship.  If you're checking for any of the children to have some condition then there is a special checkbox summary field which is very efficient for that purpose.  

        If you need to check a condition like all of the children are certain status then you need to count the number of children in one summary field and have another summary field that just counts the children in that other status, and then if you compare those two summary fields and the count of all of the children equals the count of the children in a certain status then all of them are in that status.  

        When you make an IF statement in Quickbase, unlike Excel,  you do not need to nest your IFs,  so that makes it a whole lot easier to read. So you just start like this  

        IF(

        first condition test, "result 1",

        second condition test, "result 2",

        tenth condition test, "result 10",

          "result if not previous condition was true")

        Like most of us, Quickbase really doesn't want to do any more work than necessary so it starts at the first condition in your IF statement in the sequence you have listed them and checks if that's true. If it is true it gives the results and it doesn't do any more checking.   If it's not true then it metaphorically does a sigh, and it tests the next condition and so on until it gets to the very end.  

        I suggest that you build your formula very vertically so it's easy to read.  



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------