How do I connect fields of 1 table to other table which is not the primary key plus 1 more question

[vibhudawar]

I have 2 questions to ask:

1st being this:
I have 2 tables:
1. Revelator Sheet Data
2. Client Information

The "Revelator Sheet Data" table holds the music assets of the clients belonging to our business. To map the assets to the clients (in the client information table) I will use the "payee_id" which is present in both "Revelator Sheet Data" and "Client Information" tables.

But I can only link 2 tables with primary fields. The "Client Information" has "Client ID" while the "Revelator Sheet Data" has "Id" as the primary fields. So, when I linked the "Client Information" table to"Revelator Sheet Data" table using client_id, how do simultaneously match the payee_id?

For example:

Revelator Sheet Data Table

 Above is the Revelator Sheet Data table. I have linked the client information table using client_id. Now I want that if "Payee id" (in Revelator Sheet Data) and "Payee ID" (lookup field from client information) are similar, the client_id is mapped automatically ie I basically want to map client id based on payee id.  As of now, I can do that by manually going to every record and then search client id, who has similar payeeid then map that. But that is inefficient.

Here is the image of client table:

Client Information Table

 Q2: How can I represent the Id of assets as count? In a new table named: "TEST", I want to show the count of assets linked to a particular client. For that since in the previous step, if we have linked all the assets to the client, how can I represent the number of keys in a cell as count? Can we use roll up field or what?

Test Table

For ex: in above image, since the numbers of ids of assets for particular client is 2, the column with name: "Total Number of Assets" should show count as 2, not 1. How can I do that? Did hit and trial by changing that into roll-up field, but couldn't got count as 2.

configuration of Total Number of assets (hit & trial)

[TheTimeSavingCo · Accepted solution]

For your first question, if this is a one off linking, then I would suggest:
1. Changing the primary field of the "Client Information" field to be the "Payee ID" field
2. In "Revelator Sheet Data", click the field header of "Payee ID" to select the entire column and then hit CMD / CTRL + C
3. In "Revelator Sheet Data", click the field header for "client_id" and hit CMD / CTRL + V

This will link everything automatically.  For future linkages you can build an automation with a Find Record and Update Record action to deal with it for you

---
For your second question, could you try using a "Count" type field?  Should do what you need

[vibhudawar]

@TheTimeSavingCo thanks a lot man! Much appreciated.