# Aggregate Data from linked Database through formula **URL:** \ **Category:** Get Help\ **Created:** [August 12, 2023, 11:52am UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949 "2023-08-12T11:52:37Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![dukevannori](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@dukevannori](https://community.fibery.io/u/dukevannori)\ **Post date:** [August 12, 2023, 11:52am UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/1 "2023-08-12T11:52:37Z")
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? ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 12, 2023, 2:10pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/2 "2023-08-12T14:10:18Z")
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. ---
**Author:** ![dukevannori](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@dukevannori](https://community.fibery.io/u/dukevannori)\ **Post date:** [August 14, 2023, 12:49pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/3 "2023-08-14T12:49:29Z")
@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](https://support.airtable.com/docs/rollup-field-overview) as the rollup field can be filtered by conditions. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 14, 2023, 2:30pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/4 "2023-08-14T14:30:16Z")
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: ![firefox_DmQFGjhqXx](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/c/c2261f764953e357ca56a2b2cc9f04e54166a3f3.gif) 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: ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/0/09060c3d1c1341679461b59f17d827e4773a59c5.png) If you accidentally made two jobs current, it would look like this: ![firefox_3gDmBuRrvL](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/b/befb77774d46416679d580c62b08b552308e113c.gif) But actually, without using a formula, you can use the new Grid view to only show the Current Job(s) for each Contact: ![firefox_HBAcWOknoC](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/0/013cbed8959d81a1bd72ac03fd89a589caaa80fb.gif) Or you can use good old-fashioned list view using the same principles: ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/9/935ba7e9c160d21c21256f9b9ef80391b8496ec5.png) I hope somewhere in this reply is something useful 🙂 ---
**Author:** ![dukevannori](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@dukevannori](https://community.fibery.io/u/dukevannori)\ **Post date:** [August 14, 2023, 2:49pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/5 "2023-08-14T14:49:10Z")
🤯 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! ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 14, 2023, 2:56pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/6 "2023-08-14T14:56:51Z")
> [@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): ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/1/13351a9e7fea76014c0a14b2fb17656bcef7da6e.png) ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/a/a4ceff56aba8c4853232fcbb6ed753256d0a3d3d.png) If you want to click through to the Company, then use this: `Jobs.filter(Current = true).Sort().First().Company` ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/8/855710836b1811bd8ecb30286fea98fe89e848b7.png) ---
**Author:** ![dukevannori](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@dukevannori](https://community.fibery.io/u/dukevannori)\ **Post date:** [August 14, 2023, 4:26pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/7 "2023-08-14T16:26:19Z")
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. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 14, 2023, 5:06pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/8 "2023-08-14T17:06:28Z")
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 😉 ---
**Author:** ![dukevannori](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@dukevannori](https://community.fibery.io/u/dukevannori)\ **Post date:** [August 14, 2023, 5:23pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/9 "2023-08-14T17:23:14Z")
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](https://community.fibery.io/t/automation-loop/4945) 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 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 14, 2023, 5:30pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/10 "2023-08-14T17:30:23Z")
> [@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? ---
**Author:** ![dukevannori](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@dukevannori](https://community.fibery.io/u/dukevannori)\ **Post date:** [August 14, 2023, 6:05pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/11 "2023-08-14T18:05:52Z")
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 ---
**Author:** ![dukevannori](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@dukevannori](https://community.fibery.io/u/dukevannori)\ **Post date:** [August 17, 2023, 8:44am UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/12 "2023-08-17T08:44:57Z")
![Screenshot 2023-08-17 at 10.41.48](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/9/9f2d1cf6a9dc5cf950f09ddd6bdac1b121f7c803.png) 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 ![Screenshot 2023-08-17 at 10.46.30](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/1/1608da7cb8b9ff5a24b30156f50965722a85639f.jpeg) ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 17, 2023, 4:51pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/13 "2023-08-17T16:51:39Z")
> [@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) ---
**Author:** ![dukevannori](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@dukevannori](https://community.fibery.io/u/dukevannori)\ **Post date:** [August 17, 2023, 6:11pm UTC](https://community.fibery.io/t/aggregate-data-from-linked-database-through-formula/4949/14 "2023-08-17T18:11:15Z")
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