distance from airport to near the hospital

[T-Shigenori]

There are two tables, one is a list of airports and the latlong of each airport is listed. The second is a list of hospitals and the latlong of each hospital is listed.
Using these two tables, when I select an airport on the map view on the dashboard, I want to display the hospitals near the airport selected on the deck in order of proximity. What should I do now. I would like to use distance() properly. Please tell me how to do it.

[lochtefeldm]

Step 1: Set Up Your Tables

Ensure your two tables (Airports and Hospitals) are properly set up in AppSheet with columns for latitude and longitude. These should be separate columns in each table.

Step 2: Create a Virtual Column for Distance

In the Hospitals table, create a virtual column let's call it "DistanceFromSelectedAirport". This column will calculate the distance from each hospital to the selected airport.

The formula for this virtual column would look something like this:
*DISTANCE([LatLong], LOOKUP([_THISROW].[SelectedAirport], "Airports", "Name", "LatLong"))*

In this formula, [LatLong] represents the latitude and longitude of the hospital, [_THISROW].[SelectedAirport] represents the name of the selected airport, and LOOKUP() is used to find the corresponding latitude and longitude of the selected airport in the Airports table.

Step 3: Create Interactive Dashboard Views

1. Map View for Airports: Create a Map View for your Airports table. This view will allow users to select an airport.

2. Deck View for Hospitals: Create a Deck View for your Hospitals table. This view will display the list of hospitals.

3. Dashboard View: Create a Dashboard view and include both the Map View and the Deck View. Make sure to enable interactivity between these views. This means that selecting an airport in the Map View should influence what's displayed in the Deck View.

Step 4: Sorting Hospitals by Proximity

In the Deck View of Hospitals, set the sorting to use the virtual column DistanceFromSelectedAirport. This will ensure that when an airport is selected, the hospitals are listed in order of their distance from the selected airport.

[T-Shigenori]

Is [SelectedAirport] one of the column in the hospital table?

I don't set up [SlecetedAirport] both in the Hospital table and Airport table.

I set the following column in the Hospital table.

1 [HospitalName]

2[Latlong](<-----represents the latitude and longitude of the hospital)

I set the following column in the Airports table.

1 [AirportName]

2 [Latlong](<-----represents the latitude and longitude of the hospital)

So, what should I do?

[equalmeat]

Lets make a few adjustments to ensure your tables and columns are set up correctly to utilize the provided steps effectively. Here's a breakdown of what you should do:

1. Add [SelectedAirport] Column: You need to add a [SelectedAirport] column. to the Hospitals table 

2. Create a Virtual Column for Distance: In the Hospitals table, create a virtual column named "DistanceFromSelectedAirport". The formula you provided will calculate the distance between the hospital and the selected airport. Ensure that [SelectedAirport] is a field that can be set by the user or determined through some part of your app's functionality.

   • The formula DISTANCE([LatLong], LOOKUP([_THISROW].[SelectedAirport], "Airports", "AirportName", "LatLong")) assumes [SelectedAirport] holds the name of an airport, and the LOOKUP function finds the corresponding latitude and longitude in the Airports table.

[T-Shigenori]

Does the SelectedAirport column require any function in the formula?

There are two views on the dashboard , one is map view  created from airport table and the other is deck view created from hospital table.

When users select a Airport pin showing an airport on the map view created from the Airport table, the distance between the selected airport location and the hospital location is calculated, and the deck view created from the hospital table is changed in order of proximity  on  the dashboard

Regarding the Lookup search value, if the user selects a pin on the map, we don't know if it will become the Lookup() search value. For this reason, I would like to put some kind of function in the SelectedAirport column so that when an airport pin on the map is selected, it will automatically become the value of the SelectedAirport column and become the search value.

[lochtefeldm]

The action of selecting an airpot on a map isn't going to work as its an event type that can trigger an action. You could add a trigger that is executed by the user from the maps embedded detail view such as the call or map actions below. One that updates the value of the selectedairport column.

[lochtefeldm]

You could try the idea below but its going to filter the table for ever user... unless you add another table for user selections and add a security filter...

1. Use a Slice to Filter Hospitals:

   • Create a Slice on the Hospitals table that filters the hospitals based on their distance to a selected airport.
   • Add a virtual column in the Airports table to hold the latitude and longitude of the selected airport.

2. Create a Virtual Column for Distance Calculation:

   • In the Slice for the Hospitals table, add a virtual column that calculates the distance from each hospital to the selected airport using the DISTANCE() formula.

3. Create an Action to update the Selected Airport's column in the hospital table...

Processing this could take a while depending on how many rows it would need to update in your hospital table, and if the table has a lot of columns it slows it down too.

For your specific use case where you want to sort hospitals by their distance from a selected airport, a virtual column could result in a delay every time the app syncs if you have many hospital records to process.

1.