Need to calculate a notification when a unit is needing service based on a preset mileage

[Jack_Bates]

In the annualloadbook app I need to add a notification when a unit reaches a preset time for service based on the miles that the unit travels from the last service date.

I created a form to log an event “Full Service”, “Grease” The date migrates to a date field in the

{Truck} table [Date] Column and [Odometer] Column

The mileage is calculated in the

{DATA} Table [Loaded Miles]+[Empty Miles] and the [DATE] of each load

I need help with an  expression that will calculate the miles driven from the DATA Table [Loaded Miles]+[Empty Miles]. To start calculating the miles from the TRUCK Table ,[Last Service] “DATE Column” until it reaches the [Service Interval] “Number” EX: 15,000 miles. There I can trigger it to send the user an email notification

So {TRUCK} Table [Last Service] Date column starts down counting the {TRUCK} Table [Service Interval]

and use the miles driven from the closest date from the {DATA} Table [Date] column

(Clear as MUD)

[Suvrutt_Gurjar]

I think what you are looking for could be possible. However could you share some screenshots of relevant columns in the two tables and if those tables are related with referencing etc. so that the community can give a more appropriate solution. As the example below shows, the Order details table is child table of Orders table. The Order details table references the Orders table though the [Order ID] ref column and Products table through the [Product ID] ref column. Such screenshots and a little associated description will give a better perspective of your requirement to the community.

[Jack_Bates]

I need to start the Odometer when a in SHOP SEGMENT [Truck Repair Segment]or[Trailer Repair Segment]=“Service”

The [Last Service] in {TRUCK} (Needs to reference [Odometer] in  SHOP SEGMENT for the last service [Odometer] and start calculation from [DATE]

I need a way to track the miles from the DATA Table [DATE] [Empty Miles]+[Loaded Miles]

Then TRUCK [Miles since last service]-[Service Interval] Triggers a notification to user that a service is due.Load DataShop SegmentTruckTruck

[Suvrutt_Gurjar]

Thank you for the relevant details. One detail that you could mention is how the "Shop Segment" table is related to to the other tables. Is it also a child table to the "Truck" table?

[Jack_Bates]

Thank you for the quick reply

“Shop Segmant” is actually a child table for the “Expenses” Table

Expenses When “Service”Added columns when “Service”ExpensesShop segment

[Jack_Bates]

“TRUCK” Table is Referenced in all of the tables

“DATA” is load data and the only place miles are tracked. [Empty Miles][Loaded Miles]

”Expenses” Table is parent to “Shop Segment” Table and ref. [Id_truck]

“Shop segment” Table and form only shows when “Repair” “Service” is chosen

[Jack_Bates]

This is working great for “Inspections” because it tracks (TIME) and its working.

Services track miles for a selected miles by user based on how many miles the truck ran by the date of the “Last Service” column

[Suvrutt_Gurjar]

Thank you. The referencing is indeed substantial and good to know there is an Expenses table in between that was not described earlier. This missing link could have probably impacted the solution?

Based on several screenshots shared by you, I have tried to summarize the tables by their reference relationships and relevant columns for an ease of discussion reference in the image below.

Could you please restate your requirement from the summarized image above? I believe that will help to understand the exact requirement and what issue if any you are facing better.

[Jack_Bates]

DATA: Looks right

TRUCK: [Odometer] is the starting Odometer for that unit when added [creationdate]

[Last Service] Changed to (DATE) of last service 

EXPENSES: [Truck] (REF), [Expense]=“Service” [DATE] of “service”

SHOP SEGMENTS: [Odometer] of service, [Trailer Repair Segment] or [Truck Repair Segment]=“Service

[Last Service] (DATE) in TRUCK is updated from Table EXPENSES [DATE] when  [Expense]=“Service” [Truck Repair Segment]or [Trailer Repair Segment]=“Service”. Miles are calculated in DATA [Empty Miles]+[Loaded Miles], start date(Date of [Last Service]), end date TODAY()

[Miles Since Last Service] is the difference between the Calculated Miles from DATA and Shop Segments [DATE] and [Odometer]

[Service Interval] is set by the app user 

[Service Overdue] Y/N is N when serviced and changes to Y when [Miles Since Last Service] > then [Service Interval]

[Jack_Bates]

I did this and its calculating the mile that were run for the time frame. But it needs to Sum them up. 

I made a virtual column in DATA that adds the [Empty Miles]+[Loaded Miles]

ExpressionResult

[Suvrutt_Gurjar]

If [Total Miles] is a numeric column such as number, decimal, etc. you can simply wrap your SELECT() expression with a SUM(), something like

SUM(

             SELECT(DATA[Total Miles], ...... NOW()))

           )

About your overall query,  I was away from my work desk yesterday and from tomorrow for week or so , as I am in travel. So I am sorry that I will be mostly unable to respond to requests that need spending some time to test the suggestions. I may respond to smaller queries that can be responded on mobile.  

Of course, this is an active community with several members like me volunteering. So please be assured someone else will respond.

[Jack_Bates]

Thank you I had the [Miles since last service] as a list in the TRUCK I changed it to a (Number)

It now calculates itResultExpression

[Jack_Bates]

Testing it 

It isn’t calculating right

Units with no loads or a set date for service are showing high miles since last service they should show none

other units with lots of mileage data show 0 miles

[Suvrutt_Gurjar]

I think in general, you will need to exclude / include all such records by putting the necessary conditions in your SELEC() expression. 

[Jack_Bates]

Not sure what I’m missing

I need to SUM(SELECT(DATA[TOTAL MILES] From [Last Inspection] (DATE), to current DATE

[Jack_Bates · Accepted solution]

I believe that it is working now. Thank you for the conversation that’s what I needed to get on the right track.

SUM(SELECT(DATA[TOTAL MILES],[id_data].[DATE] > [_THISROW].[Last Service]))ResultExpression

[Suvrutt_Gurjar]

You are welcome. Nice to know you got it working.

[Jack_Bates]

This is what it looks like in the app

[Jack_Bates]

The expression is working great.

I made an automation to send an email to the app user to let them know when a new service is due, or if an inspection is due.

I added a condition before it fires the Bot

[Days Since last Inspection]>[Inspection Interval]

Problem I’m having is it sends an email to the app user if any change is made to the {TRUCK} tableThe BOTThe expressionThe email