Jump to content
  • 0

Is it possible to populate a Bridge Table with two autocomplete selection elements?



As a First time Caspio user I'm sure there is a way to do this but I have been encountering some difficulties. 

I have created this solution a number of times directly using AJAX and a Databse, but there are some tiny things that seem to be holding me back in Caspio. I am hoping someone can assist me.

The situation is fairly simple. 3 tables, two of which are data and one of which institutes a many to many join between the two

So I have

Person (Table)
Person_ID (Unique) (ID)
FirstName (Text255)
LastName (Text255)
FullName (Formula FirstName + ' ' + LastName)

Employer (Table)
Employer_ID (Unique) (ID)
Employer Name

Person_ID (Text255)
Employer_ID (Text255)

Creating a DataPage I start with Person_Join_Employer and I am able to create dropdowns from Person and Employer using the FullName field for Person for the display and the Person_ID from Person for the Value and doing the same with Employer, Dropdown using EmployerName and Employer_ID for the name and value respectively. Select both, press a button and the record gets added..

Now I want to change the dropdowns to Autocompletes and everything goes of the rails. FullName is no longer an acceptable value, and I'm not able to pair a value with the selection. Can anyone help? I'd prefer not to have to perform one selection then pass the ID to another DataPage to make the second one, but I will try that if I have to.


Please Help,


Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Hello @KenWi,

As I understood the issue is the following: 
1) When the form element is Dropdown there are 2 options: Field for display and field for value (Full name and ID in your example)

2) When the form element is Autocomplete, there is only 1 option and the user submits a full name instead of ID.

You may test this solution:

1) Add a Virtual field and use an Autocomplete form element for it:


2) The table field should be a Calculated value to retrieve the ID based on the selected full name
For example:

SELECT Person_ID FROM Person WHERE FullName = '[@cbParamVirtual1]'



When the field is a Calculated value there is a 'Hide Field' checkbox on the Advanced tab. So, you may hide that field and the user will not see it.

Additional note: when 2 or more values are concatenated in the Formula field the data type of this formula is Text(64000). To improve performance you may convert the value to Text(255)

For example: CONVERT(varchar(255), [@field:FirstName]+space(1)+[@field:LastName])

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

Link to comment
Share on other sites

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.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...