Automatically Populate Status - Sales Pipeline
Looking for a way to populate aStatuscolumn (up, down, unchanged) depending upon update to aStagefield.
In English: If the entry inStagechanges, record inStatuscolumn for that record the DIRECTION of change. Example: Account currently in Stage B. Rep updates to Stage A today after conversation.Statusshould reflect UP (using the direction symbols in Smartsheet) since it went from "B" to "A."
Anyone done anything like this?
Jason
Answers
-
Ryan Kramer ✭✭✭✭
So given your example, you would need to track history of cells. There is a way to do this using the APIs. It would require some custom coding but basically, pull the cells that changed today, get get their current value and previous value, update the respective cell and you could either add a formula in your grid to evaluate the two cells or have your program make the comparison and lastly insert it into the given status column.
rough formula for generating the status columns A, B and C. On update of A, set the value of B to what A was previously. C then checks if A=1 and B=2 then "UP", if A=2 and B=1 then "DOWN" etc.
Let me know if you need some help!
Ryan
-
Andrée Starå ✭✭✭✭✭✭
Hi Jason,
Yes, I've developed something similar in many client solutions.
We can do this withAutomations and a Workflowto copy the rows automatically to another sheet and have the information added/stored.
We'd then use a VLOOKUP or INDEX/MATCH formula to collect the information back to the sheet.
Make sense? Would that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by将它标记为接受答案/有帮助. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Ryan K ✭
I think his business case was to do it within the sheet. Not to move the records to another sheet.
To do it within the given sheet, I think you need the API integration.
Ryan
-
Andrée Starå ✭✭✭✭✭✭
Yes, I know. My example doesn't move them to another sheet. We'd copy them to another sheet to lock in the values.
Make sense?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Ryan K ✭
I see. So before an update, insert the current value to a reference sheet and then reference that in the "prior status" field. It would work initially I think but would start getting clunky when there becomes many changes for a given record and your process would have to go through all records through a lookup each time and then there are the considerations on size of sheet. Ultimately you would be duplicating the data that is available from the API for a given cell history.
Right?
Ryan
-
Andrée Starå ✭✭✭✭✭✭
No, I don't think it would get clunky(depending on the specifics of the process, of course), and the size of the sheet would probably not be an issue either because soon we will have 20 000 rows available.
You're correct that we will be duplicating the data that is available with the API. Still, I think that if it makes sense for the process, it would be preferred to keep in Smartsheet without any add-ons or similar, and also, I think the investment of time/money would be less.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Ryan K ✭
Perhaps. It just depends on the total records being tracked. I have a few clients that will exceed 20,000 easily for a process like this. For a small process it may make sense.
To utilize the API for this would only take a day or so of custom development whereas managing this process would be cumulatively more time over the life of the process I think.
Ryan