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
They review and/or update household/prospect details .
Once updated, they click on an action named โNew Bookingโ to book a new appointment.
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.
I should also add that the โBooked Appointment With?โ is a REF type (to the โProspectsโ table)
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(โฆ)
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.
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)
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?
Iโd expect that SELECT() to be (based on an intuitive understanding of the DB structure):
SELECT(
Prospects[Prospect ID],
([House ID] = [_THISROW].[HouseID]) )
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โฆ
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.
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โฆ
The column type for House ID will be a REF to the โHousesโ table.
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]
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!
@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:
Correct, the House ID column should be of type Ref pointing to the Houses table.
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.
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.
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
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]).
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
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โฆ
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.
That you get a blank dropdown suggests there are no prospects attached to the house you chose.
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?
@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.
Steve,
I assume this is still the case? Main Menu and Search lumped together?
Thanks
Looks that way.
What values your column โBooked Appointment Withโ is using? Can you take a printscreen from your Prospects table?
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)
Appointments Table (which includes the value in question)
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.
And the โNew Bookingโ you are triggering from which table?
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)
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])
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |