1,515
We have added a custom field to the customer master and added the values via the list feature within the properties of the custom field. I want to include the fields in a query/statement (a parameter) within an SSRS report. Are these values stored in a table? If so, which table?
Thank you.
*This post is locked for comments
Barry,
Excellent, highly detailed, very generous explanation that is a great help. Simply can't thank you enough! I'll give it a go and let you know how I do.
I am in your debt.
Appreciatively,
Bill
Thanks,
Bill Devlin
OK - having done that...
Save your new PV as a .csv file.
Then use the Possible Values Import utility on the SL Administration Meny to import it (in SL 7.x or 2011.
Its on the Tools menu (from memory) in older versions.
Then put the PV bane into the PV property of the control.
Don't worry about the complex looking values you may see in PV properties. Just put the name of the PV.
At this point, whrn you hit F3 on your field, the PV window should appear.
But it will crash as you tab off the field.
This is becauser the kernel does an "existance check" by firing a stored procedure which has the same name as the PV, And I'm guessuing you won't have one.
So that's the final task - crewate a proc with the same name, and the same SQL as your PV definition.
(It actually doesn't have to be the same SQL.
But it is rare that the SQL is legitimately different.
Hope that helps
Barry
Ctd
=====
"Customer_Active","0826000","","Select * From Customer Where CustId like @parm1 and Status IN ('A', 'O', 'R') order by CustId","Active Customer","Customer.CustId;Customer ID;003;0","Customer.Name;Name;110;Customer1","Customer.Phone;Phone;0;Customer6","Customer.Zip;Zip;0;Customer7","Customer.Status;Status;0;Customer2"
======
After the SQL comes ","Active Customer" - the PV Window Title
Then comes
=== "Customer.CustId;Customer ID;003;0","Customer.Name;Name;110;Customer1","Customer.Phone;Phone;0;Customer6","Customer.Zip;Zip;0;Customer7","Customer.Status;Status;0;Customer2"
That blob defines all the Columns that will appear in the PV window.
The field itself must come first (CustId in this case)
The definition of the CustId column is
=== "Customer.CustId;Customer ID;003;0"=====
Customer.CustId is the table.column
Customer ID is the column title
003 is, from memory, a formatting code.
3 means (from memory) that it is a CustId and (if there are flex key rules etc.) it is formatted accordingly.
Mostly it is "0".
The "0" that follows the 003 is, as far as I recall, now redundant. It is always (?) zero.
-------------
So it is not relevant how many columns there are in the table.
It is that little block of the PV definition that defines which columns will appear in the PV window.
--------------
I'll continue in another post
Bill
Can I suggest that you use the Customer_Active PV that I referred to earlier as a"template".
Find it in the PVRec,csv file I referred to, and copy/paste it into a new file.
As the first thing you do, change the name Customer_Active to a more suitable name.
Then try editing it to do what you want, with your table.
NOTE that you shoudl edit it in Notepad (nit Excel) as you don't want Excel to do any "manipulating" of the file.
NOTE also that your PV definition must be one very long line in that file.
Inthe rest of this post, I'll break up all the PV components onto separate lines & offer a few comments.
But in your editing, it must remain as one long line.
OK - here's the full line for that PV.
=====
"Customer_Active","0826000","","Select * From Customer Where CustId like @parm1 and Status IN ('A', 'O', 'R') order by CustId","Active Customer","Customer.CustId;Customer ID;003;0","Customer.Name;Name;110;Customer1","Customer.Phone;Phone;0;Customer6","Customer.Zip;Zip;0;Customer7","Customer.Status;Status;0;Customer2"
======
The fisr field, obviously, is the PV name.
THe 0826000 is the screen that can edit.insert the "possible values" If populated, the Edit/Insert buttins will be available on the PV window.
Fot CustId, 08260 is the Cust Mtce screen.
The next blank field shoudl be left blank.
Then follows the SQL thatr is used to populate the PV Window.
You must do a "select *".
I think you must name the parms as @parm1 etc.
The field itself must be @parm1, and the SQL using that parm kust use "like" rather than "=".
ie "Where CustId like @parm1 "
When you are customizing (as opposed to writing a new screen from scratch) you may be limited to a single Parameter. More than one may be difficult (very precise formatting required) or even impossible.
This is getting verbose.
I'll coontinue in a separate post
\
Barry
Barry,
Thanks for your quick reply.
To give you context, my issue is actually in the Voucher and Adjustment screen (03.010.00) I have added a column in the details section of the screen (APTran.S4Future11) that will record a "Service Type ID" associated with a transaction.
In the APTran.S4Future input object (text field), I want to use a list of values "ServcTypeID" from a custom lookup table I have already created "xSrvcTypes" as the PV list.
I was asking how to reference a specific set of <tablename.colunmname(s)> as the source of the PV list, since my table has many more columns than just ID, description and tstamp.
Hope that is more clear. I'm fairly new to custom PV lists and appreciate your assitance.
Best regards,
Bill
Bill
Sorry - I don't quite follow.
Assume you have a CustId field in your table XMyTable, and on a particular screen you show that field, and want it to have a PV.
So you use the (say) Customer_Active PV . You can see the details of that PV in the PVRec.csv file in your db\CSVs folder.)
That Customer_Active line defines (amongst other things) the columns that will be shown in the PV window.
There is a rule that the field itself (CustId) must be the first column.
Is that the sort of thing you want to do?
Or am I totally confused !!
Barry
Hi Barry,
Pardon me for jumping in here, but I found your reply searching on custom PV's and it contains the information I have been hunting for (at a high level). You indicated that you would expand a bit more if Deb wished; would you do so for an interloper? :)
My situation is that I want to use a specific column in a custom table I have already created as the source of the PV list. How do you limit the selection to a specific column in the sequence you have described above?
Thanks in advance,
Bill
Deb
On quiet reflection, I think there is a way to do what I think you want .
This is to eliminate the List property, and use the PV property instead.
In a customization, you generally can put a PV on screen controls that the customization itself has created.
But not if the control was already on the screen.
I understand that your customization has "created" the control.
The other possible requirement is that the only parameter passed into the PV and Proc you will create is your field itself.
So - some VERY brief steps are as follows. I can expand a bit on them if that is of interest to you.
- Create a new "lookup table". As you probably know, the convention is that any objects (tables, indexes, pv's, procs etc.) start with an X.
So you could call your table XMyCodes (obviously you'd give it a better name than that!)
The table would have an ID column, a Description column, and finally a tstamp problem.
- Create a PV (name starting with an X of course)
- Create a "matching" Proc of the same name.
- In customization Manager, eliminate the List property from your control, and populate the PV property with your PV's name.
(Don't include all the other stuff you'll see in the PV property of other controls. Just use,your PV's name - nothing else.)
Finally populate your new table.
And, if all is well in the world, you now should have:-
- a working PV
- A table that you may be able to use in SSRS.
Hope those ramblings are some use.
Barry
Deb
Sorry - I had misread the question.
As you said, the customization tables hold the List property, and they are not held in plain text.
(You can view the the "conrols & properties" section of a customization by exporting it to a .cst file, then browsing through that file in Notepad. That doesn't help much either!)
If it was a PV, then the values are held in a table.
For example 08.010 uses the Customer_Active PV, which draws data from the Customer table.
But for a List, to my knowledge, the "possible values" are held only in the customization table (Custom2/CustomVBA).
Barry
Barry,
Thank you for your response. I understand how to obtain the field information and which table the custom field is located. Say Customer.User1. What I am attempting to locate is the list value within the custom field. I have added possible values to the custom field and now want to be able to run SSRS reports to use the possible values as a parameter within the report. So I am looking for the table where these might be stored.
I understand that they might be in the Custom2 or CustomVBA table, but they are encrypted, so I am not able to use those tables for my query.
I am thinking that I can only use the values once they are utilized and populate the User1 field within the customer table. I am not locating another area where I might find the values.
Thanks again for your response.
Deb
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,321
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
