Discussions

Expand all | Collapse all

Capturing A Field Value To Set Up Boolean For Custom Data Rule

  • 1.  Capturing A Field Value To Set Up Boolean For Custom Data Rule

    Posted 12 days ago
    I have records on a parent table with a field [Job].  The value in this field is passed down to the child records.  I currently have form rules in place that prevent users from changing the value in [Job] on the parent if there are child records present/created.  However, the users are still able to edit the value of [Job] on the parent record via Grid Edit. I know that I can simply take away the ability to Grid Edit the records on the parent table, but that is not advantageous for some of our work flows.

    In order to create a Custom Data Rule, I need to be able to construct a boolean argument to evaluate the rule.

    I attempted to create a summary field from the child table with the 'average' of the [Job] from the child records, but QB will not allow this. The thought was that the [Job] on the child records would not change until the parent record is saved, but this is likely considered a circular reference.  The idea here was to create a formula checkbox field to compare the lookup field to the [Job] on the parent record.

    My next thought was to create a snapshot field and capture [Job] on the parent at the time the record is first saved, but this only works with lookup fields, and, the child records are created later so it would not make sense to create a snapshot on a parent of a lookup value that does not yet exist.

    Maybe create a snapshot of [Job] on the child records, then pass the average of that value up to the parent to create the CDR?  Other ideas?

    (Whisper a prayer to the Holy User shrine for me.)

    ------------------------------
    Jon Froderberg
    PRIME Developer
    Harder Mechanical Contractors
    https://www.harder.com
    Portland, OR
    801.946.0576
    ------------------------------


  • 2.  RE: Capturing A Field Value To Set Up Boolean For Custom Data Rule

    Posted 12 days ago
    How about a low tech solution.  Make a formula field called [Job.] (with a period).

    Make the formula just a mirror equal to the [Job] fields.   Put that on the embedded report.  In Grid Edit, formula fields are not editable.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Capturing A Field Value To Set Up Boolean For Custom Data Rule

    Posted 12 days ago
    I like this approach! Thanks Mark!

    My colleague pointed out that I was attempting to do a summary (average) on a text field (which is the incorrect data type) which in turn was causing the QB error message, not that it was a circular reference.  I could go to the effort of creating an additional field which would translate the Job on the parent record into a numerical value and then set up all the related fields to evaluate an average from the children, but that's a lot of machinery to create when Mark's idea is much simpler.

    ------------------------------
    Jon Froderberg
    PRIME Developer
    Harder Mechanical Contractors
    https://www.harder.com
    Portland, OR
    801.946.0576
    ------------------------------