Seeking solution for sub-tasks. Junction table? Linking columns?

[Andrew_Carville]

Hi all,

Hoping to get some ideas on the best way to structure my base to achieve the concept below.

I work with property development projects, and use one row for each project (as in the first pic below). Some projects will have more than one dwelling built on it. Each dwelling on a site might have different characteristics (ie. number of bedrooms). Therefore also being able to separate each project into individual dwellings would also be helpful.

I’ve set up this second pic manually to demonstrate the sort of thing I’m after, but I’m looking for a way of achieving this automatically.

Is there a way to achieve this? I don’t mind if it’s all within one table, or kept in a second table. Any solutions are appreciated. Many thanks in advance!

Regards,
AC

[TheTimeSavingCo · Accepted solution]

Hi Andrew, there’s a bunch of different ways we can handle this, two of which I’ve showcased below

You can have a look at this base

In the gif below, I’m pasting the values of the Text to Paste field into the linked field.

It assumes that each Name in the Projects table is unique, and works by pasting a comma separated list of unique values into a linked field, forcing record creation. We can make an automation paste the values for us instead as well.

---

If you want, we could use a button with a scripting app to handle it. (Downside: The scripting app would have to be maintained, and also when you click the button the scripting app pops up on the side)

I’ve put this one together for you here

You can set this up in your own base by modifying the table / field names in the “Setup” section of the script.

---

Let me know if you’ve got any questions about the other two options or if you have any issues setting up the thing I made in your own base; happy to help!

[Andrew_Carville]

Hi Adam,

Thanks for the fast reply. That first option is brilliant - exactly what I’m looking for. Genius!

Yes, each of the projects will have a unique address. A few other queries:

• If I add a new project, and dwelling IDs (ie. A, B,…), will it automatically create the individual items in the second tab?

• If I have a drop down option against a project in the first table (ie. Status = “In Construction”), will all of the individual dwellings in the second table be assigned “In Construction” when created? I’m assuming I link the column to the second table?

Thanks again!
AC

[TheTimeSavingCo]

Glad I could help!

Andrew_Carville:

If I add a new project, and dwelling IDs (ie. A, B,…), will it automatically create the individual items in the second tab?

Not right now, no. We can use automations to handle this for us, however, the implementation of the automation will vary depending on two factors:

1. Whether you’re on a Pro plan
2. Whether you’re inputting the Project data via a Form

---

If you’re on a Pro plan

We’ll set up an automation that’ll run when the “Dwelling ID” multiple select field is updated, and will paste the value from Text to Paste into the linked field. You have 50 thousand automation runs on the Pro plan, and so we can afford to do this. (I’ve updated the original base with this automation and you should be able to view it there)

If you’re using a Form to input data

We’ll set up an automation that runs on the submission of a form, and does the same action

If you’re not using a Form and you’re not on a Pro plan

With only 100 automation runs on the Free plan, we have to be a bit more circumspect with when we run it

I would recommend we use a checkbox field, with the workflow being the following:

1. You input the project name
2. You select the Dwelling IDs
3. Once you’re done, have a look at the Text to Paste field to see if it looks right and then you mark the checkbox
4. Once the checkbox is marked, the automation will run and paste the text into the linked field

---

Andrew_Carville:

If I have a drop down option against a project in the first table (ie. Status = “In Construction”), will all of the individual dwellings in the second table be assigned “In Construction” when created? I’m assuming I link the column to the second table?

The records are already linked, so we’d just need to make a Lookup field in the Dwelling ID table like so:

[Andrew_Carville]

Hi Adam,

That’s brilliant - you’ve made my week.

We’re on the pro plan, but if the only additional step I have to take when entering a new project is to copy the ‘text to paste’ cell into the next column I can live with that. It also means if a project changes from 4 dwellings to 3, for example, I can just delete the dwelling ID and the relevant row in the second table.

Perfect solution - thanks again for your help!

Cheers,
AC

[TheTimeSavingCo]

With 50k automation runs, just set up the automation to paste the value in for you man! Big quality of life improvement imo

If you want, we can set up an automation that will delete the record for you automatically when the number of dwellings decreases too heh

[Andrew_Carville]

Mate, if that’s possible that would be amazing.

Random question - is it possible (using formula or automation) to automatically create the Dwelling IDs? So if I type “3” in “# of Dwellings” column, it will automatically create the “A”, “B” and “C” options in Dwelling ID?

Or is that a step too far?

Regards,
AC

[TheTimeSavingCo]

Andrew_Carville:

Random question - is it possible (using formula or automation) to automatically create the Dwelling IDs? So if I type “3” in “# of Dwellings” column, it will automatically create the “A”, “B” and “C” options in Dwelling ID?

Oh yeah, sure, totally doable. The implementation of it might vary depending on the max number of dwellings you can possibly have, whether or not you need them to be “A”, “B”, “C”, etc, but I’ve thrown as basic version together in here for you to look at

The automation works via a bunch of conditionals, where, if # Dwellings = 1, it updates the Dwelling ID field with “A”, and if it’s 2, it updates it with “A”, “B”, and so on. After that, the other automation takes over and pastes the comma separated list into the linked field, creating the records

If it’s possible to have like 20, 30 dwellings, we’re probably still good to go with modifying the automation. If we’re looking at dozens or hundreds of dwellings, I’d highly recommend a script.

---

I’ve also put together the deletion bit as you can see above. The thing about deletions though is that we can only do these via running a script, and so I’ve done that here. You’ll notice in the gif above that, when we update the # Dwellings field from “4” to “2”, it updates the Dwelling ID field by deleting “C” and “D”.

Once there’s a mismatch between # Dwellings and the number of linked records, the script will run and identify which records need to be deleted (i.e. “C” and “D” in this example) and then delete those

===

With scripts, we can do pretty much anything you’d ever need, really, so the sky’s the limit here. Let me know if you’ve got any issues setting stuff up and I’d be happy to assist

[Andrew_Carville]

Wow, that’s amazing. Thanks for your help again with this! I’ll play around with it today and see how it works…

Cheers,
AC