I am just starting with Airtable and have created a tables of Jobs, Crew, and Crew Assignments. I am trying to show where we have assigned the same crew member to multiple jobs where the dates overlap. To me it seemed like a simple ask, but maybe not.
I could do it mathematically if I knew enough about Airtable to know where to do the coding, but, I’m trying to use the AI as it is intended.
Here is some info:
I asked Omni:
Can you give me a field list for the Assignment table?
Omni response:
Assignment Table Field List
Here are all the fields in the
Assignments table:
Core Assignment Fields:
-
Assignment id
- Auto-number (Primary field)
-
Job
- Linked to Jobs table
-
Crew Member
- Linked to Crew table
-
Assignment Start Date
- Date field
-
Assignment End Date
- Date field
Travel Date Fields:
-
Travel To Date
- Date field
-
Travel From Date
- Date field
Lookup Fields from Jobs:
-
Job Start Date
- Looked up from Job
-
Job End Date
- Looked up from Job
-
Job Type
- Looked up from Job
-
Origin
- Looked up from Job
Lookup Fields from Crew:
-
Crew Member Driver?
- Looked up from Crew
-
Crew Member Primary Lead?
- Looked up from Crew
Calculated Fields:
-
Assignment Duration (days)
- Formula field
-
Travel Days (Total)
- Formula field
AI-Generated Fields:
-
Assignment Overlaps
- AI text field
-
Assignment Summary
- AI text field
Other Fields:
-
Jobs
- Single line text field
For mathematical overlap comparison, you'd primarily work with:
-
Assignment Start Date
-
Assignment End Date
-
Travel To Date
-
Travel From Date
These date fields can be used in formulas to mathematically determine overlaps without relying on AI.
My request:
I need a report based on overlapping assignment dates for Crew. I need the report to do a mathematical test on the Assigned date range, from their start date to their end date for each crew member. I want the output from this report to fill a field, named "Overlap report" in the Crew table based on the comparison.
There are 3 conditions:
If there are no overlaps in the crew members assignment i want "No overlap"
if there are overlaps that are more than a single overlap of travel dates "Job Overlap" and the Job names.
If there is only a single overlap of travel dates then I want "Travel Overlap" and the Job names.
**************************
Custom instructions created in the report agent by Omni:
You are an expert scheduling analyst skilled at detecting overlapping job assignments for crew members. Your tone should be precise, professional, and clear.
Task description:
Carefully analyze all job assignments for the crew member to determine if any date ranges for their assignments overlap. Compare the start and end dates of each assignment against all other assignments for the same crew member. Identify every case where two or more assignments have any overlap in their date ranges, no matter how brief. For each overlap found, specify the jobs involved, the exact overlapping dates, and the assignments' full date ranges. If there are no overlaps at all, explicitly state that no overlaps are found.
Output format:
If overlaps are identified, provide a list detailing each overlap. For each, include the names of the jobs, the overlapping date range(s), and the corresponding full assignment date ranges. Write clearly using plain text, do not use bullet points or headings. Example: "Assignment overlap found: Job A (3/20/26-6/7/26) and Job B (4/7/26-4/13/26) overlap from 4/7/26 to 4/13/26." If no overlaps are detected, output exactly: "No assignment overlaps found." Do not include any other text or commentary in your output. Output should be comprehensive for all detected overlaps. (If there are many overlaps or assignments, the output may be longer.)
Context and Data:
Crew Member:
Assignments:
Output:
**************************
My entry about the mis-functional report agent:
Why doesn't it work correctly? I know that Jack Hayes is assigned to the Job Luke Combs from 3/20/26 to 6/7/26 and also 4/7/26 to 4/13/26. these date ranges overlap.
**************************
Omni response:
Based on the data analysis, I can see the issue with the Overlap report agent. Here's what's happening:
Current Assignment Data for Jack Hayes
Assignment 1:
Job: Luke Combs
Assignment Start Date: 3/20/26
Assignment End Date: 6/7/26
Assignment 2:
Job: Tortuga
Assignment Start Date: 4/7/26
Assignment End Date: 4/13/26
The Problem
The Overlap report currently shows "No assignment overlaps found" but there is clearly an overlap:
The second assignment (4/7/26 to 4/13/26) falls completely within the timeframe of the first assignment (3/20/26 to 6/7/26)
These assignments overlap from April 7-13, 2026
Why the AI Field May Not Be Working Correctly
The AI-generated field may have issues because:
Incorrect job information in your question: You mentioned both assignments were for "Luke Combs," but the data shows one is for "Luke Combs" and the other is for "Tortuga"
AI field logic limitations: The AI field may not be properly parsing the assignment date ranges or may have flawed overlap detection logic
Data structure complexity: The AI field needs to analyze linked records (assignments) and compare date ranges, which can be challenging for AI-generated fields
Since this involves assignments for different jobs (Luke Combs and Tortuga), this should be classified as a "Job Overlap" with both job names listed according to your original requirements.
The AI field needs to be updated with more specific instructions for properly detecting date range overlaps and categorizing them correctly.
**************************
Is there a way to just do the math and not rely on an hallucinating AI do check this simple condition?
