Using 2 data sources for showing map data

[Antonioclk]

Hello,

i have an app based 2 data sources

1. Users sharepoint list with Person Name, email and City names
2. Locations sharepoint list with city names and their Lat and Long

When the user open the app he can select 2 screens

1. User screen where he is recognized by the app with a lookup on the email LookUp(UserData, User().Email=User.Email.
   In this screen he can use a dropdown loaded with all city names from Location sharepointList. When he select one value, the selected value for the city name will be saved in the Users sharepointList in the City names column
2. All Users screen based on a search text input, a map component and a gallery. Inside the gallery the user can see all the users from the Users sharepointList and in the map he should see pins for each user in his own location. 

The problem lays in the All Users screen.

i can easily show all users inside the gallery, using the following code in the Items element of the gallery

Search(
    AddColumns(
        UsersSPList,
        "UserName",
        User.DisplayName,
        "LocationName",
        'Location: City',
        "Location2",
        'Location: State'
    ),
    Searchbar.text,
    "UserName",
    "LocationName",
    "Location2"
)

but how can i show the same data on the map considering that it should match user and location based on the city?
For instance it should take the first user, check the City value in the Users SharepointList, lookup for that City in the Locations SharepointList and retrieve Lat and Long from there...
how can i accomplish this? what should i write inside the map Items value? how can i load two data lists inside the same search function?

I have to use this messy approach because the address component doesn't let me search for cities instead of addresses, and for privacy policies i can just ask the user for his city. Had to make this huge list with cities and their locations... 

Thank you so much

[v-bofeng-msft · Accepted solution]

Hi @Antonioclk ,

You could try:

With(
 {TheTable:Search(
    AddColumns(
        UsersSPList,
        "UserName",
        User.DisplayName,
        "LocationName",
        'Location: City',
        "Location2",
        'Location: State'
    ),
    Searchbar.text,
    "UserName",
    "LocationName",
    "Location2"
 )},
 ForAll(
   TheTable,
   Patch(
     ThisRecord,
     {
       Lat:LookUp(Locations,CityName=ThisRecord.LocationName).Lat,
       Long:LookUp(Locations,CityName=ThisRecord.LocationName).Long
     }
    )
  )
)

Best Regards,

Bof