Search the Community

Hi all, i'm trying to convert a very basic spreadsheet into Caspio but I'm really disappointed to learn that there is no alternative to INDEX/MATCH. Caspio seems incapable of working with arrays, there is no INDEX function nor MATCH function, calculated fields allow SQL but SQL queries do not allow for select * so I'm faced with a technical constraint it seems. What I'm trying to overcome (what Excel does effortlessly on the fly) is do some calculations to come to a measure, then this measure is compared to each user's custom model settings (using values in the user table) to tell the user which model to use. With an INDEX MATCH in Excel I'm able to do this 2 dimensionally with one formula. After struggling with Caspio, I found as only way to do this is with virtual fields as calculated value using CASE OK a lot of lines of code to write but hey, the result would be there ... or wouldn't it ? The result get's into place but ... it takes about 15 seconds to calculate. Does anyone have any other way to achieve this or am I bound to start looking for another platform ? Kind regards

I'm normalising a large Table which can extend to 3-4 million rows. One of the fields is 'Year' (eg '2017'). There are up to 80 different years in the database and obviously many are repeated many times, Is it worth indexing this field? I can make a table with a primary key and the related year, such as y1 = 2000, y2 = 2001, y3 = 2002 and so on, then relate the primary key to year field in the main Table. I'm just wondering whether all this fuss for so little is worthwhile. Is it going to make a performance improvement? Or should I be doing this a completely different way. Thanks for any advice