CRM Dashboard

This describes the structure and content of the CRM Dashboard and includes all the needed queries.

Structure

Each of the main sections contains

Pipeline

An overview of the

for the Opportunity Phases

Important

Remember that all inline DataviewJS queries need to be surrounded by single backticks ( `` ) and start with $= .

Disclaimer

All these queries were created, debugged, and documented with the help of the "Obsidian Oracle", a ChatGPT based custom GPT focused on Obsidian, Dataview, DataviewJS, and Templater scripts.
Not being a programmer, it really helped me, and might help you, too.

Qualify/Analyze

Weighted total

dv.paragraph("Weighted: " + dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("qualifying") || (p.Phase + "").toLowerCase().includes("needs analysis")).array().reduce((sum, p) => sum + (p["Weighted Value"] || 0), 0).toLocaleString())

Calculates and displays the total weighted value of all opportunities in the "CRM/Opportunities" directory that are in either the "Qualifying" or "Needs Analysis" phase. Here's a breakdown of each part:

  1. dv.paragraph(...): This function creates a paragraph to display the final result.
  2. dv.pages('"CRM/Opportunities"'): Retrieves all pages in the "CRM/Opportunities" directory.
  3. .where(p => (p.Phase + "").toLowerCase().includes("qualifying") || (p.Phase + "").toLowerCase().includes("needs analysis")): Filters the pages to include only those where the 'Phase' field, when converted to lowercase, contains either "qualifying" or "needs analysis". The + "" ensures that the 'Phase' field is treated as a string.
  4. .array(): Converts the filtered results into an array.
  5. .reduce((sum, p) => sum + (p["Weighted Value"] || 0), 0): This reduce function iterates over each page in the array and calculates the sum of the 'Weighted Value' field. If a page doesn't have a 'Weighted Value', it defaults to 0. The initial value of the sum is set to 0.
  6. .toLocaleString(): Converts the final sum into a localized string representation, which is more readable (e.g., adding commas as thousands separators).

Unweighted total

dv.paragraph("Unweighted: " + dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("qualifying") || (p.Phase + "").toLowerCase().includes("needs analysis")).array().reduce((sum, p) => sum + (p.Value || 0), 0).toLocaleString())

Calculates and displays the total unweighted value of all opportunities in the "CRM/Opportunities" directory that are currently in either the "Qualifying" or "Needs Analysis" phase. Here's a detailed explanation of each component of the query:

  1. dv.paragraph(...): This function creates a paragraph element to display the final result in a readable format.
  2. dv.pages('"CRM/Opportunities"'): Retrieves all pages in the "CRM/Opportunities" directory of your Obsidian vault.
  3. .where(p => (p.Phase + "").toLowerCase().includes("qualifying") || (p.Phase + "").toLowerCase().includes("needs analysis")): Applies a filter to the retrieved pages. It checks the 'Phase' field of each page and includes it in the result set if the field, converted to lowercase, contains either "qualifying" or "needs analysis". The + "" ensures that the 'Phase' field is treated as a string, preventing errors if the field is undefined or not a string.
  4. .array(): Converts the filtered results into an array for further processing.
  5. .reduce((sum, p) => sum + (p.Value || 0), 0): This is a reduce function that iterates over each page in the array. For each page, it adds the value found in the 'Value' field to an accumulating sum. If a page does not have a 'Value' field or if it's null, it defaults to 0. The initial value of the sum is set to 0.
  6. .toLocaleString(): Converts the total sum into a localized string representation. This can make the number more readable by, for example, adding commas as thousands separators depending on the locale.

Opportunities list

dv.list(dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("analysis") || (p.Phase + "").toLowerCase().includes("qualifying")).map(p => p.file.link))

Used to create a list of links to pages within the "CRM/Opportunities" directory where the opportunities are in either the "Analysis" or "Qualifying" phase. Here's a breakdown of each part of the query:

  1. dv.list(...): This function generates a list on your page. The content of this list is determined by the result of the inner query.
  2. dv.pages('"CRM/Opportunities"'): Retrieves all pages within the "CRM/Opportunities" directory of your vault.
  3. .where(p => (p.Phase + "").toLowerCase().includes("analysis") || (p.Phase + "").toLowerCase().includes("qualifying")): This filter function includes pages based on their 'Phase' field. It converts the 'Phase' field to a string and then to lowercase, and checks if it contains the words "analysis" or "qualifying". The + "" ensures that the 'Phase' field is treated as a string, which is necessary to prevent errors in case the 'Phase' field is undefined or not a string.
  4. .map(p => p.file.link): Transforms the filtered list of pages into a list of links to those pages. Each page is represented by its link, allowing easy navigation to the respective page.

Proposed

Weighted total

dv.paragraph("Weighted: " + dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("proposal")).array().reduce((sum, p) => sum + (p["Weighted Value"] || 0), 0).toLocaleString())

Unweighted total

dv.paragraph("Unweighted: " + dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("proposal")).array().reduce((sum, p) => sum + (p.Value || 0), 0).toLocaleString())

Opportunities list

dv.list(dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("proposal")).map(p => p.file.link))

Negotiating

Weighted total

dv.paragraph("Weighted: " + dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("negotiation")).array().reduce((sum, p) => sum + (p["Weighted Value"] || 0), 0).toLocaleString())

Unweighted total

dv.paragraph("Unweighted: " + dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("negotiation")).array().reduce((sum, p) => sum + (p.Value || 0), 0).toLocaleString())

Opportunities list

dv.list(dv.pages('"CRM/Opportunities"').where(p => (p.Phase + "").toLowerCase().includes("negotiation")).map(p => p.file.link))

Accounts

Lists all accounts alphabetically, grouped by Account Type.
See Accounts for all details about this entity.

Customers

List of all active accounts of the type Customer.

dv.list(dv.pages('').filter(f => f.file.path.startsWith("CRM/Accounts/") && f.Active === true && f.Type && f.Type.includes("Customer")).sort(f => f.file.name, "asc").map(f => f.file.link))

Generates a list of links to pages in the "CRM/Accounts/" directory that meet specific criteria. Here's a breakdown of the query:

  1. dv.pages(''): This function retrieves all pages in your Obsidian vault. The empty string '' is a placeholder for a query that would normally go inside the parentheses, but in this case, it's left blank to select all pages.
  2. .filter(f => ...): This method filters the pages based on the provided function. It iterates over each file f in the collection.
  3. f.file.path.startsWith("CRM/Accounts/"): This condition checks if the file path starts with "CRM/Accounts/". It's a way to narrow down the files to a specific directory in your vault.
  4. f.Active === true: This checks if the Active property of the file is true. It's likely a custom field you've set for each file.
  5. f.Type && f.Type.includes("Customer"): This is a two-part condition. First, f.Type checks if the Type property exists and is not null or undefined. If it exists, f.Type.includes("Customer") checks whether this property contains the substring "Customer".
  6. .sort(f => f.file.name, "asc"): This method sorts the filtered files alphabetically (ascending) based on their file names.
  7. .map(f => f.file.link): This transforms the sorted list of files into a list of their links. Essentially, it takes each file f and returns its Markdown link.
  8. dv.list(...): Finally, this function takes the list of Markdown links and displays them as a list in your Dataview query result.

Partners

List of all active accounts of the type Partner.

dv.list(dv.pages('').filter(f => f.file.path.startsWith("CRM/Accounts/") && f.Active === true && f.Type && f.Type.includes("Partner")).sort(f => f.file.name, "asc").map(f => f.file.link))

Suppliers

List of all active accounts of the type Supplier.

dv.list(dv.pages('').filter(f => f.file.path.startsWith("CRM/Accounts/") && f.Active === true && f.Type && f.Type.includes("Supplier")).sort(f => f.file.name, "asc").map(f => f.file.link))

Contacts

See Contacts for all details about this entity.

10 most recent

A list of the 10 most recently modified active contacts.

dv.list(dv.pages('').where(f => f.file.path.startsWith("CRM/Contacts/") && f.Active === true).sort(f => f.file.mtime.ts, "desc").limit(10).map(f => f.file.link))

Creates a list of the 10 most recently modified active contacts within the "CRM/Contacts/" directory. Here's a breakdown of how the query works:

  1. dv.list(...): This function is used to create a list on the page, where the content of the list is determined by the result of the query inside the parentheses.
  2. dv.pages(''): Retrieves all pages in your vault.
  3. .where(f => f.file.path.startsWith("CRM/Contacts/") && f.Active === true): This is a filter function that applies two conditions:
    • f.file.path.startsWith("CRM/Contacts/"): Filters pages to include only those whose file paths start with "CRM/Contacts/". This typically means it's looking for files within the "CRM/Contacts/" folder.
    • f.Active === true: Ensures that only files where the "Active" field is set to true are included. This condition filters out any inactive contact records.
  4. .sort(f => f.file.mtime.ts, "desc"): Sorts the filtered files in descending order based on their last modified time (mtime.ts). This means the most recently modified files are listed first.
  5. .limit(10): Limits the results to the top 10 files based on the sorting criteria. This is useful for focusing on the most recent or relevant items.
  6. .map(f => f.file.link): Transforms the list of files into a list of links to those files. Each item in the list will be a clickable link that takes you directly to the corresponding contact page.

Birthdays in the next 30 days

A list of active contacts with a birthday within 30 days from today's date.

dv.list(dv.pages('').filter(f => { let today = new Date(); today.setHours(0, 0, 0, 0); let thisYearBirthday = new Date(today.getFullYear(), new Date(f.Birthday).getMonth(), new Date(f.Birthday).getDate()); let inThirtyDays = new Date(); inThirtyDays.setDate(today.getDate() + 30); return f.file.path.startsWith("CRM/Contacts/") && f.Active === true && thisYearBirthday >= today && thisYearBirthday <= inThirtyDays; }).sort(f => new Date(new Date().getFullYear(), new Date(f.Birthday).getMonth(), new Date(f.Birthday).getDate()), "asc").limit(10).map(f => f.file.link))

Generates a list of links to the 10 active contacts in the "CRM/Contacts/" directory whose birthdays are within the next 30 days, sorted by the nearest upcoming birthday. Here's a breakdown of the query:

  1. dv.list(...): Creates a list on the page. The content of this list is generated by the results of the inner query.
  2. dv.pages(''): Retrieves all pages in your vault.
  3. .filter(f => { ... }): This filter function applies several conditions to each page:
    • Determines the current date and sets the time to the beginning of the day (today.setHours(0, 0, 0, 0)).
    • Calculates the date of this year's birthday for each contact (thisYearBirthday).
    • Sets a date 30 days from today (inThirtyDays.setDate(today.getDate() + 30)).
    • Filters pages based on:
      • Being in the "CRM/Contacts/" directory (f.file.path.startsWith("CRM/Contacts/")).
      • Having an "Active" field set to true (f.Active === true).
      • Having a birthday (thisYearBirthday) that falls between today and the next 30 days (thisYearBirthday >= today && thisYearBirthday <= inThirtyDays).
  4. .sort(f => new Date(new Date().getFullYear(), new Date(f.Birthday).getMonth(), new Date(f.Birthday).getDate()), "asc"): Sorts the filtered pages in ascending order based on the date of this year's birthday.
  5. .limit(10): Limits the results to the top 10 files.
  6. .map(f => f.file.link): Transforms the list of files into a list of links to those files.

(30+ days) without interaction

A list of active contacts with whom there was no interaction for more than 30 days or at all.

dv.list(dv.pages('"CRM/Contacts"').where(contact => contact.Active === true && (function() { const interactions = dv.pages('"CRM/Interactions"').where(interaction => interaction.Contacts && interaction.Contacts.includes(contact.file.name)).sort((a, b) => b.file.mtime - a.file.mtime).array(); return interactions.length === 0 || (Date.now() - new Date(interactions[0].file.mtime).getTime()) > (30 * 24 * 60 * 60 * 1000); })()).map(contact => contact.file.link))

Creates a list of links to active contacts in the "CRM/Contacts" directory who either have no recorded interactions in the "CRM/Interactions" directory or whose last interaction occurred more than 30 days ago. Here's a breakdown:

  1. dv.list(...): Generates a list on your page with the content determined by the results of the inner query.
  2. dv.pages('"CRM/Contacts"'): Retrieves all pages within the "CRM/Contacts" directory.
  3. .where(contact => contact.Active === true && (function() { ... })()): This is a filter function that includes only those contacts that are active (contact.Active === true) and meet additional criteria defined within the inner function:
    • The inner function defines a constant interactions:
      • dv.pages('"CRM/Interactions"'): Retrieves all pages within the "CRM/Interactions" directory.
      • .where(interaction => interaction.Contacts && interaction.Contacts.includes(contact.file.name)): Filters interactions to include only those that involve the current contact (contact.file.name).
      • .sort((a, b) => b.file.mtime - a.file.mtime): Sorts these interactions by modification time (mtime), with the most recent interactions first.
      • .array(): Converts the sorted interactions into an array.
    • The function then checks two conditions:
      • interactions.length === 0: There are no recorded interactions for the contact.
      • (Date.now() - new Date(interactions[0].file.mtime).getTime()) > (30 * 24 * 60 * 60 * 1000): The most recent interaction (if any) occurred more than 30 days ago. This is checked by comparing the current time (Date.now()) with the last modification time of the first interaction in the sorted list.
  4. .map(contact => contact.file.link): Transforms the list of filtered contacts into a list of links to their respective pages.

Opportunities

See Opportunities for all details about this entity.

Open

Lists all open Opportunities.

dv.list(dv.pages('"CRM/Opportunities"').where(page => page.Phase && !(page.Phase + "").toLowerCase().includes('closed')).sort((a, b) => a.file && b.file ? a.file.name.localeCompare(b.file.name) : 0).map(page => page.file.link))

Creates a list of links to pages within the "CRM/Opportunities" directory, excluding those that are in a phase marked as 'Closed'. The results are sorted alphabetically by file name. Here's the breakdown of the query:

  1. dv.list(...): Generates a list on your page with the content based on the results of the inner query.
  2. dv.pages('"CRM/Opportunities"'): Retrieves all pages within the "CRM/Opportunities" directory of your vault.
  3. .where(page => page.Phase && !(page.Phase + "").toLowerCase().includes('closed')): This filter function applies two conditions:
    • page.Phase: Checks if the 'Phase' field exists for the page.
    • !(page.Phase + "").toLowerCase().includes('closed'): Ensures that the 'Phase' field, converted to a string and then to lowercase, does not include the word 'closed'. The + "" is used to convert the 'Phase' field to a string in case it is not already a string type, preventing errors if the field is undefined or not a string.
  4. .sort((a, b) => a.file && b.file ? a.file.name.localeCompare(b.file.name) : 0): Sorts the filtered pages alphabetically by their file name. The localeCompare function is used for this string comparison, which provides a case-insensitive and locale-aware sort.
  5. .map(page => page.file.link): Transforms the sorted list of pages into a list of links to those pages. Each item in the list will be a clickable link that takes you directly to the corresponding opportunity page.

10 recent wins

Shows the 10 most recently won opportunities.

dv.list(dv.pages('"CRM/Opportunities"').where(page => page.Phase && (page.Phase + "").toLowerCase().includes('won')).sort((a, b) => b.file.mtime.localeCompare(a.file.mtime)).limit(10).map(page => page.file.link))

Generates a list of the 10 most recently modified pages in the "CRM/Opportunities" directory where the opportunity's phase is marked as 'Won'. The results are sorted by the last modified time in descending order. Here's an explanation of each part of the query:

  1. dv.list(...): Creates a list on the page, with the content of this list determined by the results of the inner query.
  2. dv.pages('"CRM/Opportunities"'): Retrieves all pages within the "CRM/Opportunities" directory of your vault.
  3. .where(page => page.Phase && (page.Phase + "").toLowerCase().includes('won')): Filters the pages based on two conditions:
    • page.Phase: Ensures that the 'Phase' field exists for the page.
    • (page.Phase + "").toLowerCase().includes('won'): Checks if the 'Phase' field, converted to lowercase, includes the word 'won'. The + "" ensures that the 'Phase' field is treated as a string, preventing errors in case the field is undefined or not a string.
  4. .sort((a, b) => b.file.mtime.localeCompare(a.file.mtime)): Sorts the filtered pages by their last modified time (mtime) in descending order (b before a). The localeCompare function is used for comparing the modification times as strings.
  5. .limit(10): Limits the results to the top 10 files based on the sorting criteria.
  6. .map(page => page.file.link): Transforms the list of filtered and sorted pages into a list of links to those pages. Each item in the list will be a clickable link that takes you directly to the corresponding opportunity page.

10 recent losses

Shows the 10 most recently lost opportunities.

dv.list(dv.pages('"CRM/Opportunities"').where(page => page.Phase && (page.Phase + "").toLowerCase().includes('lost')).sort((a, b) => b.file.mtime.localeCompare(a.file.mtime)).limit(10).map(page => page.file.link))

10 Most Recent Interactions

See Interactions for all details about this entity.
Lists the 10 most recent interactions.

dv.list(dv.pages('').filter(f => f.file.path.startsWith("CRM/Interactions/")).sort(f => f.file.mtime.ts, "desc").limit(10).map(f => f.file.link))

Generates a list of links to the 10 most recently modified notes that are located within the "CRM/Interactions/" directory. Here's an explanation of each part of the query:

  1. dv.list(): This function is used to create a list of items based on the provided criteria.
  2. dv.pages(''): This part of the query specifies that it's looking at all pages in your Obsidian vault. The empty string '' as the argument means it considers all pages.
  3. .filter(f => f.file.path.startsWith("CRM/Interactions/")): This filter function is used to select only those pages whose file path starts with "CRM/Interactions/". In other words, it's filtering for pages that are related to CRM interactions. This is useful if you organize your notes into folders, and you only want to include notes within the "CRM/Interactions/" directory.
  4. .sort(f => f.file.mtime.ts, "desc"): This part of the query is sorting the filtered pages in descending order based on their modification time (mtime). The "desc" argument means that it's sorting them in descending order, so the most recently modified pages will appear at the top of the list.
  5. .limit(10): This limits the results to only the top 10 pages after filtering and sorting. It ensures that you only see the 10 most recently modified pages related to CRM interactions.
  6. .map(f => f.file.link): Finally, this map function extracts the links to the selected pages and creates a list of those links. In Obsidian, a page link is typically enclosed in double square brackets, like [[Page Name]]. So, the output of this query will be a list of links to the 10 most recently modified pages related to CRM interactions.

Products

See Products for all details about this entity.

Active

A list of all active products.

dv.list(dv.pages('"CRM/Products"').where(page => page.Active === true).map(page => page.file.link))

Generates a list of links to CRM product pages that are located within the "CRM/Products" directory and have their Active property set to true. Here's an explanation of each part of the query:

  1. dv.list(): This function is used to create a list of items based on the provided criteria.
  2. dv.pages('"CRM/Products"'): This part of the query specifies that it's looking at all pages within the "CRM/Products" directory. The argument in double quotes '"CRM/Products"' is specifying the directory path within your Obsidian vault.
  3. .where(page => page.Active === true): This is a filtering operation. It filters the pages within the "CRM/Products" directory based on a condition. Specifically, it selects pages where the value of the Active property on the page is true. This implies that it's looking for active CRM products.
  4. .map(page => page.file.link): After the filtering, this map function is used to extract the links to the selected pages. In Obsidian, a page link is typically enclosed in double square brackets, like [[Page Name]]. So, this part of the query creates a list of links to the CRM products that meet the criteria of being active.

Inactive

A list of all inactive products.

dv.list(dv.pages('"CRM/Products"').where(page => page.Active === false).map(page => page.file.link))