Skip to main content

Advanced linking

  • July 23, 2019
  • 6 replies
  • 45 views
E

Hi - I need help with advanced linking and maybe formulas or rolls ups? I have 4 tables.

  1. Roles
  2. Products
  3. Content types
  4. Services

I created a 5th table (Touchpoints). I want to take the names from the 1st column in tables 1-4 and populate the first column in table 5 with them. So that the values in table 5 are linked back to their original tables.

However I do not want all the values from tables 1-4 to be used in table 5. And I want to be able to add values in table 5 that are not coming from tables 1-4.

Also I would like to add an additional column in table 5 that automatically populates for each record if it is a person, a product, a content type, or a service based on which table (1-4) the record came from.

    6 replies

    Justin_Barrett
    Forum|alt.badge.img+21

    Welcome to the community, Elizabeth! :grinning_face_with_big_eyes: Tables 1-4 make sense to me, but I’m struggling to understand the purpose of your fifth table as you describe it. Can you give a specific example of some of the content that might be in the other tables, and how you’d like to use that inside your [Touchpoints] table? Something tells me that a reworking of your base design might be in order, but without knowing more about your end goals, it’s hard to say what that might be.

    E

    Welcome to the community, Elizabeth! :grinning_face_with_big_eyes: Tables 1-4 make sense to me, but I’m struggling to understand the purpose of your fifth table as you describe it. Can you give a specific example of some of the content that might be in the other tables, and how you’d like to use that inside your [Touchpoints] table? Something tells me that a reworking of your base design might be in order, but without knowing more about your end goals, it’s hard to say what that might be.


    Thank you Justin.

    The 5th table is meant to serve as the full library of customer touch points. A customer touchpoint could be an interaction with 1 of the things in tables 1-4 (Product, Content, Service, Person). For tables 1-4 I am collecting information on each record that is relevant to each type.

    Ex:
    • Person - Who is it? Who do they report to? What are they measured against?
    • Product - Domain? Service model?
    • Service - Who develops it? What systems are associated to it?

    Then I would like to consolidate certain touchpoints (from tables 1-4) into a library (table 5) which attaches different pieces of information to each record. Such as…
    • Online vs. Offline Interaction
    • Touchpoint type (Product, Content, Service, Person)
    • Phase of the customer journey that it appears in
    • How performance of the touchpoint is measured
    • Who is responsible for improving performance of that touchpoint?

    Does that help?

    J
    Forum|alt.badge.img+18

    Thank you Justin.

    The 5th table is meant to serve as the full library of customer touch points. A customer touchpoint could be an interaction with 1 of the things in tables 1-4 (Product, Content, Service, Person). For tables 1-4 I am collecting information on each record that is relevant to each type.

    Ex:
    • Person - Who is it? Who do they report to? What are they measured against?
    • Product - Domain? Service model?
    • Service - Who develops it? What systems are associated to it?

    Then I would like to consolidate certain touchpoints (from tables 1-4) into a library (table 5) which attaches different pieces of information to each record. Such as…
    • Online vs. Offline Interaction
    • Touchpoint type (Product, Content, Service, Person)
    • Phase of the customer journey that it appears in
    • How performance of the touchpoint is measured
    • Who is responsible for improving performance of that touchpoint?

    Does that help?


    I, like @Justin_Barrett, am having a little trouble wrapping my head around what you are wanting to do – and we are trying to understand your use-case because it seems there must be a better way to accomplish your end goal, although, perhaps, it doesn’t look exactly the way you are envisioning it.

    I think that is likely true (there is a better approach than what you are suggesting), but since I don’t fully grasp what you’re trying to accomplish, I’ll give you the short answer to your explicit question…

    You will need 4 separate columns in Table 5 – one for each other table that you want to be able to link to. When you define a column as a “Link to another record” column, you have to specify the table the records will come from. You can’t specify multiple tables for one column. Airtable is basically reserving that column for “Links to records from Table 1”; and reserving the next column for “Links to records from Table 2”; etc.

    So, Table 5 will end up looking rather inelegant. You can smooth it over a little by pulling the title from whichever of those 4 fields is actually being linked into your Primary field, so that you can always see the name of the record you were linking to in the same column (the Primary field). But you still have to be conscious of which column you attempt to create the link in, and if you are only ever linking 1 item to a record in Table 5, you will always have 3 empty columns in each record in Table 5 (one for each Table you are not linking to).

    Hopefully that gives you a sense of how to accomplish what you want – but I’m also hoping it encourages you to rethink how you might be able to accomplish your goals, as this solution may feel a little inelegant.

    Justin_Barrett
    E

    I, like @Justin_Barrett, am having a little trouble wrapping my head around what you are wanting to do – and we are trying to understand your use-case because it seems there must be a better way to accomplish your end goal, although, perhaps, it doesn’t look exactly the way you are envisioning it.

    I think that is likely true (there is a better approach than what you are suggesting), but since I don’t fully grasp what you’re trying to accomplish, I’ll give you the short answer to your explicit question…

    You will need 4 separate columns in Table 5 – one for each other table that you want to be able to link to. When you define a column as a “Link to another record” column, you have to specify the table the records will come from. You can’t specify multiple tables for one column. Airtable is basically reserving that column for “Links to records from Table 1”; and reserving the next column for “Links to records from Table 2”; etc.

    So, Table 5 will end up looking rather inelegant. You can smooth it over a little by pulling the title from whichever of those 4 fields is actually being linked into your Primary field, so that you can always see the name of the record you were linking to in the same column (the Primary field). But you still have to be conscious of which column you attempt to create the link in, and if you are only ever linking 1 item to a record in Table 5, you will always have 3 empty columns in each record in Table 5 (one for each Table you are not linking to).

    Hopefully that gives you a sense of how to accomplish what you want – but I’m also hoping it encourages you to rethink how you might be able to accomplish your goals, as this solution may feel a little inelegant.


    Thank you for the help @Jeremy_Oglesby and @Justin_Barrett. You are most likely right that there is an easier way. I am a newbie to airtable. It is probably also important to note that tables 1-4 are linked to many other tables as well which is why they each need to be their own table rather than consolidate 1-5 in one table and creating different views. Also not every person, service, product, or content should appear in table 5 either.

    I have a picture drawn that might make more sense. But when I upload it I am getting an error message that I am not able to attach images to the post. Can I email it to you?

    Justin_Barrett
    Forum|alt.badge.img+21

    Thank you for the help @Jeremy_Oglesby and @Justin_Barrett. You are most likely right that there is an easier way. I am a newbie to airtable. It is probably also important to note that tables 1-4 are linked to many other tables as well which is why they each need to be their own table rather than consolidate 1-5 in one table and creating different views. Also not every person, service, product, or content should appear in table 5 either.

    I have a picture drawn that might make more sense. But when I upload it I am getting an error message that I am not able to attach images to the post. Can I email it to you?


    That’s an automatic safety measure applied by the forum software, likely designed to keep junk out of the system from folks (bots or not) who make an account, post some spam, then never come back. Once you’ve been active on the community for a while (and off the top of my head, I don’t know what “a while” is), certain restrictions will be lifted.

    I’ll DM you my email address. I’m happy to take a look, but I offer no guarantees regarding how much guidance I’ll be able to offer. My workload has been increasing lately, and consequently my available time for side projects is going the other direction.

    L
    • Lynnsey_Schneid
    • Participating Frequently
    • June 7, 2020

    @Elizabeth_Barker I was wondering whether you are still using Airtable for customer journey mapping - I have been using it for this purpose too. Did you ever solve the problem in your original post?

    Terms & ConditionsAccessibility statement