Skip to main content

I have two tables:

Table one shows a list of tasks done on different computers and when the task has been marked as finished:
TaskId, FinishDateTime, Computername.

In table two I have information who was logged in when on on which computer:
DateTimeStart, DateTimeStop, EmloyeeName, Computer.

 

I want to have in table one an extra column, showing the name of the EmployeeName that was working on that computer while the task was finished. So we need to use the FinishDateTime and Computername to query the other table to find a match with exactly this computer and FinishDateTime in range between DateTimeStart and DateTimeStop. The result of the formular should show just the Name of the Employee.

 

I hope this is clear. How to create the formular? I am familiar with excel, where i would do some kind of nested vlookups, but have no idea how to achieve this in airtable.

Ah, yeah this is a tricky one and it's not possible to do this with a formula alone

The easiest option involves you doing a manual step for each task, specifically, linking the records together.  You'd use dynamic filtering (https://support.airtable.com/docs/dynamic-filtering-in-linked-record-fields) to create the linked field and make it only show the logins that are in range of the start and end time of the task:


---
If you want this fully automated, the easiest option would be to use a script for it, as Airtable doesn't allow us to do a Find Record on a dynamic date at this time I'm afraid:

And if scripting isn't an option, you could try to build an automation system to:
1. Link all your tasks to a single login when the Login record is created
2. Use lookups to pull the data over to the Tasks table
3. Use formula fields to determine which tasks fall into that Login
4. Have the automation find the records that are relevant based on those formula fields and link it to the triggering Login record

Where would the script run? In general it would be a solution run the script manually, because the linkings are persistent, so after running the script on one machine, all instance will see the result. Can you please give me some hints how the script should look like, so I understand the basic syntax?

For the manual way, as far as I understand I would have to link manually, but because of the filter, there would only be one possible option, right?

Where would the script run? In general it would be a solution run the script manually, because the linkings are persistent, so after running the script on one machine, all instance will see the result. Can you please give me some hints how the script should look like, so I understand the basic syntax?

For the manual way, as far as I understand I would have to link manually, but because of the filter, there would only be one possible option, right?


re: Where would the script run? In general it would be a solution run the script manually, because the linkings are persistent, so after running the script on one machine, all instance will see the result. Can you please give me some hints how the script should look like, so I understand the basic syntax?

I was thinking it'd be an automation that triggered whenever a "Login" record had an "End Date" value, indicating that someone had logged off

The script would grab all the Task records that aren't linked to a login and then figure out which of the Tasks had an finish time that was between the triggering record's start and end time

---
re: For the manual way, as far as I understand I would have to link manually, but because of the filter, there would only be one possible option, right?

Yeap that's right

Reply


Terms & Conditions