Autocomplete not possible with a field that is not an ID

[Paolo]

Hi everybody,

I'm trying to create a form to insert data into my appliances table. I would like to use appliance_types table as a lookup table in a dropdown box. That is not a problem. The problem is when I try to use Autocomplete. It seems not possible to use autocomplete on the ApplianceType field (text) rather than on the ApplianceTypeID. Any workaround? Thank you so much .

[CoopperBackpack]

Hello @Paolo,

If I understood you correctly, you try to use Autocomplete for the ApplianceTypeID field (like shown on the screenshot below).

 

Since you are going to submit data to the appliances table, it is not possible to submit Text (ApplianceType) to the ApplianceTypeID field due to the data type compatibility.

Please try the following workaround.

1) Add the Virtual field to the Submission form. This will be the AutoComplete field. 

 

2) Set the ApplianceTypeID field as a Calculated Value

SELECT ApplianceTypeID FROM appliance_types WHERE ApplianceType = '[@cbParamVirtual1]'

 

Double-check your fields and table names.

3) Hide the Calculated Value field. Go to the Advanced tab and check the "Hide Field" checkbox.

 

Maybe someone else will suggest another workaround  Hope, this solution works for you. 

 

[Paolo]

16 hours ago, CoopperBackpack said:

Hello @Paolo,

If I understood you correctly, you try to use Autocomplete for the ApplianceTypeID field (like shown on the screenshot below).

 

Since you are going to submit data to the appliances table, it is not possible to submit Text (ApplianceType) to the ApplianceTypeID field due to the data type compatibility.

Please try the following workaround.

1) Add the Virtual field to the Submission form. This will be the AutoComplete field. 

 

2) Set the ApplianceTypeID field as a Calculated Value

SELECT ApplianceTypeID FROM appliance_types WHERE ApplianceType = '[@cbParamVirtual1]'

 

Double-check your fields and table names.

3) Hide the Calculated Value field. Go to the Advanced tab and check the "Hide Field" checkbox.

 

Maybe someone else will suggest another workaround  Hope, this solution works for you. 

 

Thanks a lot @CoopperBackpack! I will try this for sure. You are a genius!

[Paolo]

@CoopperBackpack Just tested now: it works like a charm! Wonderful and elegant solution. Thanks again