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?