I am trying to do a Database Search in a particular table in order to show the resul for the user. Example: The user enters his ID and the bot tells him when he will be able to get his next time-off (the date is already in the table).
Hi @Doggy-Wild_and_Free ,
Please check the document → Manage database tables & data | yellow.ai
to
- Create a table
- Fetch table data
- Edit table and add associate relations if required
- Insert/update data from bot flows
- Delete table or table fieldst
The result of the DB search will be stored in an Object.
{
"records": [
{
"bot_table": "x1661764941397_4bbecb68-ff6c-4288-8bb2-05cbd1",
"category": "Debit Cards",
"created_at": "2022-09-22T09:57:04.344Z",
"name": "Sam Griffith",
"updated_at": "2022-09-22T09:57:04.344Z"
},
{
"bot_table": "x1661764941397_4bbecb68-ff6c-4288-8bb2-05d1",
"category": "Debit Cards",
"created_at": "2022-09-22T09:57:04.344Z",
"name": "Rob Williams",
"updated_at": "2022-09-22T09:57:04.344Z"
}
]
}
let {var_name} = data.variables -> access response in variables
Post this you can filter the data required for you and resolve in a varible for a function and resolve it to be displayed as text in a next node.
Hello my friend Subhrajit, I really appreciate your attention and time, tha was really nice!
As a beginner, I understand your point, but I don´t know exactly where to put those things you said.
These article, I had read a couple times and it does not show us the solution you just explained to me, so, I will try to make it, and let you know. But the most important thing for me is to understand the meaning of each field, each variable and how the system works… so I will try and let you know! Thanks a lot my new friend!!! Rgds Luis
So, let us go step by step, if you don´t mind. In this SS
You can see that I have a table in the DB tab called “lead”.
So I am asking the bot to search in the table “lead”, the column “Nome” (name) looking for the variable vName (which is the name that the user inputed). So if the information is true, in other words, if he finds the name of the user in my table, I understood that in the “Response” part, I should put the column that the bot would find my answer which would be in column “ferias_a_partir_de”. And this value I would store in the variable “vFerias”. So in the next node if I put this variable, it would show me the content in the variable “vFerias”. But, what is happening is that he shows kind of [object Object] instead of the dates that I was expecting… as you can see in the bottom right side of mine SS.
Please tell me your thoughts… I am doing something wrong, but I guess it is a small thing… regards again friend!!!
My friend, I just saw one templated that you created called “Healthcare: Collect Reports”, as per my understanding, I have to create a function, is it true? … my goodnesss…hahah… waiting for your response! regards!
Yes, you need to create a function post the database call and in that function, you can write
let {var_name} = data.variables
console.log(var_name, "DB results")
to access the returned records from the DB.
In the DB response assign it to the same variable.
In the new function you can filter out any data and again resolve with the relevant data you need.
okay, so please answer this one more question and I think we will get over this soon:
my table is called = “lead”
the column that the answer that I am looking for is = “ferias_a_partir_de”
the variable of the name of the guy is = “vName”
the variable that cross checked the name in the table is called “vFerias” (object)
So, the bot is cross checking if the name of the guy is registered in the table, so far so good. And I am expecting to see the value of the same vName line but at the column “ferias_a_partir_de”
Then I have to write a function node after the database node. And this function is:
let {var_vName} = data.variables
console.log(var_vName, “ferias_a_partir_de”)
Is it right?
Please let me know how is the text inside the function and what is the name of the function! Or maybe, would you mind if we a make a quick call whenever you are available?
Gupta, I got stucked here…
Where do I write those things?
Thanks my friend, anytime you need me, I will make it if I can… I beg just a little patience… thank you
Actually I guess that instead of vName I should use the object variable which is vFerias.
Here is the right sequence to perform this action
Step1: perform the search operation in the bot flow to return the the results in the format @Subhrajit_Gupta has shared here
Step 2: Make sure you store the DB response in an object. here I’m storing it in <dbResopnseObject>
Step 3: use the function node after the DB node and call a function
Step 4: Log the data using the format shared below
return new Promise(resolve => {
let dbResponse = data.variables.dbResponseObject;
ymLib.logger.log(dbResponse,"Sample-DB-Search");
resolve();
});
Step 5: In the functions page, open up the logs by clicking on the icons on the right
This works like any other log. Click inside the logs and search for <Sample-DB-Search>
This is the DB response for the search action performed. You can now parse and use this data inside your functions
Step 6: Parse this data in the function. In this example, I’m fetching a sample mobile number that’s part of the user details table. Make sure to RESOLVE the data
return new Promise(resolve => {
let dbResponse = data.variables.dbResponseObject;
ymLib.logger.log(dbResponse,"Sample-DB-Search");
let user_phone = dbResponse.records[0].userphone;
ymLib.logger.log(user_phone,"Sample-phone");
resolve(user_phone);
});
Step 7: Back in the bot flow, store the returned value inside a variable of string datatype.
In this example, I’m storing the response in a variable called phoneNo in the bot flow and then printing it inside a text node.
Man… I am so happy!!! I got it!!! I got it in my first try!!! I am so so so so so HAPPY!!!
Thanks a lot for everyone!!! I really appreciate that!!!
But I still would like to understand the other way that Gupta had said before! But the first thing is done!!! I have got it!!! Thx
So guys, that was too easy to be real…
I got lost again, but I did a video showing you what is my step by step, so you guys may help me again, if you do not mind! thank you!
Please check out this video:
So guys, that was too easy to be real…
I got lost again, but I did a video showing you what is my step by step, so you guys may help me again, if you do not mind! thank you!
Please check out this video:
DB Search (vidyard.com)
Never mind, I gotta it!!! I am a die hard guy hahahahah