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:
- 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.
- 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.
- In
[Members], create a new single-line text field. Call it {Issue3}.
- 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.
- 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:
- 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.’
- 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.’
- 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!
