"Call a webhook" has an issue.

[T_I_Phong]

Hello everyone,

I am facing an issue while creating a "call a webhook". I would like to explain my problem and seek support from the community.

I have 3 data tables:

1. PLANCODE table: It contains production plans for a day (around 70 rows).
2. FORMULA table: It contains production formulas for each product (around 65,000 rows).
3. tbQSCTNhan table: It is an extracted data from the FORMULA table based on the products in PLANCODE.

I created a webhook and ran it through Bots to extract data into the tbQSCTNhan table (the result should be around 2000-3000 rows). My Bot executed successfully, but it did not extract the complete data I need, only about 700-800 rows. After several attempts, it still produces similar results.

I have a couple of questions:

1. Could there be any errors in the webhook I created that I am not aware of?
2. Does the webhook have a limit on the number of rows it can extract in each execution?

I would greatly appreciate any support from the community. Thank you.

The body of the HTTP request.
{
"Action": "Add",
"Properties": {
"Locale": "en-GB",
"Timezone": "SE Asia Standard Time"
},
"Rows": [
<<Start: FILTER("FORMULA",[_THISROW].[Code] = [Code])>>
{
"No": "<<[No]>>",
"MaMH": "<<[_THISROW].[Code]>>",
"TenMH": "<<[_THISROW].[TenMH]>>",
"NgaySX": "<<TODAY()>>",
"MaNL": "<<[MaNL]>>",
"TenNL": "<<[TenNL]>>",
"QuyCach": "<<[QuyCach]>>",
"Ncc": "<<[Ncc]>>",
"QuyTrinh": "<<[QuyTrinh]>>",
"KhoiLuong": "<<[KhoiLuong]>>"
},
<<End>>
]
}

[Suvrutt_Gurjar]

Could you elaborate a bit more? How is the bot triggered?

[T_I_Phong]

To activate the Bots, I have created a time schedule as shown in the image below. The Bots have been functioning, but the results are not as comprehensive as I had expected.

[Suvrutt_Gurjar]

Thank you. So is it correct understanding that the bot runs for each row in the PLANCODE table extracting matching rows for that match the PLANCODE in the FORMULA table?

So for each row in the PLANCODE ( 70 rows) , basically the <<Start: FILTER("FORMULA",[_THISROW].[Code] = [Code])>> scans for 65000 roes in the FORMULA table to look for the matching [Code]. So in all the bot probably scans 65000 X 70 records for it to extract 2000-3000 records?

[T_I_Phong]

Yes, exactly as you said.

I have also manually checked the results of the Bot. It seems that it did not iterate through all the rows of the PLANCODE table, so it couldn't extract data from the FORMULA table. Or there might be some other reason that I'm not aware of.

[Suvrutt_Gurjar · Accepted solution]

Thank you. I believe you could try the following.

It appears that the bot scans around a huge number of around 4.5 Million records ( 65000*70) that could possibly result into timing issues.

You may want to create a slice called say " Todays_tbQSCTNhan"  on the FORMULA table with an expression something like 

IN( [Code], PLANCODE[Code])

Then your START expression for the bot could be 

<<START:  Todays_tbQSCTNhan[Key column of FORMULA table]>> in place of the existing expression <<Start: FILTER("FORMULA",[_THISROW].[Code] = [Code])>>

The above modified expression will possibly run with much much smaller number of records.

Edit: removed an extra parenthesis in the expression IN( [Code], PLANCODE[Code]) above.

[T_I_Phong]

Thank you very much, I tried following your instructions and it was successful. I have gained new knowledge from this experience.

[Suvrutt_Gurjar]

Thank you for the update and your are welcome. 

For any future reader of this thread, may I recommend this post thread as an example of sufficient and systematic inputs regarding the query shared by the question poster, in this case @T_I_Phong 

Such just the right inputs and willingness to answer incremental reverse queries from the solution giver help in coming out with a good solution that results in a win-win for both the question poster and solution giver. Sometimes the requirements can be tricky for the solution giver to imagine or understand the entire scenario at first go.

[T_I_Phong]

I'm glad to hear that, and I fully support sharing this topic with future readers.

[AleksiAlkio]

One thing as a site note.. if there are any virtual columns.. even one is enough in those two tables, it limits the row count because of data change Bot's timeout (2 minutes). With the scheduled Bot, it's 5 minutes.

[Suvrutt_Gurjar]

Yes, that is another vital point  @AleksiAlkio