Select field, Edition Lookup automatic

Applications Forms
#1

Hi!

I have a form with a select (combobox) field, where it gets the options from another table / lookup table.

I select some value from the combobox(say ‘1234’) , fill other fields and insert the values in say table ‘XX’.

Problem: When the option / row ‘1234’ is deleted from the lookup table and the form where is the combobox / select field , is opened to “update” (some other field in the same form), how can we avoid the wrong lookup value being updated?? because the combo box won’t have that row which was deleted from the lookup table…

in other word, even the lookup table won’t have value ‘1234’, and when the data in the table ‘XX’ is updated, how to keep the combobox value ‘1234’ intact in table ‘XX’…

I don’t know if it is understandable what i explained…

anyone have any suggestion?

#2

Re: Select field, Edition Lookup automatic

A form shows data from the database. If you change data in the database table from within other parts of your application, then the form will of course show the new value.

What is it exactly that you want to achieve?

#3

Re: Select field, Edition Lookup automatic

I understand what you are trying to explain to me…

I am trying to keep the value of the combobox intact inserted (using the combobox, edition lookup from lookup table) to a table even though the lookup value from the lookup table is deleted, during the UPDATE of the from (where the lookup value is inserted)…

But seems like it’s not going to be easy…

Thanks for the reply!

#4

Re: Select field, Edition Lookup automatic

Is this still an issue for you? Maybe I can help, but I need some more background info then.

What is the functionality of the form?
What field are we talking about?
Why do you want to keep the value in combobox (or why do you delete a value from lookup table?)

cheers

#5

Re: Select field, Edition Lookup automatic

Thanks for the kind offer.

Yes, it is still an issue, i can’t think of anything that i can do.

Insert phase:

1 ) It is just a simple form with combobox/select field and other text fields.

  1. I add a data using the form: Select a value from the combobox( eg. ‘ABC’), fill the other fields. sc_exit(sel); brings me back to the grid.

In the grid:

  1. The grid shows the row inserted, like

(Pencil sign) | ABC text text text

–> All is fine till this stage. Combobox value is shown as it was inserted (as it should be).

In the lookup table (data accessed using editable grid view, can be deleted or edited):

When ‘ABC’ (a value from the table which is shown in the combobox in the above form) is deleted (obviously it won’t now show in the combobox above.)

In the grid again:

When i click edit the row inserted (clicking the ‘pencil sign’), the from with the previously populated data appears except for the combobox where now ‘ABC’ is not there anymore because it was deleted from the lookup table.

However, the grid shows ‘ABC’ in the same field which has been inserted, because it is getting the data from the table where the row was inserted and not the lookup table.

However the form won’t have value ‘ABC’ in the combobox, if i do some changes in the text fields press “update” in the form, the combobox value will be updated with the wrong value…

Why delete lookup value from the lookup table? : So that the combobox values which are not necessary in the future could be removed to keep the list short.

The only problem is that if the previously inserted values needs to be updated / modified, the combo wont’ find the value during update and the field will be populated with the wrong value…

Hope you understand it and makes sense…

Thanks again!

#6

Re: Select field, Edition Lookup automatic

okay. You want to be able to change the combobox value in the simple form, even if the value has been removed from the lookup table. You can achieve this by the steps below:

  1. in your simple form, add a union with a gobal value to the SQL of the combobox field:
select value
from <lookup_table>
where domain = 'YOURDOMAIN'
union
select '[old_value]'
from dual

Here, you’re selecting all currently valid values from the table that holds the lookup values, and in addition to this, you also select a value “old_value”, that will be passed from the grid to the form using a global parameter.

  1. in the grid, go to the link you already created. Click on action “link”, and select your form, click next. Now you also see the global as a parameter. Make sure the global parameter gets the correct value from your grid record!

This should do the trick.

Note that this old_value will now also be available in other records in the simple form when scrolling through the records. You can prevent navigating to others records by going to the link properties and uncheck “Enable navigation button on target application”.

#7

Re: Select field, Edition Lookup automatic

Though i haven’t tested it, it totally makes sense :slight_smile:

Wouldn’t have thought of using union select in the lookup.

Thanks a lot.

Update: Working as i wanted it to work. Thanks again.