relationships between the two tables

(0) Share

Report

Posted on by Marco Ferro

Goodmorning everyone
For internal company needs, I have to develop an application that will read the data directly in the AX database.
I have to make a select between these two tables
PURCHLINE and KIL_METALATTRIBUTES.

6811.Relazioni.png
If I go to see the relationships between the two:
PURCHLINE.recid == KIL_METALATTRIBUTES.ReferenceRecid
and
PURCHLINE.TableId == KIL_METALATTRIBUTES.ReferenceTableid
The problem is that the PURCHLINE.TableId column does not exist.

8267.campi.png
Without it I can't find a unique result. Do you have any idea how to help me?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Sort by

Blue Wang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi Marco,

TableId is specific to each table and can be used without adding such fields, like RecId.

If you want to see it, I add data sources through forms.(It's the same as AX.)

But if you want to get the value of TableId, you can look up the system table SysTableIdView.
To use TableId, you can create class and code.

I hope it will help you.

Was this reply helpful? Yes No
Martin Dráb 239,451 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Are you aware of the fact that you won't have access to the production database in cloud? This might make your overall architecture invalid. The right approach is using web services.

It will also allow you to benefit from all F&O business logic (including TableId field).

An update: Oh, I see your pictures are not from F&O, so your tag "Dynamics 365 Administration" must be wrong. Ignore the part about access to database; neverthess it's true that you shouldn't access the database directly and you'd benefit from going through the application layer.

I'll delete the invalid tag and set the product version - please pay more attention to it next time.

Was this reply helpful? Yes No
Marco Ferro 7 on at

Like (0)

Report
Copy link

Link copied!

How can I call it in a query?

Was this reply helpful? Yes No
Blue Wang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

HI Marco,

In select statement?

axility.net/.../

Was this reply helpful? Yes No
Marco Ferro 7 on at

Like (0)

Report
Copy link

Link copied!

sorry but I can't understand how I can do to visualize that column with Microsoft SQL management studio and consequently write a query. I'm not using X ++.

Was this reply helpful? Yes No
Blue Wang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Marco,

You can't see the TableId field in Microsoft SQL management studio.

Because the value of TableId is not stored in this table.

As I mentioned above, you can select SysTableIdView.

Was this reply helpful? Yes No
Marco Ferro 7 on at

Like (0)

Report
Copy link

Link copied!
sorry but I still don't understand.

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,673 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi Marco,

Write a simple job in AX

tableName2Id("PurchLine").. . This will get you tableId. This is one time need and you don't need this again and it will be static in your query. See below.

then you write your query...

where KIL_METALATTRIBUTES.ReferenceTableid = tableid value you get from above.

Thanks,

Satish Panwar

Please help verify answer(s) if they guide you in right direction so other community members can also benefit from your question with verified answer.

Was this reply helpful? Yes No
Blue Wang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Marco,

Sorry, I forgot I used F&O and you were AX.

dextersdax.blogspot.com/.../get-table-name-from-tableid-for.html

Was this reply helpful? Yes No