Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 294

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

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: Make data available to a web service that contains a union - how to?

    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.

  • James McCullough Profile Picture
    James McCullough 294 on at
    RE: Make data available to a web service that contains a union - how to?

    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 32,748 Super User 2024 Season 1 on at
    RE: Make data available to a web service that contains a union - how to?

    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 294 on at
    RE: Make data available to a web service that contains a union - how to?

    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 32,748 Super User 2024 Season 1 on at
    RE: Make data available to a web service that contains a union - how to?

    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

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans