Creating vertical views out of a very horizontal database


#1

I have a basic database that is getting too wide to use, and the gallery view–to my understanding–doesn’t allow merging of fields (eg, first name + last name) to fit more information on one card.

I would also like to show a linked table’s info in this gallery card, not just the key number.

I hope I’m being clear. Thank you!


#2

@Karen_Schlosberg - one thing you could do would be to create several views of the data ‘cut up’ into logical sections - so if, say your table is for employee information you might have one view for Personal data, one for work data etc.

It may also be worth looking at the fields and working out whether they should be in one table or would be better split up into several with links between them.


#3

Hey Karen,

I have had the same problem in the past (wanting my linkedRecord to another table to show more meaningful info).

The good news is you have a number of options here to use:

1. Use a formula in the first column of your table to make a meaningful record ID instead of just using numbers.

Example, for our Personnel table, I don’t use an auto number for the record ID, instead I use something like BillM01. This is a last name, first name, number combination.

This way, I reasonably can know which people are assigned to a certain task without needing to see the whole name, or record. The limitation of this method, is that (of course) it is impractical to add all the bits of data you may want to know about a record into one field. Still, it serves it’s purpose: reasonably fast record identification by a human.

Below is an example of the formula I have used for some of my record IDs. The formula concatenates 3 fields by:

  • grabbing the first 4 letters of the preferred last name (or legal last name if no preferred name exists),
  • grabbing the first letter of the preferred first name (or legal first name), and
  • grabbing a number field to distinguish from similar names, and appends it to the end of that chain.

CONCATENATE(
… LEFT( IF({LName-Pref} = “”, {LName-Legal}, {LName-Pref}), 4),
… LEFT( IF({FName-Pref} = “”, {FName-Legal}, {FName-Pref}), 1),
… CountNM
)

If this solution does not give you enough data, let’s look at your second option.

2. Add a “LOOKUP” field to your Very Hoizontal table, pulling the needed and related data from your Linked field

Here is how this option works.

  • Pretend you have two tables: Persons and VeryHorizontals – aka VHS.

  • Persons table has IDs significant to humans, so it…

  • has as its record ID a formula of LastName+First+ID# (see above formula), with ID’s like these: BillM01, McDoR01, HancJ01.

  • has another field called phone

  • and email

  • In VHS table…

  • You have a field called "JobAssignees"
    And you are now happy to know BillM01 is assigned to the task (instead of just 12345).
    Progress, you say, but not enough…

  • Because what you really to know is how to reach BillM01 from the Gallery View
    (without needing to open the persons table)…

  • So, Add a new field and call it "GalleryInfo" (or “AssigneeEmail” or whatever you like).
    ** Make the field type a “lookup” field.
    ** in the lookup settings, indicate which VHS field is linked to your Persons table
    . . . (ie “JobAsignees” is pulling from the Persons table, so tell it “JobAssignees”).
    ** in the lookup settings, also indicate which field you would like to see FROM your Persons table
    . . . (we want to see the Email field from the Persons table, so we select “Email”)

  • Now the VHS field "AssigneeEmail will display the email address of whomever you select as the "JobAssignee"
    You can hide this field in your normal view, but have it displayed in the gallery view to declutter your view.


#4

Hey Karen, if you don’t have a heart for my last post, here is a summary:

Two options are available for you to use to achieve what you are trying to do (display more information from a linked record in a table).

  1. Use a formula in the first column of the table you are linking FROM, inorder to make meaningful record IDs instead of just using numbers. This way you can have a rough idea about the information contained in a record just from the ID that is being displayed in the linked field.

  2. Use a “lookup” field to grab additional information about the linked record. https://support.airtable.com/hc/en-us/articles/202576519-Guide-to-formula-lookup-count-and-rollup-fields#lookup


#5

I never thought of using a formula. Great ideas here–thanks!!


#6

thanks so much! a good idea.