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.
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)
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?
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:
Whether you’re on a Pro plan
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:
You input the project name
You select the Dwelling IDs
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
Once the checkbox is marked, the automation will run and paste the text into the linked field
The records are already linked, so we’d just need to make a Lookup field in the Dwelling ID table like so:
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.
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