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
