Discussions

 View Only
  • 1.  Use Value in One Record to Determine if Additional Records Appear in Report

    Posted 08-10-2022 18:45
    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 "Value" in Record 1 = "Show Additional Records", Records 2 and 3 would appear in the report along with Record 1, otherwise, they would not.

    I can't figure out how to control what shows in a report based upon a field in one of the individual records. Is this even possible?

    Any help would be appreciated.

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


  • 2.  RE: Use Value in One Record to Determine if Additional Records Appear in Report

    Posted 08-10-2022 19:41
    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
    ------------------------------



  • 3.  RE: Use Value in One Record to Determine if Additional Records Appear in Report

    Posted 08-10-2022 20:47
    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
    ------------------------------