Forum Discussion

RoycePatton's avatar
RoycePatton
Qrew Trainee
2 years ago

Use Value in One Record to Determine if Additional Records Appear in Report

I have a table with records that only contain two fields "Name" and "Value". What I want to do is have a value in one record determine if additional records appear in a report. For example, if "Val...
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
2 years ago
I don't think there's a way to do that with a simple report.
But here's a thought that you could build on.
You could make a helper table with just one record in it. Enter that record and it will be record ID of one. Relate that table to every one of your detail records with a reference field that has a formula of one. Now this one magic record is related to every single one of your detail records.
If you know what your report filter is going to be you can make a summary field on that relationship Detect if you have any of the special kinds of records which trigger other records being shown. That information can then be passed down to all the detail record To be used in a report filter.
You would put an embedded report on that special single record


------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
  • RoycePatton's avatar
    RoycePatton
    Qrew Trainee
    2 years ago
    Thanks Mark, great answer, as always.

    I don't think my use case warrants putting too much effort into this. I have two reports of related records embedded in a form with custom formulas that calculate the dimensions for ordering an item, based upon the item type. The problem is that the item type isn't on the same record as the dimensions being calculated.

    Example:

    REPORT A

    Record 1
    Name = "Item Type"
    Value = "A"

    Record 2
    Name = "Height"
    Value = "20"
    custom formula subtracts 1 from value and displays result

    Record 3
    Name = "Width"
    Value = "40"
    custom formula subtracts 1 from value and displays result


    REPORT B

    Record 1
    Name = "Item Type"
    Value = "B"

    Record 2
    Name = "Height"
    Value = "20"
    custom formula adds 1 to value and displays result

    Record 3
    Name = "Width"
    Value = "40"
    custom formula adds 1 to value and displays result


    The idea was to show only the correct report with its corresponding calculation on the form, and to hide the other report, based upon what was entered in the Value field of Record 1. By 'hide' I mean not show those records, which would result in a costly mistake if those values were used by mistake.

    My solution was to settle for marking and color coding the reports based upon the value entered in Record 1 so that the user can clearly see which dimensions to use. The incorrect data still appears, but with a bright red "Do not Use" automatically placed at the top of the column. I was thinking a more elegant solution might exist to just not see the incorrect records, but what I have will work.









    ------------------------------
    Royce Patton
    ------------------------------