Finance | Project Operations, Human Resources, ...

json schema and sql table definition for data entity

(1) Share

Report

Posted on by Dev

390

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

All responses (15)

Answers (0)

Suggested answer

Anthony Blake 2,213 Super User 2024 Season 2 on at

Like (1)

Report

json schema and sql table definition for data entity

Hey

To generate a schema, I would usually create a sample payload and put it into a schema generator. There are lots out there to chose from: https://stackoverflow.com/a/30294535

Anthony
Martin Dráb 230,492 Most Valuable Professional on at

Like (0)

Report

json schema and sql table definition for data entity

F&O won't generate a JSON schema for you. If they (the vendor, the other team or who is it) insist on having it, someone must create it from available information - either you or them. But maybe they just hoped you have that and can live without it. For me, this is just an implementation detail, not a business requirement.

Regarding the metadata API, you can see an example in my blog post New metadata API. And as I mentioned, it's what AOT Browser uses to get information about data entities (among other things), therefore you can see other examples in its codebase.
Dev 390 on at

Like (0)

Report

json schema and sql table definition for data entity

Thanks Martin.

They want a json schema with json data types (they maybe want to validate the json file they receive at there end with the json schema). I was thinking to provide the staging table definition but I am not sure how we can convert it to json schema.

Also, can you please provide any link to refer to metadata API in F&O / add-on to F&O.
Martin Dráb 230,492 Most Valuable Professional on at

Like (1)

Report

json schema and sql table definition for data entity

All right, now it's clear that your actual requirement isn't about JSON. Your requirement is getting the field names, data types etc.

If you want to see an example of valid data, simply use the export in data management (to CSV, in your case).

To get additional details, check out whether the entity is public. If so, you can utilize OData metadata. As I already mentioned, you can even use it to generate code, so you don't have to worry about implementation details such as the datatime format, you'll get data type validations etc.

Even more details can be extracted with the metadata API in F&O. You might also be interested in AOTBrowser (an add-on to F&O), which utilizes this API.

A possibility of looking at the view definition or the staging table definition was already mentioned.
Dev 390 on at

Like (0)

Report

json schema and sql table definition for data entity

Hi Martin,

Yes they will convert the json in flat file csv format but they somehow need the schema for the CSV files.For example, required fields, data types, if a string, what is the pattern or max length, Datetime is not a type for csv it is a string for example.
Martin Dráb 230,492 Most Valuable Professional on at

Like (1)

Report

json schema and sql table definition for data entity

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.
Martin Dráb 230,492 Most Valuable Professional on at

Like (1)

Report

json schema and sql table definition for data entity

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.
Suggested answer

Bill Ngo 370 on at

Like (0)

Report

json schema and sql table definition for data entity

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.
Suggested answer

Anthony Blake 2,213 Super User 2024 Season 2 on at

Like (0)

Report

json schema and sql table definition for data entity

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:
Dev 390 on at

Like (0)

Report

json schema and sql table definition for data entity

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.