User wants to Upsert Data/Rows based on Actions/Analytics Apps

[stevelogue1980]

Hi Alteryxians,

There is a Simple Sales Report and user wants to Enter Forecast Sales for some products otherwise add 10% by default.

Coming to Data, there are 2 Data sources (Product ID is joining key)

           1)  Products, Sales (In real, Database)

           2) Products, Forecast Sales (In real writes to a Database file)

Appreciated with some sample Alteryx workflow as a start up (taking 1 or 2 Insert as example)

or, any closer approaches

Please find the excel inputs & Alteryx workflow attached 

[danilang · Accepted solution]

Hi @stevelogue1980 

From looking at your workflow, it looks like you're trying to give the user the ability to modify the values or insert new rows in the input excel file through the analytic app UI as required.  Unfortunately, this just isn't possible.  The Alteryx UI tools are there to provide simple inputs to generally control the flow of data through and not manipulate the input data itself aside from very specific cases.

As an example of a workflow control, you can connect a text box/dropdown to a filter tool and change the filter criteria. 

About the only way that you can modify input data is to connect a UI tool to a Text Input and have the action tool modify a specific cell, like you do in your workflow.  This action tool can only modify data that is already in the Text Input Tool and you need one interface tool for each cell that you want to modify.  Since the UI can't be modified inside the workflow, there is no way to have a UI that can modify any cell in a text input box if the number of rows is variable.     

You could put a Text Input with a 3 by 5 grid of null cells and have 15 UI tools to modify the various cells.  Put a filter after the Text Input and select only the values that are not null.  That would allow the user to enter up to 5 new rows of information but no more.  The UI for this would be ugly since the there is no way to align UI controls horizontally.  You could group each row's controls but they will show up vertically.

Luckily, there exists an external tool that contains all the editing, insertion and deletion features that you need and is already available to you at no cost.  That magical tool is...Excel!  Have the user perform all the required data manipulations in the source excel file and then input the file as-is to your workflow.  This isn't the optimal solution, but until Alteryx provides some form of HTML grid tool, which would allow the user to change any value in a row of data and pass all the values to the workflow, Excel will need to be your input editor of necessity.          

Dan