Hello All, I should start by saying, if ther...

[Jonathan_Havens]

Hello All,

I should start by saying, if there’s a better command to use (rather than LINKTOFORM), please let me know. Been using AppSheet for a few months now…still learning.

The relevant tables I’m using are…

“Prospects” (Key = “Prospect ID”),

“Houses” (Key = “House ID”),

“Appointments” (Key = “Appointment ID”).

Multiple prospects can reside at one house and multiple appointments can be booked for one household.

When someone enters the app to book a new appointment, I have it setup to where they need to update any/all household and/or prospect details (and add an additional prospect, or resident, if we’ve obtained new information about the prospect’s spouse while booking a new appointment)… Once Prospect’s info is up to date, the user can proceed with booking a new appointment.

To do so 1) The user selects a house

2. They review and/or update household/prospect details .

3. Once updated, they click on an action named “New Booking” to book a new appointment.

4. Once they click on “New Booking” is where I’m having an issue…

Objective: User is linked to a form that has a filtered dropdown for that asks who they spoke to (“Booked Appointment With?” is what the form asks). I’d like for this dropdown to only list those 1 or 2 prospects that reside at the residence that they looked up prior to clicking on the action that brings them to the form.

I’m trying to use the LINKTOFORM option with a SELECT() command within the expression. Have tried lots of different expressions (using different combinations of [_THISROW], [House ID], and [Prospect ID]) but it continues to list all prospects when clicking on the dropdown.

Here’s a visual of one of the expressions I’ve tried (though I haven’t achieved the desired result with it)…

LINKTOFORM("(Primary-Tabbed) NewAppt", “Region”, USERSETTINGS(Select Region),

“Caller Name”, USEREMAIL(), “Booked Appointment With”,

SELECT(Prospects[House ID],

[Prospect ID] = [_THISROW].[HouseID]))

Thanks in advance.

[Jonathan_Havens]

I should also add that the “Booked Appointment With?” is a REF type (to the “Prospects” table)

[Aleksi_Alkio]

The SELECT expression will give you always a list, not a value… even if there is only one value in that list. Before going too deep, try ANY(SELECT(…)

[Jonathan_Havens]

A step forward!

I’ve tried this and, although the dropdown still lists all prospects in DB, the form was pre-filled with the prospects name…BUT, this is only when one person lives at that household.

If there are 2 residents that we have for that household, it pre-fills the form with an EnumList type of value, which only lists the prospect ID…rather then their full name…heres a screenshot of this…

Instead of this, I’d like to achieve a filtered dropdown which shows these two Prospects’ full names, then the user can select which person they spoke with.

[Jonathan_Havens]

Also, to answer your ‘Main question’ from above…

To start, the majority of households in DB will only have one resident. But, as time goes on, we highly encourage users to add data on each household as we interact with them. So, as time goes on…more and more households will begin to have the husband and the wife listed as residents of each house.

(Also, here’s a screenshot of the pre-filled value when only one resident lives there)

[Steven_Coile]

Your original expression, reformatted for my clarity:

LINKTOFORM(

“(Primary-Tabbed) NewAppt”,

“Region”,

USERSETTINGS(“Select Region”),

“Caller Name”,

USEREMAIL(),

“Booked Appointment With”,

SELECT(

Prospects[House ID],

([Prospect ID] = [_THISROW].[HouseID])

) )

Specifically:

SELECT(

Prospects[House ID],

([Prospect ID] = [_THISROW].[HouseID]) )

This confuses me. Your’e requesting the list of house IDs (Prospects[House ID]), but you seem to already have the house ID ([_THISROW].[HouseID]). And you’re comparing a prospect ID to a house ID?

[Steven_Coile]

I’d expect that SELECT() to be (based on an intuitive understanding of the DB structure):

SELECT(

Prospects[Prospect ID],

([House ID] = [_THISROW].[HouseID]) )

[Steven_Coile]

Rather than trying to pass a computed list of prospects based on the house, I’d pass the house ID and let the form itself compute the list of possible prospects using a Valid_If formula for Booked Appointment With that uses the passed house ID to construct the list of prospects. But it appears the house ID isn’t currently stored in the appointments table…

[Steven_Coile]

The reason you’re getting the behavior you are is because Booked Appointment With is (presumably) a non-list column type (presumably Ref to Prospects).

When your SELECT() results in only a single prospect, the Booked Appointment With column is getting exactly what it expects: a single prospect ID.

When the SELECT() produces multiple prospects, the Booked Appointment With column isn’t expecting a comma-separated list, so it tries to interpret what it’s given as a single prospect ID. But because it isn’t a single prospect ID, it can’t find a matching row in the Prospects table, so it can’t get a label to display. With no label to display, it falls back to displaying the given (unrecognized) value.

[Jonathan_Havens]

Hi Steve, appreciate the input!

The original expression I’d posted was more for a visual reference as to what I was trying to achieve within the question I was asking.

Still a bit of an amateur with AppSheet and trying to understand the science behind the way the SELECT() function works when listed within another function while used to pre-fill a form.

I’m not sure why I do not have the house ID listed within appointments table, but now that you mention it…its gonna be vital for future endeavors and operations I have in mind for our office. So thank you for bringing that up!

To make sure I understand once Ive added [House ID] to appointments table…

1. The column type for House ID will be a REF to the “Houses” table.

2. Since I’d want the value of House ID to be hidden and automatically filled in with each appointment submission, the “App Formula” would be [House ID].[House ID]

3. I should enter Valid_If expressions within the “Prospect Lookup” column and the (newly created) “House ID” column (and not use any SELECT() function within the LINKTOFORM() expression that has been previously discussed) in “Appointments” table…

Within the “Appointments” table, the expressions for these columns should be…

“Prospect Lookup” column (or “Booked appointment With?” as listed in form)

Valid_If Prospects[Prospect ID]

“House ID” column Valid_If Prospects[House ID]

Does all this look correct?

Again, thanks to both commentators…this discussion has been very helpful so far!

[Steven_Coile]

@Jonathan_Havens, we’re all learning.

SELECT() is a particularly complex function and easily one of the hardest to grasp. You aren’t alone in finding it a challenge!

Glad you’ve taken the suggestion to add house ID to the appointment table!

To address the numbered points of your reply:

1. Correct, the House ID column should be of type Ref pointing to the Houses table.

2. In fact, you do not want an App formula for House ID, as that will prevent it from receiving the value from elsewhere.

If you want to prevent the user from modifying the House ID value in the appointments table, you can set the column’s Editable_If property to =FALSE.

You could use the House ID column’s Show_If property to hide it from from the user in form view, but be careful if Apply show-if constraints universally is ON in UX > Options > FORMS. If ON, the Show_If property will affect the column’s visibility everywhere.

You could instead use a slice (Data > Slices) to hide the column in the form only.

It may not be a bad idea to keep the column visible, though, to remind the user, especially if it’s a long form.

3. Correct that you should not use a SELECT() statement with LINKTOFORM(). Correct that you should use an expression in the Valid_If property of the Prospect Lookup column. Correct that the expression should make use of the new House ID column to locate the appropriate prospects.

The specific Valid_If expression for the Prospect Lookup column can be as simple as:

=Prospects[Prospect ID]

which will produce a list of all prospects, but you said you only want to present the prospects attached to the house. So Valid_If is where you want to use your SELECT():

=SELECT(

Prospects[Prospect ID],

([House ID] = [_THISROW].[House ID]) )

Look familiar? After all this, you just had your SELECT() in the wrong place.

[Jonathan_Havens]

Yes, my head was swimming the other day trying to figure out what I was doing wrong within that expression! So, it’s good to hear I’m not alone on this.

I’ve tried using your advice in a few different ways and am getting an empty dropdown for the “Prospect Lookup” field in form.

Am I understanding correctly… 1) add Prospects[Prospect ID] in the “Prospect Lookup” column and 2) add your concluding SELECT() expression within the Valid_If of the “House ID” column?

Or are you saying just to use the SELECT() expression within the “Prospect Lookup” column, instead of the first Prospects[Prospect ID] Valid_If expression you’d mentioned?

Maybe neither are correct? haha

[Steven_Coile]

Strictly speaking, you don’t need a Valid_If expression for House ID, since you’ll be populating the form value from LINKTOFORM().

So we’re really only concerned with Prospect Lookup.

For Prospect Lookup, we want to tell AppSheet to only allow prospect IDs.

To do that, we’ve made the column type Ref pointing to the Prospects table.

And we want AppSheet to present the user with a dropdown menu of valid prospects that the user can choose from. We can do that using the expression, =Prospects[Prospect ID].

But this gives a list of all prospects, where we only want the prospects associated with this house.

This is where the SELECT() expression comes in. Wrapped around Prospects[Prospect ID], the SELECT() “selects” only some of the values instead of all. Which values is determined by the subexpression, ([House ID] = [_THISROW].[House ID]).

[Jonathan_Havens]

I’ve tried this out. It’s filling the field with the House ID or the prospect ID (as I’m trying different expressions)…rather then the prospects name. Even when it fills with the ID, If I click on the dropdown, its still showing every prospect name, not just the ones who reside at that address.

I’m not quite sure that I’m using the combo of [_THISROW], [House ID], and [Prospect ID] in the correct sequence within the expression though either (which is why I keep “trying different expressions”).

Additionally, I’ve been trying to find a helpful tutorial and I’ve come across one were there’s one that’s pre-filling the form without using the [_THISROW] feature. Shown here… youtube.com - AppSheet Office Hours | How to Make an Inventory Management App II (Relevant material starting at 24:10)

AppSheet Office Hours | How to Make an Inventory Management App II

[Jonathan_Havens]

Ok, I’ve copied and pasted the expression you’d listed and tested it out.

It’s giving me a blank dropdown for the “Spoke with?” field.

I also ran a test on the expression and something jumped out at me…the value that’s filled in for [_THISROW] becomes “Appointment ID”. This should be altered, yea?

Here’s the screenshot of the test…

[Steven_Coile]

It’s OK that it replaces [_THISROW] with [Appointment ID]. Because the Appointment ID column is the KEY column for the Appointments table, the two are equivalent.

[Steven_Coile]

That you get a blank dropdown suggests there are no prospects attached to the house you chose.

[Jonathan_Havens]

I ended up looking for other things that could have been causing the issue…since there were prospects attached to the houses I’d tested this on (I deleted all the address without the attached prospect info). Slices, partitions (I have 3), and security filters…couldn’t find anything that would be blocking the prospect info from showing up.

However, I had the idea to place the “House ID” above the “Prospect Lookup” within the form, since it’s always one unique value…then list the related prospects, based on the household with a Valid_If…IT WORKED!! Super excited to have gotten this fixed!!

Thank you so much for all your help throughout this process! You and Aleksi have been quick to respond and, at a certain point, my mind was swiss cheese with all the possibilities as to where I’d need to put the SELECT() function (is it the App formula, Valid_If, within LINKTOFORM() function, etc.).

“With great power comes great…possibilities”

ha

Anyhow, it’s working great. Now…if it’s not too much of a headache, I’d like to add the “New Booking” as a prominent action within the “Prospect Details” view. When clicked, have it pre-fill the “House ID” and the “Prospect Lookup” values. I’m guessing it’ll just be a small variation from the settings you’ve been helping with.

Also, I wanted to ask you about the “Hide menu and search buttons” setting. It seems illogical to group these two functions together as one setting. I’d like to keep the search button throughout the app, but turn off the hamburger menu. Is this a possibility?

[Steven_Coile]

@Jonathan_Havens There is no way to turn off the main menu without also turning off search. I don’t know why those are lumped together.

[Lucinda_Mason]

Steve,

I assume this is still the case? Main Menu and Search lumped together?

Thanks

[Steve]

Looks that way.

[Aleksi_Alkio]

What values your column “Booked Appointment With” is using? Can you take a printscreen from your Prospects table?

[Jonathan_Havens]

Sure. Here are the 3 tables I’m using. I’ve circled the column which I’m trying to fill. It’s actually named “Prospect Lookup”…I’ve been saying “Booked Appointment With” as an easier way for the community to understand what I’m trying to achieve. In expression, I’m using “Prospect Lookup”.

Prospects Table (Ill post the other 2 in separate comments)

[Jonathan_Havens]

Appointments Table (which includes the value in question)

[Jonathan_Havens]

Houses Table

I really appreciate your help here. I was trying at this for a few hours…got very frustrating. Usually I’m able to track down the solution. Being that this is an expression within an expression…I haven’t been able to find much help on how this should work.

[Aleksi_Alkio]

And the “New Booking” you are triggering from which table?

[Jonathan_Havens]

In this case, I have the action “Displayed prominently” within the Houses_Details view.

I also wanted to create an action within the Prospects_Details view.

So, whether user is looking at household details or an individual prospects details, they can click on the “New Booking” action and the form will either:

A) From Houses_Details > filter the “Prospect lookup” to only show the prospects that reside at that residence.

or

B) From Prospects_Details > pre-fill the “Prospect lookup” with the prospect’s name that they were viewing when clicking on “New Booking”

But I feel like I can figure option B if I have option A figured out (which seems to be the more complicated one)

[Aleksi_Alkio]

I try to summarize… You want to read the related Prospect from the Houses record. The problem is the value itself… because it’s a list of Key column’s values. Main question is… does that list contains more than one value or is it always just one value?

The below formula would give you the same result… I mean list of values but it’s a start to find the suitable value from that list.

LINKTOFORM("(Primary-Tabbed) NewAppt", “Region”, USERSETTINGS(Select Region),

“Caller Name”, USEREMAIL(), “Prospect Lookup”,

[Related Prospects])