web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Make data available to...
Small and medium business | Business Central, N...
Answered

Make data available to a web service that contains a union - how to?

(0) ShareShare
ReportReport
Posted on by James McCullough 379

Hey Everyone:

I'm having a hard time articulating this to Dr. Google, and I keep getting all kinds of SQL answers.  I don't need SQL answers, I need to understand from a Business Central / AL perspective how to do this.

The Problem

My client just migrated from Dynamics GP and wants to have a web service that provides a continuous stream of customer invoice transactions.  In SQL, no problem.  I can even see in Business Central that I can arrange the information into two separate web services - one for the current information and one for the historical table "GP RM20101" in the Intelligent module.  What I can't do is provide a single web service that puts them into one JSON object.

What I've Tried

I've got two queries that each produce the correct information each of the tables.  They have the same structure, the same naming conventions, etc.  I've tried finding information on how to do this on Google, and in my reference books, but ... I can't see how to do it.  A codeunit that puts them into a temporary table?  The problem is I don't know how to publish the result of a temporary table to a web service.  (Or an API, even, I know they're slightly different but I can do either one.)  

Does anyone have any advice, or an example of something similar or anything that they can point me to?  I'd really appreciate any help.

Thanks.

-J

I have the same question (0)
  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,111 Moderator on at

    I think you should look at a solution where you do not put the result in a temporary table but in a normal table.

    Then you can build an API page that gives you access to the data in your customized table.

    Yes, it will take some extra space in your database - but it will make the publication of the data in an API much easier.

    learn.microsoft.com/.../devenv-develop-custom-api

  • James McCullough Profile Picture
    James McCullough 379 on at

    I think perhaps I wasn't clear.  Let me try again, please...

    I have two tables.  I need to COMBINE the data into one for a single API call.  AL doesn't support unions.  That's what I'm trying to overcome.  I already have an extension, so I know how to do that part.  It's the union that's flummoxing me.  Make more sense?

    (edit: I have already developed an extension; that's not the issue)

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,111 Moderator on at

    Yes, I guess i was not so clear either maybe.

    I am aware that AL does not do unions.

    So what I would do is to create a codeunit that takes the date from your two tabes and put them into a 3de table that will be your joined data table.  And then build the API page that table.

    So you have table I that is the Sales data from GP

    Table II the sales data from BC

    Table III that is the keeps all the data from table I and II and you create a API page based on table III.

    I hope that made sense ?

  • James McCullough Profile Picture
    James McCullough 379 on at

    Awesome!  This is most of the way to what I was after.

    My remaining concern about this tactic is: concurrency.  How I do make sure that the target table (III) is the right one?  I'm assuming a temporary table?  If so, I was having a hard time figuring out how to send that back through the API.  Please forgive me, I'm a long time coder, but new to AL and there's some quirks to it that I find confusing.  (But it's WAY better than Dexterity.)

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,111 Moderator on at

    I think you should rule out the temporary table because you can really not build an API page on them that will work very well.

    So it basically comes down to how updated you want your API table (table III) to be.

    If you want to secure concurrency you can subscribe to events in table I and II that makes sure that every time something is modified in table I and II those data are also edited in table III.

    If you are happy with not being up to date all the time you can create a codeunit that keeps table III updated and schedule that codeunit to run in a job queue with the frequency you want  - hourly, daily or what every you chose.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,664

#2
YUN ZHU Profile Picture

YUN ZHU 960 Super User 2025 Season 2

#3
Jainam M. Kothari Profile Picture

Jainam M. Kothari 773 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Cascading Enum / Option Fields in Business Central

Product updates

Dynamics 365 release plans