Hello! Thank you for your videos, they are amazing! I've got three questions about line 13 (Filter) 1. What exactly are you filtering here? 2. What's the purpose of writing a filter in this case? 3. What would be the output without the filter? Many thanks in advance for your answers
Thank you for your kind words about the videos. I'd be happy to answer your questions about the filter on line 13. 1. What exactly are you filtering here? Answer: On line 13, I haven't written the actual condition for filtering yet. It's just a placeholder to show that we can add a filter here when needed. Filters are used to narrow down the data retrieved from the underlying table based on specific conditions. 2. What's the purpose of writing a filter in this case if it's just a basic code? Answer: Even though it may seem like basic code, adding a filter can be useful to demonstrate that you have the option to include filtering in your queries. In real-world scenarios, filters play a crucial role in fetching specific and relevant data from large datasets. 3. What would be the output without the filter? Answer: Without the filter, the query would retrieve all the data from the underlying table without any restrictions. In other words, it would fetch all records available in the table. By adding filters, you can control which records are retrieved and work with a more focused set of data that meets certain conditions. Sample code filter(AmountFilter; "Amount") { // Filter expression for the "Amount" column // Include records where the "Amount" field is greater than myInt result := Rec."Amount" > myInt; } } var myInt: Integer; trigger OnBeforeOpen() begin // Set the value of myInt before opening the query myInt := 1000; end; Hope you understand the concept. If you want I can create a separate video for filters. Happy day ahead,
@@gomstechtalks Many thanks again for your explanation. Now I understood well how filter works. It would be great if you could explain sometime how to use the function assert(); I'm writing test code units in VS and, for example, I took me a long time to understand how asserterror() works and what its outputs really mean.
First, declare your variable in the query var TotalQuantity: Decimal; When defining your data item in the query elements, you can work with this variable dataitem(YourDataItem; "Your Table") { column(Quantity; Quantity) { } column(Remaining_Quantity; "Remaining Quantity") { } column(Total_Quantity; TotalQuantity) { Expression = Quantity + "Remaining Quantity"; } } If you need to perform additional calculations or manipulate the variable, you can also use triggers like OnAfterGetRecord. Try and let me know.
I am getting this error: The source of a Column or Filter must be a field defined on the table referenced by its parent DataItemALAL0345. Unable to resolve this. Please help me in this.
Please send the detailed explanation below as the priority DM adding your code and error. Or schedule a call with me topmate.io/drgomathi_gomstechtalks
Top Number Of Rows property not working in my BC application & in the RTC i can't get the ALL table in from which you are getting the query can you help me out?
Thank you ma'am for video... But i have one confusion.. After adding querycatogary, why you changed startupobjectid and startupobjectype from 50107 to 22 and query to page respectively in launch.Json
Thank you for watching the video! I'm glad you found it helpful. Regarding your confusion, the changes in startupobjectid and startupobjectype were made because, I used the Customer Table, and to view that I should open the Customer Page. The objectid for Customer pages is 22. And the object type is Page. Hope you understand.
Hi, I am very much new to the Business Central. I watched your video but doesn't make sense to me because I am trying to learn however my question is; 1. In SQL server, we use SQL query to retrieve data and we can do same to get data in power bi from SQL server, however in Business Central, how is that possible? 2. It sounded like query objects is similar to my question above but I am not clear how you got that query object designer. Can you please share from the scratch how and where we get the object designer. 3. What is the use/benefit of query object designer?, it seems so complicated to understand. I know lot of people will understand SQL query vs this one. Can you please put some light to it. 4. My company is migrating data to Business Central and use power BI to build reports and I am trying to learn how those things will integrate with each other. Thank you for your time.
Hi, 1. Retrieving Data from Business Central for Power BI Unlike SQL Server, where you directly run SQL queries to retrieve data, Dynamics 365 Business Central provides APIs and web services for data access, ensuring secure and standardized data retrieval. Power BI connects to Business Central through an OData feed or using the Business Central connector available in Power BI, enabling you to fetch data without writing SQL queries. Steps to Connect Power BI to Business Central: In Power BI, select Get Data. Choose Online Services and find Dynamics 365 Business Central. Follow the prompts to connect, using your Business Central credentials. Once connected, you can select the tables and fields you want to import into Power BI for your reports. 2. Accessing the Query Object Designer in Business Central Query objects in Business Central allow you to specify a set of tables and fields from which to retrieve data, similar to a SQL query but using Business Central's design environment. To access the Query Designer: You'll need access to the Business Central Development Environment (also known as C/SIDE in older versions) or the AL Language extension for Visual Studio Code for newer versions. The approach depends on your Business Central version (on-premises vs. cloud, and the specific version). In the AL Development Environment (Visual Studio Code with the AL Language extension): Create a new AL project. Add a new file with an .al extension. Start typing query and use the snippet to create a new query object. This is where you define your data structure. Queries are primarily used in development scenarios for creating complex data structures that can be consumed in reports, APIs, or integrations. 3. Benefits of the Query Object Designer The Query Object Designer in Business Central is a powerful tool for several reasons: Efficiency: Queries allow efficient data retrieval by letting you specify exactly which tables and fields you need data from, and how they should be joined. Performance: They can be optimized for performance, reducing the load on your database compared to fetching entire tables. Complex Data Structures: Queries can combine data from multiple tables, allowing you to create complex views of your data that would be difficult to achieve through direct table access. Integration: Designed to facilitate data access for external applications, web services, and reporting tools like Power BI, making data export and reporting easier. 4. Integrating Business Central with Power BI for Reporting Integrating Business Central with Power BI involves: Using the Power BI Business Central Connector: This is the simplest way to get started. Power BI's connector for Business Central simplifies the process of importing your Business Central data into Power BI for analysis and report creation. Custom Queries and APIs: For more complex reporting needs, you might use custom queries through Business Central's API. This requires some development work to set up but offers more flexibility in data retrieval.
Yeah, it's deprecated it seems. Please write a query and use it in the report. You can check here for more details ruclips.net/video/T8PC6i9LXgU/видео.html
@@gomstechtalks For me is also not working. All records are shown. In both versions same result. DE Business Central 20.0 (Platform 20.0.37114.38150 + Application 20.0.37253.38230) and DE Business Central 22.0 (Plattform 22.0.56306.0 + Anwendung 22.0.54157.54644)
Very useful. Learning ....
Simply Great !
Glad you found it useful. 👍
Step by step explanation, Thanks
Glad. Thank you
Hello,,Thank you for your content on Business Central.Where can I get the best resources for business central,kindly help
Microsoft documentation. May I know what kind of content you are expecting?
content for a technical consultant.
Great Work!!
Thank you.
Great Job dear Gom
Thank you.
Hello! Thank you for your videos, they are amazing! I've got three questions about line 13 (Filter)
1. What exactly are you filtering here?
2. What's the purpose of writing a filter in this case?
3. What would be the output without the filter?
Many thanks in advance for your answers
Thank you for your kind words about the videos.
I'd be happy to answer your questions about the filter on line 13.
1. What exactly are you filtering here?
Answer: On line 13, I haven't written the actual condition for filtering yet. It's just a placeholder to show that we can add a filter here when needed. Filters are used to narrow down the data retrieved from the underlying table based on specific conditions.
2. What's the purpose of writing a filter in this case if it's just a basic code?
Answer: Even though it may seem like basic code, adding a filter can be useful to demonstrate that you have the option to include filtering in your queries. In real-world scenarios, filters play a crucial role in fetching specific and relevant data from large datasets.
3. What would be the output without the filter?
Answer: Without the filter, the query would retrieve all the data from the underlying table without any restrictions. In other words, it would fetch all records available in the table. By adding filters, you can control which records are retrieved and work with a more focused set of data that meets certain conditions.
Sample code
filter(AmountFilter; "Amount")
{
// Filter expression for the "Amount" column
// Include records where the "Amount" field is greater than myInt
result := Rec."Amount" > myInt;
}
}
var
myInt: Integer;
trigger OnBeforeOpen()
begin
// Set the value of myInt before opening the query
myInt := 1000;
end;
Hope you understand the concept. If you want I can create a separate video for filters.
Happy day ahead,
@@gomstechtalks Many thanks again for your explanation. Now I understood well how filter works. It would be great if you could explain sometime how to use the function assert(); I'm writing test code units in VS and, for example, I took me a long time to understand how asserterror() works and what its outputs really mean.
Sure. Planned the next one month videos. Surely will do it.
what to write in place of TopNumbeOfRows ?
It won't work directly in some versions
Thanks Doctor, How to add Group By, example I want the Top 50 Customer but want to group it by Customer Posting Group
Please check this and try to relate it to your expectation. ruclips.net/video/T8PC6i9LXgU/видео.html
@@gomstechtalks OK I will do thanks so much
Hi Mam,
I want to know how to add a dummy column in a query in al language to show the sum of two columns. Can you please help me in this?
First, declare your variable in the query
var
TotalQuantity: Decimal;
When defining your data item in the query elements, you can work with this variable
dataitem(YourDataItem; "Your Table")
{
column(Quantity; Quantity) { }
column(Remaining_Quantity; "Remaining Quantity") { }
column(Total_Quantity; TotalQuantity)
{
Expression = Quantity + "Remaining Quantity";
}
}
If you need to perform additional calculations or manipulate the variable, you can also use triggers like OnAfterGetRecord.
Try and let me know.
@@gomstechtalks Thank you Mam
I am getting this error: The source of a Column or Filter must be a field defined on the table referenced by its parent DataItemALAL0345. Unable to resolve this. Please help me in this.
Please send the detailed explanation below as the priority DM adding your code and error. Or schedule a call with me
topmate.io/drgomathi_gomstechtalks
@@gomstechtalks query 70005 CheckCreditLimit
{
QueryType = Normal;
Caption = 'Credit Limit Threshold Monitoring';
elements
{
dataitem(Customer; Customer)
{
column(No_; "No.") { Caption = 'Customer No.'; }
column(Name; Name) { Caption = 'Customer Name'; }
column(Credit_Limit__LCY_; "Credit Limit (LCY)") { Caption = 'Credit Limit'; }
column(Balance__LCY_; "Balance (LCY)") { caption = 'Sales completed'; }
column(TotalAmount; TotalAmount)
{
TotalAmount = "Balance (LCY)" + "Credit Limit (LCY)";
}
}
}
var
TotalAmount: Decimal;
}
The errors are:
[{
"resource": "/c:/Users/lavis/Documents/AL/UserStory/.vscode/CreditLimitQuery.al",
"owner": "_generated_diagnostic_collection_name_#0",
"code": {
"value": "AL0353",
"target": {
"$mid": 1,
"path": "/dynamics365/business-central/dev-itpro/developer/diagnostics/diagnostic-al353",
"scheme": "https",
"authority": "learn.microsoft.com",
"query": "wt.mc_id=d365bc_inproduct_alextension"
}
},
"severity": 8,
"message": "A Column must have a valid data source or have the 'Method' property set to 'Count'",
"source": "AL",
"startLineNumber": 19,
"startColumn": 20,
"endLineNumber": 19,
"endColumn": 31
},{
"resource": "/c:/Users/lavis/Documents/AL/UserStory/.vscode/CreditLimitQuery.al",
"owner": "_generated_diagnostic_collection_name_#0",
"code": {
"value": "AL0345",
"target": {
"$mid": 1,
"path": "/dynamics365/business-central/dev-itpro/developer/diagnostics/diagnostic-al345",
"scheme": "https",
"authority": "learn.microsoft.com",
"query": "wt.mc_id=d365bc_inproduct_alextension"
}
},
"severity": 8,
"message": "The source of a Column or Filter must be a field defined on the table referenced by its parent DataItem",
"source": "AL",
"startLineNumber": 19,
"startColumn": 33,
"endLineNumber": 19,
"endColumn": 44
},{
"resource": "/c:/Users/lavis/Documents/AL/UserStory/.vscode/CreditLimitQuery.al",
"owner": "_generated_diagnostic_collection_name_#0",
"code": {
"value": "AL0124",
"target": {
"$mid": 1,
"path": "/dynamics365/business-central/dev-itpro/developer/diagnostics/diagnostic-al124",
"scheme": "https",
"authority": "learn.microsoft.com",
"query": "wt.mc_id=d365bc_inproduct_alextension"
}
},
"severity": 8,
"message": "The property 'TotalAmount' cannot be used in this context",
"source": "AL",
"startLineNumber": 21,
"startColumn": 17,
"endLineNumber": 21,
"endColumn": 28
}]
Top Number Of Rows property not working in my BC application & in the RTC i can't get the ALL table in from which you are getting the query can you help me out?
Let me check and get back to you,
Thank you ma'am for video... But i have one confusion.. After adding querycatogary, why you changed startupobjectid and startupobjectype from 50107 to 22 and query to page respectively in launch.Json
Thank you for watching the video! I'm glad you found it helpful. Regarding your confusion, the changes in startupobjectid and startupobjectype were made because, I used the Customer Table, and to view that I should open the Customer Page. The objectid for Customer pages is 22. And the object type is Page. Hope you understand.
@@gomstechtalksThank you ma'am
Hi, I am very much new to the Business Central. I watched your video but doesn't make sense to me because I am trying to learn however my question is;
1. In SQL server, we use SQL query to retrieve data and we can do same to get data in power bi from SQL server, however in Business Central, how is that possible?
2. It sounded like query objects is similar to my question above but I am not clear how you got that query object designer. Can you please share from the scratch how and where we get the object designer.
3. What is the use/benefit of query object designer?, it seems so complicated to understand. I know lot of people will understand SQL query vs this one. Can you please put some light to it.
4. My company is migrating data to Business Central and use power BI to build reports and I am trying to learn how those things will integrate with each other.
Thank you for your time.
Hi,
1. Retrieving Data from Business Central for Power BI
Unlike SQL Server, where you directly run SQL queries to retrieve data, Dynamics 365 Business Central provides APIs and web services for data access, ensuring secure and standardized data retrieval. Power BI connects to Business Central through an OData feed or using the Business Central connector available in Power BI, enabling you to fetch data without writing SQL queries.
Steps to Connect Power BI to Business Central:
In Power BI, select Get Data.
Choose Online Services and find Dynamics 365 Business Central.
Follow the prompts to connect, using your Business Central credentials.
Once connected, you can select the tables and fields you want to import into Power BI for your reports.
2. Accessing the Query Object Designer in Business Central
Query objects in Business Central allow you to specify a set of tables and fields from which to retrieve data, similar to a SQL query but using Business Central's design environment. To access the Query Designer:
You'll need access to the Business Central Development Environment (also known as C/SIDE in older versions) or the AL Language extension for Visual Studio Code for newer versions. The approach depends on your Business Central version (on-premises vs. cloud, and the specific version).
In the AL Development Environment (Visual Studio Code with the AL Language extension):
Create a new AL project.
Add a new file with an .al extension.
Start typing query and use the snippet to create a new query object. This is where you define your data structure.
Queries are primarily used in development scenarios for creating complex data structures that can be consumed in reports, APIs, or integrations.
3. Benefits of the Query Object Designer
The Query Object Designer in Business Central is a powerful tool for several reasons:
Efficiency: Queries allow efficient data retrieval by letting you specify exactly which tables and fields you need data from, and how they should be joined.
Performance: They can be optimized for performance, reducing the load on your database compared to fetching entire tables.
Complex Data Structures: Queries can combine data from multiple tables, allowing you to create complex views of your data that would be difficult to achieve through direct table access.
Integration: Designed to facilitate data access for external applications, web services, and reporting tools like Power BI, making data export and reporting easier.
4. Integrating Business Central with Power BI for Reporting
Integrating Business Central with Power BI involves:
Using the Power BI Business Central Connector: This is the simplest way to get started. Power BI's connector for Business Central simplifies the process of importing your Business Central data into Power BI for analysis and report creation.
Custom Queries and APIs: For more complex reporting needs, you might use custom queries through Business Central's API. This requires some development work to set up but offers more flexibility in data retrieval.
Thank you so much for so much detail explanations@@gomstechtalks
TopNumberOfRows = 5 or 10 or 3; not work for me. all records show.
Yeah, it's deprecated it seems. Please write a query and use it in the report. You can check here for more details
ruclips.net/video/T8PC6i9LXgU/видео.html
@@gomstechtalks Thank you.
not work for me too also order by
You have to write the query.
While doing this i got error
The metadata object page was not found
Page view- top 5 customer query has to close
Please help me
Check whether the page is created. The error states that the Page object is not available.
Regards,
Dr. Gomathi
Please tell me how to do that??
TopNumberOfRows = 5; not work for me. all records show.
OK let's wait for the update
for me too it's not working😐
@@gomstechtalks
For me is also not working. All records are shown. In both versions same result.
DE Business Central 20.0 (Platform 20.0.37114.38150 + Application 20.0.37253.38230) and
DE Business Central 22.0 (Plattform 22.0.56306.0 + Anwendung 22.0.54157.54644)