Solved: Auto-fill Form from text value

Nicolas_Feldman · 06-18-2020 02:16 PM

I was wondering if you could help me with a little project I have. Im testing this automated Check-in app for a small hotel but im struggling a bit with the “dereference” formula.

This is the thing, I have two tables, one with all the information of my guests, and another table, which is a Check-in Form.

So basically, what im looking for is that when a guest checks-in, all they have to do is download the app and fill the form for check-in. I’ve gotten this far with no problem.

What I want to happen and haven’t been able to do so, is basically when they TYPE their name on the first question, I want some of the following questions (booking number, checkin and checkout date, and booking channel) to be filled automatically. This info can be fetched from the reservation table.
The name or “key” column has to be “typed” as I don´t want users to know the name of other guests.

There’s more info in the Check-in form also that the guest should actually fill in manually such as ID image, and signature, I guess with this ones I won’t have a problem.

Steve

Wrap the SELECT() expression with ANY(), then DATE(), like DATE(ANY(SELECT(...))).

AND

Change the Check-In column type from Text to Date.

View solution in original post

Austin

Use an Initial value that is a lookup based on this name column. Once they fill out the Name, the other fields should auto populate. If these fields don’t ever need to be changed by the user then you should hide them and also put the expression in the formula field instead of initial value as this disables edit by default. If they might need to change those values then don’t fill the formula field.

Lucinda_Mason

Something else to consider, besides the very helpful advice from Austin. Will you have repeat guests? If so, consider having them type in their booking number, use as a key, and their last name, for security so someone can’t just enter random numbers to try to get a match.

If you just use their name, you could end up with multiple records being pulled.

Nicolas_Feldman

That´s a great point I missed. Thank you Lucinda. I guess what I could do, is once they type their full name, they can get an autopopulated drop down of reservation numbers where they can select the correct one.
I will try this and keep all posted.
Thank you!

Nicolas_Feldman

I wasn´t able to get more than one result with LOOKUP formula, for example for guests that have more than one reservation, its only showing the first reservation on the table and they should be able to select the actual reservation.
I´ve selected the Row Type as “List”, but still not working. Any ideas?

Steve

Nicolas_Feldman

Thank you Steve, both options worked and filtered the reservations under the name of the guest. But they can only select one reservation. My reservation system handles only one cabin per reservation, so a guest can have one or more reservations for the same dates if they booked more than one cabin.
What would be a good way to let them select more than one reservation?
Most of the columns that are auto-populated after the guest selects their reservation would have the same info: such as “Check-in” and “Check-out date”, but other columns such as “RoomName” and “NoPeople” could change.
Is there a way to add all the results for each column into one cell separating the values with comas? Or how would this work?

Thanks again!

Steve

Change the column type to EnumList, which allows multiple selections. Note, though, that you’ll then have to change your approach to using the selections since there may be more than one.

See also:

Nicolas_Feldman

Hey Steve, thanks a lot! I used EnumList and refered to the “Reservas” table and it worked, so now users are allowed to select more than one “Booking number”.
Now, I am working in the selection formulas for the next data fields to be autopopulated.

I am trying with this two formulas and having the same error:
FILTER(“RESERVAS”, ([Booking number] = [_THISROW].[ID Reserva]))
SELECT(Reservas[Status Reserva], ([Booking number] = [_THISROW].[ID Reserva]))

Error displayed:
Cannot compare Text with List in ([Booking number] = [_RowNumber].[ID Reserva])

I tried changing the column type to list, but tahts only possible for virtual columns. Tried also to make it a Ref column, but that didnt work either.

Any ideas?

Steve

I expected this, which is why I posted those doc links.

Also, you shouldn’t be using _ROWNUMBER as the key column.

Nicolas_Feldman

Hey Steve, thanks for the help, so I refined my formula:

SELECT(Reservas[Status Reserva], IN([_THISROW].[ID Reserva], RESERVAS[Booking number]))

Now I am getting a list of all the values from “Booking number” column. It should be selecting only those where the conditions is met: IN([_THISROW].[ID Reserva], RESERVAS[Booking number]))

Any ideas on what could be wrong??
The formula seem to work fine.

Steve

You are correct to use IN(), since you want to know if a single value is in a list of values. The problem appears to be you’re looking in the wrong list.

I have to guess that ID Reserva is the list of booking numbers the user selected? If so, you want to ask if the booking number of each row in the Reservas table ([Booking number]) is in that user-supplied list ([_THISROW].[ID Reserva]😞

SELECT(
  Reservas[Status Reserva],
  IN([Booking number], [_THISROW].[ID Reserva])
)

Nicolas_Feldman

Thank you Steve, that worked for most of the columns, used ANY() for those that had repeated data, and let the SELECT() formula by itself for those columns with variable data.

The problem I have now is that for the “check-in date” the result im getting is this:
“Sun Jun 02 2019 00:00:00 GMT-0500 (hora de verano central)”

Any idea on how to format that SELECT result back into date format?

I tried wrapping the formula in DATE(), the formula seemed to work but it didn´t bring up any result back.
In the google sheets table I have two columns from where I can pull the check-in date, I have configured those in different dates format to see if any would work and tried them both, but had no luck.
So i guess the answer is in the formula.

Thanks again Steve! You are very kind in helping out the community.

Steve

Please post the following screenshots:

The complete SELECT() expression that produces the undesired result.
The configuration of the column to store the result from that SELECT() expression.
The configuration of the column whose values that SELECT() expression is getting. For instace, in SELECT(Reservas[Status Reserva], ...), it would be the Status Reserva column of the Reservas table.

Nicolas_Feldman

Hey Steve, im sorry I thought I did copy the formula on my last comment.

screenshot 1 - expression with “undesired long date format” :

screenshot 2 - 1. The configuration of the column to store the result from that SELECT() expression.

screenshot 3 - The configuration of the column whose values that SELECT() expression is getting.

screenshot 4 - formula wrapped in DATE() wat does is that the column doesnt even show in the app although the formula is valid.

Thank you again Steve for taking some time to help out!

Nico

Steve

Wrap the SELECT() expression with ANY(), then DATE(), like DATE(ANY(SELECT(...))).

AND

Change the Check-In column type from Text to Date.

Nicolas_Feldman

Great Steve, it worked. I´m not sure why…
If you don´t mind, can you explain to me please why would ANY() help format the date?

Steve

SELECT() produces a list of values. It may be a list of only one value (or even none!), but it’s a list. ANY() pulls out the first item in that list, producing a singular result, which is what you’re looking for. DATE() then converts the DateTime value gathered by SELECT() into just a Date.