Query Table Data similar to Google Sheets vlookup - Genealogy Project

[EarthChalk]

I am working on a genealogy project to help me learn Obsidian. I am trying to use Dataview to create tables to help me make sense of the data I am getting, but running into roadblocks.

What I’m trying to do

I am getting data from Passenger Arriving and Departing Manifest List. Each Manifest may have multiple family members on it (or multiple people going to the same small town - which is a clue). But if I pull data for multiple people in an outline format, data points that are the same name for each person makes it so DataView work pull it (or pulls all the matching tags).

Basically, I want a history of their travels so I can easily compare data to ensure I have the right person. (For context, I am doing this with Chinese names and the spelling of those are dependent on the person who was writing them. Tai=Dai, Fo=Foo=Fui, Chee=Chi, Kwon=Quan - you get the idea). Also, sometimes surnames and first names are swapped so Chen is should be the family name but shown in the data as the first name.

For example, Ship Alaska sails from SFO to Hong Kong. On that boat, there are 3 family members, Mom/Dad/Kid. Mom is 25, Dad is 30, Kid is 5 years old. Their naturalization or visa numbers are all different.

Currently I have in a note for the ship manifest that day (which I have named Alaska SFO HKG ).

Person Traveling: Mom
Age: 25
Visa: X123

Person Traveling: Dad
Age: 30
Visa: Y123

Person Traveling: Kid
Age: 5
Visa: Z123

In the page I have for each person, I am trying to pull all their individual travel records into one table for all the manifests that I have.

Ship | Age | Visa

Dataview pulls in all the ages with bullet points. So right now I get:

Alaska | * 20, *30, *5 | *X123, *Y123, *Z123

Sorry, I don’t know how to show it in markdown but basically the stars are bullet points and the commas are showing up with basically vertical data in a list.

Things I have tried

I can not find anything that seems to work. But I fully admit I may not have enough knowledge of the formats and terminology to be effective with search parameters.

I had thought that if I made a table in the file that has the data then maybe there was something like the google Sheet vlookup that I could use instead.

So the idea would be the Passenger list file would have a table that says:

Person Traveling | Age | Visa
Mom | 20 | X123
Dad | 30 | Y123
Kid | 5 | Z123

This seems impossible though. Especially where there right now at least 10 manifests I am looking at.

Is there an easier way to do this?

[dawni]

If you’re willing to indent your traveler lists like:

- Person Traveling: Mom
	- Age: 25
	- Visa: X123

- Person Traveling: Dad
	- Age: 30
	- Visa: Y123

- Person Traveling: Kid
	- Age: 5
	- Visa: Z123

… then here are some queries to get you started.

Change FROM "travel manifests" to your manifest notes’ folder path or tag.

Manifests:

```dataview
TABLE rows.person AS "People", rows.age AS "Ages", rows.visa AS "Visas"
FROM "travel manifests"

FLATTEN filter(file.lists, (x) => startswith(x.text, "Person Traveling: ")) AS p
FLATTEN replace(p.text, "Person Traveling: ", "") AS person
FLATTEN replace(filter(p.children, (x) => startswith(x.text, "Age: ")).text, "Age: ", "") AS age
FLATTEN replace(filter(p.children, (x) => startswith(x.text, "Visa: ")).text, "Visa: ", "") AS visa

GROUP BY file.link AS Manifest
```

Travelers:

```dataview
TABLE rows.manifest AS "Manifests", rows.age AS "Ages", rows.visa AS "Visas"
FROM "travel manifests"

FLATTEN filter(file.lists, (x) => startswith(x.text, "Person Traveling: ")) AS p
FLATTEN replace(p.text, "Person Traveling: ", "") AS person
FLATTEN replace(filter(p.children, (x) => startswith(x.text, "Age: ")).text, "Age: ", "") AS age
FLATTEN replace(filter(p.children, (x) => startswith(x.text, "Visa: ")).text, "Visa: ", "") AS visa
FLATTEN file.link AS manifest

GROUP BY person AS Person
SORT Person ASC
```

Full list:

```dataview
TABLE WITHOUT ID file.link AS "Manifest", Person, Age, Visa
FROM "travel manifests"

FLATTEN filter(file.lists, (x) => startswith(x.text, "Person Traveling: ")) AS p
FLATTEN replace(p.text, "Person Traveling: ", "") AS Person
FLATTEN replace(filter(p.children, (x) => startswith(x.text, "Age: ")).text, "Age: ", "") AS Age
FLATTEN replace(filter(p.children, (x) => startswith(x.text, "Visa: ")).text, "Visa: ", "") AS Visa
```

[EarthChalk]

That was EXACTLY what I was looking for. I thought indenting might help but you showed me what I need to do to get there. THANK YOU so much!!

[holroy]

You should also look into using inline fields, as that would save you all that text replacing.

Having all of the fields in one list item will also make it easier to build queries on top of your structure.

Please let us know if you want more information