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

[KenWi]

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_Join_Employer
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,

Ken

[CoopperBackpack]

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. 

[KenWi]

@CoopperBackpack thank you for the speedy reply. I'll see about implementing this method.

 

Out of curiosity, I have seen a lot of the Caspio videos using space(1), is there a reason to use that over ' ' ? (New to Caspio)

 

Thanks again,

Ken

[KenWi]

Follow Up: that works perfectly. 

[CoopperBackpack]

Hello @KenWi,

Thanks for the update.

As for the space() function it is a matter of personal preference. For me, it looks more readable especially when there are multiple values to concatenate.

But from the output point of view, it does not matter what to use: just a ' ' or the space(0 function.