Select not showing up in a form view

sheebee22 · 04-28-2022 01:16 PM

Hi everyone. I have spent absolutely hours trying to resolve this and scouring google, but no luck! I really hope somebody on here can help me!

In my app, when somebody visits a school, they fill in a "school visit form". When they fill in the form, they can select the school and teacher using dependant dropdowns. Now what I'm trying to get it to do, is to work out what the teacher's previous target was. This information comes from the same table ("Visits"). So I want Appsheet to search through the selected teacher's previous targets, and then EITHER display a list of all of their previous targets, so the user can select the most recent, OR (I gave up on this ages ago), display only the teacher's most recent target.

Based upon trying to do the first one (displaying a list of the teacher's previous targets), I have been trying various combinations of this formula:

SELECT(Visits[New target], ([Teacher name] = [_THISROW].[Teacher name]))

Appsheet says the formula is valid, but when you go to the form, this part just isn't showing at all. I read that this is likely because it can't find the values, so I'm wondering if I've missed something out in the formula. Or, is there a specific "type" it needs to be to ensure it shows up in the form? (I've already checked "show?") Or is there something else I need to put into the data validity or auto-compute to make this work?!

Sorry if this isn't very clear. Essentially, I want it Appsheet to see the name of the teacher selected, and from that show all the targets assigned to that teacher, so that the user can select the most recent one from that list.

Please help!! THANK YOU!!

Lynn

Hi @sheebee22

When you "test" your your formula, what does it show?

sheebee22

It shows a list of all teacher targets. But I want it to show a target specific to the teacher selected in the form, which wouldn't show up in the test I don't think?

TeeSee1

For a table (main) like this,

A formula -

SELECT(
 main[school],
 [id] = maxrow("main","_RowNumber",[teacher]=[_THISROW].[teacher])
)

in the suggested values of [school] gives you the following dropdown

for bob, C high and for suzan, D high

when adding a new row.

sheebee22

Thank you. When I put this formula in, it says the formula is valid, but it's no showing up in form view. I need it to show in the form, so when I select a teacher and school, I want it to show the relevant targets linked to that teacher.

Ryan_Mortimer

Rather than test this in “form” view. I would create a virtual column and try to generate the desired result in a table/detail view before anything else.

Once you have the behaviour you are seeking, then move onto the form view.

Without digging too deep

are you using your forumula in “suggested values” or in formula?
you could also go a different route, create a virtual column that references the other relevant rows, then do a straight dereference / reference to that VC

im on my phone right now, but with some more information I can help you.

sheebee22

Thank you for your response. I've tried suggested values and formula, but neither work in terms of not showing up in the form.

The problem seems to be that I can't get the formula to reference the selected teacher in the form. If the formula can understand that it needs to look at the teacher name and then find me the relevant targets linked to that teacher, then that's what I want it to do! But I can't work out how to do this!

Ryan_Mortimer

I have another post where I cover this in detail, I will try and find it. However, it is probably not showing in form view because you don’t have the expression in suggested values.

Ryan_Mortimer

So you have another table that hosts all the information about the schools, teachers, etc. that is seperate to your visits table. Let’s call it “SITEINFO” for example:

I would use the following expression

SELECT(SITEINFO[TARGETCOLUMN], [TEACHER] = [_THISROW].[TEACHER])

0 Likes

Steve

https://help.appsheet.com/en/articles/961274-list-expressions

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-FILTER-LOOKUP-MAXROW-MINROW-REF-ROWS-and-SEL...