Comparing links to text in Dataview

What I’m trying to do

I have some notes set up as entities, i.e. Person, Organisation etc.

I am trying to create a list/table of people belonging to a given organisation on each organisation’s note.

I have frontmatter in each person. Since I found I can add links to the frontmatter I have been doing so, but most of my older notes have plaintext.
So, typically, the person frontmatter is one of the two examples below:

Example 1 (with links):

org:
  - "[[6. Entities/Organisations/Some Org | Org One]]"
  - "[[6. Entities/Organisations/Some Org | Org Two]]"

Example 2 (without links):

org:
  - Org One
  - Org Two

In my organisation notes I have frontmatter for aliases. The main organisation name is always included in the aliases.

name: Org One
aliases:
 - Org One
 - Org One Alias

I want a dataview listing people who have a value from org matching one of the values in aliases.

Things I have tried

This works for Example 1 (where matching org value is a link):

TABLE WITHOUT ID
link(file.name, name) AS Name, job_title AS "Job Title", Organisation, Alias
FROM #e/person
FLATTEN org as Organisation
FLATTEN this.file.aliases as Alias
WHERE icontains(meta(Organisation).path, Alias)
SORT name ASC

This works for Example 2 (where matching org value is plaintext):

TABLE WITHOUT ID
link(file.name, name) AS Name, job_title AS "Job Title", Organisation, Alias
FROM #e/person
FLATTEN org as Organisation
FLATTEN this.file.aliases as Alias
WHERE icontains(Organisation, Alias)
SORT name ASC

I have then tried combining the WHERE clauses using OR, as I would expect this to work, but it gives the same result as the first dataview (where it matches org values with links)

TABLE WITHOUT ID
link(file.name, name) AS Name, job_title AS "Job Title", Organisation, Alias
FROM #e/person
FLATTEN org as Organisation
FLATTEN this.file.aliases as Alias
WHERE icontains(meta(Organisation).path, Alias) OR icontains(Organisation, Alias)
SORT name ASC

I’d expect the OR clause to bring in both sets of results, but it’s not working.
Can anyone shed any light on what I’ve done wrong, or misunderstood?

Cheers

Topic : 1/2

Summary
  • How to compare the org field whch is an array of links or an array of strings to the aliases field whch is an array of strings? (DQL10, DVJS10)

Before you use the meta function, you have to transform a string like “Org One” into a link like “[[Org One]]”. Otherwise, the file like dic_19630330 will not be taken into consideration in any DQL.


Test

Summary
  • dataview: v0.5.46

Input

Summary

dictionary files: Organizations

Summary_O
  • Location: “100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Organizations”

folder: Test

  • filename : Org One Test
---
name: "Org One"
aliases:
  - "Org One"
  - "Org One Alias"
---

### DQL01

### DQL03

### DQL10

### DVJS10



folder: Another

“Another Org” = “Org One X” = “Org Two Y”

  • filename : Another Org
---
name: "Another Org"
aliases:
  - "Org One X"
  - "Org Two Y"
  - "Another Org"
---


  • filename : Org One X
---
name: "Org One X"
aliases:
  - "Org One X"
  - "Org Two Y"
  - "Another Org"
---


  • filename : Org Two Y
---
name: "Org Two Y"
aliases:
  - "Org One X"
  - "Org Two Y"
  - "Another Org"
---


folder: Some

“Some Org” = “Org One” = “Org Two”

  • filename : Some Org
---
name: "Some Org"
aliases:
  - "Org One"
  - "Org Two"

---


  • filename : Org One
---
name: "Org One"
aliases:
  - "Org One"
  - "Org Two"

---


  • filename : Org Two
---
name: "Org Two"
aliases:
  - "Org One"
  - "Org Two"

---


dictionary files: Persons

Summary_P
  • Location: “100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons”

folder: 03_String

  • filename : dic_19630330
---
Date: 1963-03-30
name: "March"
job_title: "engineer"
org:
  - "Org One"
  - "Org Two"
---


folder: 04_Link_with_display

  • filename : dic_19630401
---
Date: 1963-04-01
name: "April"
job_title: "manager"
org:
  - "[[Some Org| Org One]]"
  - "[[Some Org| Org Two]]"
---


folder: 07_Link_without_display

  • filename : dic_19630707
---
Date: 1963-07-07
name: "July"
job_title: "operator"
org:
  - "[[Org One]]"
  - "[[Org Two]]"
---


folder: 09_Link_with_path

  • filename : dic_19630912
---
Date: 1963-09-12
name: "September"
job_title: "CEO"
org:
  - "[[100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Organizations/Some/Org One.md]]"
  - "[[100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Organizations/Some/Org Two.md]]"
---


folder: 11_excluding_String

  • filename : dic_19631130
---
Date: 1963-11-30
name: "November"
job_title: "principal"
org:
  - "Org One X"
  - "Org Two Y"
  - "Another Org"
---


folder: 12_excluding_Link

  • filename : dic_19631201
---
Date: 1963-12-01
name: "December"
job_title: "teacher"
org:
  - "[[Another Org| Org One X]]"
  - "[[Another Org| Org Two Y]]"
---


  • filename : dic_19631207
---
Date: 1963-12-07
name: "December"
job_title: "teacher"
org:
  - "[[Org One X]]"
  - "[[Org Two Y]]"
  - "[[Another Org]]"
---


  • filename : dic_19631212
---
Date: 1963-12-12
name: "December"
job_title: "teacher"
org:
  - "[[100_Project/22_DVJS/V01_CF/V01_T/Organizations/Another/Org One X.md]]"
  - "[[100_Project/22_DVJS/V01_CF/V01_T/Organizations/Another/Org Two Y.md]]"
  - "[[100_Project/22_DVJS/V01_CF/V01_T/Organizations/Another/Another Org.md]]"
---


DQL01_deal_with_an_array_of_lnks_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL01
_deal_with
_an_array_of_lnks
_and_TABLE
org:
an array of lnks
no 1.To filter by org
2.To break up the list org into each individual element like Organization
3.To break up the list this.file.aliases into each individual element like Alias

4.To define a field variable H with assigning the value as meta(Organization)
5.To define a field variable P_display with removing the leading whitespaces of the H.display
6.To define a field variable Q_path with getting the filename of the H.path

7.To filter by typeof(Organization)
8.To filter by upper(P_display) or upper(Q_path)
9.To sort by name(“ASC”)
10.To display the result as a table

Code DQL01_deal_with_an_array_of_lnks_and_TABLE

Summary_code
title: DQL01_deal_with_an_array_of_lnks_and_TABLE =>1.To filter by `org` 2.To break up the list `org` into each individual element like `Organization` 3.To break up the list `this.file.aliases` into each individual element like `Alias` 4.To define a field variable `H` with assigning the value as `meta(Organization)` 5.To define a field variable `P_display` with removing the leading whitespaces of the `H.display` 6.To define a field variable `Q_path` with getting the filename of the `H.path` 7.To filter by `typeof(Organization)` 8.To filter by `upper(P_display)` or `upper(Q_path)` 9.To sort by `name`("ASC") 10.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      link(file.path, name) AS "Name",
      job_title AS "Job Title",
      Organization AS "Organization",
      Alias AS "Alias",
      P_display AS "P_display",
      Q_path AS "Q_path"

FROM "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons"

WHERE org

FLATTEN org AS Organization
FLATTEN this.file.aliases AS Alias


FLATTEN meta(Organization) AS H

FLATTEN regexreplace(H.display, "^\s*", "") AS P_display

FLATTEN regexreplace(H.path, "^(.*/)(.+)(.md)$", "$2") AS Q_path


WHERE typeof(Organization) = "link"
WHERE upper(P_display) = upper(Alias) OR 
      upper(Q_path) = upper(Alias) 

SORT name ASC

```

Screenshots(DQL01)


DQL03_deal_with_an_array_of_strings_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL03
_deal_with
_an_array_of_strings
_and_TABLE
org:
an array of strings
no 1.To filter by org
2.To break up the list org into each individual element like Organization
3.To break up the list this.file.aliases into each individual element like Alias

4.To filter by typeof(Organization)
5.To filter by upper(P_display) or upper(Q_path)
6.To sort by name(“ASC”)
7.To display the result as a table

Code DQL03_deal_with_an_array_of_strings_and_TABLE

Summary_code
title: DQL03_deal_with_an_array_of_strings_and_TABLE =>1.To filter by `org` 2.To break up the list `org` into each individual element like `Organization` 3.To break up the list `this.file.aliases` into each individual element like `Alias` 4.To filter by `typeof(Organization)` 5.To filter by `upper(P_display)` or `upper(Q_path)` 6.To sort by `name`("ASC") 7.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      link(file.path, name) AS Name,
      job_title AS "Job Title", 
      Organization AS "Organization",
      Alias AS "Alias"

FROM "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons"

WHERE org

FLATTEN org as Organization
FLATTEN this.file.aliases as Alias


WHERE typeof(Organization) = "string" 
WHERE upper(Organization) = upper(Alias)

SORT name ASC


```

Screenshots(DQL03)


DQL10_deal_with_an_array_of_lnks_or_strings_and_TABLE

Summary

Main DQL

Code Name Data type Group By Purposes Remark
DQL10
_deal_with
_an_array_of
_lnks_or_strings
_and_TABLE
org:
1.an array of lnks
2.an array of strings
no 1.To filter by org
2.To break up the list org into each individual element like Organization
3.To break up the list this.file.aliases into each individual element like Alias

4.To define a field variable LINK with transforming Organization into a link

5.To define a field variable H with assigning the value as meta(LINK)
6.To define a field variable P_display with removing the leading whitespaces of the H.display
7.To define a field variable Q_path with getting the filename of the H.path

8.To filter by upper(P_display) or upper(Q_path)
9.To sort by name(“ASC”)
10.To display the result as a table
1.The DQL10 does what both the DQL01 and DQL03 do.

2.The Purposes 4 is the key idea.
2.1 Before you use the meta function, you have to transform a string like “Org One” into a link like “[[Org One]]”. Otherwise, the file like dic_19630330 will not be taken into consideration in any DQL.

3.The Regular Expression declared as a variable like Q_path in the DQL10 is based on the DQL10 in the following topic.
- Solutions: by Justdoitcc

Notes

Summary

Q1: How to modify the following code with simplifying the content of the file like dic_19630401?

Summary_Q1
Original Example: Q1 (To be modified)
```dataview
FLATTEN regexreplace(H.display, "^\s*", "") AS P_display
```
folder: 04_Link_with_display (To be modified)
  • filename : dic_19630401 (To be modified)
---
Date: 1963-04-01
name: "April"
job_title: "manager"
org:
  - "[[Some Org| Org One]]"
  - "[[Some Org| Org Two]]"
---


A1:

Another Example: A1_11
```dataview
FLATTEN H.display AS P_display
```
folder: 04_Link_with_display (After being modified)

There are no whitespaces on either side of the symbol like “|”.

  • filename : dic_19630401 (After being modified)
---
Date: 1963-04-01
name: "April"
job_title: "manager"
org:
  - "[[Some Org|Org One]]"
  - "[[Some Org|Org Two]]"
---


Code DQL10_deal_with_an_array_of_lnks_or_strings_and_TABLE

Summary_code
title: DQL10_deal_with_an_array_of_lnks_or_strings_and_TABLE =>1.To filter by `org` 2.To break up the list `org` into each individual element like `Organization` 3.To break up the list `this.file.aliases` into each individual element like `Alias` 4.To define a field variable `LINK` with transforming `Organization` into a link 5.To define a field variable `H` with assigning the value as `meta(LINK)` 6.To define a field variable `P_display` with removing the leading whitespaces of the `H.display` 7.To define a field variable `Q_path` with getting the filename of the `H.path` 8.To filter by `upper(P_display)` or `upper(Q_path)` 9.To sort by `name`("ASC") 10.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID
      link(file.path, name) AS "Name",
      job_title AS "Job Title",     
      Organization AS "Organization",
      Alias AS "Alias",
      P_display AS "P_display",
      Q_path AS "Q_path"

FROM "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons"

WHERE org

FLATTEN org as Organization
FLATTEN this.file.aliases as Alias

FLATTEN choice(typeof(Organization) = "string", link(Organization) ,Organization) AS LINK

FLATTEN meta(LINK) AS H

FLATTEN regexreplace(H.display, "^\s*", "") AS P_display

FLATTEN regexreplace(H.path, "^(.*/)(.+)(.md)$", "$2") AS Q_path


WHERE upper(P_display) = upper(Alias) OR 
      upper(Q_path) = upper(Alias) 

SORT name ASC
```

Screenshots(DQL10)


Reference

Summary

To get the filename of a link: the Q_path in the DQL10


Before you use the meta function, you have to transform a string like “Org One” into a link like “[[Org One]]”. Otherwise, the file like dic_19630330 will not be taken into consideration in any DQL.

Topic : 2/2

Summary
  • How to compare the org field whch is an array of links or an array of strings to the aliases field whch is an array of strings? (DQL10, DVJS10)

Before you use the meta function, you have to transform a string like “Org One” into a link like “[[Org One]]”. Otherwise, the file like dic_19630330 will not be taken into consideration in any DQL.


DVJS10_deal_with_an_array_of_lnks_or_strings_and_TABLE

Summary

Main DVJS

Code Name Data type Group By Purposes Remark
DVJS10
_deal_with
_an_array_of
_lnks_or_strings
_and_TABLE
org:
1.an array of lnks
2.an array of strings
no The DVJS10 does what the DQL10 does.

Code DVJS10_deal_with_an_array_of_lnks_or_strings_and_TABLE

Summary_code
title: DVJS10_deal_with_an_array_of_lnks_or_strings_and_TABLE =>The DVJS10 does what the DQL10 does.
collapse: close
icon: 
color: 
```dataviewjs
// M11. define pages: the notes from Persons
// #####################################################################
let pages = dv
    .pages('"100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons"')
    .where((page) => page.org);


// M13. define ORG_note: the note from Organizations
// #####################################################################
let ORG_note = dv.current();


// M21. define aoh_rows: a JavaScript array
// #####################################################################
let aoh_rows = [];


// M23. update aoh_rows
// transform a JavaScript array into a Dataview data array by using dv.array
// #####################################################################
aoh_rows = dv.array(aoh_rows);



// M31. deal with ORG_note.aliases:
// #####################################################################
for (let Alias of ORG_note.aliases) {

    // M31.FR10 deal with pages:
    // #################################################################
    for (let page of pages) {
        
        
        // M31.FR10.FR05 define ORGs:
        // #############################################################    
        let ORGs = dv.array(page.org);


        // M31.FR10.FR07 define P_display:
        // #############################################################
        let P_display = "";
        
        
        // M31.FR10.FR09 define Q_path:
        // #############################################################
        let Q_path = "";
        

        // M31.FR10.FR11 deal with ORGs:
        // #############################################################
        for (let ORG of ORGs) {


            // M31.FR10.FR11.IF10 deal with "link":
            // #########################################################
            if (dv.func.typeof(ORG) === "link") {

                // M31.FR10.FR11.IF10.FI21 update P_display:
                // To remove the leading whitespaces of the `ORG.display`
                // 
                // dic_19630401:
                // Input:"[[Some Org | Org One]]"
                // `ORG.display` from Input :" Org One"
                // Output from `ORG.display`:"Org One"
                // 
                // #####################################################
                if (ORG?.display !== undefined) {
                    P_display = ORG.display.replace(/^\s*/, "");
                }


                // M31.FR10.FR11.IF10.FI31 update Q_path:
                // To get the filename of a link like `ORG.path`
                // 
                // dic_19630912:
                // Input:"[[100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Organizations/Some/Org One.md]]"
                // Output:"Org One"
                // 
                // #####################################################
                if (ORG?.path !== undefined) {
                    // Q_path = ORG.path.replace(/^(.*\/)(.+)(.md)$/, "$2");
                    Q_path = dv.func.regexreplace(
                        ORG.path,
                        "^(.*/)(.+)(.md)$",
                        "$2"
                    );
                }
            
            // M31.FR10.FR11.IF20 deal with "String":
            // #########################################################
            } else {
                P_display = ORG;
            }


            // M31.FR10.FR21 gather matches:
            // push the key-value pairs into the array aoh_rows:
            // #########################################################
            if (
                P_display.toUpperCase() === Alias.toUpperCase() ||
                Q_path.toUpperCase() === Alias.toUpperCase()
            ) {
                aoh_rows["values"].push({
                    name_Of_Person: page.name,
                    job_title_Of_Person: page.job_title,
                    Organization_Of_Person: ORG,
                    P_display: P_display,
                    Q_path: Q_path,

                    Alias_Of_ORG: Alias,

                    Name_Of_File: page.file.name,
                    Path_Of_File: page.file.path,
                    Link_Of_File: page.file.link,
                });
            }
        }
    }
}



// M39. dedbug output aoh_rows:
// #####################################################################
// dv.span("The following is the content of the `aoh_rows` from the DVJS10.<br>");
// aoh_rows.forEach((h_row) => {
//     if (h_row.Name_Of_File === "dic_19631130") {
//         dv.span(JSON.stringify(h_row, null, 2), "\n");
//     }
// });


// The following is the content of the aoh_rows from the DVJS10.
// [
//     {
//         name_Of_Person: "March",
//         job_title_Of_Person: "engineer",
//         Organization_Of_Person: "Org One",
//         P_display: "Org One",
//         Q_path: "",
//         Alias_Of_ORG: "Org One",
//         Name_Of_File: "dic_19630330",
//         Path_Of_File:
//             "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons/03_String/dic_19630330.md",
//         Link_Of_File: {
//             path: "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons/03_String/dic_19630330.md",
//             embed: false,
//             type: "file",
//         },
//     },
//     {
//         name_Of_Person: "April",
//         job_title_Of_Person: "manager",
//         Organization_Of_Person: {
//             path: "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Organizations/Some/Some Org.md",
//             type: "file",
//             display: " Org One",
//             embed: false,
//         },
//         P_display: "Org One",
//         Q_path: "Some Org",
//         Alias_Of_ORG: "Org One",
//         Name_Of_File: "dic_19630401",
//         Path_Of_File:
//             "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons/04_Link_with_alias/dic_19630401.md",
//         Link_Of_File: {
//             path: "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons/04_Link_with_alias/dic_19630401.md",
//             embed: false,
//             type: "file",
//         },
//     },
//     {
//         name_Of_Person: "July",
//         job_title_Of_Person: "operator",
//         Organization_Of_Person: {
//             path: "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Organizations/Some/Org One.md",
//             type: "file",
//             embed: false,
//         },
//         P_display: "",
//         Q_path: "Org One",
//         Alias_Of_ORG: "Org One",
//         Name_Of_File: "dic_19630707",
//         Path_Of_File:
//             "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons/07_Link_without_alias/dic_19630707.md",
//         Link_Of_File: {
//             path: "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons/07_Link_without_alias/dic_19630707.md",
//             embed: false,
//             type: "file",
//         },
//     },
//     {
//         name_Of_Person: "September",
//         job_title_Of_Person: "CEO",
//         Organization_Of_Person: {
//             path: "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Organizations/Some/Org One.md",
//             type: "file",
//             embed: false,
//         },
//         P_display: "",
//         Q_path: "Org One",
//         Alias_Of_ORG: "Org One",
//         Name_Of_File: "dic_19630912",
//         Path_Of_File:
//             "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons/09_Link_with_path/dic_19630912.md",
//         Link_Of_File: {
//             path: "100_Project/02_dataview/Q24_MetaFunction/Q24_test_data/Persons/09_Link_with_path/dic_19630912.md",
//             embed: false,
//             type: "file",
//         },
//     },
// ]


// M51 output aoh_rows: TABLE
// #####################################################################
dv.table(
    ["Name", "Job Title", "Organization", "Alias", "P_display", "Q_path"],
    aoh_rows
        .sort((h_row) => h_row.name_Of_Person, "asc")
        .map((h_row) => [
            dv.fileLink(h_row.Path_Of_File, false, h_row.name_Of_Person),
            h_row.job_title_Of_Person,
            h_row.Organization_Of_Person,
            h_row.Alias_Of_ORG,
            h_row.P_display,
            h_row.Q_path,           
        ])
);

```

Screenshots(DVJS10)