Category & Attributes User Field and Popup Text Fields

I have developed a LiveReport based upon a Custom Category that I have developed. It works very well except for a couple of items: 1. One of the fields is a User field--It shows the user ID (number) instead of user Name. How do I convert it?2. I have 2 Imput fields on the LiveReport that the user has to type in a Facility and Department. In the Category these are Text Popup fields. Is there any way to have the drop down show up for the user so they don't have to type it in? I have a Word doc attached that includes my SQL statement, screenshots of the LiveReport and Category. Any help would be appreciated. Thank you.

Find more posts tagged with

Comments

Tim_Hunter

1) You need to join to the KUAF table to get the user information associated. See the querySELECT dtree.dataid, dtree.SubType, dtree.GIF, dtree.PermID, dtree.Reserved, dtree.ReservedBy, dtree.name, kuaf.name, a.valdate "Docreview", b.valstr "Docnum", c.valstr "Doctitle", d.valint "Owner", e.valint "Number", f.valstr "Facility", g.valstr "Department"FROM dtree, kuaf, llattrdata a, llattrdata b, llattrdata c, llattrdata d, llattrdata e, llattrdata f, llattrdata gWHERE dtree.ReservedBy = kuaf.idAND (a.valdate >= %1AND a.valdate <= %2AND a.defid = 2259851AND a.attrid = 10AND dtree.subtype = 144AND a.id = dtree.dataidAND a.vernum = dtree.versionnum)AND (b.defid = 2259851AND b.attrid = 2AND dtree.subtype = 144AND b.id = dtree.dataidAND b.vernum = dtree.versionnum)AND (c.defid = 2259851AND c.attrid = 4AND dtree.subtype = 144AND c.id = dtree.dataidAND c.vernum = dtree.versionnum)AND (d.defid = 2259851AND d.attrid = 7AND dtree.subtype = 144AND d.id = dtree.dataidAND d.vernum = dtree.versionnum)AND (e.defid = 2259851AND e.attrid = 19AND dtree.subtype = 144AND e.id = dtree.dataidAND e.vernum = dtree.versionnum)AND (f.valstr = %3AND f.defid = 2259851AND f.attrid = 5AND dtree.subtype = 144AND f.id = dtree.dataidAND f.vernum = dtree.versionnum)AND (g.valstr = %4AND g.defid = 2259851AND g.attrid = 6AND dtree.subtype = 144AND g.id = dtree.dataidAND g.vernum = dtree.versionnum)ORDER BY a.valdate2) not sure how to solve this one.

Mary_Slane_Corona

The report didn't cause errors but it didn't retrieve any documents based upon the Category. There should be 6 items. The d.valint "Owner" attribute is what I am trying to convert from number to name. Thank you for your help.

Tim_Hunter

I didn't understand your original request.This would be the easiest way to change:Try replacing d.valint "Owner", with (select kuaf.name from kuaf where kuaf.id = d.valint) "Owner",

Mary_Slane_Corona

TimThank you so much for your help.It works. Here's what I used:SELECT dtree.dataid, dtree.SubType, dtree.GIF, dtree.PermID, dtree.Reserved, dtree.ReservedBy, dtree.name, kuaf.name "Owner", a.valdate "Docreview", b.valstr "Docnum", c.valstr "Doctitle", d.valint, e.valint "Number", f.valstr "Facility", g.valstr "Department" FROM dtree, kuaf, llattrdata a, llattrdata b, llattrdata c, llattrdata d, llattrdata e, llattrdata f, llattrdata g WHERE kuaf.id = d.valint and (a.valdate >= %1 and a.valdate <= %2 and a.defid = 2259851 and a.attrid = 10 and dtree.subtype = 144 and a.id = dtree.dataid and a.vernum = dtree.versionnum) and (b.defid = 2259851 and b.attrid = 2 and dtree.subtype = 144 and b.id = dtree.dataid and b.vernum = dtree.versionnum) and (c.defid = 2259851 and c.attrid = 4 and dtree.subtype = 144 and c.id = dtree.dataid and c.vernum = dtree.versionnum) and (d.defid = 2259851 and d.attrid = 7 and dtree.subtype = 144 and d.id = dtree.dataid and d.vernum = dtree.versionnum) and (e.defid = 2259851 and e.attrid = 19 and dtree.subtype = 144 and e.id = dtree.dataid and e.vernum = dtree.versionnum) and (f.valstr = %3 and f.defid = 2259851 and f.attrid = 5 and dtree.subtype = 144 and f.id = dtree.dataid and f.vernum = dtree.versionnum) and (g.valstr = %4 and g.defid = 2259851 and g.attrid = 6 and dtree.subtype = 144 and g.id = dtree.dataid and g.vernum = dtree.versionnum) Order by a.valdate

Mary_Slane_Corona

Tim helped with converting the user field from a number into the user name. Now, can anyone help with the Popup Text field. I am using the Input string on the LiveReport and the field I am trying to use the Input is a Popup Text field. Is there any way that I can get the Popup values to show so that a user can select one of them? (screenshots are in the attachment on my original discussion) Thanks so much.