Add a link in the linked field of many records, without deleting existing links

It seems really really simple, sorry I can’t see how to do it.

To precise the context : it’s about a monthly magazine.

I have a table with Members and a table with Issues, and a Linked field between them.

Some Members are related to Issue 1 and Issue 2.
Others to Issue 2 only because they subscribed later

I want to had a new link, to the new Issue 3.

How can I had the Issue 3 in the Linked field, without opening one by one each cell ? (we speak about 2.000 membres)

Sorry for a so evident question, I begin and can’t find the way to do it.

It’s not at all an obvious question. There is a way to do it, but how well it works for you depends on what else your base does. This is because the only way I know how to do it requires you to delete—

No: wait. I just thought of a way that allows you to make the addition in-place. It’s a little riskier than my first approach, so let me see if I can build in some safeguards…

OK, try this:

1. First, select the ‘history’ icon in the upper right. (It looks like the hands of a clock surrounded by an arrow running counter-clockwise.) From the history menu, select ‘snapshots’ followed by ‘take snapshot.’ That’s your defense in case anything goes wrong: You can restore from the snapshot.
2. In your [Members] table, right-click on the linked field. (I’m going to call it {Issues}.) Duplicate the field. This is a second-line backup — you can use it to get back to your original state, as well — but you’ll also use it when creating your new, triple-linked field.
3. In [Members], create a new single-line text field. Call it {Issue3}.
4. For every Member who is supposed to receive the third issue, set {Issue3} to the value of the primary field in [Issues] for issue 3. Let me phrase that a little differently, in case it wasn’t clear: Whatever the value of the first field is for the ‘Issue 3’ record in the [Issues] table, take that value and place it in the {Issue3} field for every record in [Members] that should receive the third issue.
   . You can do this a number of ways; for instance, you could enter the value in the {Issue3} field in the first row that should receive issue 3, click the lower-right corner of that field, and drag the box that appears to the last row you want to have that value, and all the rows you drag through will be filled. However, with 2,000 or so records, you’re probably better off with copy-and-paste: Click on a field containing the value (you can even use the primary field of the Issue 3 record in [Issues]), press Ctrl-C to copy it to the clipboard, click in the {Issue3} field in the topmost row to fill, scroll to the last row to fill, while holding the Shift key click the {Issues3} field in the bottom-most row to fill, and press Ctrl-V to paste. All selected cells will be filled with the desired value.
5. In the [Members] table, you should now have your original {Issues} field (with Issues 1 and 2); a duplicate {Issues} field, which I will call {Dupe}; and an {Issue3} field, set to the value of the primary field of the Issue 3 record in [Issues]. Now here comes the scary part:
6. Right-click on your original {Issues} field and select ‘Customize field type.’ Change it from ‘Link to Issues’ to a Formula field and configure the formula to this:
   {Dupe}&','&{Issue3}
   Take a deep breath, and click ‘Save.’
7. Your {Issues} field should now be a formula field containing one, two, or all three [Issues] primary field separated by commas. (Those members who joined after Issue 2 will contain only ‘,Issue3’ – assuming ‘Issue3’ is Issue 3’s primary field.) Now for the magic part: Right-click on the field and select ‘Customize field type,’ again. Change the field type from Formula to ‘Link to another record.’ When prompted, select the [Issues] table, make sure ‘Allow linking to multiple records’ is turned on, and click ‘Save.’
8. Your {Issues} field should now once again be a linked record field, containing links to one, two, or all three [Issues].

Now, at Step 7 you may notice other fields in your table displaying a red exclamation point (’!’) indicating you’ve broken them. This would occur if other formulas or fields expect {Issues} to be a linked record field. Frankly, I’ve not sure if they heal themselves upon {Issues} being returned to a linked record field in Step 8 or not; if not, you’ll have to repair them by hand. Unfortunately, the other processes for adding this link definitely require you to fix such things by hand, so there is at least a chance you may not have to do so with this process.

If anything goes wrong, you have your duplicate {Issues} field and the snapshot to fall back on — but you shouldn’t need either one; I’ve just stepped through this process three times while writing this reply, and it’s worked every time. Once you have a triple-linked {Issues} field, and everything is OK, you can go ahead and delete the {Dupe} and {Issue3} fields from [Members].

I know this is a complicated procedure, and it is probably not as well-documented as it could be, so please read through it several times, and if you have any questions, do not hesitate to ask.

Hope this helps!

Hi

I think there is a way to do this - it involves an additional table called, say, All Members, which needs a single record with Name = Member and you then Link to this from your members table. This creates a field with all members in a single field. In the All Members table you should then create a Member Names field which is a ArrayUnique roll up from Members - this givers you a comma separated string of all member names:

Next, on your issues table create a link field to All Members and for each issue select the Member record to link to. Then create an All Members field on Issues - this is a Lookup field from All Members and will always display the full list of all members. Now, as this is always a full list, we need a way to copy a snapshot of that list into another field when the Issue record is created - this is to get a list of only those members at the time of the issue.

This can be done with a Zap triggered by the new Issue record and which then searches for the Issue record and updates another field called Members for this Issue with the current contents of the All Members field. Here’s the Zap:

(Sorry about the formatting)

You should then have an issues table looking like this:

What I haven’t tested is whether this will cater for the number of records involved (for example Zapier could have a limit on how many characters can be transferred). Note also that a paid Zapier account is required as this needs a multi step Zap.

Thanks a lot for your long reply !

I tried it and it works well, except:

• I had the red exclamation point in other fields and had to repair manually (scary to do this every month)
• it breaks also the filter of the view (the {Issues} field is used by the filter)

I will look at Julian_Kirkness option, but don’t fell very confortable with too.

I really couldn’t imagine it would be so difficult to add a link in a field.

But thanks again you two for your incredible help

Let me add a couple of points to my last note.

1. Unfortunately, you will have to hand-repair any fields broken by your re-configuration of a linked records field to a formula field and back again. For the reason why, see the next point.
2. When you change the linked records field {Issues} to a formula field, the corresponding linked records field from the [Issues] table back to the [Members] table also gets converted – in this case, from a linked records field to a single-line text field. (I’ll calls this reciprocal link field {Members}.) Unfortunately, when you convert the {Issues} formula field back to a linked records field, it leaves the original {Members} as a text field and creates a new {Members} linked records field. This raises two issues: First, any lookup, rollup, count, or related field in [Issues] is still pointed at the text {Members}, which is an invalid construction. Second, you now have two fields in [Issues] with the same name, which will cause problems with any future attempts to reference {Members}.

So, once you add the link for Issue 3 to your [Members] table, when it comes time to clean up by deleting {Dupe} and {Issue3}, also go to the [Issues] table and delete the redundant (and now pointless) text fields.

Other than that, my initial description still stands. You should be able to add the third link with just a few minutes’ work.

Edit:
I see we had dueling updates. :winking_face:

I don’t see any way to eliminate the need to repair broken fields. Fortunately, once you repair those fields directly affected — for instance, count or lookup fields that directly use the link back to [Members] — those broken as a result of the primary breaks should recover on their own.

I’m not sure why the conversion to a formula field and back again would break the filter — but I suppose it depends on exactly how you have the filter defined. In my [admittedly sparse] tests, I defined a filter selecting on one of the issues, and it survived the manipulation of the {Issues} field with no problem. (It even worked correctly against the formula version, if for some reason that was needed.) Could you say a little more about how the filter broke?

Eureka ! (hope so…)

Am I wrong doing this?:

1. Copy all the colonne with the linked field
2. Past in Excel or G Sheet, add the new link in the cells
3. Copy/ Past in Airtable

I tried with a list of 10 Records, it was OK. Is it possible / safe with 2.000 records ?

Is there a more “elegant” way than copy/past ? (like export/import) [sorry I’m really new in Airtable and moreover in database - but Airtable is really great for beginners…]

Honestly, I’ve never even tried to paste directly into a linked record field – I’ll have to give it a try.

If it seems to work, you can always duplicate the base (click the down-arrow in the lower right of the base icon on your main Airtable page and choose ‘Duplicate base’) and test it out on the duplicate. If it works for 10 records, I see no reason it wouldn’t work with 2,000 — but take a snapshot and duplicate the base to be safe.

Edit:
I’ll be — that seemed to work. I had to mark the first and last cells to replace, but a paste-overwrite worked fine. (What’s more, I’d forgotten to create a new row in [Issues], but Airtable did so for me.) I think you have a winner, there; a little inelegant, true, but pretty damn efficient…

Edit 2:
And without breaking existing fields or formulas!