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"
)
)
)
Jon_S:
for some reason i’m struggling to bring up a dynamic list of Refs based on their distance
Can you elaborate on what isn’t working for you? Are you getting errors? Incorrect results?
@Steve I was getting some errors about my select() not being valid. I really want to try your “simplified” version.
@LeventK thanks I’ll try your suggestion!
Guys thanks for getting back to me so quickly. Much appreciated!
Hi @Steve
Your expression works but the list of bookstores in simply in alphabetical order it seems.
@LeventK your expression brings up the following error.
Jon_S:
@LeventK your expression brings up the following error.> >
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.
Jon_S:
Hi @Steve> > Your expression works but the list of bookstores in simply in alphabetical order it seems.
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
Even with the FALSE parameter, it’s still simply the list of BOOKSTORES in alphabetical order A-Z.
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?