Complex Financial Functions

prosperly

Airtable has been very useful to me because of their ability to perform Excel-like functions. For example this monthly payment formula can be done by reference other fields in a record:

{Loan Amount}*({Monthly Rate}*POWER(1 + {Monthly Rate}, INT(Amortization)*12))

/ (POWER(1+{Monthly Rate},(INT(Amortization)*12))-1)

From what I can tell, Xano allows for basic math, ie addition/subtraction, but not the above?

How would I go about replacing these formulas so I can migrate out of Airtable and into Xano.

Thanks!

Ray Deck

Xano can do all those. I thought your formula was interesting so I took a run at it as a custom function in Xano here: https://share.descript.com/view/TP2a6mEvrIO

Cool question! This hard end of the questions is what we regularly work on in our daily office hours at State Change Pro! Getting more out of Xano - and in particular getting complex answers from it - is a common topic on which we all make progress.

prosperly

@Ray Deck Amazing, I knew i was missing something. And thank you for the detailed response. Will check out State Change Pro.

prosperly

@Ray Deck For my use case, any change initiated by the user, ie updating a value in a form for 'Loan Amount' would have a knock on effect for values in the record.

That change may require many functions to be run. Will Xano know to re-run all the functions to update the record and all its fields?

In airtable, that was the case.

Ray Deck

Airtable and Xano are similar in that they mesh code and data. But their mental models are very different. Airtable abstracts you from how the computer thinks to the model of an Excel spreadsheet. You type in formulas and its like you changed data. But what actually is happening is that the system stores the data and is representing back to you the combination of data and code from the formulas for the "calculated" cells.

Xano lowers you to how the computer thinks about the data and code separately, trying to give you a more human-legible view of that mental model so you can work with it directly. This takes more work. You represent the data back to the customer by applying transformations at the time of your API call or background task. You start with the data at rest and apply that code for your customer. Thats you doing what airtable previously did for you.

The trade-off - at least initially - is ease of use vs control. For small, simple applications, the spreadsheet model may be just right. Later, the abstraction provided by the spreadsheet model creates its own frictions that degrade the ease of use. Just ask anyone who's built a massive Excel-based model. (Been there!) The clarity of the code vs data lets us do more with less friction as the use case gets more substantial.

For my part as I work with data where accuracy matters and the complexity is high, the clarity and control of "what I know" (the data) and "what I think" (the formulas) staying separate is a huge benefit. That's the difference in the mental models of a "no-code" spreadsheet-plus system like Airtable vs the "low-code" database-plus model like Xano.

prosperly

Thanks @Ray Deck ,

What I didn't quite understand when I asked the question was that the custom functions need to be a part of the API call. Conceptually that makes sense. In airtable, I had additional columns of data and applied the functions to those. So my calculation for 'Monthly Payment' on a mortgage became a new column in my table.

I guess, in the Xano world, I am not creating a new column in my data table, but instead running a function to calculate 'Monthly Payment' and adding the output of that to the API endpoint response?

Sidenote: i've joined you at state change pro, so I'll try and get some clarity then!