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"?>

   <dialogMode height="600"
   <layout expand="windowActions"
       <fieldGroup id="fieldGroup"
           <column width="250px">
               <field property="mfr"
               <field property="productDescr"/>
               <field property="mfrProduct"/>
               <field property="caseGtin"/>
               <field property="boxGtin"/>
               <field property="unitGtin"/>
               <field property="unitsPerBox"/>
               <field property="unitsPerCase"/>
       <frame id="windowActions"



    <collectionDatasource id="orgDs" 
            <![CDATA[select e from pdssr$OrgMfr e ]]> 


            <field property="mfr"

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