How to use a virtual field and autocomplete when editing an existing record?

[ClayG]

I have been able to set up datapages using the technique of having a virtual field that is configured with AutoComplete which is then combined with using a Calculated Value element to select the appropriate record_id value using a SELECT statement and WHERE clause, e.g., = '[@cbParamVirtual1]'

That works well for new records in a submission form.

I'm struggling though with how to do something similar when using a Reports --> Tabular datapage, and you need the user to be able to change the existing field but still offer an AutoComplete.

To use a common example, let's say I have a simple order entry app, and thus I have a table for ORDER_DETAILS and a table for PEOPLE. The table for PEOPLE has a person_ID field (unique record identifier) and that value gets stored in the ORDER_DETAILS table as the ship-to-customer.

If I create a datapage to edit ORDER_DETAILS, when the user finds the order to edit, how can I make it easy for the user to change the ship-to-customer with an AutoComplete feature?

When I attempt to set up my detail page fields similar to a submission form, it doesn't work. The current "ship-to-customer" doesn't appear on the detail page screen unless I configure that field as either "Display Only" or perhaps as a Dropdown, but that's not practical since we have 1000's of people records.

 

[Tubby]

How is your table ORDER_DETAILS set up? Do you have the person_ID field in this table as an ID/unique data type? Thats the only thing I can think of that will restrict your form element options to display only. If it is a normal integer/text field it should have the option to set the field as autocomplete.

[ClayG]

Thanks, while the person_ID field is configured as unique in the PEOPLE table, I can't do so in the ORDER_DETAILS table since the same person could have multiple orders in the future.

I'll add that my person_ID field is set as a text(255) field in the ORDER_DETAILS table. In the PEOPLE table, it's also set that way but marked as unique. The values are similar to GUID (e.g., aa12710e-6085-4c30-9d7f-8ba6eef397de) but an external system is in control of these lengthy GUID values so I can't let Caspio pick them nor can I set the field type to GUID in Caspio for the same reason.

But I'm going to see if another 8-digit number field that I also have as an option for serving as a person_ID might work better...

Edited February 23, 2023 by ClayG
Additional info

[ClayG]

For others who might come along later, I was able to use my other ID field that was an 8-digit number. I had to first get it changed to be a unique column in my PEOPLE table (and *bad* on me for not setting like that from the start!), and then as was well.