Hello:
I have a table of CLIENTS and a table of CONTRACTORS, each one of them have Coordinates of their location. I have a formula that calculates the DISTANCE between each one.
How can i show in the dropdown list of CONTRACTORS in a form the ones that are closer to the CLIENT based on the distance?
Hi @Cambia_Tu_Techo, You may wish to create a slice on CONTRACTOR table wherein the sliceโs row filter condition is
[Distance]<=100 or whatever distance you wish to have. to select the contractors.
You can then refer this slice in Customer table instead of the entire contractor table.
If you can use a ref field, you have a way to use ORDERBY expression.
Hi @Aleksi_Alkioโ , Yes that is a much better way. Your solution is much sleeker and compact as usual.
@Suvrutt_Gurjar Thank you for the advice.
@Aleksi_Alkio Where should i use the ref field? I have an ORDER table where i indicate the CLIENT and CONTRACTOR fields to calculate.
You should have it in the Client table.
I have a similar request but for some reason iโm struggling to bring up a dynamic list of Refs based on their distance (Low > High)
Iโve got two tables: Books, Bookstores.
Each contains a column called [LatLong].
Hereโs my formula for that VC column that will live in the BOOKS table:
ORDERBY(
SELECT(
BOOKSTORES[Record_ID],
TRUE
),
DISTANCE(
INDEX(SELECT(BOOKS[LatLong],[Unique Book Record ID] = [_THISROW].[Unique Book Record ID]),1),
INDEX(SELECT(BOOKSTORES[LatLong],[Record_ID] = [_THIS].[Record_ID]),1)
)
)
Iโm simply trying to get a list of BOOKSTORES that are closest to the given BOOK.
Sounds like an elementary formula butโฆ iโm missing something.
Many thanks for your help.
@Jon_S
You can try with this:
ORDERBY(
SELECT(
BOOKSTORES[Record_ID],
TRUE
),
DISTANCE(
INDEX(
SELECT(
BOOKS[LatLong],[Unique Book Record ID] = [_THISROW].[Unique Book Record ID]
),1
),
INDEX(
SELECT(
BOOKSTORES[LatLong],[Record_ID] = [_THISROW].[Record_ID]
),1
)
)
)
@LeventKโs expression corrects your misuse of [_THIS]
:
SELECT(
BOOKSTORES[LatLong],
([Record_ID] = [_THIS].[Record_ID])
)
by replacing it with [_THISROW]
:
SELECT(
BOOKSTORES[LatLong],
([Record_ID] = [_THISROW].[Record_ID])
)
Your expression can be simplified:
ORDERBY(
BOOKSTORES[Record_ID],
DISTANCE(
[LatLong],
LOOKUP(
[_THISROW].[Unique Book Record ID],
"BOOKS",
"Unique Book Record ID",
"LatLong"
)
)
)
Can you elaborate on what isnโt working for you? Are you getting errors? Incorrect results?
I have exactly used your expression parameters. The error indicated that the [Record_ID] column does not exists. Check your column names and tablenames if they are relevant, associated and matching as well.
With @Steveโs kind permission, a small touch to his expression might help:
ORDERBY(
BOOKSTORES[Record_ID],
DISTANCE(
[LatLong],
LOOKUP(
[_THISROW].[Unique Book Record ID],
"BOOKS",
"Unique Book Record ID",
"LatLong"
)
),
FALSE
)
The FALSE parameter at the end, sorts the list in ascending order. Provided you require descending order, change it to TRUE
Where are you seeing the improperly sorted results?
In the list that you suggested and subsequently @LeventK suggested.
Have a TRUE or FALSE makes no difference to how the bookstores are displayed in the listโฆ
Let me be more specific: is the list displayed in a drop-down menu, or in a deck/gallery/table view?
Itโs a VC that should display a table view of these stores.
Ah, okay. That would be a table view. ORDERBY() is not going to work for you. Lemme think on thisโฆ
@Steve even a simple list of unique ids (kinda like the virtual column below that section) will work just fine for meโฆ
This list (once actually in place) will be limited by a TOP() functionโฆ so iโll get the 5โฆ 10โฆ 20โฆ etc closest stores to that book.
Once i have the list, iโll webhook the submission to zapier where it will create new rows of BOOK | BOOKSTORE pairs automatically. Something that appsheet canโt do at the mo unfortunatelyโฆ
Okay, that makes it easier. Use the ORDERBY() expression as given (with or withou the FALSE
; both are equivalent), but change the column type from List to EnumList with a base type of Ref to BOOKSTORE.
Or, if you arenโt going to display the column, just leave it as-is. The ordering problem youโre seeing is a display issue; the list itself in the VC is ordered correctly, Iโd guess.
Once we get this worked out, Iโd like to hear more about why you intend to use Zapier. I wonder if thereโs a reasonable way to avoid it.
Steve you.are.a.genius! Trooper!!!
So hereโs what weโre trying to do:
The client is shipping books to bookstoresโฆ but matching books and bookstores one pair at a time is a very VERY tedious thing to do in a Junction table.
So, now this list will be parsed as comma-separated LINE ITEMSโฆ and Zapier will create new rows in junction table called LEDGER in the following fashion
LEDGERENTRY 1 | BOOK ID 1 | BOOKSTORE 1
LEDGERENTRY 2 | BOOK ID 1 | BOOKSTORE 2
LEDGERENTRY 3 | BOOK ID 1 | BOOKSTORE 3
So iโm creating relationshipsโฆ obviously. Appsheet still struggles with functionality like this ie. โpress a button and create new rows in another tableโ.
Ah. Yeah, I canโt give you alternative for that.
Anyway - thanks for resolving this issue!
Would you be kind enough to explain why the EnumList? Because in theory a List type should workโฆ the formula was correct.
The ordering problem youโre seeing with the List is a display issue; the list itself in the VC is ordered correctly, Iโd guess. An EnumList is implicitly in-order, and AppSheet does not apply any display magic (i.e., a view) to an EnumList.
Ok so itโs basically a bug. And the EnumList method is a sort of workaround.
Iโm getting this right? In anycase i can finally progress after 5-6 hours trying to figure this out
The list itself in the VC is ordered correctly per ORDERBY(). Itโs ordering is changed during display. If you use it in any other way, it will be ordered as per ORDERBY().
Gotcha - backward but works
Love it
Hereโs a screenshotโฆ
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |