How to get the ending miles to automatically filling the starting miles entry

[MarkEMark2]

I am building a Mileage app to track miles per gallon. It’s a very simple app, and I am using a date column, a starting and ending miles column, gallons used, etc.

I want the value of the previous dates ending mileage to populate the new date’s ‘starting miles’ box in the new entry dialog box. I’ve looked for ways to do this but have not figured it out. Can you point me in the right direction to this?

In summary, the ending miles from the last fueling stop should automatically fill in the new “starting miles” box on the next new fueling stop. How should I do this?

Thanks for the help!

[Lynn]

Hi @MarkEMark2
maybe try something like this

Previous registration to get the difference Questions

If you type this formula LOOKUP(MAXROW(“TableName”,“Date”,[Code]=[_THISROW].[Code]),“TableName”,“KeyColumn”,“Current Reading”) into your initial value, it will read the previous reading when new record is created. Please use your own table and key column name with the formula.

[MarkEMark2]

Also, is there a reference list of functions and syntax for AppSheets somewhere? I didn’t see the LOOKUP() function in amy of the expression builder areas. I’m wondering what is available to use as programming language and where to find that info.

Many thanks again!

[MarkEMark2]

I will give that a try. It’s the starting point I needed I believe! Thanks!

[Lynn]

Hi @MarkEMark2 There is some good documentation here.

help.appsheet.com

Expressions | AppSheet Help Center

How to use expressions to change the behavior of your apps.

[MarkEMark2]

I am having trouble with the expression formula returning a value in the form when I run the form. Here’s the format of the table “Mileage” that I am running the LOOKUP as an Initial Value formula (where Date is the Key). These are the relevant column headers in the table:

Date - Starting miles - Current miles

The goal is to have the form pre-fill the new fueling stop’s dialog box with the last fueling stop’s Current miles data as the NEW fueling stop entry’s Starting miles dialog box. This should be editable in case one fueling event was missed along the way and needs to be added manually.

Here is the format of the formula I have adapted based on the one you provided - I have spent a while studying the structure and believe I understand each of the arguments in the process. I am putting this formula in the “Initial Value” formula box on the “Starting miles” data line in the data editor.

My problem is that while the formula gets “accepted” by the verification process, it does not return the value into the “Starting miles” dialog box when I go to add a new fill up. Here’s the formula as I have adapted it:

LOOKUP(MAXROW(“Mileage”,“Current miles”,[Current miles]=[_THISROW].[Current miles]),“Mileage”,“Current miles",“Current miles”)

If I understand the syntax of this correctly (which evidently I don’t), this should tell the LOOKUP command to:

Find the row in the Mileage data sheet with the maximum value from the column “Current miles” and copy the data in the Current miles column from that row and place it in the data set “Mileage”, in the column “Starting miles” since that is the place the LOOKUP formula is located in the Initial Value spot.

I based that assumption on reading this formula for the LOOKUP() function:

LOOKUP(find-value, in-dataset, in-column, return-column)

Where the “Find-Value” is MAXROW(Table, Column, reference)

Attached is a screenshot of the spreadsheet. The formula I am working with is above.

QUESTIONS:

1. Is the _THISROW part of this equation referencing the new line in the spreadsheet (in which case the line would be blank thus returning a blank value) or is it referencing the line found with MAXROW(). I think it is the latter, but since the new row is blank might be what I am seeing: a blank return.

2. Am I interpreting the return-column argument correctly: It is the location for the value of the identified search - in this case the “starting miles” of the new dialog box.

Sorry for the long post question. I am trying to learn the program. Any suggestions on a video series or other aids I can use to learn on my own would be appreciated!

Mark

[Marc_Dillon]

MarkEMark2:

LOOKUP(MAXROW(“Mileage”,“Current miles”,[Current miles]=[_THISROW].[Current miles]),“Mileage”,“Current miles",“Current miles”)

Your third argument in MAXROW() is probably not necessary. You’d use that third argument to further restrict which set of records to pull the max-valued row from. You’d use that argument, for example, if you were tracking more than 1 car.

MAXROW() returns a Key value. So the 3rd argument in the LOOKUP() expression needs to be the Key column of the indicated Table.

In the case that you really are only tracking a single vehicle, it would probably work to use a much simpler:
MAX( table[ending-mileage] )

Disclaimer: I did not read your posts extensively, just skimmed them.

[MarkEMark2]

Also, here is what the Expression Assistant returns when I enter the formula:

Note, this expression could significantly impact sync time. One randomly chosen value from this list

( …The list of values of column ‘Current miles’ …from rows of table ‘Mileage’ …where this condition is true: ((The value of column ‘Current miles’) is equal to (One randomly chosen value from this list ( …The list of values of column ‘Date’
…from rows of table ‘Mileage’
…where this condition is true: (ALL these statements are true: …1: (The value of column ‘Current miles’) is equal to (The value of ‘Current miles’ from the row referenced by ‘Date’)
…2: (The value of column ‘Current miles’) is equal to (MAX( …The list of values of column ‘Current miles’ …from rows of table ‘Mileage’ …where this condition is true: ((The value of column ‘Current miles’) is equal to (The value of ‘Current miles’ from the row referenced by ‘Date’)))))))))

The problem is I am not referencing “Date” in the lookup… Is that because it is the Key in the data set? It looks to me that the system is looking for the value in the Date column and not the mileage column. Or am I wrong?

[MarkEMark2]

Thank you Marc. That seems simpler.

When I replaced the formula in the Initial Value field of the Starting Miles assistant the system accepted it with no issues. I saved the form and tested it in the emulator and the “Starting miles” field is no prepopulated with the mileage from the “Current miles” value in the column on the spreadsheet as I was hoping.

How do I get the value of the spreadsheet “Current miles” to prepopulate the form when I click the + sign to start a new entry?

Thanks for the guidance! I have really been playing with options here to get it to work and feel stumped.

[MarkEMark2]

Update: It appears the formula Marc provided works for the pre-population of the “Add” button on the screen when I place it in the Auto Compute “App Formula” field, and not in the “Initial Value” field (Thanks Marc!).

So, if I wanted to edit the Starting miles in the form (if for instance I forgot to record the mileage from a previous fill up) the form wouldn’t let me edit the number since it is in the “App Formula” field. Not a deal killer, but I am trying to learn why the field will not pre-populate in the same way when using the “Initial Value” part of the form. I even tried turning the “Editable” switch setting off and it still didn’t work. However, the formula Marc suggested worked perfectly in the “App Formula” field. Any thoughts?

Thanks for the patience in walking me through this. Hope not to post so many questions as I move along!