Create a pulldown for a foreign key in edit mode with a key from a different table than the foreign key

First I looked for an example and found one in
sample-library publications edit.

Accordingly I have modified by view file as follows

<?xml version="1.0" encoding="UTF-8" standalone="no"?>








   </dsContext>
   <dialogMode height="600"
               width="800"/>
   <layout expand="windowActions"
           spacing="true">
       <fieldGroup id="fieldGroup"
                   datasource="productDs">
           <column width="250px">
               <field property="mfr"
		optionsDatasource="orgDs"
		required="true"/>
               <field property="productDescr"/>
               <field property="mfrProduct"/>
               <field property="caseGtin"/>
               <field property="boxGtin"/>
               <field property="unitGtin"/>
               <field property="unitsPerBox"/>
               <field property="unitsPerCase"/>
           </column>
       </fieldGroup>
       <frame id="windowActions"
              screen="editWindowActions"/>
   </layout>

diffs:

11a12,19

    <collectionDatasource id="orgDs" 
                          class="com.pds.pdssr.entity.OrgMfr" 
                          view="_local"> 
        <query> 
            <![CDATA[select e from pdssr$OrgMfr e ]]> 
        </query> 
    </collectionDatasource> 

20c28,30
<

            <field property="mfr"
   	optionsDatasource="orgDs"
   	required="true"/>

The screen now looks likelookup

The tables are:

create table org (
org_id serial primary key not null,
org_cd varchar(16),
org_nm varchar(32)
)
;–
create table org_datafeed (
org_id serial primary key not null references org(org_id)
)
;–
create table org_mfr (
org_id serial primary key not null references org(org_id),
cds_mfr_id varchar(10) not null
)
;–
create table product (
product_id serial primary key,
mfr_id integer not null references org_mfr,
product_descr varchar(60) not null,
mfr_product_id varchar(8),
case_gtin char(14) not null,
box_gtin char(14),
unit_gtin char(14),
units_per_box numeric(9) not null,
units_per_case numeric(9) not null
)
;–

So in actuality I need to have the org.org_cd displayed for
orgs in org_mfr, that would give me a valid foreign key but the
information I need to show in the pull down would come from org
while org_mfr is the foreign key on the product table.

There is some magic occurring because when I set the datasource to pdssr$org I couldn’t even pull up the edit screen without it complaining that pddss$org was invalid.

Please format code in your message - it is now almost unreadable. Also, a test project with reproducing scenario would help to understand the problem.

1 Like