Finance | Project Operations, Human Resources, ...

json schema and sql table definition for data entity

(1) Share

Report

Posted on by Dev

451

hi,

I have 2 queries :

1. I was looking to do create table query on the data entity to get the sql definition but I dont see the entity in the db instead only staging table.

2.Also how can I convert the sql schema into json schema.

Thanks

I have the same question (0)

All responses (15)

Answers (0)

Bill Ngo 372 on at

Like (0)

Report
Copy link

Link copied!

Hi,

What is your current version of SQL? Which DB and table name of D365FO you want to create table query?

Rgds,

Bill

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

Like (1)

Report
Copy link

Link copied!

1. If you see a staging table, it means that you're looking at tables, but that's wrong. You'll find data entities under Views.

2. I have no idea what you mean by SQL schema. You can, for example, get a piece of SQL language for the view definition, but that's not a data schema. A SQL schema is just a collection objects inside database, e.g. dbo.InventTrans is InventTrans table inside a schema named dbo. Maybe you should forget technical details for a moment and rather tell us what business problem you're trying to address.

Was this reply helpful? Yes No
Dev 451 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

The middleware needs the sql table definitions for the entities used in integration. Just like we do a create table query on the table I need to do that for the data entity.

Also, is there any way we can convert that query into json schema with json data types.

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

Like (1)

Report
Copy link

Link copied!

The middleware can't access F&O database, therefore implementation details of F&O database is irrelevant to it. You need to ask good question about what they actually need, otherwise you'll waste time with something that won't solve anything.

First of all, figure out what the middleware will do, e.g. whether it'll be used to export data and populate a data warehouse, it'll transform data to be imported or so.

Then think about how it'll communicate with F&O. For instance, it may read F&O from Azure Data Lake. Or it may use OData to import data. Or data management APIs.

You mention JSON, which might (but doesn't have to mean) that they want to use OData. Then you can get data in JSON format simply by making a GET request to the OData endpiont. You can also use OData metadata to thing like generated classes in the middleware to work with the data in an object-oriented manner, without any need to work with the particular data format (JSON) directly.

Was this reply helpful? Yes No
Suggested answer

Anthony Blake 2,977 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi,

You can export the full list using code, here is an example from Brewsterware

GitHub - Brewster35/365DataEntityList

Anthony

Was this reply helpful? Yes No
Dev 451 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

They need it for Data package import where they receive few json file from external system which need to be mapped to different entities for F&O.

Was this reply helpful? Yes No
Suggested answer

Anthony Blake 2,977 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Dev,

Also take a look at the d365fo.integrations powershell library.

https://github.com/d365collaborative/d365fo.integrations/blob/development/docs/Get-D365DmfDataEntity.md#default-default

This request specifically has an -OutputAsJson option:

Was this reply helpful? Yes No
Suggested answer

Bill Ngo 372 on at

Like (0)

Report
Copy link

Link copied!

Hi Dev,

With your last update. You can write code to insert/update data from Json string into tables of D365FO.

There are two ways:

- with current D365FO, MS has provided tools which have enabled direct API communication through the codes of the VS project for "Financial Operation". You can read and utilize Anthony noted below:

here is an example from Brewsterware > see "Using VS Code and RapidAPI to call D365FO custom services"

- otherwise, you have to create C# project to handle this integration.

Additional, you have to define json schema according to tables structure of D365FO. To do this you need to map and define your json string as same as D365FO table's formats.

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

Like (1)

Report
Copy link

Link copied!

JSON format isn't supported by data management in F&O, therefore they can't import that. They'll need to extract data from their JSON objects and produce files in a different format, such as XML or CSV.

You can use JSON schema to generates some objects for mapping, but it's just an arbitrary requirement. You just need to get the information somehow, e.g. you can get the structure from data management in XML. And if you really want JSON, transforming XML to JSON is trivial.

It's important to distinguish business requirements (that need to be address) and someone's idea about the implementation (which can be ignored, if there is a better way).

Regarding Get-D365DmfDataEntity, it seems to provide just entities available through OData, not all entity that can be imported via Data Management.

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

Like (1)

Report
Copy link

Link copied!

Using a custom service isn't a good recommendation in this case.

The OData service is more appropriate when working with data entities, and according to last information from Dev, the actual requirement is to import a data package.

Was this reply helpful? Yes No