When a large customer adds a parameterized lookup, (i.e. the OOTB Department) to Project List or other similar pages in Classic, the Export to Excel is extremely slow and times out
STEPS TO REPRODUCE:
Expected Results: The export to work within reasonable time
Actual Results: Export to Excel never completes, the Department browse query is getting stuck on the database for hours (very large query with many lines). Error may be thrown : Processing another export request. Please try again
Note: This may be happening on other parameterized lookups, OOTB and custom lookups as well
Release : 16.0.3, 16.1.0, 16.1.1, 16.1.2, 16.1.3, 16.2.0, 16.2.1
There are investments which have the department code but are missing captions, because they are not associated with any location
This is DE68536 / DE77285, which is addressed by a user story, fixed in 16.2.2+
Also valid for custom parameterized lookups or OOTB ones such as Project Category / Project Type.
Fix the missing Department / Location captions:
You may use the below query to identify the problematic records for Departments:
SELECT ii.code FROM INV_INVESTMENTS ii
WHERE ii.code IN (SELECT ii2.code FROM PAC_MNT_PROJECTS pmp, INV_PROJECTS ip, INV_INVESTMENTS ii2
WHERE pmp.id = ip.PRID AND ii2.id = ip.prid AND pmp.DEPARTCODE NOT IN ( SELECT departcode FROM LOCATIONDEPT l))
Note that we recommend not to remove location association of any department to avoid this issue happening going further
Valid for other lookups than Departments, will not work for Department due to constraints
0. Identify the lookup code for the parameterized lookup
1. On the Clarity app server, run the command:
admin system-options -add LOOKUP_CACHE_OPTION <LOOKUP_CODE> -multi
Note: Replace <LOOKUP_CODE> with the actual lookup code for the parameterized dynamic lookup that has the issue
2. Restart the services
For Project Category / Project Type OOTB lookups which include User Lookup Mapping
1. Go to Home - Lookup Mapping
2. Ensure all the combinations you see on database side for Project Category/Type values exist as User mappings
3. If anything missing, recreate the user mappings and you should see the value appear in Project List