how to get table rows dynamically in botpress
# 🤝help
g
Hello guys, i have a bunch of CSV records that i pop into botpress About 228 of them. And i want my bot to display 3 specific columns in that row; Title column, Preacher column and download link column. Based on the key word it detects in the user request. for instance if the user says i want a sermon on faith, i want my bot to scan through the table records and look for titles that go with faith, and then send those 3 important columns. including The download link, which is the most important because its a sermon recommendation feature i am making with this method. Currently i use an ai task for this. But I noticed that with the personality agent turned on, the bot has been making stuff up and sending links that are not working. so i decided maybe i should just do the same functionality with code. but i actually dont know how to, because my coding skill is not good enough for now. can anyone suggest a good method to get rows from a table based on what the user request.
c
Hi there, for this I would recommend using the
filter
method to get a specific range of records from your Tables e.g. `all records that begin with X" . You can go into the tables and set-up your query using the built-in Query maker, paste the code there, then paste and replace it in the following code:
Copy code
js
try {
  const clientNumbers = await Data1Table.findRecords({
    filter: {"Phone":{"$regex":"^\\+55"}},
    limit: 30,
    offset: 0
  })
  workflow.phonearray = clientNumbers.map((product) => {
    return {
      name: product.name,
      phone: product.Phone 
    }
  })
} catch (error) {
  console.log('Something went wrong: ', error)
}
Take a look in the following thread where I answered a similar question: https://discord.com/channels/1108396290624213082/1232337094685491290 I know it can be difficult to use code but you can build complex search queries with it. As an alternative you can look more into the
QueryKnowledgeBase
card but in my personal experience code is superior. https://cdn.discordapp.com/attachments/1234845603528441906/1234899418788925470/brave_buPYoCqrGp.png?ex=66326934&is=663117b4&hm=d9b195ff1523428e17aa51d8af92a93b480ffe2f6bfa58b73d56c205d4e7b442&
g
@User okay thank you do much @cold-jewelry-54343 i was able to fight tooth and nail till i was able to get a code that worked after several correcting chatgpt workout. the code looks like this: // Function to search for sermons based on keyword async function searchSermons(keyword) { try { // Assuming Botpress provides a method to query data from the TcnSermonTable const tableData = await TcnSermonTable.findRecords({ filter: { Title: { $regex: keyword, $options: 'i' } } }) const results = tableData.map((row) => ({ Title: row.Title, Preacher: row.Preacher, DownloadLinks: row.DownloadLinks })) return results } catch (error) { console.error('Error retrieving data from TcnSermonTable:', error) return [] // Return an empty array if there's an error } } // Function to display matching sermons function displayMatchingSermons(matchingSermons) { if (matchingSermons.length === 0) { console.log('No sermons found matching the keyword.') } else { for (const sermon of matchingSermons) { console.log(
Title: ${sermon.Title}, Preacher: ${sermon.Preacher}, DownloadLinks: ${sermon.DownloadLinks}
) } } } // Perform search based on user input async function handleSearch() { const keyword = workflow.SermonQuery // Get user input from workflow.SermonQuery try { const matchingSermons = await searchSermons(keyword) workflow.userdownload = matchingSermons // Store the result in workflow.userdownload displayMatchingSermons(matchingSermons) } catch (error) { console.error('Error searching for sermons:', error) } } // Call handleSearch to start the search process handleSearch() and it works well. however the problem i am having is that the result is displaying in the console and not as a text card. in the code i told it to display result in workflow.userdownload. its not doing this. i know its some minor thing left but i dont know what it is.
@User if you look at the chatbox here it doesnt display the sermon details rather it just returned the text in my text card, and displayed the content in the console. any helps from here.? https://cdn.discordapp.com/attachments/1234845603528441906/1234928981040697437/image.png?ex=663284bc&is=6631333c&hm=d1ed969d07e98ddb1cf2ae0707f597aa237a0e3a47f56be98a9ec7a30f7a6854&
b
it's because the conversation and the log aren't the same thing
as I mentioned on the stream today, for instance
what you could do is save the value to a variable, and then send that in a card
c
Copy code
handleSearch() and it works well. however the problem i am having is that the result is **displaying in the console **and not as a text card. in the code i told it to display result in workflow.userdownload. its not doing this. i know its some minor thing left but i dont know what it is.
Copy code
js
async function searchSermons(keyword) {
  try {
    // Assuming Botpress provides a method to query data from the TcnSermonTable
    const tableData = await TcnSermonTable.findRecords({
      filter: {
        Title: { $regex: keyword, $options: 'i' }
      }
    });

    const results = tableData.map((row) => ({
      Title: row.Title,
      Preacher: row.Preacher,
      DownloadLinks: row.DownloadLinks
    }));

    return results;
  } catch (error) {
    console.error('Error retrieving data from TcnSermonTable:', error);
    return []; // Return an empty array if there's an error
  }
}

// Function to display matching sermons
function displayMatchingSermons(matchingSermons) {
  if (matchingSermons.length === 0) {
    workflow.matchingSermonsMessage = 'No sermons found matching the keyword.';
  } else {
    let message = "";
    for (const sermon of matchingSermons) {
      message += `Title: ${sermon.Title}, Preacher: ${sermon.Preacher}, Download Links: ${sermon.DownloadLinks}\n`;
    }
    workflow.matchingSermonsMessage = message;
  }
}

// Perform search based on user input
async function handleSearch() {
  const keyword = workflow.SermonQuery; // Get user input from workflow variable
  try {
    const matchingSermons = await searchSermons(keyword);
    workflow.userdownload = matchingSermons; // Store the result in workflow variable
    displayMatchingSermons(matchingSermons);
  } catch (error) {
    console.error('Error searching for sermons:', error);
    workflow.matchingSermonsMessage = 'An error occurred while searching for sermons.';
  }
}
You could try the following code. Make sure you make and display the
workflow
variables used inside the code like
workflow.userdownload
(to do so, go the left variable tab + create variable and call it workflow.userdownload). The reason btw why your code does not work is that variables in Botpress need to be declared using the
workflow
prefix ( theres also user, session but these are usecase specific)
g
i teied the code you suggested and it doesnt display anything at all. i declared the variable, workflow.userdownload and workflow.matchingsermonmessage, and asked a text card to display the content of workflow.userdownload as you advised. but it still displayed nothing. in the previous code it was displaying in the console. but this time. i doesnt display anything at all. Does this give you any further clues as to what i am doing wrong.? https://cdn.discordapp.com/attachments/1234845603528441906/1234960520721862706/Screenshot_2024-04-30_211046.png?ex=6632a21c&is=6631509c&hm=8a1b87e621c16e6421c7bcd4c25e9ddf4987b539332f9a8fa1aabf6bcf7947d7&
does anyone have any ideas yet.?
hi. guys. @bumpy-butcher-41910 @cold-jewelry-54343 Thanks for the support. I was finally able to figure out why the code was not displaying to the text card. It was because of the async function in the code. . so a friend of mine simply added await before the handle search e.g await handlesearch() and it worked. Apparently the text card was executing before the result of the search was revealed. So by the time the text card displays, the result of the search was not ready. The text card then prints empty. But prints to the console instead since the text card was already used up. I was also able to realise from chatgpt that the Execute code card in Botpress was itself an asynchronous function hence why you can put an await in front of the handle search and not get it flagged as a misstatement. I hope that makes sense tho. Suppose it did, this is me saying thanks for the help anyways.
c
@gentle-lunch-37811 you've saved me so much time 😭 i was having a similar issue with some, but not all, variables getting updated and i felt crazy. thank you so much for posting an update 🙏
g
You're welcome Hannah
12 Views