Caspio not a capable replacement for Excel ? Missing INDEX/MATCH, VLOOKUP, ARRAY, SQL SELECT * functions

[Ulensr]

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

[Ulensr]

No one ?
Maybe I need to explained further

Here is Excel table

 

 

Based it a number of calculations by the form, the user can find what the metric content of their object would be after routing (colored columns based on the type of router used)

The formula would find the value of the computer model and toolset to apply e.g.  1m7)

The use can configure the machines they have and the technical capability (throughpout) using a single record update form which will add this to the user_table on the user's row

The formula in Excel is IF(B10=_MODELTYPE;INDEX(_MODELSPECS;MATCH(B16;_SELECTEDMODELTYPE;0)+_X);0)) where it will first look at the model the customer has , E.g. 4D model and look into that column which content and find the value which is above the content size/

In Caspio I only found doing this with a CASE - statement but that means 45 WHEN THEN statements to achieve the same which obviously makes it terribly slow.

Example

CASE

WHEN   _MODELTYPE = 4D AND MODELSPECS > 34 AND MODELSPECS <= 37
THEN 1M0

etc 
END

 also tried to using SQL bt no way to select the entire user row and build a query from there

 

Any help is appreciated !

[KlisaN137]

Hi @Ulensr,

What is the DataPage type on which you want to show this? Is the user inputting the MODELTYPE and MODELSPECS? 

After the upload to your account, is your table looking like this? (I added letter 'a' as prefix, because the system does not allow field names that starts with a number):

 

If so, 

[Ulensr]

Hi @KlisaN137 

the data page type is a report and the user manages this from his / her profile (which is a single record update form) so all these values are stores in individual columns in the user table, not in 2D as in you example as these settings are user dependent

So each user has a row there with User_ID, measurement system they use and the maximum content their 2D or 3D etc device is capable of handling

So onload all these values are present in the database, I don't even need to build relationships or anything

[KlisaN137]

I am not sure I follow your workflow.

Could you attach the screenshot of the table you have?

[Ulensr]

Certainly

The profile single record update form asks users for the maximum dimension of each model

SO They can update / customize it their according to their machinery

These values are added to the user_table where I store password and personal settings

And then I need to have the calculated value [@cbParamVirtual12] be able to find the model which upper limit is lower than the max capable of that model but higher then the next for cost reasons

 

Example of case where user selects 3D model (select in value 12) and has a calculated value to 245 (calculated in value 31)

CASE

WHEN ("[@cbParamVirtual12]" = "2D" AND  [@field:Dim_2M4_2D] > 240 AND [@cbParamVirtual31] <= [@field:Dim_2M5_ZD])
THEN 2M4

 

[Ulensr]

Anyone ? 

I guess my assumption is correct then that Caspio is not a good replacement for excel

My team will look at some competitors and also PowerApps 

[roattw]

 Caspio definitely is not a one-for-one replacement for Excel.   Has some limitations when trying to maintain its "low code" model.  I still have to use Excel (often with Power BI) simply because many simple calculations in Excel are strangely difficult or impossible in Caspio (unless you are good at coding). Esp the Excel functions you are looking for, or even a basic COUNTIF or tallying text string fields.  If those could be incorporated it could be a game changer.