Fill in a field from an entered value based on a multiple select field

[Jim_Graziano]

I track project sales leads. Every lead has the potential to turn into a project, so when I enter the lead, I also enter an estimated value. If it does turn into a lead I want the estimated value to go into the correct field depending on the status of the lead/project (Sales Status Multiple Select).
Field: Estimated Estimated value goes into this field when the Sales Status is ‘Lead’, ‘Warm’ or ‘Scheduling’
Field: Booked/WIP Estimated value goes into this field when the Sales Status is ‘Booked’ or ‘WIP’
Field: Invoiced Estimated value goes into this field when the Sales Status is ‘Invoiced’.

Of course, when a value goes into the correct field, the field it came from gets zeroed out. Right now I have to manually move that amount between fields depending on the status of the project. Sometimes I forget, or I put a value in one and forget to delete it from the other, which artificially inflates the project value.
Any ideas?
TIA!

[Vivid-Squid]

This can be done with automations but there are a lot of workflows to think through. If the initial value is removed based on status, the next step in the flow will need to know where the value is now located. Do you see status always following the same flow? Will they ever go Warm to Booked and back to Warm? I assume you will want the value to always follow the status. Will the value ever change after the status is set?

[Jim_Graziano]

Actually, what I am thinking is that it is not really an initial value, it is a value that is input when the lead is entered, or really any time, and it gets copied to the appropriate field if/when it changes or status changes.
If ‘status’ or ‘value’ change, zero all status value fields
If ‘status’ = ‘warm’ or ‘status’ = ‘scheduling’ then copy ‘value’ into ‘estimated lead value’ field
if ‘status’ = ‘booked’ or ‘status’ = ‘wip’ then copy ‘value’ into ‘booked/wip’ field
if ‘status’ = ‘invoiced’ then copy ‘value’ into ‘invoiced’ field
Does that make sense? It would have to work on every record.
I think having 1 ‘value’ field that gets copied as opposed to moving around makes more sense than the way I originally stated it. I’ve never programmed in Airtable so I don’t know where to start!
Thanks!

[Vivid-Squid]

This is all possible and you don’t even need to write any code. You can do this with Automations.

Am I right to assume the reason you want the value moved to various fields is to have a running total for each phase of the lead life cycle?

If that is the case you could do this more simply by just having the one value field and leaving it. Then create views or groupings based on the various status options.

Then each view will show you the running total only for the leads in that view.

[Jim_Graziano]

Yes, correct about the running total for each phase.

Hmmm, interesting, I need to think about that.

Thanks!

[Vivid-Squid]

Here is an example of a View for Warm and Scheduling

Here is an example of all records but grouped by Status

[Jim_Graziano]

This makes a ton of sense, it took me about 15 minutes to reconfigure my current Receivable Summary to this format. I was sorting by Status but not Grouping. I use Grouping for other (non-numeric) things, I actually didn’t realize it would provide sub-totals. And my brain was stuck in spreadsheet mode (with separate columns for the Status amounts) even though I love database and have been working with them for years. Disadvantages: It’s obviously not quite as compact as my old layout and I can’t use the Summary App to break down values for each status since it reads one column. But of course the breakdown is in the Group subtotals. Now I have it just displaying Total Value. I wish there was a way to assign those column summaries to variables.
Thanks!

[Vivid-Squid]

If you create a view for each Status item, you should be able to use the Summary App to show the total for each status/view

[Jim_Graziano]

Yup, that’s exactly what I did.
Thanks!