How to add fields from multiple tables to a searchable form?

[Golden]

In a searchable form, can you add fields from tables other than the source table? For example, my application provides users the ability to pull data into a report based on their selections in listbox fields. One of the form fields I need are located in a different table. I reviewed the information on views and joining tables, but there really is not a relationship that I can establish between the two tables. 

Users will select job titles from a list box and rates will pull from the source table. The user also needs to be able to lookup a city and state to get a differential (%) that will be used to calculate a new rate for that specific area. I need a field (s) from the table with the differentials that will provide the user with the ability to select city and state so that the differential can be looked up and used in the new rate calculation. I will need a similar set up with another field from another table. 

I am new to Caspio. 

[DrSimi]

Hi @Golden,

Maybe I didn't understand your use case very well, but from what I gathered is that you have multiple fields used to filter a Report, and not all of the values in them may come from the Datasource of the DataPage. Are the values present in the datasource though? Or will you use the values selected as 'parameters' for other Calculated Fields?

Anyways, I think you can set your filtering field to be Dropdown form element, and choose Source->Lookup Table/View or "Both" as the Datasource. If you need to also be able to run SQL queries or even configure Cascading elements in the Search fields, I believe separating the Search and Results into two DataPages is the way to go. Check this out Separating Search Page and Results Page into Two Different Web Pages. If you want to use the values selected as parameters you can simply enable "pass value as parameter on exit" option under the Advanced tab, and use that parameter inside your Calculated Field. The value will be passed when they click Search. See Passing Parameters through Caspio.