Forum Discussion

StevenPearson's avatar
StevenPearson
Qrew Assistant Captain
6 years ago

Report fields as table fields

I have a date field called [Field Work Complete Date].
I have a multiple choice field called [Status].
I can build a report that shows me how many field work dates are not empty. I can also build a report that shows me how many tasks have the status Ready for QC. These are in the Tasks table. I also have a Projects table with a relationship to the tasks table. How can I make a field that shows me the numbers in the reports? And more specifically, how can I show total tasks minus these totals? So it would be Total Tasks - Field Work Complete Yes = or Total Tasks - Tasks with Status of Ready for QC=. I've tried most of the formula functions shown in the help section but nothing has worked.
Formulas and functions
Tables and fields
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    6 years ago
    In your Projects < Tasks relationship:

    1.  Build a Summary Count field called [Total # of Tasks] to count the # of Tasks against the Project.

    2.  Build a Summary Count field called [# of Completed Tasks] to count the number of Tasks where the [Field Work Date] is not equal to empty (optional)

    3.  Build a Summary Count field called [# of Ready for QC Tasks] to count the number of Tasks where the [Status] of the Task is equal to "Ready for QC" (optional)

    4.  Build a Summary Count field called [# of Complete or Ready for QC Tasks] to count the # of Tasks where the [Field Work Complete] is not equal to empty OR the [Status] = "Ready for QC"
    (this would be an "any" condition in the summary field criteria)

    In the Projects table, build a formula numeric field called [# of Completed/Ready for QC Tasks]:

    with the formula:
    [Total # of Tasks] - [Total # of Completed or Ready for QC Tasks]
  • XavierFan's avatar
    XavierFan
    Qrew Cadet
    6 years ago
    You can use Summary fields for this - to get the Task #s into the Project table, and then create a formula field in the Projects table.

    1)  Create the summary fields
    - go to the relationship between Projects and Tasks
    - on the left hand side, create the following Summary fields

    a)  [Total Tasks] - "count" of Tasks for that Project
    b)  [Tasks - Field Work Complete Yes] - "count" of Tasks where Field Work Date is not empty
    c)  [Tasks with Status of Ready for QC] - "count" of Tasks where Status = Ready for QC

    2)  Create the formula fields in the Projects table
    - Now you have the components for your formulas
    - in the Projects table, create the following Formula Numeric fields

    a) [Tasks - Field Work Complete No] - formula:  [Total Tasks] - [Tasks - Field Work Complete Yes] 
    b) [Tasks - Status Not Ready for QC] - formula:  [Total Tasks] - [Tasks with Status of Ready for QC]

    ====

    This gives your summary numbers for the various Tasks numbers for that Project.

    If you want to see "global" numbers, for "all" Total Tasks vs. "all" "Tasks - Field Work Complete", you can create a "Global" table and relate all Tasks to that Global record.

    Or if the Projects table have a parent table - you can repeat this process to create summary numbers up to the parent table, and do your formula there.

    ====

    Online help for Summary Fields
    https://help.quickbase.com/user-assistance/create_summary_field.html