bbeshlian Posted May 22, 2019 Report Share Posted May 22, 2019 Hello, Can a relationship be built between a Formula type Primary Key in one table and a Formula type Foreign Key in another table? Thanks. Quote Link to comment Share on other sites More sharing options...
0 Aether Posted May 22, 2019 Report Share Posted May 22, 2019 Hello @bbeshlian, No, it cannot. Using a formula DataType to set up a relationship between two tables is not possible. You can check this documentation for more information: https://howto.caspio.com/tables-and-views/relationships/creating-and-managing-relationships/ I hope this information is helpful ~WatashiwaJin~ Quote Link to comment Share on other sites More sharing options...
0 SunakoChan Posted May 24, 2019 Report Share Posted May 24, 2019 Hi @bbeshlian, Here are the DataTypes that are compatible with each other. Data Type Description Examples Conversion Compatibility Text (255) Used for a string of text of up to 255 alphanumeric characters and/or symbols. This data type is the most common data type and yields the fastest performance in searches. You can also use this data type for numeric characters that are not used as numbers in calculations or formatting—such as phone numbers, zip codes, and social security numbers. Not doing so impacts formatting and prevents proper sorting by this field. First_Name, State, Phone, Zip_Code Text (64000) File (provided that the text field contains proper file paths) Text (64000) Used for a long string of text of up to 64,000 alphanumeric characters and/or symbols. Use this data type for description fields or other lengthy text data. Otherwise, use Text (255), which performs much faster. Description, Comments Text (255) (longer strings are truncated) File (provided that the text field contains proper file paths) Number Used for decimal numbers. Weight, height, area, percentage values Text (255) Text (64000) Integer (decimal values are truncated) Currency (allows up to four decimal points) Integer Used for numbers that do not have a decimal point, can be used as IDs and in relationships. Age, number of children Text (255) Text (64000) Number Currency Currency Used for money fields in any currency. Price, Salary Text (255) Text (64000) Integer (decimal values are truncated) Autonumber An automatically-assigned ID field. The value is incremented by 1 for each new record and cannot be changed except by resetting it for the entire table. Customer_ID, Record_ID Text (255) Text (64000) Number Integer Currency Prefixed Autonumber An automatically-assigned ID field with the ability to add a prefix. Use the Options area to configure the prefix and number format of the ID code to be generated. Customer_ID, Record_ID Text (255) Text (64000) Random ID A unique system-generated random ID field with the ability to add a prefix as well as define the length and composition of characters, digits, or both. Use the Options area to configure the prefix and number of characters the ID code should contain. You can also specify whether to include alphabet characters only, numbers only, or both (alphanumeric). Customer_ID, Record_ID Text (255) Text (64000) GUID A system-generated and globally-unique identifier value, typically used as a complex unique value. Customer_ID, Record_ID Text (255) Text (64000) Date/Time Used for date and time data. DataPages automatically display a calendar picker for date/time fields. ‘Precision’ is specified in the DataPage and is used to configure which part of the date or time part is used. Use the Options area to specify whether or not to allow blank values in this field. Followup_Date, Date_of_Birth Text (255) Text (64000) Timestamp Yes/No Used for fields that allow only two possible values: yes or no (true or false). By default a Yes/No input field appears as a checkbox in forms. Active_User, Requested_Newsletter, Published Text (255) Text (64000) Number File Used to associate files with a record. File fields allow your app users to upload files using a web form. Files are stored in your database and can be used in DataPages. Files can also be accessed in the Files area of All Assets, organized in a file folder structure. Profile_Photo, Resume, Contract Text (255) may be truncated Text (64000) Password Used for storing user passwords. The value of this field is always encrypted and cannot be seen in Datasheet or DataPages. Password None Timestamp A timestamp is a type of smart field that automatically records the date and time when a record is submitted and/or updated. Use the Options area to configure the time zone and the general behavior of the timestamp. Date_Submitted, Date_Updated Text (255) Text (64000) Date/Time Formula A formula field is a calculated value based on other fields of the record and various math, date/time, text and other expressions. Use the Options area to create the formula. Full_Name, Profit, Shift_Duration The data type of a formula field depends on its expression and return value and follows the conversion compatibility for that data type. After any conversion, the computed values are replaced with static values. List A special data type for storing a collection of strings, numbers or dates in a single field. Allergies, Pizza_Toppings None You may also check this HowTo documentation: https://howto.caspio.com/tables-and-views/data-types/ . I hope this helps. Quote Link to comment Share on other sites More sharing options...
0 Glitch Posted May 24, 2019 Report Share Posted May 24, 2019 Technically speaking, in order to create a Unique Formula Field inside your table, one of the fields inside the Formula field SHOULD be unique. Otherwise, possible chances are - there could have been duplicates for the value. If you want to create a relationship from one table to another, I suggest that you use the unique field instead. I hope this helps. Quote Link to comment Share on other sites More sharing options...
0 DefinitelyNot31337 Posted May 26, 2019 Report Share Posted May 26, 2019 Quick answer as to if it is possible to create a relationship with a formula field is No. This is because Formula fields are not actually stored or "persisted", AFAIK. If Caspio allows this as a feature in the future, I bet it's more or less similar to the workaround provided below (happening behind the scenes) The closest way to do so is to create a Text Field to permanently store the Formula Field's information. Specify a trigger to write values to this text field where in (OnInsert, OnUpdate) Update #inserted SET TextField = FormulaField. Then, you may treat the TextField as the actual foreign key. Quote Link to comment Share on other sites More sharing options...
0 bbeshlian Posted June 5, 2019 Author Report Share Posted June 5, 2019 Here is the trigger I built. It validates. However when I go to update/insert I get the error stating the Key field is required. Here are the Editing options for the Results page Thoughts? Thanks. Quote Link to comment Share on other sites More sharing options...
0 Queso Posted January 9 Report Share Posted January 9 Hi, if you are establishing a relationship between tables, you have to follow compatible data types. Please see the following screenshot for Relationship Compatibility: Quote Link to comment Share on other sites More sharing options...
Question
bbeshlian
Hello,
Can a relationship be built between a Formula type Primary Key in one table and a Formula type Foreign Key in another table?
Thanks.
Link to comment
Share on other sites
6 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.