Hi!
I am new to airtable so I am looking for some help. I am trying to create a database/input form for data that will be loaded into powerBI.
The goal is to analyze revenue streams in PowerBI. Each revenue stream has a set of fields, among them are StreamID (Unique), report date, department, revenue type and $ sum. For each revenue stream I want to be able to allocate a percentage of the sum to the brokers in the company. The number of brokers per project varies. What I am wondering is how I can create multiple key value pairs for each revenue record in my database?
I currently have two ideas for how it could be solved:
1. Putting an allocations field in the revenuestreams table that contains multiple key-value pairs, i.e.:
| StreamID | (many fields) | Allocation |
| 1 | ... | {Peter: 20%, John: 80%} |
| 2 | ... | {Peter: 40%, Eva: 60%} |
I know this would be possible with a multiple select field where the user enters a delimited string, i.e. "peter-20", "john-80", however this is a very iffy database design and I would like to avoid this. Does anyone know if airtable has support for multiple selection fields where each selection is a key-value pair?
2. The other option is creating one RevenueStreams table and one allocations table that would look something like this:
| StreamID | (many fields) |
| 1 | ... |
| 2 | ... |
With a separate Allocations table looking something like this:
| StreamID | Broker | Allocation |
| 1 | Peter | 20% |
| 1 | John | 80% |
| 2 | Peter | 40% |
| 2 | Eva | 60% |
While the last solution works and is a better database design, I encounter a couple of issues>
Firstly, I want the users who enter the data to be able to enter it in a single form/sheet. I don't want the user to have to enter the revenue streams first in one table and then copy paste the stream id to register each allocation percentage.
Secondly the dataentry user should ideally be able to read out the allocations through the revenuestreams table directly. For instance if our boss wonders what the allocation is for revenuestream with streamID 1, he should be able to read this from the revenuestreams sheet without manually looking up the streamID in the allocations table.
Does anyone know a good solution to my task? I am open to other database designs as well if another solution would work better
