Converting an index match setup to Tadabase

[slimpens]

Hi, I am new here. Is it possible to setup a database table in such a way to get the folllowing result: input LTV → reflect interest %? The screenshot is from Google Sheets.

[moe]

There is no native way of doing this, but I’m sure we can find a way.

Here’s a few ideas:

A) If you’re working with just a few basic values, you can use an equation field with IF statements.

How to use Equation Fields in your Tadabase app

B) If it’s somewhat more complex, you can use some JavaScript Pipe:

C) You can also the Rest API to lookup based on a single value, not exactly like Index and Match, you can see similar post here:

Calculate selling price automatically based on markup range cost falls into

Sorry about the delay. Here’s a video demonstrating how to do this with a pipe. I wish it was simpler, but hopefully this can help for now. Main issue with this which is still being worked on is that if the value falls on an exact number in the max or min field it will not work. For that we need an operator that is “higher than or equal to” and “lower than or equal to” [Using a pipe to do a lookup] Please let me know if you have any questions.

D) If you need a true Index and Match you can turn your a Google Spreadsheet into a standalone database and run a request against it in conjuction with a Pipe. This is complex.

I’ll make a video shortly to explain how to do this. It’s complex and will require advanced knowledge in Pipes and Google Sheets.

[moe]

@slimpens, just made you a video with the full walk through.

Video:

Using Index and Match Pipe Via Google Sheets and Pipes

Here’s the doc I’m using:

Index Match

Google App Script Code:

function doGet(e) {
  
  var INCOME=e.parameter.INCOME
  var RATE=e.parameter.RATE
  
  var response=findRow(INCOME, RATE)
  return ContentService.createTextOutput(JSON.stringify(response) ).setMimeType(ContentService.MimeType.JSON); 
}
function findRow(INCOME, RATE){
  const ss=SpreadsheetApp.getActiveSpreadsheet()
  const sheet=ss.getSheets()[0]
  const data=sheet.getDataRange().getValues()
  const header=data[0]
  var result=[]
  
  data.forEach(function(row){
    if(row[0]==INCOME) return result=row
  })
  
  var val1;
  for(var i=0; i<header.length; i++) {
    Logger.log(header[i]+ ' ' + RATE)
    if(header[i] == RATE) {
      val1=result[i]
      return;
    }
  }
  
  if (val1 == undefined) {
    for(var i=1; i<header.length-1; i++) {
      if (header[i] < RATE && header[i+1] > RATE) {
        if ( Math.abs(header[i]-RATE)  > Math.abs(header[i+1]-RATE) ) {
          val1 = result[i+1];
        } else {
          val1 = result[i];
        }
      }
    }
  }
  return (val1)
}
function toJson(header,values){
  var obj={}
  header.forEach(function(key,i){
    obj[key]=values[i]
  })
  
  return obj
}

Regards and I hope this helps.