Query object in Business Central | Technical Consultant | Query in bc | complete tutorial for bc

[@IshtiAh]

Thanks for the sharing all in brief. We are going to implement BC and I am learning from your vdo's.

[@gomstechtalks]

Glad to hear. All the best.

[@تكنوإدراك]

Your explanation was very helpful. Thank you!

[@gomstechtalks]

Thank you for your kind words! I'm happy to know that my explanation was helpful to you.

[@JouhaynaZeidan]

hi Dr., how can i reach the blog and the documents you talked about in video . thank you very valuable video.

[@gomstechtalks]

Please check here www.learnwithgoms.com/2022/09/designing-report-from-multiple-tables.html

[@JouhaynaZeidan]

thank you @@gomstechtalks

[@pthapa55]

My company is migrating data to BC and I am learning from your videos. 1. Can you please share how the object explorer is setup? 2. A query is similar to stored procedure in SQL? 3. When a query is created, can I use that query to run in Power BI to get the data? how can I achieve that? Thank you.

[@gomstechtalks]

Object Explorer in Microsoft Business Central Setup: In Microsoft Business Central, the Object Explorer is typically organized in a tree structure on the left-hand side of the development environment. It allows users to navigate through various objects such as tables, pages, reports, codeunits, queries, and more. These objects are organized based on their respective functionalities and relationships. You can typically find the Object Explorer within the development environment of Business Central, where developers and administrators can manage and customize the application. Query vs. Stored Procedure: In SQL, a stored procedure is a precompiled collection of SQL statements and procedural logic that is stored in the database and can be executed later. It is typically used for repetitive tasks, complex operations, or to encapsulate business logic. A query, on the other hand, is a single SQL statement that retrieves data from a database based on specified criteria. While a query can be similar to a stored procedure in the sense that it retrieves data from a database, it is typically simpler and does not include procedural logic. Queries are often used for ad-hoc data retrieval or as the basis for reports. Using Query in Power BI: Yes, you can use a query created in Microsoft Business Central in Power BI to retrieve data. Here's how you can achieve that: a. Open Power BI Desktop. b. Navigate to the "Home" tab. c. Click on "Get Data." d. Choose "OData Feed" or "Web" depending on how you expose your Business Central data. e. Enter the URL of your Business Central instance, along with the query string if you have specific filters or conditions. f. Power BI will connect to your Business Central instance and retrieve the data based on the query you provided. g. You can then transform the data, create visualizations, and build reports within Power BI using the retrieved data. Make sure you have proper permissions and access to the data in Business Central that you intend to use in Power BI. Additionally, ensure that the query you're using in Power BI is properly formatted and retrieves the desired data from Business Central.

[@pthapa55]

I really appreciate your detail explanations, makes so much clear now. Thank you very much.@@gomstechtalks

[@shamsheerbashashaik6480]

Nice explanation

[@gomstechtalks]

Thank you

[@mhamed1197]

Hello Dr.Gomathi, First I want to thank you for sharing your precious knowledge with us. I'm a BI Intern and your videos are helping me A LOT. I have a question conserning queries and AZ Wizard though. When creating a query with AZ Wizard, the query's columns names are automatically chosen by removing spaces between words of the source table's columns names. Is there a way to change that ? See, I have many columns in my queries, and my superior wants the words in the columns names to be separated by underscores. Any help would be appreciated ! Thank you

[@gomstechtalks]

Thank you so much for your kind words and I'm glad to hear that my videos have been helpful to you as a BI Intern. I understand your question regarding the column names in queries created with AZ Wizard. By default, AZ Wizard removes spaces between words in the source table's column names when generating a query. However, if you want to separate the words in column names with underscores instead, there is a way to achieve that. When creating a query with AZ Wizard, you can manually modify the column names in the query after it has been generated. Here's how you can do it: After generating the query with AZ Wizard, review the column names that have been automatically chosen. Identify the columns whose names you want to modify by separating the words with underscores. Rename the columns in the query by adding underscores between the words. For example, if you have a column named "Customer Name" and you want it to be "Customer_Name," you can edit the query and replace the column name accordingly. Repeat this process for all the columns you want to modify in the query. By manually editing the query, you can customize the column names to meet your superior's requirements. Just make sure to review and test the modified query to ensure it functions correctly with the updated column names. I hope this helps! If you have any further questions or need additional assistance, feel free to ask. Good luck with your BI internship! Regards, Dr. Gomathi

[@mhamed1197]

@@gomstechtalks Hello again Dr.Gomathi, Thank you for your answer and your kindness. I'm sorry to bother you again with this, but I didn't get the answer I expected because of lack of details in my question. I actually know that it is possible to manually modify the column names when creating a query with AZ Wizard, and it is going to be my last-resort solution if I can't find a proper one. Indeed, I'm actually searching for a solution that would automatically modify the column names as I want them to be (separated by underscores), something like a function or a trigger when creating a query, or maybe a settings option to modify somewhere. Does a solution like that exist ? I think that doing it manually would lessen the convenient side of creating a query with AZ Wizard, and I want to avoid that. Thank you again, Mhamed

[@UmarFarooq-lc3on]

Hi Gomathi Thanks for shearing such an amazing stuff. I am getting following error, whey trying to open the Query. "The metadata object Table 50106 was not found for the compiler with emit version 22005."

[@gomstechtalks]

Hi, Glad the videos are useful. Please check whether you have created the table 50106.

[@UmarFarooq-lc3on]

@@gomstechtalks no new table was created in that task/practice, actually with Id 50106 query object was created and retrieved customer data.

[@boota4474]

where you specify your launching id in launch.json there is a object type change it to query as you are tyring to launch a query not a table compoler is looking for a table

[@UmarFarooq-lc3on]

@@boota4474 Type was already set to query like "startupObjectType": "Query"

[@perwejhussain5583]

Facing the same problem... I was following the same step as in the video but getting same error... After changing to 22 and page I received Hello in bc... Even after adding querycatogary it didn't appear in query list

[@quintero177]

Hello, it compiles well and loads the page, but when I look for it by the tell it doesn't appear and it doesn't appear in the QueryCategory

[@gomstechtalks]

Yes. Some changes in the property.

[@rakshitkumar1703]

Hello Mam , Actually i also create the same query but after publishing it showing the metadata object Page 50100 was not found.

[@gomstechtalks]

You are trying to execute an object 50100 which is not created. Please check the launch.json

[@chirangaranasinghe19]

How you learn everything like this? I wanna be perfect like you.

[@gomstechtalks]

Thank you. You too will learn. 👍🏻

[@purushottamsharma4033]

Thanks a lot

[@gomstechtalks]

Most welcome

[@ivanpolanco2585]

Hello and good morning. I wanted to mention that when I was creating the customers. It gives me an error when I test in business central. The customer section. Where this gives me the following error: Something went wrong. An error has occurred Date and time: Tue, 21 Mar 2023 13:32:03 GMT Azure AD tenant ID: common Could you tell me or someone here how to solve this problem?

[@gomstechtalks]

This could be the error when Business Central is busy or due to server issues. Please try again later or contact your system admin.

[@salamimaruff5602]

Hi Mam, please can you post video for report

[@gomstechtalks]

Please check here kzbin.info/www/bejne/Z3jHq6Ckjqt8gJo Let me know if you have any other queries

[@salamimaruff5602]

@@gomstechtalks Thanks ma, I do really appreciate.

[@OmoolaOlayinka]

Are you available for a quick chat to explore strategies that can enhance your Udemy course success?

[@gomstechtalks]

Please tell me

[@ScrollwithSpyder]

hello mam I created a query and tried to attach it to my list using that [ Query Category = 'Employee List ' ] but it no showing in the dropdown of ALL filter 26:16

[@gomstechtalks]

There should not be any space between query and the category I hope there is an Employee list. Use caption. Let me know if you get any errors.

[@ScrollwithSpyder]

@@gomstechtalks yeah in Code I written without spaces and I had created a custom table and page AS Employee List am following the same steps as in the video Thanks..

[@gomstechtalks]

Great please save, make sure the changes are applied and execute the code.

[@taejung4006]

Hello mam, I want to learn business central as a developer, can you be a trainer for me , I will pay the money.. Thanks in advance

[@gomstechtalks]

Hi, happy to hear that you wanna learn BC. Currently I am into projects. You can see the playlist from the beginning kzbin.info/aero/PL9FfW__Sm3pLgZkPec1oZs_EdjEt88fjn. I will be uploading the videos twice or thrice a week in this playlist. Please watch and let me know if you have any queries, I will surely help you. Regards, Dr. Gomathi

[@taejung4006]

@@gomstechtalks thanks for the reply, actually I saw all ur Microsoft dynamic videos, it's really amazing and I also learned some beginner level of, the thing is I want to deep dive into BC so if you are free please ping me asap. Thank you ✨

[@gomstechtalks]

Nice to hear that you have gone through the contents. Please share the topics which you wanted to learn. I will be creating content and post it. Not immediately but definitely will do that. 👍🏻

[@thilansuminda6253]

how to get grand total of query Customer 1 100 Customer 2 200 Total 300

[@gomstechtalks]

Step 1: Create a new AL codeunit or open the existing codeunit where you want to implement the query. Step 2: Define a variable to store the grand total. Step 3: Initialize the variable to 0. This step is important to ensure proper calculation later. Step 4: Use the `CALCFIELDS` statement to calculate the sum of the 'Customer' field. Replace `'Customer'` with the actual field name in your table. Step 5: Calculate the grand total using the `SUM` function and store the result in the variable you defined earlier. Step 6: Display the grand total. You can use the `MESSAGE` statement to show the result in a message dialog, or you can use it in any other way you prefer. Step 7: Save your codeunit. Step 8: Run your codeunit in Business Central, and it will calculate and display the grand total. Remember to replace `'Customer'` with the actual field name in your table where you store the amounts.

[@funonitspeak7720]

how can we use blob field ?

[@gomstechtalks]

Yeah, we can use. Please check this reference link: learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/blob/blob-data-type

[@funonitspeak7720]

​@@gomstechtalks we want to use work description field in posted sales invoice query but it show error on work description, kindly guide how to use it if you know?