oracle forms field validation

Question

screen shot

           CREATE TABLE TAB_COL1
            (
              PROFS_ID           NUMBER(38) ,
              DSP_PROFILE2       VARCHAR2(12 BYTE),
              DSP_PROFILE_TYPE2  VARCHAR2(10 BYTE),
              DSP_DESCRIPTION2   VARCHAR2(60 BYTE),
              DSP_ACTIVE4        VARCHAR2(1 BYTE)
            );

            SET DEFINE OFF;
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (986, 'A', 'GRP', 'Exechon', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (987, 'A1', 'GRP', 'JT9 Costed Inventory', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (988, 'A2', 'GRP', 'JT8 Costed Inventory', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (989, 'A3', 'GRP', 'GE Costed Inventory', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (990, 'A4', 'GRP', 'A4', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (991, 'A5', 'GRP', 'CF34', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (992, 'A6', 'GRP', 'ESL Costed Inventory', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (993, 'A7', 'GRP', 'ESL Costed Inventory', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (994, 'A8', 'GRP', 'A8', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (995, 'A9', 'GRP', 'A9', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (996, 'AA', 'GRP', 'AA', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (997, 'AB', 'GRP', 'Airbus', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (998, 'AC', 'GRP', 'AC', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (999, 'AD', 'GRP', 'MTU Consignment', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1709, 'AE', 'GRP', 'AE', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1000, 'AF', 'GRP', 'AF', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1710, 'AG', 'GRP', 'AG', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1001, 'AH', 'GRP', 'Hamilton Sundstrand / Traditional  - AH', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1002, 'AI', 'GRP', 'AI', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1711, 'AJ', 'GRP', 'SASOF II CFM56', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1003, 'AK', 'GRP', 'AK', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1004, 'AL', 'GRP', 'AL', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1005, 'AM', 'GRP', 'AM', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (5282, 'AN', 'GRP', 'AN', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (5437, 'AO', 'GRP', 'AO', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1006, 'AP', 'GRP', 'Apollo Consignment - CFM', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1712, 'AQ', 'GRP', 'AQ', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1007, 'AR', 'GRP', 'ATR (ACLAS)', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1008, 'AS', 'GRP', 'AS', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1009, 'AT', 'GRP', 'Aerothrust Stator Exchange Program', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1010, 'AU', 'GRP', 'AU', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1011, 'AV', 'GRP', 'Avio SPA JT8 Consignment', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (5603, 'AW', 'GRP', 'AW', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (1012, 'AX', 'GRP', 'AX', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (5723, 'AY', 'GRP', 'JT8-200 SERIES COSTED', 'Y');
            Insert into TAB_COL1
               (PROFS_ID, DSP_PROFILE2, DSP_PROFILE_TYPE2, DSP_DESCRIPTION2, DSP_ACTIVE4)
             Values
               (5095, 'AZ', 'GRP', 'AZ', 'Y');
            COMMIT;

Created a sample table.

Also will send screen shot how the form looks. If the user chooses profile type as 'Group Code', then uses a lov button to choose values, eg 'AB' and save, the value populates when I post-query the record. But if user types the value as 'AB' and saves, it saves, but on post-querying the value is not there.

[screen shot 2screen shot 3

Answer 1

I think you have more than one returning item for your LOV, one of them returns value for value field. And i assume you have unique values for each of value column.

Take your query possibly your LOV's query in Record Groups is select value, division, description into :value, :division, :description from sales , consider select division, description into :division, :description from sales where value = :value where value = 'AB' for your case, in POST-TEXT-ITEM or WHEN-VALIDATE-ITEM trigger of value field.

Answer 2

Put the same code (you use in the POST-QUERY trigger) into the WHEN-VALIDATE-ITEM trigger on that item (profile type, perhaps? I'm not sure, didn't quite understand what you've said).