Discussions

Expand all | Collapse all

Dynamic Form Rule - Adding Record in Related Sub Table

  • 1.  Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-15-2019 18:58
    Hi:

    I have a master table for Work Orders.  When a work order is complete, and an invoice is generated, I want to create an entry in a related receivables table [Date Invoiced].

    What would be the best way to accomplish?

    Thank you,

    Dirk


  • 2.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-16-2019 13:33
    I would suggest using Automations:

    Online Hlep for Automations
    https://help.quickbase.com/user-assistance/about_quick_base_automations.html

    For the trigger, you would want "when data changes".

    You can specify the fields that changed, and the filter condition for the trigger to fire - such as:
    [Status] = "Complete] for the Work Order
    AND
    [# of Invoices] > 0

    Then the action would be something like:

    - Add a record in the Receivables table
    - with [Date Invoiced] = [Date of Last Invoice]  (or something similar)





  • 3.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-16-2019 15:33
    Xavier:

    Totally makes sense.   I am just having trouble with "Add a Record."  I am not seeing this option in drop down.  Any ideas?

    Thank you,

    Dirk


  • 4.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-16-2019 15:38
    Note that I was suggesting using Automations, NOT Dynamic Form Rules.

    You can access Automations by going to settings, and then Automations -> New Automation.

    Form rules cannot add a new record - so that would not be suitable for this.


  • 5.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-18-2019 17:29
    Xavier:

    Simple Automation seems easy enough.

    I am having trouble adding a record in a related table (I'm pretty certain).
    Passing record ID or something like that.

    Dirk


  • 6.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-18-2019 17:43
    Assuming that your relationship is as follows:

    Parent Table:  Work Orders
    Child Table:  Receivables

    1 Work Order has many Receivables

    Then when you have the Action in your Automation for "Add a Receivable Record", you would want the [Related Work Order] to be [Record ID#] from the Trigger Work Order.

    For example - if one of your trigger conditions is that in a Work Order, [Status] = "Complete", then now the fields in that Trigger Work Order are available for you to use in the Automation.

    So you would want the [Record ID#] for that Trigger Work Order to be the parent of your new Receivables record (by specifying that as the value for your [Related Work Order]).


  • 7.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-18-2019 17:49
    I'm close.... I'm actually not using Record ID#.  I am using [Work Order Number] and [Related Work or Number] in sub table.
    _


  • 8.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-18-2019 17:54
    When you view that relationship (between Work Order and Receivables), what is the "Reference Field"?

    That's what you should be specifying for the parent Work Order.

    By default - the Key field in Work Order (or any table) is [Record ID#], and the default Reference field would be something like [Related Work Order].  That's why normally you would fill in [Record ID#] into [Related Work Order] for the new Receivables record.

    If you're using other Key fields (or Reference fields) - you would adjust accordingly.


  • 9.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-18-2019 18:15
    Xavier.... im getting old.  More an issue of focus and attention to detail.

    Appreciate all your help.  Just got Automation to function as desired!

    Regards,

    Dirk
    _


  • 10.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-18-2019 18:19
    Great to hear! =)


  • 11.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-18-2019 19:26
    One last question:

    Can you sort of do the reverse?

    If the Related Procurement Table has a zero value for an item purchased, can you select [Do not invoice] checkbox on [Parent Work] Order Table?


  • 12.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-18-2019 19:47
    Yes - as long as there is a valid Triggering condition, and trigger record knows the reference field for the parent work Order.

    So for example - if you add / modify an Procurement Line Item record, with a [Value] = 0, then as long as you have the reference field back up to the Parent Work Order record, you can do the same thing.

    i.e. Have an automation check the [Do Not Invoice] checkbox on the parent Work Order.


  • 13.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-23-2019 20:54
    Hi Xavier

    Hmmmm took me a while, but I think I have it working.

    Seems like I need to do a screen refresh to see check mark.  Could one modify to auto refresh as part of Automation?

    Dirk


  • 14.  RE: Dynamic Form Rule - Adding Record in Related Sub Table

    Posted 07-23-2019 21:41
    At this time - "auto refresh" is not available as an action for Automations.

    That would be a great feature - though I can see how it might be tricky to implement. 

    For example - the Automation might be changing values in multiple records, in different tables.  How would it know which record you're viewing on your browser (vs. that of other users) to do the refresh, etc.