Lookup issue

[Fof_Webmaster]

I have a column GPS Location in a Reporting table, which is type latlong. Normally reporters tap the spinner to get their current location. But for the location type, “Admin Facility”, all the locations are buildings where latlong locations are known. For these I’ve entered each building’s latlong in table “Location Type”. The column “Name” are names of various building names. A third column is “GPS Location” (I’ve tried type text and latlong), with a latlong value for each Name.

In case user enters a Name that is an Admin Facility I want to lookup the GPS location and enter it in GPS Location. But the following expression doesn’t work as an app formula or as an initial value.

ANY(SELECT(Location Type[GPS Location], [Name] = [_THISROW].[Name]))

I’m hoping someone can sort me out.

Thanks
Bob

[Aleksi]

May I ask it doesn’t work in what way?

[Fof_Webmaster]

Sorry about missing the main point. The expression results in no entry in the GSheet database.

Bob

[Aleksi]

Does it give you the location when filling a new or updating existing record?

[Fof_Webmaster]

Aleksi
I figured out the problem. I was referencing [_THISROW].[Name] where Name is the column in the child table. The correct name is from the parent table: [_THISROW].[1st Trail Name]. I also added the IF statement with HERE() to give latlong when Location Type is not Admin Facility. Thanks!

IF([Location Type] = “Admin Facility”,
ANY(SELECT(Location Type[GPS Location], [Name] = [_THISROW].[1st Trail Name])), HERE())

[Aleksi]

Glad you were able to find the reason!