Forum Discussion

MikeTamoush's avatar
MikeTamoush
Qrew Commander
2 months ago

How to construct this table?

I have a table of Projects. 

Each Project has the following fields (for this example, they can be checkbox fields)

Requested by/Submitted to Customer
In Preparation
Submitted to HNC
Approved by HNC
Executed
Disapproved/Cancelled
In Progress

*Multiple checkboxes may be true for any given record. 

Additionally there is a multiple Choice that labels each record as: RSO, ESO, Urgent, or Long Lead (only one selection possible), and a Total Cost field.

The goal is to make a table or summary report that looks like below:

Because a single record may fit into multiple categories, it is stumping me. My only current idea is to use a helper table that has 7 records (corresponding to each SO Category). Then use formula queries to get the all the numbers, so in total 5 formula queries (RSO, ESO, Urgent, Long Lead, and Total Cost). I dont anticiapte more than say, 1000 projects so I don't think the formula queries will be too memory intensive, but I also try to stay away from them as much as possible since they can crash apps.

Alternately, perhaps I can make 7 relationships from the helper table to the Projects table, where my key field in the helper table is the SO Category (and instead of checkboxes I use formula fields that simply state the text of the Category if it applies), then use summary fields. But 7 relationships seems a bit excessive.

Any better ideas?



------------------------------
Mike Tamoush
------------------------------

11 Replies

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

    np, no FQs required.

    Just make a set of helper fields.

    [RSO Count] ( you can rename that later when its working to just RSO or whatever touy want your column to read)

    The formula is IF([multiple Choice]="RSO",1) 

    Then 

    [ESO Count] ( you can rename that later when its working)

    The formula is IF([multiple Choice]="ESO",1)

    etc for all your columns on the proposed report. 

    Then make a summary report and under Summarize What - add in those 4 helper count fields, then plus the numeric cost summary field.

    The reason that the formula is not

    The formula is IF([multiple Choice]="ESO",1, 0)  is that you want the user to be able to drill into the supporting detail and not get drill down hits on the zeros.



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

      Mark,

      Won't that only work if each Project Record can fit neatly into a single category? If I simplify to the chart below (where number of SOs is simply ESO+RSO+.....), I will have the need for the same record to be counted in the Submitted row, AND the Approved row AND the Executed Row. At a certain point, projects actually fit into multiple categories simultaneously. 

      Where I am lost is - the original Summary Sort needs to be by SO Category right? But if I do that sort, won't it only be able to put the record into one of the categories?



      ------------------------------
      Mike Tamoush
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew #1 Challenger
        2 months ago

        :)  

        You missing the fact that by creating say four helper fields you can summarize four fields (as well as that numeric field) in the summarize what section. 

        The Summary report DOES NOT have column grouping, just row grouping.

        Make the first two of those fields I suggested (or all 4 while you are at it) and you will see. You know tht in the section for 

        Summarize Data

        you can add as many fields as you like to summarize and they become more columns.  Just hover beside the first field you selected and you will get the opportunity to add I think an infinite number of more fields to summarize.



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