Aggregate Data from linked Database through formula

[dukevannori]

I am trying to create a SubTable and then aggregate filtered data to the main DB with a formula to put a more meaningful name on display.

I have a main DB with a field “current job”.
and a subDB where i define “company”(rel), “department”, “job title”, “active/past”(checkbox)

now i have a form field in mainDB that aggregates the current jobs by a formula field “Jobs.Filter(Past = false)” … and i would like to actually show “company.name”-“department”-“jobtitle” there …

another option i though of was to make a form field in the connected DB to “make” that combined name and then look that up - but i cant filter that. i would say “Filtered Lookup” … but that is probably a wrong term

NOTE: current jobs and past jobs can have more than one entry each. but i guess aggregated and consolidated data from a related DB is not that unusual as a want-to-have, isnt it?

How would you go about this? is this unusual or do i just miss the correct search for the docs?

[Chr1sG]

I’m not sure I understand how you have your dbs and fields set up, but first things first: what is your ultimate objective? It sounds like you’re trying to store people with information about the jobs they have held/job they currently hold.
Is that right? And how does ‘department’ fit in to the overall setup? Does every company have the same possible departments, or does each company have specific departments unlike othe r companies?
Can a person have a job with a company, but not affiliated to any department? Or is a person’s job always tied to a department?
And finally, how would you ideally want to visualise/present the stored info?

Sorry for all the questions, but my experience is that digging to the ultimate need is more effective than coming with solutions straight away.

[dukevannori]

@Chr1sG , you are amazing. thanks for taking the time to ask these questions.
ultimately I just want to have a intermediate database, that stores information, that links two other database entries and add new data to the link. then display filtered information about those two things in the linked database.

in this (simplified) case i want this:

1.DB “Contact”:
name (txt), current job(s) (relation, filtered by “current/past = true”, combined field information “company + dep + job title”), past jobs (same, but filtered “not true”)

2.DB “Companies”
name, contact informations etc

3.DB “Jobs”
Job Title, Department (just text, not a select or other field - but could be), Company (rel to companies), Contact (rel to Contacts), Current/Past (checkbox)

so i want to have one main db, where i can see the “contact name” and “current position(s) at company(s)”.

so if the formula field could work something like: (Jobs.Name + Jobs.Dep + Jobs.Company) WHERE jobs.contact == contact.name AND jobs.past = true/false

it does get the names of the field, but not the corresponding other fields.

of course i could setup automations to generate the name of the job incl. company & dep. (not using a formula, as I would not be able to quickly create a new job then by entering the name of the job in the relationship field then). but i guess or hope that there is a simpler way of doing this.

In airtable f.e. this is solved by Rollup Field - Overview | Airtable Support as the rollup field can be filtered by conditions.

[Chr1sG]

I think it’s actually pretty simple to do what you want. Create dbs

• Company
• Contact
• Job

with these relations:
Contact 1:n Jobs
Company 1:n Jobs

Set up primitive fields for Company db:
Name, Address, Phone, …

Set up primitive fields for Job db:
Title, Department, Current

On your contact entity view, you will see a Jobs field, which is a relation to all the jobs a Contact has had.
But, you can add alternate views, with filters, to show only the current one, or only the previous ones:

If you really want to create a Table view of all contacts and their current job (showing job-related info), then you will need to create a formula like this in the Contact DB:
Jobs.filter(Current = true).Join(Company.Name + " - " + Department + " : " + Title,"")

Note, .join may not be doing what you think it is doing here.
.Join is used to get text from all items in a set (in this case the set of items is the Jobs collection, filtered to only include those where Current is true).
So we’re getting the Company.Name + " - " + Department + " : " + Title from all items, and joining them together (with no space in between), but hopefully there is only one.

It’ll look like this:

If you accidentally made two jobs current, it would look like this:

But actually, without using a formula, you can use the new Grid view to only show the Current Job(s) for each Contact:

Or you can use good old-fashioned list view using the same principles:

I hope somewhere in this reply is something useful

[dukevannori]

absolutely amazing. thank you. there is definitely something to find here.

would be great to have the Jobs.filter(Current = true).Join(Company.Name + " - " + Department + " : " + Title,"") option putting out separate, clickable options (like it would without the join) but your solutions are something I will definelty try out and see if it fits (i guess it will)

thank you for your amazing support!

[Chr1sG]

@dukevannori

would be great to have the Jobs.filter(Current = true).Join(Company.Name + " - " + Department + " : " + Title,"") option putting out separate, clickable options (like it would without the join)

I’m not sure where you expect to be taken when you click, but here’s some ideas:

If you use this formula in the Contact db:
Jobs.filter(Current = true).Sort().First()
you’ll have a field which is a link to the (hopefully) only current job.
(you may want to use a Name formula so that the Name of a job is built up from the Company, Department and Title):

If you want to click through to the Company, then use this:
Jobs.filter(Current = true).Sort().First().Company

[dukevannori]

that is exactly what i had in mind first, but with the option in creating new jobs (without the associated company/dep) just by entering a name in the field … i guess i need to setup the automations here too to create the name when fields are updated. not using the comfort of the formula field but relying on the automations.

[Chr1sG]

You can certainly use an automation to populate the fields, but if you want to…

@dukevannori

creating new jobs (without the associated company/dep) just by entering a name in the field

then I guess you’re talking about writing the job title in the Name field?

In that case, you can have an automation that runs on creation to copy the Name to the Job title, and then optionally another automation to update the Name to include the Company / Department if these are filled in.
You’ll need to be careful to avoid a loop, but I think you know how to fix that

[dukevannori]

exactly, that was the initial idea to have the name field as “Job Title”. but to make it a bit more readable ill add the automations to get company info in the name if its entered later
and to awoid loops i`ll do as you explained here: Automation Loop

maybe a filtered rollup could be placed in the feature request somewhere for the future to make this a bit easier

thanks in any way for the fast and plentiful solutions

[Chr1sG]

@dukevannori

maybe a filtered rollup could be placed in the feature request

I’m not sure what you’re after with this. I mean, this
Jobs.filter(Current = true).Sort().First().Company
is basically a filtered-lookup.

Can you explain what you would want to be able to see/achieve that you can’t already?

[dukevannori]

Its all there… in the opened up view there is an option to show the fields in the list and to filter these too.

so basically what i would love is a condensed version of that in the grid view. all smashed in one cell. click on cell opens up all sub-articles. and i have some formating options.

but as you clearly pointed out in all your fantastic how-tos, its all possible to do

maybe i am just to overwhelmed at the moment and to fixed in the way i am thinking coming from these other tools.

thanks for now! i will dig into this

[dukevannori]

i would add a feature request for having this kind of options in the field creation settings - so that i have control over what info is shown in the link.
this is functionality that you already have, but only it would be so amazing to have these options in the table view as well. then it would be easy to filter for the correct entities and only show the fields that actually have valuable information in it. without the need for formulas, automations, scripts or anything else

[Chr1sG]

@dukevannori

so that i have control over what info is shown in the link.

The selection of which fields are shown for a link to an entity is not the same in all places. So an entity in one relation list view can display different fields than are visible in another relation view (or left menu view).
So it doesn’t make sense to have field visibility settings available during field creation.

(i assume that I am answering what you’re asking)

[dukevannori]

ok, yes - you are completely right, that its not practicable at creation. but to have these “fields” options as a “create displayed name with” would be nice to have in table view too