Hello: I have a table of CLIENTS and a table...

[Cambia_Tu_Techo]

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?

[Suvrutt_Gurjar]

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.

[Aleksi_Alkio]

If you can use a ref field, you have a way to use ORDERBY expression.

[Suvrutt_Gurjar]

Hi @Aleksi_Alkio’ , Yes that is a much better way. Your solution is much sleeker and compact as usual.

[Cambia_Tu_Techo]

@Suvrutt_Gurjar Thank you for the advice.

[Cambia_Tu_Techo]

@Aleksi_Alkio Where should i use the ref field? I have an ORDER table where i indicate the CLIENT and CONTRACTOR fields to calculate.

[Aleksi_Alkio]

You should have it in the Client table.

[Jon_S]

@Aleksi

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.

[LeventK]

@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
   	)
   )
)

[Steve]

@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])
)

[Steve]

Your expression can be simplified:

ORDERBY(
  BOOKSTORES[Record_ID],
  DISTANCE(
    [LatLong],
    LOOKUP(
      [_THISROW].[Unique Book Record ID],
      "BOOKS",
      "Unique Book Record ID",
      "LatLong"
    )
  )
)

[Steve]

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?

[Jon_S]

@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!

[Jon_S]

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.

[LeventK]

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.

[LeventK]

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

[Jon_S]

@LeventK @Steve

Even with the FALSE parameter, it’s still simply the list of BOOKSTORES in alphabetical order A-Z.

[Steve]

Where are you seeing the improperly sorted results?

[Jon_S]

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…

[Steve]

Let me be more specific: is the list displayed in a drop-down menu, or in a deck/gallery/table view?

[Jon_S]

It’s a VC that should display a table view of these stores.

[Steve]

Ah, okay. That would be a table view. ORDERBY() is not going to work for you. Lemme think on this…

[Jon_S]

@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…

[Steve]

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.

[Steve]

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.

[Jon_S]

Steve:

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 .

Steve you.are.a.genius! Trooper!!!

[Jon_S]

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”.

[Steve]

Ah. Yeah, I can’t give you alternative for that.

[Jon_S]

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.

[Steve]

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.

[Jon_S]

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

[Steve]

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().

[Jon_S]

Gotcha - backward but works

Love it

[Jon_S]

Here’s a screenshot…