create view consist of many tables

(0) Share

Report

Posted on by Community Member

Hi guys,

Is it possible to create a view which consist of multiple table and in one of the column, we have the table name or id of the source ?

For example, I created a view, consist of Table A, B, C. Probably I just need their recId, so I just add 1 field for Recid.

And then add a display method that identify first where is this Recid coming from ? I probably need the TableId and the table name, then add this method as the 2nd column of the view.

So basically it will have these 2 column in the view :

SourceTable	RefRecid
Table A	1234231
Table B	2222232
Table A	1232131

If possible, what would be the code for the display method ? Or is there any other approach ?

Kindly advice,

Wong

I have the same question (0)

All responses (13)

Answers (1)

nmaenpaa 101,162 Moderator on at

Like (0)

Report

You can't get table name based on RefRecId. But if your view has RefTableId you can use that to get the name of the table (in x++ display method, but not in Computed column - at least not so easily).

Could you share your business requirement? Why would the consumer of the view need to know the name of the table for some field?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Nikolaos,

Because the tables that will built this View are not the same structure, so I think I just need their Recid, with the tableId (and probably their name, to make this view more readable)

Then I'm gonna use this view for my lookup. So we will have the list of recId from all the tables member of this view with their respected table.

In more advance, I think based on that record, for example ::

Source RefRecid

SalesTable 123121

PurchTable 444222

When user select record #1, it will open Sales Order form, and if select #2, it will open Purchase Order form instead. (this is an example only, my real scenario is using custom table and each have their own form)

Thanks,

Wong

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Where can I get this RefTableId, btw ?

It is not in the field list when we add datasource during View creation, right ?

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

It's just TableId. Every table has TableId field.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Alright, seems I need to add the field manually, and select in Property. Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok, one more thing need to confirm. I decided to create Query first, a Union type for all the tables required, then use it in my View.

This should be the correct one, right? And while adding the field Query, is it correct for just select one of the underlying table ? lets say I select the 1st one only, it will still included the other table, right ?

Thanks,

Wong

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

While already has those two fields : RefTableId & RefRecId, am having trouble also to create computed column for just displaying the table name.

Can someone help to advice what will be the computed column looks like?

Thanks,

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

Here's info about computed columns on views: docs.microsoft.com/.../walkthrough-add-a-computed-column-to-a-view

Remember that computed column can't run any x++ code so you can't use AX functions for converting table id to table name. The computed column is computed in the database so all the information that you need when computing it must be fetched using SQL queries.

One option is also to go back to the business requirement and re-evaluate if there's some other way to solve it.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Nikolaos,

Noted, in that case I better drop first the ComputedColumn since it is unnecessary. I got a bigger problem, when Build the View it give me an error saying "Mapped vew field cannot be set to TableId"

So basically here is what I did :

1. Create a query, union to all necessary tables, with only consist of RecId and TableId

2. Create View, use the Query,

3. Add New field from the View, 2 fields : RefRecId and RefTableId

4. in RefTableId property, I select the 1st table in Data Source and choose TableId in Data Field from the selection.

Apparently, this setting makes error.

Please help.

Thanks

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

In general, nowhere in the system you have such lookups that would list data from many tables and direct you to different tables based on the selection. Are you 100% sure you need it for your solution? Would it not be easier if the users would use similar navigation patterns that they are anyway using elsewhere in the application.

One very simple option is also to have two lookups. First, the user selects table from one lookup (to make it simpler, you should use enum that contains only names of the tables you want to support in this process). Based on the selection on that field, the second lookup shows data from the correct table.

Was this reply helpful? Yes No

Join the conversation

Community site session details

create view consist of many tables

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Source	RefRecid
SalesTable	123121
PurchTable	444222

Community site session details

create view consist of many tables

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates