Search the Community

I am attempting to write a formula to LookUp a value in another table. This is what I have but it did not work. LookUp(tblUnit, UnitRent, UnitID=[@field:XBSUnitID]) tblUnit: The name of the table to query. UnitRent: The field containing the rent value to retrieve. UnitID=[@field:XBSUnitID]: The condition for the lookup, where UnitID in tblUnit must match the value in the field named XBSUnitID.writing

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 have a rather complicated task that I'm trying to execute. This may take a bit of explaining. It references three lookup tables and two other tables. The point of this task is to set a number of days required to complete a specific task (Engineering, Welding, etc.) in a project. These days vary based on the unit size and type. Here is part of the table that I am using for reference so that you can understand what I'm working with. So for a job making a "Non Jacket Bin" that is under 500L, the Engineering task should take 5 days and the Weld task should take 10 days. Here is the task as I currently have it. So the table that this is updating is specifically for tracking the number of days required to complete each operation for a job line. It is connected to the job lines table via a Line Number. Right now I only have it updating the days it takes in Engineering but I will add Weld and more. That's why I have the joins in the Update statement and not the select - just to avoid redundancy. So first, it joins the days line number to the lines line number. Then it takes the unit size and joins it to a lookup table. This lookup table is used to convert the size to an integer. In the lines table it is written as "100L" so this join just allows for a value "Liters" to be used later which removes the L. The next join connects the Unit Type to another lookup table which will be used to find the general category the type is in ("Bin" rather than a code like "BINX"). Finally, this is joined to a table that is similar to the one pictured above. Here is part of that table for reference: (The days are the days before the ship date) The Unit Size refers to anything that is less than or equal to that number. Thus, why my join is on Liters >= Unit Size. I then sort the select by the unit size ASC which should connect Liters to the smallest Unit size without going over. Instead, what I get is all units, regardless of size, assigned the smallest days. What am I doing wrong? Any help would be appreciated!

So I'm trying to make a trigger so that when new data is inserted/updated in my table it checks that the address doesn't match any existing addresses for that Customer and if not, it stores the address in a lookup table for easier inputting in the future. This is the trigger I set up but it isn't working. Anyone know a better way to do this?

I got this dialog box in one of my Report DataPage. I have a dropdown in my search where I used a lookup table as a source. Right now, I just close this dialog box as I am not sure if I should confirm. Also, cannot remember when the pre-existing got generated. I cannot find any documentation about this in Caspio's online help center. Anyone who encountered this also?

I am using a Tabular Report to view data, and update some fields. There is a field I want to use as a unique key to a lookup table and display the description from the lookup. For example, I have rows of data regarding trees. For field tree_name e.g. "Oak Tree" I want to display the Latin Name which would be contained in the lookup table "trees_and_latin_names" with key of tree_name (and another field with the Latin Name in it) . Having solved this, if there is no value in the Tabular Report, I'd like the user to be able to type in their own value, this being written to their own data (not the lookup table "trees_and_latin_names". Any help would be appreciated ! Thanks, Mike.

Sharing this in case you need to show the text instead of ID value in report datapage. Steps: 1) Make sure you have a table that has your lookup values 2) Add a Calculated Field in your Tabular Report DataPage and insert the code below SELECT LookUpTableDisplayText FROM LookUpTable where LookUpTableIDvalue='[@field:IDFieldInDataPage]' 3) If you want to hide the ID column, you can use the steps below.

Is it possible to have validated dropdown options when entering data into a table? I know how to do this in a datapage, but I would like to provide a similar option for internal users who have access to data on the back end.

Is there a way to display multiple fields in a Lookup? My DataPage adds records to an Activity Log. Some activities are related to a record in the Students table, some are not. I would like to have a Lookup Table for the Student_ID field that displays First_Name and Last_Name from the Students table, but the value for the field is Student_ID. The field can also be left blank.

I have a table "Product"; Product_id (autonumber) Product_name (text) Product_valid (yes/now) I an different table, i have a lookup Field to table "Product" I only want the user to be able to select records where I have marked "yes" in the checkbox? Do I have to create a own view, or is it at better way?