Forum Discussion

bharathreddy's avatar
bharathreddy
Qrew Trainee
16 hours ago

Capture Previous values using Formula URL button & Pipeline

I am looking for a solution that can assign value on click of a button and then saves the record, so that the pipeline can capture the previous values a record.    

I have different formula URL buttons that updates stage (submitted, rejected, approved) and this change is captured in Audit table through pipeline.

URLRoot() & "db/" & Dbid() & "?a=API_EditRecord" & "&apptoken=XYZ" 
& "&rid=" & [Record ID#] 
& "&_fid_49=Approved" //Stage = Approved
& "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Record ID#] & "&dfid=2&z=" & Rurl())

I also need to capture the current and previous values of fields that were modified when Approved. The above formula did not work because QB saves the record first, then applies the field changes. This means that by the time a pipeline is triggered, the "previous values" are already overwritten.

As an alternative, I excluded fid_49 assignment in the formula, which worked as standard save and updated pipeline to trigger when any of field in the form is modified. This carries the previous values to capture the audit. The problem with this approach is that the pipeline runs every time a record with fields defined in the pipeline is modified and saved either through grid edit or using standard save button.

Please let me know if there is any other solution that I can try.

Pipelines

3 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    16 hours ago

    This should be no problem for the pipeline.

    Trigger the pipeline based on the status change and the have a create record step to populate the old stage and the new stage.

    Initially drag the stage field into both fields on the audit record (old and new values) .

    Then edit the box with the </> icon to see the code and insert $prev into the code.

    {{a.$prev.Stage}}

    • Bharath's avatar
      Bharath
      Qrew Member
      15 hours ago

      Hi Mark

      I can copy the stage values but not the fields on the form. When the button is clicked it updates the record with new stage. But the metadata of all other fields that were modified before this stage update (through button) is lost.

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        15 hours ago

        well, you will need to trigger on any field that could be edited which you need tracked.  You will have a design issue as to if the audit log record will have the field name and the old value and the new value, or if it will be a really "wide" record with say 10 field names and 10 old values and 10 new values.  If you go the former route, you will need to have either multiple pipelines (yuk) or else a pipeline that will have a step per tracked field.  That will result in many records being created in the audit log when no change happened to have happened on that field, but you can filter those off reports where old and new values are the same, andhave a process to auto delete them each night or even as the last step of the pipeline ... ie search for records to be deleted where old and new are the same. 

        I prefer the narrow audit table with a record per field changed