Trying to calculate Service Mileage interval to trigger update request and reset counter

cbsarge

Hello everyone. I'm trying to build a solution to manage our fleet of equipment. One of the things I need to track/manage is mileage and when certain maintenance tasks should be done. We have the task grouped into A (7000 miles), B (14000 mile) and C (40000 miles). Our technicians will be performing a daily check of the vehicle through an update request. One of the fields will be the current mileage of the vehicle. In my sheet I have a [Mileage] field that receives the update info. I also have a [Group A Last Service Mileage] field that will receive it's mileage from another Update Request triggered by a workflow monitoring a third field called [Group A Service Mileage Counter]. This is a formula field with the following formula:

=IF(Mileage@row > [Group A Last Service Mileage]@row, +(Mileage@row - [Group A Last Service Mileage]@row))

The thinking here is when the technician processes the update request for maintenance the last service mileage will get updated. The issue I'm running into is I'm setting a check box to identify the need for service using the following formula:

=IF([Group A Service Mileage Counter]@row > 6900, 1)

I can then use that checked status to fire off a workflow creating the update request for maintenance. I was going to try to not be super fancy and just include text in the update request saying set the [Group A Service Mileage Counter] to 0 and uncheck the [Needs Group A Service] checkbox before submitting the update request. Unfortunately, the first time I tried it I realized that because both of these fields are formula fields, they were read-only in the update request.

I know this is a lot of text but, I'm hoping someone can offer advise on how to proceed. Thank you in advance!

David Jasven

To streamline the process of managing service intervals for your fleet without manual resets and without using the API, you can introduce helper columns in Smartsheet that semi-automate the tracking and resetting process. Here's how you can set this up:

### Step 1: Setup Helper Columns

1. **Current Mileage Input**: Continue using your [Mileage] column for technicians to input the current mileage.

2. **Maintenance Interval Columns**: Create separate columns for each maintenance type (A, B, C) that automatically calculate the mileage since the last service. For example, `[Miles Since Last A Service]`, `[Miles Since Last B Service]`, and `[Miles Since Last C Service]`.

3. **Service Due Columns**: Add a column for each service type to indicate when service is due, e.g., `[A Service Due]`, `[B Service Due]`, `[C Service Due]`. These will use formulas to determine if the mileage threshold has been reached.

### Step 2: Formula Setup

1. **Maintenance Interval Calculation**: In your maintenance interval columns, use a formula to calculate the difference between the current mileage and the mileage at the last service. For Group A, the formula in `[Miles Since Last A Service]` would be:

```

= [Mileage]@row - [Group A Last Service Mileage]@row

```

2. **Service Due Indicator**: In the service due columns, use a formula to indicate whether service is due. For example, in `[A Service Due]`, the formula could be:

```

= IF([Miles Since Last A Service]@row > 7000, 1, 0)

```

This formula sets the cell to 1 (true) if service A is due, otherwise 0 (false).

### Step 3: Reset Mechanism

Without using the API, fully automating the reset of the service mileage counter upon completion of maintenance is challenging. However, you can simplify the process with a "Reset" helper column.

1. **Reset Column**: Add a `[Reset A Service]` checkbox column. When this box is checked, it indicates that service A has been completed and the counter needs to be reset.

2. **Auto-Reset Formula**: Adjust the formula in your `[Miles Since Last A Service]` to account for the reset. This could involve using an IF statement to check if `[Reset A Service]` is checked, and then manually updating the `[Group A Last Service Mileage]` with the current `[Mileage]` value. Unfortunately, without manual intervention or API use, automatically updating one cell based on another cell's value is limited.

### Step 4: Workflow Automation for Notifications

- Use **Automated Alerts** in Smartsheet to notify the responsible party when the `[A Service Due]` column indicates that service is due.

- Similarly, set up an alert to notify when the `[Reset A Service]` checkbox is checked, indicating that the mileage counter needs to be manually updated to reflect the service completion.

### Limitations and Considerations

- The "auto-reset" mechanism described still requires manual intervention due to Smartsheet's limitations on automatically writing values from one cell to another based on conditions without using the API.

- Regularly scheduled reviews of the service due and reset columns will be necessary to ensure maintenance is tracked and recorded accurately.

This setup aims to streamline the management of your fleet's service intervals within Smartsheet's constraints, reducing manual input errors and keeping a clearer record of service needs and completions.

cbsarge

LOL - I guess my post wasn't super clear because what you're describing is what I'm already doing. I actually chuckled a bit while reading it thinking "this sounds just like what I'm doing". I had even started working on the reset portion using a second checkbox (maintenance complete) in the maintenance update request and a workflow that says when that box is checked to go ahead and reset the maintenance interval counter and check boxes. I'll have to test some more but, it seemed to work when I did a test daily check and bumped the mileage to get the counter over the threshold.