Change status based on number increment..

[Pcullc]

I have a app that is based on automobiles.  I have a column that users put their mileage in every time they get gas.  I can't seem to figure out how to send out a notification every time the mileage column hits an increment of 5000.  

[Joseph_Seddik]

https://help.appsheet.com/en/articles/4865326-bots-the-essentials

[graham_howe]

You could have a cumulative total in a virtual column which is of the form

 floor( sum(...) / 5000 )

This will always return an integer of the last multiple of 5000 that the cumulative mileage has hit. When the integer increases, then you know they hit another milestone.

[Pcullc]

Still can not get it to work..  floor(sum(mileage)/5000)). will not work..

[graham_howe]

Do you already have a running total or cumulative mileage column? Or are the users just entering mileage for a particular trip? I thought you already had such a column, in which case the expression I provided will result in an integer measure of each 5000 miles and as I said you could monitor that.

floor(sum(table[CumulativeMileage])/5000)

If you need to calculate cumulative mileage then you need to do something like this:

sum(
  select(
    table[Mileage], 
      and(
        [UserId] = [_THISROW].[UserId]),
        [ROWNUMBER] <= [_THISROW].[ROWNUMBER]
      )
    )
  )
)

[Pcullc]

Here is what I have for the cumulative mileage column..

MAX(SELECT(Expense[Mileage], and( ([Date] < [_THISROW].[Date]), ([Vehicle] = [_THISROW].[Vehicle]) ) ))

This calculates the previous mileage for the user when they enter the gas fill up screen.  Then they put their current mileage in.  How can I get a notification every time the previous miles hits an interval of 6000 miles?  I tried the 

floor(sum(expense[mileage]) / 5000

but came up with an error.  

[graham_howe]

What is the error, that can help resolve the problem. Also is [mileage] the cumulative mileage field or the field the user is entering their recent trip mileage? My expected logic is to look at the floor of cumulative mileage divided by 5000 and compare the floor of cumulative+trip divided by 5000. If the resulting integer has increased then the threshold has been passed and you can fire the notification.

[Pcullc]

Finally got the floor(sum(expense[mileage]) / 5000) to work, but in the column it comes up with 24,317.  Not sure what that was supposed to do, but I think it just divided...  Is it maybe supposed to send a notification when it finally reaches 25?  So in other words, it's kinda like a percent.. so were almost a 3rd through a 7000 miles oil cycle?  Also, I just figured out that there are 4 different vehicles as well, so I suppose I need to filter them by vehicles..?  Oh boy..  

[graham_howe]

Can you show your table structure, including the column you have for cumulative mileage? The result of the floor() expression is an integer, so unless someone has traveled around the earth over 4000 times, I think there's an error somewhere.

The purpose of the floor() expression was simply to count how many '5000' intervals had passed. You would use automation to monitor this and send the notification accordingly.

[Pcullc]

Not totally sure how I would get that table structure.

[graham_howe]

Just give a screen shot of the Columns, that's usually a good place to start. Also provide the expression you are using for the cumulative mileage column, I believe you said it was the following, but I wanted to confirm that:

MAX(
  SELECT(
    Expense[Mileage], 
    AND( 
      ([Date] < [_THISROW].[Date]), 
      ([Vehicle] = [_THISROW].[Vehicle]) 
    ) 
  )
)

[Pcullc]

I am forgetting the categories that are used for the expense tab.  That has been forgotten the whole time.  How would I add a column to that expression above?  Expense[Mileage] And([Category]=Auto,[Category]=Oil Change .... ?