Column disappears in form view and always show invalid data validation,

[Ian_Li]

Hi, I am new to the appsheet. I first thought it could be helpful to our company even I had no coding knowledge or programming background.

Background info of the app I am working on:
Basic employee info. list,
I have a google sheets with main tab “employee list” and some other tabs with information of either personal info. or corporate info. of the employee, which I want to use as reference.

In one tab, I have all job titles, with it’s corresponding dept and level.
I’ve made a dependent drop down for the job titles I can choose from with the dept info. chosen above.
And I want to get the “level” info. corresponding to the job title chosen.

As both dept and job title was set to type “text”. I can not use dereference for the "“level”.
So then I tried several ways, I made “level” as ref and linked to the “job title” sheet which has all job titles, related level and dept. I used in valid if, lookup([Employee Level], “Job Title”, “Job Title”, “Employee Level”). But it’s not working. Also tried in app forumla, not working.

And before I figured that out. When I tried to make it as pure text, I found the level itself disappered or missing from the form view, whether I double confirmed several time the tick of “show” or in the form view, column order, manually added all the columns. Neither was working. And only way is to go back to the sheet and input it there, which then showed up in the app view.

So I have these 2 problems,

1. the missing of “level” column in the form view
2. how to get the “level” info. related to the job title (chosen on the dependent drop down list).

Not sure if I described the situation clear enough, really need some help here.

Thank you so much in advance!!!

[Marc_Dillon · Accepted solution]

If you put an expression into valid_if that doesn’t return any results, the system will automatically hide the column, since there are no valid data points to add there. The LOOKUP() expression you mentioned is not a valid valid_if expression, and thus won’t return any results.

I assume your Employee records are where you have the dropdown for [Job Title]? You should make this a Ref type column, pointing to the Job table. You probably don’t need any valid_if expression. Then you can de-reference the “level” value with:
[Job Title].[Level]

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

https://www.appsheet.com/samples/This-app-shows-the-difference-between-keys-and-row-labels?appGuidSt...

[Ian_Li]

Hi Marc, thanks for your reply!!!
I think I get it now why the column disappeard.
And I think I fixed the problem by using another dependent drop down for the “level” column just now.

I’ve also read the links you shared. I guess I will still meet problems though…even I think I understand what those English are about now… lol

Thank you again!!!