Update rows and add new ones using an api in loop

App Building
, ,
1

Hello everyone,

I hope you are doing well and taking care of yourself.

Problem Statement

  1. Enrich a table of HSN codes with their correct tax rates and descriptions
  2. Deactivate the HSN codes for which the API shows no result or the result contains a different tax rate than what is in the table.

Method

  1. Create a CSV file with hsn_codes and upload it to retool using the upload file component
  2. Parse the CSV data to the table using : "{{fileButton1.parsedValue['0']}}"
    Screenshot 2022-08-01 at 1.15.34 PM
    Screenshot 2022-08-01 at 1.15.34 PM2124×774 50 KB
  3. Create a javascript looper resource query using "Scripting Retool Document"
var rows = table1.data
var errors = '';
var total = rows.length;
var responses = '';
// var hsn_code = '';

function runQuery (i) {
  // Update the statusText with the current progress
  
  
  if (i >= rows.length) {
    console.log('Finished running all queries');
    return;
  }

  console.log('Running query for row', i);

  ctapi.trigger({
    additionalScope: { i: i }, // This is where we override the `i` variable from step 2!
    // You can use the argument to get the data with the onSuccess function
    onSuccess: function(data) {
      responses += data.results['0'].hits['0'].chapter_name + '<>' + data.results['0'].hits['0'].product_description + '<>' + data.results['0'].hits['0'].product_rate + '<>' + data.results['0'].hits['0'].product_hsn_code + '\n\n';
      // hsn_code = data.results['0'].hits['0'].product_hsn_code;
      statusText.setValue(responses);
            runQuery(i + 1);
    },
    onFailure: function(error) {
      // Update the errorsText with all the errors encountered
      errors += i.toString() + ':  ' + error +  '\n\n';
      error_hsn.setValue(errors);
      runQuery(i + 1);
    }
  });
}

runQuery(0);
  1. The query above will run for each value of hsn_code mentioned and the response will be as follows:
{
  "results": [
    {
      "hits": [
        {
          "chapter_index": "12",
          "chapter_name": "Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
          "chapter": "12 - Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
          "product_hsn_code": "1206",
          "hsn_code_length": "8",
          "type": "HSN",
          "product_rate": "5%",
          "product_description": "Sunflower Seeds, Whether Or Not Broken",
          "product_cess": "",
          "product_effective_date": "01/07/2017",
          "product_rate_revision": "",
          "Keywords": "",
          "": "",
          "objectID": "573fbddb62922_dashboard_generated_id",
          "_highlightResult": {
            "chapter_index": {
              "value": "12",
              "matchLevel": "none",
              "matchedWords": []
            },
            "chapter_name": {
              "value": "Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
              "matchLevel": "none",
              "matchedWords": []
            },
            "product_hsn_code": {
              "value": "<strong>1206</strong>",
              "matchLevel": "full",
              "fullyHighlighted": true,
              "matchedWords": [
                "1206"
              ]
            },
            "product_description": {
              "value": "Sunflower Seeds, Whether Or Not Broken",
              "matchLevel": "none",
              "matchedWords": []
            },
            "Keywords": {
              "value": "",
              "matchLevel": "none",
              "matchedWords": []
            }
          }
        },
        {
          "chapter_index": "12",
          "chapter_name": "Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
          "chapter": "12 - Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
          "product_hsn_code": "120600",
          "hsn_code_length": "6",
          "type": "HSN",
          "product_rate": "5%",
          "product_description": "Sunflower Seeds, Whether Or Not Broken",
          "product_cess": "",
          "product_effective_date": "01/07/2017",
          "product_rate_revision": "",
          "Keywords": "",
          "": "",
          "objectID": "1c399c2ddd65d5_dashboard_generated_id",
          "_highlightResult": {
            "chapter_index": {
              "value": "12",
              "matchLevel": "none",
              "matchedWords": []
            },
            "chapter_name": {
              "value": "Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
              "matchLevel": "none",
              "matchedWords": []
            },
            "product_hsn_code": {
              "value": "<strong>1206</strong>00",
              "matchLevel": "full",
              "fullyHighlighted": false,
              "matchedWords": [
                "1206"
              ]
            },
            "product_description": {
              "value": "Sunflower Seeds, Whether Or Not Broken",
              "matchLevel": "none",
              "matchedWords": []
            },
            "Keywords": {
              "value": "",
              "matchLevel": "none",
              "matchedWords": []
            }
          }
        },
        {
          "chapter_index": "12",
          "chapter_name": "Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
          "chapter": "12 - Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
          "product_hsn_code": "12060010",
          "hsn_code_length": "8",
          "type": "HSN",
          "product_rate": "5%",
          "product_description": "Sunflower Seeds, Whether Or Not Broken Sunflower Seeds, Whether Or Not Broken : Of Seed Quality",
          "product_cess": "",
          "product_effective_date": "01/07/2017",
          "product_rate_revision": "",
          "Keywords": "",
          "": "",
          "objectID": "16add90c9564cb_dashboard_generated_id",
          "_highlightResult": {
            "chapter_index": {
              "value": "12",
              "matchLevel": "none",
              "matchedWords": []
            },
            "chapter_name": {
              "value": "Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
              "matchLevel": "none",
              "matchedWords": []
            },
            "product_hsn_code": {
              "value": "<strong>1206</strong>0010",
              "matchLevel": "full",
              "fullyHighlighted": false,
              "matchedWords": [
                "1206"
              ]
            },
            "product_description": {
              "value": "Sunflower Seeds, Whether Or Not Broken Sunflower Seeds, Whether Or Not Broken : Of Seed Quality",
              "matchLevel": "none",
              "matchedWords": []
            },
            "Keywords": {
              "value": "",
              "matchLevel": "none",
              "matchedWords": []
            }
          }
        },
        {
          "chapter_index": "12",
          "chapter_name": "Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
          "chapter": "12 - Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
          "product_hsn_code": "12060090",
          "hsn_code_length": "8",
          "type": "HSN",
          "product_rate": "5%",
          "product_description": "Sunflower Seeds, Whether Or Not Broken Sunflower Seeds, Whether Or Not Broken : Other",
          "product_cess": "",
          "product_effective_date": "01/07/2017",
          "product_rate_revision": "",
          "Keywords": "",
          "": "",
          "objectID": "1113a3859b38d0_dashboard_generated_id",
          "_highlightResult": {
            "chapter_index": {
              "value": "12",
              "matchLevel": "none",
              "matchedWords": []
            },
            "chapter_name": {
              "value": "Oil seeds and oleaginous fruits, miscellaneous grains,seeds and fruit; industrial or medical plants; straw and fodder",
              "matchLevel": "none",
              "matchedWords": []
            },
            "product_hsn_code": {
              "value": "<strong>1206</strong>0090",
              "matchLevel": "full",
              "fullyHighlighted": false,
              "matchedWords": [
                "1206"
              ]
            },
            "product_description": {
              "value": "Sunflower Seeds, Whether Or Not Broken Sunflower Seeds, Whether Or Not Broken : Other",
              "matchLevel": "none",
              "matchedWords": []
            },
            "Keywords": {
              "value": "",
              "matchLevel": "none",
              "matchedWords": []
            }
          }
        }
      ],
      "nbHits": 4,
      "page": 0,
      "nbPages": 1,
      "hitsPerPage": 20,
      "exhaustiveNbHits": true,
      "query": "1206",
      "params": "query=1206&optionalWords=1206&highlightPreTag=<strong>&highlightPostTag=</strong>&typoTolerance=false",
      "index": "HSN_SAC_2021",
      "processingTimeMS": 1,
      "processingTimingsMS": {
        "getIdx": 1,
        "total": 1
      }
    }
  ]
}

If you would notice, the response results in 4 results as mentioned in nbHits : 4 and I take specific fields from the first object under the hits array in the looper query as:

      responses += data.results['0'].hits['0'].chapter_name + '<>' + data.results['0'].hits['0'].product_description + '<>' + data.results['0'].hits['0'].product_rate + '<>' + data.results['0'].hits['0'].product_hsn_code + '\n\n';

Assistance required
I want to save every such response in a table with column headers:

  • hsn_code
  • chapter_name
  • description
  • tax_rate
    couple other columns maybe added as per requirement in future.
    All of these responses should be saved in a separate row. Please let me know how can I achieve this.
2

Hey @IshanBizongo!

Does your enriched table need to be saved to an external DB or are you just looking to view it in Retool? Either way, this post on running a query for each item in an array and returning all the results together might be helpful. You may be able to use something like the following:

const originalData = fileButton1.parsedValue[0]; //should be [{hsn_code: 4203}, {hsn_code: 4202}]
async function enrichRow(row){
   const data = await enrichingQuery.trigger({additionalScope: {hsn_code: row.hsn_code}});
   const additionalData = _.pick(data.results[0].hits[0], ['chapter_name', 'product_description']); //this uses the lodash _.pick function to grab the properties you want but you may need to do some extra parsing here instead
   return Object.assign(row, additionalData); 
}
const enrichedData = await Promise.all(originalData.map(enrichRow));
return enrichedData;

Once you have the enriched data you can either pass it to and update query to modify an external database or store it in a temp state if you only intend to use it within the current session.

This likely won't be the exact syntax you need but is intended to give an idea of what's possible. Let me know if this seems like it could work or if you have any further questions :slightly_smiling_face:

1 Like
3

Thank you @kabirdas. I will try this out.