Set up a targets in xls form builder where a notification pops us on a multiplication of 5000 KMs

Hi community, I need assistance in a vehicle movement form am building. We have a fleet of ambulances where we have servicing at certain points and where we record movement in KM from point A to B… i would like to set up like lets say servicing of vehicle is in the next 5000km. How would i set up in where every KM movement that is recorded subtracts from the 5000km so that we can get to know when the next service is approaching soon? even a notification like lets say its 500km remaining to get to servicing. Your response would be highly appreciated.

Do you have a sample of how the vehicle movement log you have at present.

Hi, I am not sure how you plan to sum all the distances travelled across multiple days but the calculation you want for the remaining kilometres until next service is:
5000 - (${total_km} mod 5000)

The results of the calculation could be displayed in a note. In an example of an ambulance having travelled 16,500km it would calculate as 16,500 mod 5000 = 1500 and then then output 3500.

1 Like

At the moment the logs are done manually then have it recorded on an excel as seen below. So we were looking for a way where it reminds the logistics officer when the next service is due based on the movements made each time as seen.kobolog

This will be summed up the the vehicle operators where they record their movement in a journey log which I managed to build that calculates distance covered from point A to B… so each KM covered is like a threshold to the targeted next mileage service. I hope its clear now?

@NoelCartONG how would that formula appear on the xls form? I would like to try that formula out and see the outcome

Hi @jkiruri,

Looking at the sample provided, maybe you could design your xlsform as outlined in the image below:

In the survey tab of your xlsform:

Data entry screen as seen in Enketo:

Reference xlsfrom:

Vehicle Log.xls (24 KB)

Note: This is a sample that capture your service duration till 25000 KM. If you wish to have more than that you will need to amend the if statement.

Thank you @Kal_Lam this works perfectly. But just wondering, since we service our vehicles after every 5000km, much of our fleets are well over 100,000 km. How would the system calculate this since if I add the formula, if((${cal1}=1),‘5000’ until over 100,000km wouldn’t be wise. Because if I have a vehicle currently going to servicing and the mileage is at e.g. 146075, the formula will not work.

Hi, there should be no need for if statements. I modified the above example to include the simplified calculation

Vehicle Log.xls (24.5 KB)


@NoelCartONG, Thank you for the response this works fine. But I have attached an image showing my scenario in terms of vehicle movement. We have the opening KM and Closing KM then the Total KM covered for that movement, how will that total KM be deducted on the balance on next service and enable that figure remain as it is when the form is opened up the next time by each driver where the last figure is as it was when the form was closed?

Hi @jkiruri
What you are looking for is a dynamic reference to previously entered data. As it is, this is not straight up the functionality of the system. The system is not designed to process longitudinally referenced data. To be more specific, once you have collected the data i.e. the driver has filled in the form, the next time they fill in the form there is no way to link that later filling to the previous answers they entered automatically.

As such, you will need to create your form with pull data functionality which requires that you process your data to create a CSV of the data which can be pulled into the next filling of the form. As you may have noticed by now, that this requires that all previously filled forms are submitted and someone process the data to ensure that this is updated. This requires complex form design and real-time submissions to work. I, therefore, suggest that you try it out if you do have the necessary skills. If not, you could source someone to help you create this via our job board

All the best

1 Like