Advise on Adding Different Objects from Same Reference Table

[Former Member]

Hi Experts,

I have the following scenario:

I have an Article Table where in , we have many fields like:-

ARTICLE_ASSIGNED_TO_REF

ARTICLE_OWNER_REF

ARTICLE_SERVICED_BY_REF

ARTICLE_SELLER_REF

All the above fields contain the reference from a SINGLE table - PERSON TABLE.

When the user pulls the above object in the Report he has to get the Person Names from the Person Table. Hope I am clear in asking the question.

How do I achieve this?

Thanks in Advance,

Nat.

[former_member197846]

Hi Vasu,

In order to get the Person Name from PERSON table for each of the below fields in ARTICLE table, you need to create ALIAS for PERSON table and join them with the below fields.

ARTICLE_ASSIGNED_TO_REF

ARTICLE_OWNER_REF

ARTICLE_SERVICED_BY_REF

ARTICLE_SELLER_REF

The steps that you need to follow -

1. Create Aliases of PERSON table for each of the above fields in ARTICLE table. e.g.

ALIAS_ASSIGNED_TO_PERSON

ALIAS_OWNER_PERSON

ALIAS_SERVICED_BY_PERSON

ALIAS_SELLER_PERSON

2. Join primary key from these aliases to each of the above fields. Assuming PERSON_ID id is the primary key of PERSON table and the same is present in the above ARTICLE fields as well, following would be joins

ALIAS_ASSIGNED_TO_PERSON.PERSON_ID=ARTICLE.ARTICLE_ASSIGNED_TO_REF

ALIAS_OWNER_PERSON.PERSON_ID=ARTICLE.ARTICLE_OWNER_REF

ALIAS_SERVICED_BY_PERSON.PERSON_ID=ARTICLE.ARTICLE_SERVICED_BY_REF

ALIAS_SELLER_PERSON.PERSON_ID=ARTICLE.ARTICLE_SELLER_REF

3. Include the above joins in all appropriate Contexts.

4. Create objects for Name from each of the aliases.

ALIAS_ASSIGNED_TO_PERSON.NAME

ALIAS_OWNER_PERSON.NAME

ALIAS_SERVICED_BY_PERSON.NAME

ALIAS_SELLER_PERSON.NAME

5. Save and Export the Universe.

After this when ever the user pulls any of the above NAME objects in Query, automatically corresponding join to Alias table comes in picture and the respective name is populated.

Hope this helps.

Thanks,

Abhijit

[Former Member]

Hi Abhijit,

Thanks for your detailed explanation, but currently that is what I am doing.

Is there a better way to do this, The Problem is suppose if there are 100 such objects, should we add 100 Alias.

Is there no other way to do this?

Thanks again for your answer.

Vasu Natari.

[former_member197846]

Hi Vasu,

As each of these columns have different meaning from Business point of view, I think this is the way to implement it. We can not use the same PERSON table for multiple columns in ARTICLE as BO will include unwanted joins while creating the query. You can re-use these aliases for different meaning in different context.

Also you can create the objects only for the fields that are required for Reporting from these aliases to keep Number of Objects in Universe minimum.

Thanks,

Abhijit

[Former Member]

Hi Vasu,

As mentioned all these tables contain reference with PERSON Table, therefore, all must have primary key of Person table as foreign key.

While creating universe, you can drag all tables along with Person table and create joins between them.

After this, you can create classes and objects based on these tables.

On dragging one object from say class of table ARTICLE_ASSIGNED_TO_REF and another from Persons table, the required join will automatically come into picture.

However, for that you need to have a NAME object from PERSON table.

Hope I am able to understand the requirement

Regards,

Yuvraj