confused about Data Validation & prefilling values; now getting errors "Cannot compare List with..."

[SparklyGeek]

Hi, everyone. I'm working on my very first AppSheet project, and am finding it a lot trickier than all the YouTube videos I've watched made it look. I'm trying to use it in a project for which I'm currently using Google Forms, Sheets, and Apps Script, and to which I will add G Calendar. Probably some day I'll want to turn the whole thing into an app, but for now I would be very happy just to get AppSheet to take the place of Google Forms. The inability to access answers from a Google Form prior to the User's submission has forced me to split the intended form into two parts connected by an email with a link to the second part with certain values pre-filled, along with another McGyver I am unhappy with.

This is no doubt the first of several posts I'm going to make. Not looking for anyone to design this for me, but I need help making sense of the error messages I'm getting and how to react to them, and I would like to ask questions as I work through this part of the project. Open to big-picture questions and suggestions.

(sub)Project Plan:
I'm afraid my first question is going to require that I first explain the project, or at least this Form section of it. The project is for a moving company, and the Form aspect is a form which is sent to the Crew Leader of a moving job to fill out and occasionally pass the customer.

By selecting their initials from drop-down menus in answer fields, the customer acknowledges the times the crew arrived and completed the job, and if they took lunch, when they started and finished. From these figures is computed the total time the crew worked, and together with a few other figures that are either entered by the Crew Leader, or pre-filled in the Sheet before the day of the move, a total charge is computed.

The customer than fills in how much they wish to tip, and how much they are paying by cash and/or cashier's check, and signs their name in a signature field.

Finally, the Crew Leader has the option to add notes and photos pertinent to the address the customer is moving to, so that in the future any moves from that address may be planned with information of any complications such as narrow staircases, etc. which may require more time or crew members.

The main Sheet governing the form is named "moves." It is intended to be where all information pertinent to all move jobs past and future is kept, along with its supporting Sheets "customers", "addresses", and "crew." Upcoming moves are differentiated from past moves by the "Status" column; past jobs are marked "completed" here, and when the form is submitted, the value here for the appropriate row is to change to "completed." 
"moves" Tab
It may be indexed by a combination of "Customer Name" and "Date" columns, since the same Customer may appear more than once and there will certainly be multiple moves on many of the Dates, but we can be sure that no Customer will move twice in the same day, so each Customer Name-Date combination is unique. For the AppSheet version, I have added a column that concatenates the two into one, and I am using this single column for the Label instead of the two, and formItemNumber for the Key (does that sound like the right thing to do?)

The form must interact with the Sheet in several ways:

1.  It must be able to take pre-filled values from the Sheet for its answers when those answers have already been entered in the "moves" Sheet, and leave fields blank otherwise. The Crew Leader must be able to overwrite these values if necessary.
2. All of the Data Validation in the drop-downs must be updated with every form submission, and the values are to come from the appropriate columns of the "moves" Sheet, filtered by the Status column so that Completed jobs are filtered out and only upcoming job info will be included. These drop-down answers are the Customer Name - Date combo and the customer's initials, which appear in four answers on the form.
3. All of the pre-filled values are to come from the row associated with Customer Name-Date combo and should change when the Crew Leader selects a different combo. This will be the first field on the form. The pre-filled values will always include the addresses, and will usually include information such as the per-hour rate of the job. 
4. When the form is submitted, the answers are to be copied to the appropriate cells of the Sheets "customers" (URL of signature pic), "addresses"  (the optional notes and URLs/IDs of pictures), and "moves" (everything else.) Relevant values will also will be sent to a separate Sheet from which an invoice PDF may be made and emailed.
5. Finally, as I mentioned before, the Status of the move must be changed to "completed" in the Status column.

Where I'm at and where I'm stuck

I created a new Sheet named "form", which I'm using as the basis for the View in AppSheet.

I think I made a wrong turn here, but made a sheet called "dataValidation." to filter out the completed jobs. But instead of using it, I'm trying to use AppSheet's built-in data validation.

I've managed to get pre-filled values in the form using expressions like 
lookup([_thisrow].[Customer Name And Date],"moves","Customer Name And Date","Address From")
However, the results are not editable by the user even though I have marked them "editable."

My Customer Name and Date and Customer Initial fields are not excluding the completed moves; here is where I'm trying to use the built-in data validation, and am having problems.

Specifically, I'm now getting these error messages:

Column 'Address From' in Table 'form_Schema' of Column Type 'Address' has an invalid expression in the Formula field.'=lookup([_thisrow].[Customer Name And Date],"moves","Customer Name And Date","Address From")'. Cannot compare List with Text in ([Customer Name And Date] = [_thisrow].[Customer Name And Date])

and so on for all of the fields I added the lookup expression to.

Here I'm embarrassed to admit that I seem to have lost track of the last thing I did, because I have repeatedly tried undoing it, without success. I thought the last thing I did was change the Type of Customer Name and Date from "Text" to "Enum", but I've changed it back, and tried a lot of other values, and nothing will work. My attempt to add data validation to a field was not even saved, so that can't be it.

Any idea what I'm doing wrong?
Thanks for your help! 

[Dohman89]

About the only thing i can help with is the undoing part. You can go back to a previous version in the app settings just like you can with google sheets. Also if your sheets changed format, columns names, number of columns, or something else in anyway that can/will cause issues.

i guess i can make an attempt at your lookup problem, since i also run into the issue and dont know why. But if I use the SELECT() expression it will work when LOOKUP() wont the majority of the time. the biggest problems i have are with preset dates and idk why. So my workaround was to textjoin() the date/time with some other descriptive column value in sheets, then in in appsheet i had type=enum basetype=text. I also used suggested values instead of initial values.

Dont know if any of this will help, but i havent gotten any help here with my questions, so figured i would make an attempt.

[SparklyGeek]

Thanks,  I did not know there was a revision history (it's been removed from GAS, as you probably know) and I was able to restore a version from a few days ago, so now it's functioning again. I see here the value of Customer Name + Date is Enum, but that is one of the values I tried earlier, so it must have been something else.

Have you been able to prefill values and still allow the user to override them?

Sorry you haven't had luck getting questions answered here. There seems to be a great lack of AppSheet Groups on Facebook; if this forum isn't helpful I guess I'll try Stacks next.

[Dohman89]

i have not been able to get initial values to prefill things that have multiple values, seems to just take the first value that meets the criteria. But i have been able to get suggested values to work, but not everytime. But that could be because of the data im using and trying to accomplish. essentially i have suggestions based off the first selection, then suggestions based off of that selection, then i wanted suggestions based off of that but it wouldnt work with the last one.

this is the first formula for the first selection.... type is enum with base type name

select(summary[title],[Team]=[select team]) ...... this creates the value for "TITLE"

ths second selection is TYPE date/time.

select(summary[Date/time],[title]=[_thisrow].[title])

For some reason comparing text to a list is a problem so i feel like you have to find a type that will work, idk why since if i think about it in a literal way that is exactly what a list is for.

[Dohman89]

So i figured out a workaround for how to generate a list of dates. In your Google sheet creat a column to format your column containing dates. use =arrayformula(text(A1:A,"mm/dd hh:mm AM/PM"))

substitute the actual range for the A1:A. Then have that column be formatted as plain text. Then in appsheet you should be able to generate a list of the dates.

[SparklyGeek · Accepted solution]

I have solved the problem. I had made several mistakes and wound up redoing a great deal of the app. I'm not sure I can even remember everything I did since I made this post, but the most crucial thing was under Auto Compute: I left the App Formula field blank and used the Initial Value field instead. Here, I used formulas like
lookup([_thisrow].[Customer Name And Date],"moves","Customer Name And Date","Address To")

Other crucial changes I made included removing every single formula in the Moves Tab, and getting rid of that Forms Tab that was duplicating info from the Moves. This was superfluous and only created confusion for me; there were places where I was trying to use functions like lookup, but was using the wrong Tab for the reference.