Juction tables for tracking freelancers' monthly jobs


#1

Hello!

I hope you can help me with a question on how to deal with some information I need to look at in a certain way in Airtable.

My table is configured to coordinate translation work. I receive a request which creates 1 record. Each record will belong to 1 language combination, and on the same record I want to keep track of who is translating and reviewing the document. So I have 1 record, 1 language combination field, one translator (or more), one reviewer. All the language professionals are in a table that links to the “translator”, “reviewer”

Can I create a junction table where I can see the information from the language professionals point of view, to see which jobs they have to invoice each month and each of them becomes a record itself?

For example. In my ongoing work table, I have in 1 line:

Job number Translator Reviewer
GLO-FEB-01 Nelson Anderson Anna Zars
GLO-FEB-02 Maria Yale Anna Zars

Could I have a junction table where I see the information this way? And how would I set it up?

Translator/reviewer Job number Activity
Nelson Anderson GLO-FEB-01 Translation
Anna Zars GLO-FEB-01 Review
Anna Zars GLO-FEB-02 Review
Maria YaleGLO-FEB-02 Translation

Thanks very much in advance!


#2

Hey Belen! Here’s one potential solution…

Currently it looks like you have two tables:

Translation requests

  • Request ID
  • Language
  • Translator(s)
  • Reviewer

Language professionals

  • Name

The challenge is that you want to take the translation requests table and break it down further to only show one language professional by row. Ideally, this would just be a new view using the same table, but this isn’t really possible with the current setup.

If you instead separate your translation requests table into two tables you can accomplish something like what you’re looking for. Here’s what I mean:

Translation Requests

  • Job ID

Language Professionals

  • Name

Activities

  • Activity ID (this can be whatever you want)
  • Language Professional (this links to the Name from our Language Professionals table)
  • Translation Request (this links to the Job ID from our Translation Requests table)
  • Activity (this can be a single select consisting of Translator or Reviewer)

With these tables in place, the default Activities table serves as the one you were hoping to create. Now, to recreate your original requests view, you can create a new grid view on the Activities table and select group by Job ID.

Now, when a request comes in, a Job ID should be created in the Translation Requests table. When translators and reviewers are assigned, instead of adding them to your translation request as you had done previously, you’ll do the reverse—create a new activity and assign the translation request and language professionals to the activity.

Hope that helps!


#3

Hi Daniel,

Thanks so much for your suggestion and taking the time to propose a potential solution! It does look really promising :grinning: !

I have an extra question. When you say:

“Now, when a request comes in, a Job ID should be created in the Translation Requests table. When translators and reviewers are assigned, instead of adding them to your translation request as you had done previously, you’ll do the reverse—create a new activity and assign the translation request and language professionals to the activity.”

When I create a new activity and assign the translation request and language professional to it, do I then have to go to the “translation requests” table and select those translator/reviewers again, or can it auto-populate with a look-up, for example?

Sorry for another question! I’m struggling a bit to make these more advanced (to my not so techie brain) work.

Thank you!

Belén


#4

That’s okay! It can be a weird concept to grasp at first.

With this new setup, you wouldn’t use the Translation Requests table the same way. You would actually remove the Translator and Reviewer fields from that table.

Instead, you would use that new view (the grid view grouped by Job ID) in the Activities table to see the info you were previously using the Translation Requests table for.

Here’s a simple example of what that might look like. Notice how there are two different grid views under the Activities table.


#5

Hi Danny,

Just wanted to say THANK YOU for your help yesterday, I’m going with your solution!!

You’re a true :star2:

Belén