Utilizes Discord's prefix event trigger to search for records in a QuickBase database and return results in a nice embed.
const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN});
const userToken = process.env.QUICKBASE_TOKEN;
const channel_id = context.params.event.channel_id;
// This is the general quickbase API URL for queries
const url = `https://api.quickbase.com/v1/records/query`;
const searchValue = context.params.event.content?.substring(11) || '';
//making sure all search values are correct
console.log('Search value:', searchValue);
if (!context.params.event.content) {
console.log('Text property not defined');
return;
}
const currentDate = new Date().toISOString().split('T')[0] + 'T00:00:00.000Z';
// you need to define your field numbers based on whatever is in your database.
// fields 1 and 2 need to be replaced with numbers in this case. You can add as many as you like.
// In this example, field 1 is a date. I want all records filtered by current date, onwards.
const filters = [
{field: '1', name: 'FIELD_NAME_HERE', operator: 'CT', value: searchValue},
{field: '2', name: 'FIELD_NAME_HERE', operator: 'CT', value: searchValue},
{field: '3', name: 'FIELD_NAME_HERE', operator: 'CT', value: searchValue},
{field: '4', name: 'FIELD_NAME_HERE', operator: 'CT', value: searchValue},
{field: '5', name: 'FIELD_NAME_HERE', operator: 'CT', value: searchValue},
{field: '6', name: 'FIELD_NAME_HERE', operator: 'CT', value: searchValue},
{field: '7', name: 'FIELD_NAME_HERE', operator: 'CT', value: searchValue},
];
// Generate the WHERE clause dynamically based on the filters object
const otherFiltersWhereClause = filters
.map((filter) => `{${filter.field}.${filter.operator}.'${filter.value}'}`)
.join('OR');
const dateFilterWhereClause = `{1.GTE.'${currentDate}'}`;
const whereClause = `(${otherFiltersWhereClause})AND(${dateFilterWhereClause})`;
//make sure filtering is correct
console.log('WHERE clause:', whereClause);
// Define the request body as a static variable. Make sure to add your APP ID as notated below.
// Docs: https://developer.quickbase.com/operation/runQuery
const requestBody = {
from: 'QUICKBASE_APP_ID_HERE',
select: [1, 2, 3, 4, 5, 6, 7],
where: whereClause,
sortBy: [{fieldId: 1, order: 'ASC'}],
groupBy: [{fieldID: 2, grouping: 'equal-values'}],
options: {
top: 3,
},
};
// replace your QB-Realm-Hostname with your Quickbase URL.
const result = await lib.http.request['@1.1.7']({
method: 'POST',
url: url,
headers: {
Authorization: userToken,
'QB-Realm-Hostname': 'DEMO.QUICKBASE.COM',
'User-Agent': 'Quickbase_API_Client',
'Content-Type': 'application/json',
},
body: JSON.stringify(requestBody),
});
const responseData = JSON.parse(result.body.toString());
// checks to make sure response data is correct
console.log('Response data:', JSON.stringify(responseData, null, 2));
// Send a temporary 'Fetching Results' message
const fetchingMessage = await lib.discord.channels['@0.3.4'].messages.create({
channel_id: channel_id,
content: '⠋ Fetching Results ⠋',
});
// this is where you can name your fields for the Discord embed. Name whatever you want based on field data.
const fieldMap = {
1: 'Name 1',
2: 'Name 2',
3: 'Name 3',
4: 'Name 4',
5: 'Name 5',
6: 'Name 6',
7: 'Name 7',
};
// Create an array of fields for all records. If no records exist, then put a N/A
const fields = responseData.data.map((record) => {
const fieldData = [];
const nameOne = record[1]?.value || 'N/A';
const nameTwo = record[2]?.value || 'N/A';
const nameThree = record[3]?.value || 'N/A';
const nameFour = record[4]?.value || 'N/A';
const nameFive = record[5]?.value || 'N/A';
const nameSix = record[6]?.value || 'N/A';
const nameSeven = record[7]?.value || 'N/A';
fieldData.push({id: 1, name: '*Name1*', value: venueName});
fieldData.push({id: 2, name: '*Name2*', value: engagements});
fieldData.push({id: 3, name: '*Name3*', value: presenterName});
fieldData.push({id: 4, name: '*Name4*', value: agencyName});
fieldData.push({id: 5, name: '*Name5*', value: playbackStatus});
fieldData.push({id: 6, name: '*Name6*', value: contractStatus});
fieldData.push({id: 7, name: '*Name7*', value: conductor});
return fieldData;
});
// create columns in the embed and add specific field data under them
const columnFields = {
'__**COLUMN TITLE 1**__': [1, 2],
'__**COLUMN TITLE 2**__': [3, 4],
'__**COLUMN TITLE 3**__': [5],
'__**COLUMN TITLE 4**__': [6, 7],
};
// Create an array of embeds from the fields
const embeds = fields.map((fieldData, index) => {
const numFields = fieldData.length;
const numColumns = Object.keys(columnFields).length;
const columns = {};
for (const [columnName, fieldIds] of Object.entries(columnFields)) {
columns[columnName] = [];
for (const field of fieldData) {
if (fieldIds.includes(field.id)) {
columns[columnName].push(field);
}
}
}
const embedFields = [];
for (let i = 0; i < numColumns; i++) {
const columnName = Object.keys(columns)[i];
const columnFields = columns[columnName];
const fieldValues =
columnFields
.map((field) => `${field.name}: **${field.value}**`)
.join('\n') || 'N/A';
embedFields.push({
name: columnName,
value: fieldValues,
// change this to false if you don't want columns displayed, and you want everything displayed vertically
inline: true,
});
}
// Let's add some styling to the embed.
const embed = {
title: `${fieldData[1].value} *(#${index + 1})*`,
color: 0xfd3e91, // pinkish col
fields: embedFields,
footer: {
text: `FOOTER TEXT HERE (result #${index + 1})`,
icon_url: `https://ICON_URL_HERE`,
},
};
// Check each field value for certain text and change the color accordingly
for (const field of embed.fields) {
console.log('Field value:', field.value);
console.log('Embed color:', embed.color);
if (field.value.includes('Confirmed')) {
embed.color = 65280; // Green color
} else if (field.value.includes('Inquiry Made')) {
embed.color = 16763904; // Orange color
}
}
return embed;
});
// Edit the 'Fetching Results' message to show the number of results found and the 'top' value
await lib.discord.channels['@0.3.4'].messages.update({
channel_id: channel_id,
message_id: fetchingMessage.id,
content:
responseData.data.length === 0
? `No results found.`
: `Found **${responseData.metadata.totalRecords}** total records. Displaying the results (max of **${requestBody.options.top}** at a time).`,
});
// Create and send the embeds
for (let i = 0; i < embeds.length; i++) {
await lib.discord.channels['@0.3.4'].messages.create({
channel_id: channel_id,
content: '',
embed: embeds[i],
});
}
// Show a message reminding the user to use more specific search strings
if (responseData.data.length > 0) {
await lib.discord.channels['@0.3.4'].messages.create({
channel_id: channel_id,
content: `Found **${responseData.metadata.totalRecords}** total records. I can display a max of **${requestBody.options.top}** result(s) at a time.`,
});
}
console.log('Message sent.');
