Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

OVSD 4.5/SP19 Displaying person's address

SOLVED
Go to solution
Highlighted
James Mohr
Member

OVSD 4.5/SP19 Displaying person's address

Hi All!

I am trying to create a view the persons objects that contain their addresses, or at least their primary address. On the one hand it makes sense because a person can have many different kinds addresses, so including "the" address does not make sense in this context. However, I have a problem accepting that it is simply impossible.

Our goal is to extract user and company data, so I looked into data extraction in the online help. It refers to a "Extraction
Configuration Wizard", which I cannot find, either on the client or the server.

Any help is appreaciated.

Regards,

jimmo





www.linux-tutorial.info
13 REPLIES
George M. Meneg
Honored Contributor
Solution

Re: OVSD 4.5/SP19 Displaying person's address

Hello James,

Addresses is a set on Persons. You cannot have them in Person's Views just like you cannot have Work Orders on Service Call's view.

menes fhtagn
James Mohr
Member

Re: OVSD 4.5/SP19 Displaying person's address

Hi George!

Hmmmm. Not good.

I found the "Extraction Configuration Wizard" and tried to do something with it. However, the doc disappoints! . Big time! Any suggestions how to extract the address information and the person information at the same time?

regards,

jimmo

www.linux-tutorial.info
George M. Meneg
Honored Contributor

Re: OVSD 4.5/SP19 Displaying person's address

Hello,

ECW is not that bad provided that you know the schema of the data from where you want to do extraction.

Run this query, will fetch your data

select per.per_name as 'Person Name', cdl.cdl_name as 'Address Type',
adr.adr_street1,adr.adr_zippostalcode,adr.adr_countryregion,
adr.adr_stateprovince,adr.adr_city,adr_street2
from itsm_persons per inner join itsm_addresses adr on per.per_oid=adr.adr_per_oid
inner join itsm_codes_locale cdl on cdl.cdl_cod_oid=adr.adr_typ_oid
where cdl.cdl_lng_oid=1033 and adr.adr_street1 is not null
order by per.per_name asc, cdl.cdl_name asc

1033 is for the US English. If you use different language modify the query accordingly.
menes fhtagn
James Mohr
Member

Re: OVSD 4.5/SP19 Displaying person's address

I change cdl.cdl_lng_oid=1031 since we are running in German and then I tried the query directly on the DB machine using sqlplus (Redhat ES 3/Oracle 10.1), but I got no output. Even leaving out the where condition completely, I get no output.
www.linux-tutorial.info
George M. Meneg
Honored Contributor

Re: OVSD 4.5/SP19 Displaying person's address

Hello Jimmo,

The query I posted is correct, I tested it to my system. Make sure that you are use the addresses set and not some custom fields on persons. Run the query select count (*) from itsm_addresses to make sure that there are entries.

Also if you want CDL_LOCALE to taken into calculation then enter the appropriate value for 'Address Type'. 'Address Type' is name only for cdl_lng_oid=1033
menes fhtagn
George M. Meneg
Honored Contributor

Re: OVSD 4.5/SP19 Displaying person's address

Hmm, the second part of what I wrote is not correctly. If lng_oid isn't taken into account then duplicate entries are retrieved.
menes fhtagn
James Mohr
Member

Re: OVSD 4.5/SP19 Displaying person's address

select count (*) from itsm_addresses;

shows me 104 entries, so there is obviously something in the table.

I looked in

select per.per_name as 'Bezeichnung', cdl.cdl_name as 'Adressenart', adr.adr_street1,adr.adr_zippostalcode,adr.adr_countryregion, adr.adr_stateprovince,adr.adr_city,adr_street2 from itsm_persons per inner join itsm_addresses adr on per.per_oid=adr.adr_per_oid
inner join itsm_codes_locale cdl on cdl.cdl_cod_oid=adr.adr_typ_oid
where cdl.cdl_lng_oid=1031 and adr.adr_street1 is not null
order by per.per_name asc, cdl.cdl_name asc

This still shows nothing. I got the translations out of the label text in the admin console, but I was thinking that might not be right.

I am a little confused about the construct

select per.per_name as 'Person Name', cdl.cdl_name as 'Address Type',

I though the "as" operator simply allows you to use an alias. So I am confused as to why it makes a difference which language you use or where in the query it is using 'Person Name' or 'Address Type' as a qualifier.


www.linux-tutorial.info
George M. Meneg
Honored Contributor

Re: OVSD 4.5/SP19 Displaying person's address

Hello Jimmo,

You are 100% right. The select ... 'as' ... is used as alias, it has nothing to do with the actual data. You can skip it.

If you skip the cdl.cdl_lng_oid= you should get twice as much data as without it.

Try removing the "adr.adr_street1 is not null" clause. And just for the fun of it, instead of 1031 use 1033.
menes fhtagn
James Mohr
Member

Re: OVSD 4.5/SP19 Displaying person's address

I am confused then about

"Also if you want CDL_LOCALE to taken into calculation then enter the appropriate value for 'Address Type'. 'Address Type' is name only for cdl_lng_oid=1033"

www.linux-tutorial.info
George M. Meneg
Honored Contributor

Re: OVSD 4.5/SP19 Displaying person's address

Hello Jimmo,

"Also if you want CDL_LOCALE to taken into calculation then enter the appropriate value for 'Address Type'. 'Address Type' is name only for cdl_lng_oid=1033"

As I wrote earlier this is not correct if you use 'Address Type' as column alias.

For example consider the query:

select per.per_name as 'Person Name', per.per_searchcode as 'Searchcode', per.reg_created, cdl.cdl_name as 'Address Type',
adr.adr_street1,adr.adr_zippostalcode,adr.adr_countryregion,
adr.adr_stateprovince,adr.adr_city,adr_street2
from itsm_persons per inner join itsm_addresses adr on per.per_oid=adr.adr_per_oid
inner join itsm_codes_locale cdl on cdl.cdl_cod_oid=adr.adr_typ_oid
where cdl.cdl_lng_oid=1033 and cdl.cdl_name='Business'
order by 3 asc

Since I specified as condition cdl.cdl_name='Business' I must also do cdl.cdl_lng_oid=1033 because cdl_name is Business for lang=1033.

If i have left the label "Business" untranslated in OVSD then there will be two rows matching cdl_text='Business' so the query will return double rows.

If label "Business" is translated then I can't use "Î Î¹ÎµÏ Î¸Ï Î½Ï Î· Î Ï Î³Î±Ï Î¯Î±Ï " (the translation) in conjunction with lng_oid=1033 because for lng 1033 the label is "Business". So if I choose to use the greek label I must also specify the greek locale (1032)

But as I wrote in earlier post this has to do with conditions on the query and NOT with column aliases
menes fhtagn
Naveen kesari_1
Collector

Re: OVSD 4.5/SP19 Displaying person's address

Hi James,

In the Workgroup form, find the tab page containing the members list.
Click Relate, then find and select an existing person using the advanced find dialog box.

Click Add, enter details of a new person in the displayed form, then click Save and Close.

Thanks,
Naveen Kumar
Be Positive
James Mohr
Member

Re: OVSD 4.5/SP19 Displaying person's address

Naveen,

Sorry but I have no idea what you are getting at.

Regards,

jimmo

www.linux-tutorial.info
James Mohr
Member

Re: OVSD 4.5/SP19 Displaying person's address

George,

I am finally getting back to this and finally got some output by removing the last part of the where condition in your last query:

and cdl.cdl_name='Business'

The problem is that I am only getting some of the entries because these are the only ones that actually have an address for the person. In most cases we simply rely on the fact that the person's address is the same as the company's address, so we did not include a separate address for the person. However, I am on the right track.

Regards,

jimmo



www.linux-tutorial.info
//Add this to "OnDomLoad" event