Skip to main content

(Cross-)Populating indirectly linked fields

  • October 26, 2018
  • 2 replies
  • 43 views
K

I’m new to Airtable and have been exploring it as a possible equipment inventory solution. I have a primary table called ‘Inventory’ within a base. Among the fields listed there are ‘Manufacturer’ and ‘Model,’ as well as ‘Item Number.’ ‘Item Number’ is a linked field, linking to a second table titled ‘Catalog Items’ which includes ‘Manufacturer’ and ‘Model’ fields and is based on an import from a separate database we use for planning.

I would ideally like users in the field to be able to enter ‘Manufacturer’ and ‘Model’ manually OR to be able to select an ‘Item Number’ and have that data populate from the ‘Catalog Items’ table to the ‘Manufacturer’ and ‘Model’ fields in the ‘Inventory’ table.

Much less importantly, if the field user populates the ‘Manufacturer’ and ‘Model’ fields in the ‘Inventory’ table and then decides to make a new ‘Item Number’ to add to that field, is there a way to have those fields populate back into the ‘Catalog Items’ table?

The goal is to not have folks in the field touch anything but the ‘Inventory’ table. I appreciate your help and hope that the question is not terribly redundant.

      2 replies

      J
      Forum|alt.badge.img+18

      Airtable (and any database software, really) always deals best with consistent data structures and methods for inputting that data. What you want to do is allow for an inconsistent data structure (2 different possible ways for defining the Manufacturer/Model of an Inventory item) – this is definitely possible, but it is going to be a little bit more messy by requiring extra fields.

      Let’s deal with pulling in Manu. and Mod. from the Catalog Items table automatically first.

      When the user links an Inventory item to an Item Number, you can create Lookup fields that can pull the Manu. and Mod. from that Item Number in the Catalog Items table. Select field type “Lookup”, choose the “Item Number” field as the target of the Lookup, and for the Manu., select the Manu. field from the Catalog Items table, and for the Mod. select the Mod. field from the Catalog Items table. Now when a user links an Item Number to an Inventory item, those two fields will auto-populate with the Manu. and Mod. for that Item Number – easy enough. Since you already have fields named “Manufacturer” and “Model”, let’s assume you named these fields “Lookup Manufacturer” and “Lookup Model”.

      Now you want a user to be able to manually link a Manufacturer and manually enter Model, and if these things are done, have them be the de-facto Manu. and Mod. for the item. You already have fields for this in your table, which means that after adding the two lookup fields above, you essentially have duplicate Manu. and Mod. fields (this is the messiness I was talking about). I would suggest cleaning that up in your reference views ( the views you use to lookup equipment and reference info about equipment ).

      I would do this by first moving your Autonumber field out of the primary field spot (you’ll see why in a moment). Just select the dropdown arrow for that field and select “Duplicate Field”. It will create a copy of it to the right, in the first spot after the primary field spot. Now that primary field is freed up for us to use. I’d suggest making the primary ID of each Inventory Item a combination of it’s Auto-number and its Manu. & Mod. You can do that with the following formula:

      {Record Key}
&
" | "
&
IF(
   {Manufacturer},
   {Manufacturer},
   {Lookup Manufacturer}
)
&
IF(
   OR(
      {Manufacturer},
      {Lookup Manufacturer}
   ),
   " - "
)
&
IF(
   {Model},
   {Model},
   {Lookup Model}
)

      The & operator in there just concatenates, or strings together the different values as a line of text. So it first puts the Autonumber ({Record Key}) followed by " | ";
      then, if there is a manually entered {Manufacturer}, that will be entered next – if there is nothing there, it will enter the {Lookup Manufacturer} instead, and if both are missing, nothing will be entered there;
      next, if either Manufacturer field actually produces something, it will add " - " to separate Manufacturer from Model, whereas if there is no Manufacturer, you don’t need this extra padding;
      lastly, if there is a manually entered {Model}, that will be entered – if there is nothing there, it will enter the {Lookup Model} instead, and if both are missing, nothing will be entered there.

      So in the formula above, manually entered Manufacturer and Model will always take precedence over those looked up from the Catalog Items table, if they exist. You will end up with Record ID’s in the primary field that look something like this:
      7 | NuStep - TRS4000

      This means you can search by any of these criteria (Key number, manufacturer name, model name) against the Record ID (Primary) field, and find what you are looking for. And since all that info is consolidated into one field, you can hide all the input fields in your working/reference views, to help clean it up a bit.

      Now let’s tackle this part…

      We essentially need to do something similar in the Catalog Items table. We will end up with 3 Manufacturer fields and 3 Model fields, but one of them will be the “Master” field for each. Here’s how.

      You already have manually entered Manu. and Mod. fields. So let’s make Lookup fields for each again ({Lookup Manufacturer} and {Lookup Model}), which look at the “Inventory Item” linked record field, and reference the {Manufacturer} field and the {Model} field respectively.

      We now have duplicate fields. If a user creates a new Item Number from within the “Inventory” table, the “Catalog Items” record that gets created will Lookup the manually entered Manu. and Mod. from the “Inventory” table. And if this is the case, we can leave the manual entry {Manufacturer} and {Model} fields blank. So now, we need to know which one, the manually entered field, or the lookup field, is to be used when, in the future, this “Item Number” is linked to an “Inventory” item record.

      So we make a master field for each, Manu. and Mod., that is pulled into the “Inventory” field when it makes its Lookups. We need a formula field for each, perhaps call them {Master Manufacturer} and {Master Model} (you can use any names you like for all these fields, of course, I’m just ad-libbing for the sake of examples), with these formulas:

      IF(
   {Manufacturer},
   {Manufacturer},
   {Lookup Manufacturer}
)

      and

      IF(
   {Model},
   {Model},
   {Lookup Model}
)

      Now, finally, we can go back to the “Inventory” table, and update the {Lookup Manufacturer} and {Lookup Model} fields to reference these “Master” fields, instead of the manual entry fields, from the “Catalog Items” table.

      This should do everything you asked for, if not as pretty as you might have hoped for. Let me know if you need any further help setting that up.

        K
        • Koby_Kenney
        • Author
        • New Participant
        • October 26, 2018

        Airtable (and any database software, really) always deals best with consistent data structures and methods for inputting that data. What you want to do is allow for an inconsistent data structure (2 different possible ways for defining the Manufacturer/Model of an Inventory item) – this is definitely possible, but it is going to be a little bit more messy by requiring extra fields.

        Let’s deal with pulling in Manu. and Mod. from the Catalog Items table automatically first.

        When the user links an Inventory item to an Item Number, you can create Lookup fields that can pull the Manu. and Mod. from that Item Number in the Catalog Items table. Select field type “Lookup”, choose the “Item Number” field as the target of the Lookup, and for the Manu., select the Manu. field from the Catalog Items table, and for the Mod. select the Mod. field from the Catalog Items table. Now when a user links an Item Number to an Inventory item, those two fields will auto-populate with the Manu. and Mod. for that Item Number – easy enough. Since you already have fields named “Manufacturer” and “Model”, let’s assume you named these fields “Lookup Manufacturer” and “Lookup Model”.

        Now you want a user to be able to manually link a Manufacturer and manually enter Model, and if these things are done, have them be the de-facto Manu. and Mod. for the item. You already have fields for this in your table, which means that after adding the two lookup fields above, you essentially have duplicate Manu. and Mod. fields (this is the messiness I was talking about). I would suggest cleaning that up in your reference views ( the views you use to lookup equipment and reference info about equipment ).

        I would do this by first moving your Autonumber field out of the primary field spot (you’ll see why in a moment). Just select the dropdown arrow for that field and select “Duplicate Field”. It will create a copy of it to the right, in the first spot after the primary field spot. Now that primary field is freed up for us to use. I’d suggest making the primary ID of each Inventory Item a combination of it’s Auto-number and its Manu. & Mod. You can do that with the following formula:

        {Record Key}
&
" | "
&
IF(
   {Manufacturer},
   {Manufacturer},
   {Lookup Manufacturer}
)
&
IF(
   OR(
      {Manufacturer},
      {Lookup Manufacturer}
   ),
   " - "
)
&
IF(
   {Model},
   {Model},
   {Lookup Model}
)

        The & operator in there just concatenates, or strings together the different values as a line of text. So it first puts the Autonumber ({Record Key}) followed by " | ";
        then, if there is a manually entered {Manufacturer}, that will be entered next – if there is nothing there, it will enter the {Lookup Manufacturer} instead, and if both are missing, nothing will be entered there;
        next, if either Manufacturer field actually produces something, it will add " - " to separate Manufacturer from Model, whereas if there is no Manufacturer, you don’t need this extra padding;
        lastly, if there is a manually entered {Model}, that will be entered – if there is nothing there, it will enter the {Lookup Model} instead, and if both are missing, nothing will be entered there.

        So in the formula above, manually entered Manufacturer and Model will always take precedence over those looked up from the Catalog Items table, if they exist. You will end up with Record ID’s in the primary field that look something like this:
        7 | NuStep - TRS4000

        This means you can search by any of these criteria (Key number, manufacturer name, model name) against the Record ID (Primary) field, and find what you are looking for. And since all that info is consolidated into one field, you can hide all the input fields in your working/reference views, to help clean it up a bit.

        Now let’s tackle this part…

        We essentially need to do something similar in the Catalog Items table. We will end up with 3 Manufacturer fields and 3 Model fields, but one of them will be the “Master” field for each. Here’s how.

        You already have manually entered Manu. and Mod. fields. So let’s make Lookup fields for each again ({Lookup Manufacturer} and {Lookup Model}), which look at the “Inventory Item” linked record field, and reference the {Manufacturer} field and the {Model} field respectively.

        We now have duplicate fields. If a user creates a new Item Number from within the “Inventory” table, the “Catalog Items” record that gets created will Lookup the manually entered Manu. and Mod. from the “Inventory” table. And if this is the case, we can leave the manual entry {Manufacturer} and {Model} fields blank. So now, we need to know which one, the manually entered field, or the lookup field, is to be used when, in the future, this “Item Number” is linked to an “Inventory” item record.

        So we make a master field for each, Manu. and Mod., that is pulled into the “Inventory” field when it makes its Lookups. We need a formula field for each, perhaps call them {Master Manufacturer} and {Master Model} (you can use any names you like for all these fields, of course, I’m just ad-libbing for the sake of examples), with these formulas:

        IF(
   {Manufacturer},
   {Manufacturer},
   {Lookup Manufacturer}
)

        and

        IF(
   {Model},
   {Model},
   {Lookup Model}
)

        Now, finally, we can go back to the “Inventory” table, and update the {Lookup Manufacturer} and {Lookup Model} fields to reference these “Master” fields, instead of the manual entry fields, from the “Catalog Items” table.

        This should do everything you asked for, if not as pretty as you might have hoped for. Let me know if you need any further help setting that up.


        Jeremy, thank you so much for the thorough help. I probably won’t get a chance to work it out until Monday, but I wanted to acknowledge you now for the time you took in helping me with this.

        I will provide feedback after I’m able to get to it.

          Terms & ConditionsAccessibility statement