Look up a value

[JediG]

Hi everyone. I'm brand new to Caspio coming from Filemaker Pro. I am trying to create a mock storefront in which a user (actually a student) selects an item they want to purchase (not real money) and a new record would be created in the Purchases table. I have all the items for sale in the Items For Sale table and I want the system to look up the price in the Items For Sale table. I can't figure out how to do this. I set up a relationship (based on my Filemaker knowledge) but I have no idea if it's correct. Any and all guidance is much appreciated.

[CoopperBackpack]

Hello @JediG,

The solution depends on your workflow. As far as I understand, you are going to create a Submission Form DataPage. 

1) You may try to use the Cascading Elements https://howto.caspio.com/datapages/datapage-components/cascading-elements/ 

For example, the Item field is a dropdown element that uses your Items table as a Lookup table. The Price can be the the Cascading dropdown that cascades from the Item name in the Items  table.

Here is the example:

You may use different Cascading elements. Please learn more about it in the article.

Maybe the article about Lookup tables will be helpful as well https://howto.caspio.com/tables-and-views/lookup-tables/

2) The second approach is to use the Calculated value Form Element with the SQL Select statement to receive the required value. 

For example:

 

Please replace the names of the table and names of the fields with your local names.

More details about the Calculated values https://howto.caspio.com/datapages/datapage-components/calculated-values/

Feel free to update this thread if you have any questions or concerns. 

[telly]

Hi @JediG,

Here are some guidelines documentations for starter's:

Database: https://howto.caspio.com/tables-and-views/table-and-database-design/
Database Relationships: https://howto.caspio.com/tables-and-views/relationships/
DataPages: https://howto.caspio.com/datapages/datapage-types/
For elements:  https://howto.caspio.com/datapages/forms/form-element-types/

You may also watch their tutorial video's at https://www.youtube.com/user/CaspioInc