Initial Value in Dropdown is same as last entry (learning alternatives to VLOOKUP)

[L_Hamilton]

Hello! Just started to use Appsheet so apologies if this is very obvious.
I’m making a very basic calculator for the cost of bus transfer tickets in a remote area. People board and pay in groups instead of singly, and there’s currently only a CC machine on board, which doesn’t calculate prices or record the individual sales.
For the driver’s convenience, the Initial Value for the Date is set as TODAY().
There are several possible Departure Times which are selected from a drop down menu. But for the driver’s convenience it will be great to have the Initial Value in the Departure Time field to be the same as the the last entry, so they don’t have to select it every time.

Can anyone point me where to look for guidance here, or just what search terms I should use?
Thank you in advance?

[hru]

L_Hamilton:

to have the Initial Value in the Departure Time

What if use TIMENOW() as initial value?

help.appsheet.com

TIMENOW()

Current time

[L_Hamilton]

Good Morning Heru,
I did think of TIMENOW, but the bus is set to depart at regular hours, so I want only these times to be used. But thank you for taking the time to reply.

[Suvrutt_Gurjar]

L_Hamilton:

There are several possible Departure Times which are selected from a drop down menu. But for the driver’s convenience it will be great to have the Initial Value in the Departure Time field to be the same as the the last entry, so they don’t have to select it every time.

I believe you may wish to add more details.Could you please elaborate what you mean by “Departure Time field to be the same as the the last entry” You may wish to make it clearer by “last entry” , do you mean previous arrival time ,for example?

Also these entries may be driver specific? Meaning “last entry” for one driver may not be same as that for the other driver? Also "last entry " at the beginning of the day may be some default value?

In general , you may wish to explore an expression based on MAX() function in your initial value for last entry.

help.appsheet.com

MAX()

Highest item in list

[L_Hamilton]

Good Morning Suvrutt and thank you for your reply.
This app is just for one driver, on one bus going back and forth short trips everyday; just to save them making a tally on a clipboard, until the new POS system comes. So they aren’t driver specific, but thank you for going ahead to think of that.

What made me stumble was instead of VLOOKUP we have LOOKUP in Appsheet, sorry, very new user! In Google Sheets =vlookup(max(A:A), A:C,3,false) would be my idea to show the last input departure time.
So now I have LOOKUP(MAX"Timestamp",“Paxes”,“Timestamp”,“Dep Time”) in the Initial Value Column for Departure Time, but still I’m doing something wrong as it’s not filling the Departure Time field on the app. Any pointers?

PS Probably I’m being the typical Excel user, over dependant on vlookup! Thanks for helping me to move disciplines

[Suvrutt_Gurjar]

Thank you for the details.

Could you please try

MAX( Paxes[Dep Time])

or following expression

Edit:
If the table has multiple day entries, for making it day specific for today, could you please try an expression something like

MAX(SELECT(Paxes[Dep Time], DATE([Timestamp])= TODAY()))

[L_Hamilton]

Hello again!
I’ve put in as you advised, and got this error message.

If it helps, here is the interface the driver will see - you can see more clearly why it will be handy for the driver to have the Departure Time preset as the groups board. Perhaps there’s another way?

[Suvrutt_Gurjar]

Is the column [Dep Time] of type TIME ? The above mentioned expressions should work if the [Dep Time] column is TIME type.

Its sounds that [Dep Time] is of ENUMLIST or ENUM type with base type as non numeric?

Could you please update what are the possible values it can have and what is the base typeof that column?

If it is not TIME type, then I believe the initial expression will need to be build around the Timestamp column.

It will be an inefficient expression something like below for a only today specific latest dep time.

LOOKUP(MAX(SELECT(Paxes[Timestamp], DATE([Timestamp])=TODAY())), “Paxes”, “Timestamp”, “Dep Time”)

If there are only single day ,that is current day entries in the table ,then the expression could be a bit simpler such as below
LOOKUP(MAX(Paxes[Timestamp]), “Paxes”, “Timestamp”, “Dep Time”)

Edit: please ignore above. I mistakenly put the test expression I used. I will repost with correct table and column names.
Edit 2: Please consider the modified expressions mentioned above.

[L_Hamilton]

Suvrutt_Gurjar:

LOOKUP(MAX(SELECT(Paxes[Timestamp], DATE([Timestamp])=TODAY())), “Paxes”, “Timestamp”, “Dep Time”)

Oh excuse me! While I was writing, you made an edit
The 12 clock is showing in the preview, but not on the phone app. Weird, but the driver will not mind that.

Now the Departure Time field is prefilled, however, it is prefilled with ‘Invalid Date’. And I just noticed that there are a lot of Invalid Date options at the bottom of the dropdown list, which aren’t sown in the ENUMLIST in the Dep Time field. Really grateful if you can shed any light!

[L_Hamilton]

Hello Suvrutt
You are correct, [Dep Time] is an ENUMLIST. I already knew that in excel MAX will not work with text values of course, so, I had changed the base type to Time.
Your mentioning the base type made me wonder, perhaps since I copy pasted them from another list which was in AM/PM format, that perhaps it would work if I changed them to in 24 hr (that is always a problem in Excel). Noe I have them all in the format hh:mm 24h, but, as you see they are still appearing the in drop down list as 12h.

[Suvrutt_Gurjar]

Could you please make the “Dep Time” as Enum type with time as base .Then I believe earlier expression should work.

It need not be enumlist type. Enumlist allows more than one value to be entered in one field and constructs a list. I believe driver needs only one time value at a time , so enum type should be ok.

MAX(SELECT(Paxes[Dep Time], DATE([Timestamp])= TODAY()))

or the below

MAX( Paxes[Dep Time])

[Suvrutt_Gurjar · Accepted solution]

I believe since the "dep Time " is a range of selections from predefined values, you will need a LOOKUP() expression as mentioned before.

So please try with “Dpe Time” as Enum with base type as time and any of the LOOKUP ()expressions mentioned above.

[L_Hamilton]

Dear Suvrutt,
I changed ENUMLIST to ENUM and it does work There is now quite a long list, with some times shown twice, but that is not going to bother the driver at all, he won’t scroll further than he needs to. Much better to have it prefill the Dep time being currently used so he doesn’t need to select it for each group, only for once at the beginning of that departure times boarding.

I think this will work fine until we get the new system, and I would like to thank you for your help. Obviously lots more for me to learn. Appreciate your time!

[Suvrutt_Gurjar]

Thank you for the update. Good to know it works now.

Yes, AppSheet is great for business apps as you will experience. We all have initial learning cycles on any new system. All the best.