Categories and search screen with Long Translate values only

I had a mapping table that each group of mapping belonged to a category.  The requirement was to make a search page for just the category displaying the long description.  For each category the user could add multiple mappings.  Here is how I created the custom search page for just a list of category translate values.

Field

Create my category field MCM_ADS_CAT which had about a dozen translate values.

Record

My page required a parent record that included my new category, effective date and effective status.  The child record obviously included category, effective date followed by my addition fields for the mappings.

Page

I created a page to edit the listings within each category or Translate grouping and effective date.  This page requires 2 scroll levels to work properly the first level being the parent record and the second level the grid is the child data.  The initial search record or ADS Category you see at the top is actually a View that I will describe later.

Component

The component includes the new page but for the search record we are going to use a new view that selects the distinct values from our parent record.

View

The view is where I need to get the distinct values along with the long description translate values.  This view will be used as the search for record on my new component.  The view includes 2 fields my MCM_ADS_CAT and the XLATLONGNAME.  Here is the SQL to get the distinct in use values with long description from the translate items table PSXLATITEM.

SELECT DISTINCT aa.mcm_ads_cat, bb.xlatlongname 
  FROM ps_mcm_ads_eff aa, psxlatitem bb 
 WHERE aa.mcm_ads_cat = bb.fieldvalue
   AND bb.fieldname = 'MCM_ADS_CAT'
   AND bb.effdt = ( SELECT MAX(cc.effdt) FROM psxlatitem cc 
                    WHERE bb.fieldname = cc.fieldname 
                      AND bb.fieldvalue = cc.fieldvalue 
                      AND cc.eff_status = 'A')

Now on your view record field set the following properties to use only the long translate value on the search page:

 

ADS_CATEGORY

• Key Yes
• Search Key Yes
• List Box Item No

XLATLONGNAME

• Key No
• Search Key No
• List Box Item Yes

Search Page Results