#ERROR when dividing a linked record that is currency + setting up reference table

[IceColdDrink]

Hi Folks,

I am essentially trying to automate quota calculations for the sales team in Airtable. As I explore setting up the tables, I'm running into constant brainfogs.

1. Im dividing the "record" (quarterly target) by "3" (to break this into a monthly target) but i'm receiving a #ERROR. Can anyone help me fix this?

2. The way I set up these records is to have the employee ID + Month when the plan start date is; Therefore, each employee has 12 records. How can I build a reference table so I can link the records and specifically link the values into the current table? I currently calculate the quotas in Gsheet to then copy and paste in the current process but I'd like to automate this. Additionally, if I need to link the record to each of the 12, how can I automate that? Seems like automation setup could address this issue.

• I thought about setting up the reference table to still have the following (see below) but if the records are currently by column, how do I link it in a way where the value I'd like to have by row (i.e. the 12 months of record per employee) is captured in the column (targets by month)? Perhaps im thinking too much of a spreadsheet when I should imagine it as a database so I appreciate some advice to shift my thinking

[Sho]

Hi @IceColdDrink,

1.　This is an error because it is trying to divide the strings by 3.
it needs to be converted to numeric values with the Value() function and then divided by 3.

VALUE({Amount})/3

[IceColdDrink]

Thank you Sho! That fixed my issue and I appreciate the context behind the why.

[TheTimeSavingCo]

Hmm for your second question could you provide some screenshots of the main table, the reference table, the relevant fields, and ideally how you'd want the data to look like after the automation has ran?  That information would make it a lot easier to help!

[IceColdDrink]

Hi Adam, 

I appreciate the help. I'm still trying to conceive the design of this table (it's quite difficult). Let me breakdown some screenshots although I will likely still be vague due to being cautious of disclosing info. The main sheet I'd like is essentially populating the rows with the Revenue quota. You can see that the primary key is broken out by the emp ID and the month they would be assigned the quota

Im thinking to somehow link the primary key over but my main sheet where I would run calcs are essentially flipped / transposed.. Im wondering if I can do something with that. At the top, I would source over data likely at the quarterly level or sometimes monthly depending on the team that provides me the info. If not, its straightforward enough to break into monthly; 

The challenge im having now is think about sourcing the data across the columns in this second screenshot over to the primary key that can get pulled nito one column "XSell Quota" vs. under the respective month (M1, M2, M4, etc). 
The complexity is that in the table within the second screenshot, I have it broken down by territories (which I think I would somehow convert / assign in a table that is similar to the 1st primary key and this might just have to serve as a 3rd table in the process) that ultimately get assigned to a Person (lets call it P1) that reports into a leader (L1) and ultimately into a senior leader (SL1). Note that Several "Ps" could roll up into L1 etc. Sorry if its still ambigious but I am just trying to be cautious without disclosing confidential or personal data. 

[TheTimeSavingCo]

Hmm, apologies, I'm not really following.  If you could create a simplified base with some example data of what you're trying to do that would be really helpful; without knowing how your base is set up or what you'd like the outcome to be it's very difficult to help

I have no idea whether this is helpful, but I sometimes end up creating a summary table where each record represents a single person and time period, and then I create rollups based on that.  For example, I might have a table full of sales data where each record represents a sale and it contains the date of the sale and the sales agent. 

I'll then create another table where each record represents a single sales agent and a single date period, so I'd end up with "Bob - Jan 2024", "Bob - Feb 2024", "Alice - Jan 2024" etc

I would then use automations to link the sales data to the appropriate summary record as needed, and use rollups in the summary table to sum up the total amount of sales made per agent per month

Sorry, I'm not even sure if what I've just mentioned is relevant, but I kind of think that's what you're trying to do?  Not sure!