Forum Discussion

JonathanSpencer's avatar
JonathanSpencer
Qrew Trainee
6 years ago

Trigger validation rule in parent record

I have an issue with validation rules that I would appreciate some help with.

I have two tables for timesheets ? one for header (date, employee, shift) and another for line item details ? job, hours worked, downtime.  The relationship is one header to many line items.

I want to enforce a rule so that there must be a minimum of 9 hours (hours worked + downtime) for each header. 


The problem I have is that the total is held in the header record, and it?s the child records where the user makes changes that affect the total, so the validation rule isn?t triggered by saving the child record(s) .  If I click save on the header record it?s triggered ? but of course the user has no actual need to trigger save on that record because the total hours figure gets updated automatically via the relationship.  

Any ideas on how I can get around this much appreciated....
Forms

5 Replies

Sort By
  • EvanMartinez's avatar
    EvanMartinez
    Qrew Elite
    6 years ago
    Hi Jonathan,

    When working with the validation rules and running into an issue like that one suggestion I make is once you have the summary field up to your Header table you can then create it as a lookup field down to your Line Item Details table, this way as you add more records to the Line Items table it will lookup the value of your summary from Header. That way you can write your validation rule on the Line Items table to use your lookup field to prevent a save. 

    If([Lookup field of Summary]<9, "Must have more then 9 hours of hours worked and downtime to save please review and change", "")

    I believe that should allow you to create the validation you need. I hope that is helpful. 
  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    6 years ago
    You have a sum of hours worked on the header record, can you just look that up down to the child record and base the validation rule on that?
  • JonathanSpencer's avatar
    JonathanSpencer
    Qrew Trainee
    6 years ago
    Hi Alex
    I tried that, but the value doesn't get passed back to the child record until after the child is saved - by which time it's too late. 
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      6 years ago
      Ah. Okay.
      Well, if that workflow's not malleable, you can engineer solutions from that point.
      Instead of 'SAVE' on the child record, display 'CHECK HOURS'. Have 'CHECK HOURS' pass the hours value up and back down the chain, as well as invisibly dirty the record, and don't allow saving an un-dirtied record.
      It's not a GOOD solution but it seems that without changes to workflow, there may not be good ones left. As Mark suggests below, social engineering is also always an option in circumstances like these.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    I don�t think there will be any way to block the city. But you can certainly have a warning message on the record itself and instant email firing to the person who last modified record and also a daily subscription to haunt that same person to fix the record.