Comparing links to text in Dataview

face0b1101 · January 13, 2023, 7:33pm

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

justdoitcc · January 25, 2023, 8:49am

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

Q88_Links: Solutions

justdoitcc · January 25, 2023, 8:53am

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.

justdoitcc · January 26, 2023, 4:20am

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)

system · April 26, 2023, 4:21am

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.

Comparing links to text in Dataview

What I’m trying to do

Things I have tried

Topic : 1/2

Test

Input

dictionary files: Organizations

folder: Test

folder: Another

folder: Some

dictionary files: Persons

folder: 03_String

folder: 04_Link_with_display

folder: 07_Link_without_display

folder: 09_Link_with_path

folder: 11_excluding_String

folder: 12_excluding_Link

DQL01_deal_with_an_array_of_lnks_and_TABLE

Main DQL

Code DQL01_deal_with_an_array_of_lnks_and_TABLE

Screenshots(DQL01)

DQL03_deal_with_an_array_of_strings_and_TABLE

Main DQL

Code DQL03_deal_with_an_array_of_strings_and_TABLE

Screenshots(DQL03)

DQL10_deal_with_an_array_of_lnks_or_strings_and_TABLE

Main DQL

Notes

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

Original Example: Q1 (To be modified)

folder: 04_Link_with_display (To be modified)

A1:

Another Example: A1_11

folder: 04_Link_with_display (After being modified)

Code DQL10_deal_with_an_array_of_lnks_or_strings_and_TABLE

Screenshots(DQL10)

Reference

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

Topic : 2/2

DVJS10_deal_with_an_array_of_lnks_or_strings_and_TABLE

Main DVJS

Code DVJS10_deal_with_an_array_of_lnks_or_strings_and_TABLE

Screenshots(DVJS10)

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

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