Discussions

 View Only
  • 1.  Aggregate data from multiple fields into one

    Posted 13 days ago

    Hi!

    I have a notes table setup where a user can add a note from different tables and relate it to that record, but since it's pulling from multiple different relationships

    (EX: QRA# is coming from the Units table, the Wells table, or the Calculations table, I want a field that will pull that QRA# into one field, instead of it being in one of the others.

    Does that make sense?



    ------------------------------
    Renee Hansen
    ------------------------------


  • 2.  RE: Aggregate data from multiple fields into one

    Posted 13 days ago
    you can make a formula field called QRA#

    if(
    [Units - QRA#] > 0,  [Units - QRA#],
    [Wells - QRA#] > 0,  [Wells - QRA#],
    [Calculations - QRA#] > 0,  [Calculations - QRA#])



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



  • 3.  RE: Aggregate data from multiple fields into one

    Posted 13 days ago
    Thanks Mark! Do I need to have a count so the formula works? and then would those be numeric formulas? and what if there is a txt field

    ------------------------------
    Renee Hansen
    ------------------------------



  • 4.  RE: Aggregate data from multiple fields into one

    Posted 13 days ago
    In answering this question I am assuming that you have three look up fields for the same field coming from three different possible parent records. So these fields,  I assume,  are going to have have similar but different names. If these fields were text fields are not numeric fields then the formula would be this.  

    if(
    [Units - QRA#] <> "",  [Units - QRA#],
    [Wells - QRA#] <> "",  [Wells - QRA#],
    [Calculations - QRA#] <>"",  [Calculations - QRA#])


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



  • 5.  RE: Aggregate data from multiple fields into one

    Posted 12 days ago

    Thanks Mark! I went with that assumption and it worked great, 

    Here's what I did for anyone wondering:

    This was helpful to add the "NONE" option because sometimes a Unit name is not available.

    If(
    [Distinct Count UNIT NAME - UNIT TABLE] > 0, [UNIT NAME - UNIT TABLE],
    [Distinct Count UNIT NAME - WELLS TABLE] > 0, [UNIT NAME - WELLS TABLE],
    [Distinct Count QRA - CALC TABLE] > 0, [UNIT NAME - CALC TABLE],
    [Distinct Count UNIT NAME - UNIT TABLE] < 1, "NONE",
    [Distinct Count UNIT NAME - WELLS TABLE] < 1, "NONE",
    [Distinct Count QRA - CALC TABLE] < 1, "NONE")



    ------------------------------
    Renee Hansen
    ------------------------------