Input object - how to replace the display of the index number field with its value field if we use two linked tables?

[dimonsg]

I have two index-linked tables:
table1 with fields: id1, nameTable2, param1, param2
table2 with fields: id2, name
The fields table1.nameTable2 and table2.id2 are linked by an index.
I made a form for displaying and editing records in table1. In the main form, I set up an SQL query to replace the index display of the associated table1.nameTable2 field with table2.name - everything works beautifully. In the edit form, the fields with the values tabel1.param1, tabel1.param2 are displayed without problems, but in the last field I see the value of the indexes table1.nameTable2.
Help me set up this field. I need the values from the table field to be substituted into it table2.name , and also when entering characters, the filter was triggered as in the figure (I did this) and if we entered a value that is not in table2, then when saving it, it was automatically saved to this table

[kev1n]

Hi,

Can't you use a select object?

[dimonsg]

I can use select object, but then how to enter values that are not in table2. I want to automatically fill table 2 with the missing values in it and suggest using them the next time a record is created.

[kev1n]

Could you kindly provide the SQL you're using?

[dimonsg]

I'm using a SELECT table2.name FROM table2
in an object of type Input.

I want the user, when entering a value in this field, to receive a hint for what I already have in the database and if he does not see what he wants, he could enter his data, which will be automatically recorded in the database and will be offered as a hint in the future. That is, in fact, with a new value, two records should be added - to table2 a record with a new value (id2, name) and to table1 a record (id1, nameTable2, param1, param2) where table1.nameTable2 refers to a new record from table2 (table2.id2).

[Janusz]

Hi, maybe this example can help:
https://www.youtube.com/watch?v=NRX9pR5VWk4

[nac]

Hello dimonsg,

I think that using a Lookup object may work for you. A normal Select offers only a 'closed response' list. If the item you want is not in the Select source, you need to add a record first (in table2) then go back to the Select, refresh and choose the new item you have entered. You cannot do this directly in the Select object.

Lookup is an enhanced way of doing a selection. It uses a nuBuilder Browse form to help you find the item you want. If you do the Lookup with a Browse/Edit form (on table2) then you can click the Add button to create a new record if the one you want is not there. Once it is added, it will appear in the Lookup Browse list and you can then choose it.

I am not sure if I have fully understood your question but you can try this if it helps. Of course you need to first create a Browse/Edit form for the columns in table2 from which you wish to choose.

Neil

[dimonsg]

I have studied all this, but these solutions are not beautiful. Let's look at the situation from the user's side.
The user begins to enter characters into the field - with each character input, certain options from the table2 are offered to him just below. If the user likes one of the suggested values, he chooses it, if not, he leaves his own. After clicking the Save button, the entered value with its own unique index, if it is not in table2, is written there and the index of this value is written to table1. I think this solution is beautiful - there are no extra buttons for the user - it's something like a mixture of Select and Input objects.
I thought this solution was already implemented in nubuilder, but I just didn't understand how to use it.

[kev1n]

Saving newly entered values to the database can be achieved with PHP. Place the code in the Before Save (BS) or After Save (AS) events.