Expand all | Collapse all

Trigger Action on Summarized Field

  • 1.  Trigger Action on Summarized Field

    Posted 04-15-2018 22:50
    Table A is the parent table and Table B is the child.

    Monthly, we go onto a website and download data that gets loaded into Table B

    It all gets complied into one spreadsheet.

    Table A's primary key is record ID, so I use a VLOOKUP formula in the complied spread sheet to get that record ID for the relationship. This based on a report I built that we just download monthly so only information that pertains to records in Table A is uploaded. 

    There are summary fields in Table A that are working perfectly summarizing the record of Table B appropriately. This all works smoothly, and is saving time.

    Now the issue is that I would like an action to be triggered in Table A whenever a specific summary field changes. Previously, we would read the downloaded data and create activities in the activity table and bill clients based on the summarized data. Currently, all that we need to do is compile all the data in one spreadsheet and run a macro I create then upload it. But we are missing the billing component. 

    From what I can see it is not possible to trigger an action because you cannot use summarized fields.

    Any thoughts or work arounds?

  • 2.  RE: Trigger Action on Summarized Field

    Posted 04-15-2018 23:02
    You have not really described what the ACTION does.  But the Parent record can know via a Summary field of the Maximum [Date Created] of the child records.

    The Excel upload process is manual anyways, so my suggestion is to run an ACTION on the Parent records subject to a filter that the children have been uploaded (re Maximum date above is say today or this month - whatever your billing cycle is).

    So, how to act on those parents.  One way is to have a dedicated table called like Trigger Billing with exactly 1 record in it.  It will be Record ID# = 1.   Set up a formula field on the Parent records equal to a 1, which are all now children to that single Trigger Billing record.

    Then you will do the upload manually and then when you are ready to trigger billing, you will toggle a checkbox on the Trigger Billing and that will kick off the ACTION.  You can make a URL formula button to flip the value of that checkbox.

  • 3.  RE: Trigger Action on Summarized Field

    Posted 04-15-2018 23:22
    The action will be adding a record for each in another table where billing is done and will contain the summarized information from Table A.  

    What I am trying currently, was I had a formula field I called Trigger, which was
    [Summarized Field] +1
    So when the specific field trigger was changed, and after it changed the result was greater then one. it would fire the action