Skip to main content

Is this base design possible? See image. I need to schedule a variety of sessions categories (29) across a variety of times. Each of the session categories has a varied quantity of allocations. In the photo attached, at the top you can see the session times and the rooms. In each cell is a pull-down list of the allocation type. At the bottom left in the image, you can see the allocation type and the quantity. The table at the bottom sums up the amount of time session allocation abbreviation is entered in the top table and then allows for a horizontal sum calculation.

The top table would be very easy to construct. What I’m wondering is if we can make the bottom table a visible entity on the same table? It will be important to see totals as the sessions are allocated.

 

Hmm, kind of, I’ve set it up here for you to check out and this is what it might look like:

 


And you’d have an interface set up like this:

The main issue with this set up is that it works great for keying in info, but isn’t great data wise; each allocation should really be its own record instead of its own column

With your business requirements I couldn’t think of a way to both structure your data properly as well as give you the ability to easily set up your time slots / rooms and see the totals, and hopefully someone else has a better idea!

Hi,
You can use Linking records way, or you can use Single select way.
Such type of data design is not usual for Airtable, so a bit workaround needed.
I don’t know which way is better, each has it’s contras, for me Single select is easier

linked - if you create 29 linked fields, you will have 29 in the second table. then you need to summarize them. (by creating count field for each, and SUM() them). Or make a bit more complex formula

select - it’s OK as soon as allocations list is static. if you add new option you should add it to each of 29 fields settings. luckily, you can do it by script.
you need just 1 linked field, but link ‘all_to_all’. Thus, you can get rollup containing a total string of all allocations in Table 1. Then a formula like  (LEN(Total_string)-LEN(SUBSTITUTE(Total_string, {Allocation}, ’’ ) ) / LEN ({Allocation})

You can start with smth like that, fill values in lines 1 & 2
This, for example, will create 12 single-select fields like in your table_1,  with empty options. 
Adjust first field - just copy-paste Allocations there to create options.
I’ll help to clone these options to other single selects

const table=base.getTable('YOUR_TABLE_1')
const names=E'Theater_5','CSS_7']
const unwrap=(name,x)=>s...Array(+x).keys()].map((_,ix)=>`${name} ${++ix}`)
for (let fld of names.flatMap(name_num=>unwrap(...name_num.split('_')))) 
  await table.createFieldAsync(fld,'singleSelect',{choices:s]})

 

Reply


Terms & Conditions