How to join 2 tables with concordance from a 3rd table

Support
1

I am looking to join 2 tables with a concordance list:
Table 1 has ID-x, LastName…data columns a,b,c, etc
Table 2 has ID-y, Lastname…data columns d,e,f, etc
Table 3 has only ID-x and ID-y as a concordance list: ID-x, ID-y. Each is a list of uniqe ID’s that relate only to the other ID. ID-x == ID-y and no other, and vice versa.

I’ll be damned if I can populate a new column automatically in either table 1 or 2 with the corresponding missing ID. I can put in a select from another table, but have to click, search for ID-x, the click ID-y in the linked table then save that single entry, then do the next one - calculating all doesn’t seem to work.

Thanks all.

2

Seems like you need to do one big table. Then do 2 versions of it showing only what you need on each.

1 Like
3

Hi,

You may follow the steps below:

Step 1. Make sure all ID-x and ID-y are listed in Table 3.
Otherwise, the values in Table 1 and 2 will be cleared.

Step 2. Download a manual backup of the database.

Step 3. In Table 1, change the “ID-x” field to “Select from other sheet” field type, linking to the “ID-x” field on Table 3.

Step 4. Save the design and load the “ID-y” from Table 3 to Table 1 in the link manager.

Step 5. Save the design and repopulate loaded fields.

Then, you should have an “ID-y” field with corresponding values on your Table 1. You may apply the same logic to Table 2 to get the “ID-x” values.

In case you have issues when setting up above, please kindly let us know in which steps that you have the issue and share a screenshot of the error for us to refer to.

4

Well this may be the problem - I am 870 ID’s short in Table 3.
Table 3 is developed from Table 2 source, which is a subset of Table 1.
Table 1 only has ID-x and needs ID-y.
Table 2 has ID-x and ID-y
Can you think of a workaround - I just expected some to not populate.

5

Yea, this is a non-starter. In a 450k record database, there’s some probs. Even after cleaning it up and substituting 9999nnnnn numbers for missing values, it still says there’s a duplicate when there isn’t … I think is hasn’t re-read the file.

And why the fxck would it clear the values, not be able to restore them, and make me spend a day re-uploading those records. Tried repopulating those values from the same file to no avail.

Gotta say, RAGIC isn’t performing “like a spreadsheet” for me.
I see a lot of potential, but I’ve put a couple hours daily in to this for a month and am no further along in trying to link these tables, and I’ve even tried smaller versions … but if everything isn’t “perfect” it fails.

Then if I do get a link - I can’t unlink it b/c “referenced from another sheet” which also says the same thing. Gotta say, I signed up for another month to try to beat this bastard, but I’m not hopeful.