Is it possible for a WorkFlow to update a column during Various Users Tasks?

bdetcher
bdetcher Contributor
in Workflows

I'm developing a WorkFlow Process that has a few user-based tasks. The workflow is triggered by the addition of a Form Submission that will update a Dataset. As the Workflow progresses (users approve tasks), I would like to have a service automatically update the status in the original Form Dataset that will be used for Reporting purposes. I see there is an AppendtoDataSet function but I do not see a function to update a record already existing on the dataset.

Tagged:

Best Answer

  • DanHendriksen
    DanHendriksen Domo Employee
    edited October 2024 Answer ✓

    @bdetcher - totally get it. I build stuff like that all the time. The good news is it can be done without any writing any code at all, and without using any bricks.

    If you want to do what you originally outlined, then you'll want to:

    • Enable upsert on the form dataset/define upsert keys (I haven't tried to do this on a form based dataset but I'd imagine it's possible)
    • Use the "append row" function in the Domo Datasets Package, and it will update existing rows

    As a warning - I suspect you'll see your dataset alerts that trigger based on a row updating fail once the row count gets very high. I don't know where they were at on that, but somewhere around 300 or 500 rows, the dataset alert for API driven datasets will lose the option to be triggered upon a row being added.

    I'm happy to help you however you'd like - I was just sharing the approach that I thought would be the simplest, most robust and most efficient. Certainly lots of ways to achieve it though.

Answers

  • DanHendriksen
    DanHendriksen Domo Employee

    I would not have the form write to a dataset.

    I would use a customized workflow start form to have the user fill that out, and then use AppDB for the logging and updating.

  • DanHendriksen
    DanHendriksen Domo Employee

    If you DO want to update a record in a dataset you can, but you need to enable upsert and upsert on that dataset. It's much more nuanced though.

    Leveraging an AppDB collection will be more efficient, and give you a lot more control. Saving the responses being written to the dataset and having an alert trigger the workflow by just using the customized start form will be big efficiency gains too.

    I will be doing a webinar for the community in a couple of weeks where we go through and build out some of the concepts.

  • bdetcher
    bdetcher Contributor

    @DanHendriksen Thank you for the input. I am not too familiar with AppDB, so I am trying to develop a process that requires minimal coding and doesn't require the use of a Brick.

    The goal of the process is to be able to have a reporting dataset that users can see the individual status of their requests as it moves through various manual user tasks within the process. I was hoping the "Status" field from the Form input (generated either as a Form or at the start of the Workflow) could automatically be updated within the dataset as it moves through the process. I know the Queues option could work, but if there are multiple requests, it will be confusing for the user to know which request is which. Also, the department needs to be able to use some of the captured information to determine some basic reporting information.

    My original thought is that Inline Editor in combination with Workflows will work but I was hoping to automate the update part of the Workflow.

  • DanHendriksen
    DanHendriksen Domo Employee
    edited October 2024 Answer ✓

    @bdetcher - totally get it. I build stuff like that all the time. The good news is it can be done without any writing any code at all, and without using any bricks.

    If you want to do what you originally outlined, then you'll want to:

    • Enable upsert on the form dataset/define upsert keys (I haven't tried to do this on a form based dataset but I'd imagine it's possible)
    • Use the "append row" function in the Domo Datasets Package, and it will update existing rows

    As a warning - I suspect you'll see your dataset alerts that trigger based on a row updating fail once the row count gets very high. I don't know where they were at on that, but somewhere around 300 or 500 rows, the dataset alert for API driven datasets will lose the option to be triggered upon a row being added.

    I'm happy to help you however you'd like - I was just sharing the approach that I thought would be the simplest, most robust and most efficient. Certainly lots of ways to achieve it though.

  • bdetcher
    bdetcher Contributor

    Thanks @DanHendriksen

  • DanHendriksen
    DanHendriksen Domo Employee

    I should clarify the solution I proposed (moving to a workflow start form as opposed to a dataset based form and using AppDB to status updates, reporting, etc…) is the that one requires no code, no bricks, etc…

    Of course your solution doesn't require those things either, but the upsert part will bring a high level of nuance. You'll want to make sure in your alert you pass EVERY SINGLE VALUE on the row to the workflow, because it will need all of them to perform the upsert function.

  • JohnXClifton
    JohnXClifton Member

    Similarly to the original poster I'm building a workflow to process a credit application so as it goes through an approval I can keep track of the status of the application.
    I'm trying to understand how this "Use the "append row" function in the Domo Datasets Package, and it will update existing rows" can be used to update an existing row.
    How do you pass the identifying values of the row you are trying to update in this function?

    An update sql command will update all the table entries of any row that matches the 'where' condition, but the Query SQL function in workflow doesn't seem to allow anything other than a select statement from working. I tried using as an example UPDATE TABLE SET Status = 'Received', Outcome = 'undergoing Assessment' WHERE Application ID = '1D' but got an error message with no details on what the error is.

  • bdetcher
    bdetcher Contributor

    @JohnXClifton - I was able to get a working solution running utilizing a Webform and then a Dataflow that output an Upsert dataset.

    I setup a Domo Webform with all of the fields that I wanted within the dataset making sure each field had the proper schema and the exact name as the variable in the Workflow. From there I ran a DataFlow to create an "Upsert" datastet. The key is to make sure that each row (workflow process) has a unique identifier.

    Then when I use the "Append to Dataset" function in a Workflow, I point the service at this Upsert dataset. The Upsert Dataset then updates the specific row. I was also able to use "Text Substitution" service to automatically update my status fields as the users proceed through the Workflow.

    I believe Dan demonstrated an alternative solution recently on an ACE webinar where he utilized an AppDb collection to do this type of data capture and update. That might be worth checking out.

  • JohnXClifton
    JohnXClifton Member

    Thanks for this and I've tested doing what you've suggested but it's all overly complex for what should be a straight forward process.
    When I append the original entry into my Credit Application dataset I create a unique identifier as part of that process, then I just want to update that row with the outcome of the approval step.
    I've got about 50 business processes to create as workflows with approval functions so I can't be creating a complementary dataflow to go with each new workflow, when a simple update row function in the workflow should be available to do this.

  • DanHendriksen
    DanHendriksen Domo Employee
    https://community-forums.domo.com/main/discussion/comment/100582#Comment_100582

    I know the product team is working hard on some of these data update features…maybe we will see something at Domopalooza?

    I do lots of these things with AppDB. The way that update a row works, is by defining an UPSERT key (which is a unique identifier for each row, and typically a combination of columns) and then sending a "new row" to the dataset. If that UPSERT key exists, then it replaces that row. If it doesn't, it adds it.

    Hopefully that makes sense…happy to go in to more detail. If you want to shoot me a PM, I'd be happy to schedule some time with you to go over the options in more detail and help get you over the hurdle you're facing.

  • DanHendriksen
    DanHendriksen Domo Employee
    edited February 11
    https://community-forums.domo.com/main/discussion/comment/100582#Comment_100582

    The approach that I personally would likely take, is writing this new data (application decision) to AppDB and joining it back to your source data in a VIEW (no processing time and no credits used in the join), and then materialize it back to the source data on infrequent schedule.

  • JohnXClifton
    JohnXClifton Member

    With help of our Domo Solution Consultant this issue has been resolved.
    The dataset needs to updated for upsert via a CLI script:

    1. connect -s <domain> -t <access_token>
    2. define-upsert -i <dataset_id> -c <field_name>

    In this case the field "Application ID" which I generate as unique ID in the workflow is the key field.
    When the workflow runs now the latest record appended will update an existing row with the matching Application ID, otherwise it will append the record as a new row.

    Be warned that if you then go into the dataset and use the edit webform say to remove redundant rows from your testing then the upsert will be cancelled and you will need to run the script again, ask me how I found that out…

  • DanHendriksen
    DanHendriksen Domo Employee
    https://community-forums.domo.com/main/discussion/comment/100720#Comment_100720

    Yeah - upsert is awesome, but comes with it's own set of challenges/caveats. AppDB provides more flexibility for edits, but if you haven't used it before can be daunting/intimidating.

    I'm glad you got it up and running the way you want.

Categories