Fuel Price Tracker

[longdoanduc]

Hello, 

I'm building an app to manage logistics using AppSheet. I'm struggling in creating a feature that can calculate the total price when our truck refuels. I already have a formula in a spreadsheet that calculates the price based on the date range. For example, the price is $1 on January 1, 2023, and it changes to $2 on February 2, 2023. So, any day between January 1 and February 2, the spreadsheet will automatically use the price of $1, and starting from February 2, the price will be updated to $2.

I tried using the "Spreadsheet formula" but it doesn't automatically update the row number 

Thanks for any tips

[WillowMobileSys]

First, I don't recommend using sheet formulas in this instance.  In fact, I don't recommend using them at all as they create complications should you ever decide to move your app to a database.  Think of it as a soft of future-proofing the app.

You can create this functionality in AppSheet with the aid of a simple lookup table that retrieves the fuel price - aptly named "Fuel Prices".  The table would/might have the following columns:

• Fuel Price ID - a random row key 
• Fuel Type - Diesel, Unleaded, Regular, Jet Fuel, Bio-diesel, etc.
• Start Date - Date the Price started
• End Date - Date the Price ended
• Price - Amount per gallon of fuel  (assumes all fueling is by the gallon)

Then in the app in your Form where you enter the refueling details, I would assume you have the Fuel Date, Fuel Type and the Number of gallons.  With these details, you can calculate the "Fuel Cost" with an expression like this:

[Number of Gallons] * ANY(SELECT(Fuel Prices[Price], 
                                 AND([Fuel Type] = [_THISROW].[Fuel Type], 
                                     [Start Date] <= [_THISROW].[Fuel Date], 
                                     [End Date] >=[_THISROW].[Fuel Date]
                                 )
                          )
                      )

 The SELECT() function performs the lookup and retrieves the correct Fuel Price row grabbling it Price column.   We extract the Price value using the ANY() function so it can be used in the calculation.

I hope this helps!!